In this article I will explain with an example, how to insert data into database using Stored Procedure in Three Tier Architecture in ASP.Net using C# and VB.Net.
ADO.Net will be used to perform insert operation in ASP.Net.
Note: The complete implementation of Three Tier Architecture using Stored Procedure is explained in my article Implement Three Tier Architecture using Stored Procedure in ASP.Net and it is required to be read first in order to understand this article.
 
 
Database
I have made use of the following table Customers with the schema as follows.
Insert data into database using Stored Procedure in Three Tier Architecture in ASP.Net
 
Note: You can download the database table SQL by clicking the download link below.
         Download SQL file
 
 
Stored Procedure
The following Stored Procedure will be used to Insert data into the SQL Server database table.
This Stored Procedure accepts Name and Country parameters, which are used to INSERT the records in Customers Table.
CREATE PROCEDURE [Customers_InsertCustomer]
      @Name VARCHAR(100),
      @Country VARCHAR(50)
AS
BEGIN
      INSERT INTO [Customers]
                  ([Name]
                  ,[Country])
      VALUES
                  (@Name
                  ,@Country)
 
      SELECT SCOPE_IDENTITY()
END
 
 
Implementing Insert Stored Procedure in Three Tier Architecture
The complete implementation of Three Tier Architecture using Stored Procedure is explained in my article Implement Three Tier Architecture using Stored Procedure in ASP.Net and it is required to be read first in order to understand this article.
 
Data Transfer Object (DTO)
The Customer class consists of the following properties.
///<summary>
/// Customer Data Transfer Object.
///</summary>
public class Customer
{
    public int CustomerId { get; set; }
    public string Name { get; set; }
    public string Country { get; set; }
}
 
 
Data Layer (CustomerDL class)
The CustomerDL class belongs to the Data Layer project and it consists of following method.
InsertCustomer
This method accepts the Customer class object as parameter and uses Customers_InsertCustomer Stored Procedure.
Inside this method, the parameters are passed as Dictionary object to the GetScalar method.
Finally, the GetScalar method returns the CustomerId as scalar value.
public class CustomerDL
{
    ///<summary>
    /// Insert Customer data.
    ///</summary>
    ///<param name="customer">Customer object.</param>
    ///<returns>CustomerId object.</returns>
    public static int InsertCustomer(Customer customer)
    {   
        string sql = "Customers_InsertCustomer";
 
        Dictionary<string, object> parameters = new Dictionary<string, object>();
        parameters.Add("@Name", customer.Name);
        parameters.Add("@Country", customer.Country);
        return Convert.ToInt32(sql.GetScalar(parameters, true));
    }
}
 
 
Business Layer (CustomerBL class)
The CustomerBL class belongs the Business Layer and it implements the InsertCustomer method which makes call to the InsertCustomer method of the Data Layer class.
public class CustomerBL : ICustomerBL
{
    ///<summary>
    /// Insert Customer data.
    ///</summary>
    ///<param name="customer">Customer object.</param>
    ///<returns>CustomerId object.</returns>
    public int InsertCustomer(Customer customer)
    {
        return CustomerDL.InsertCustomer(customer);
    }
}
 
 
Business Layer (ICustomerBL interface)
The interface implements the InsertCustomer method of the CustomerBL class.
public interface ICustomerBL
{
    ///<summary>
    /// Insert Customer data.
    ///</summary>
    ///<param name="customer">Customer object.</param>
    ///<returns>CustomerId object.</returns>
    int InsertCustomer(Customer customer);
}
 
 
Front End Layer
The Business Layer method will be called in the Front End layer.
 
HTML Markup
The HTML Markup consists of:
HTML Button – For opening the Bootstrap Modal Popup.
DIV – For Bootstrap Modal Popup contents.
TextBox – For inputting Name.
DropDownList – For selecting Country.
Button – For submitting the Form.
The Button has been assigned with OnClick event handler.
Inside the HTML Form the following Bootstrap CSS is inherited.
1. bootstrap.min.css
 
Then following jQuery and Bootstrap JS Scripts are inherited.
1. jquery-3.2.1.slim.min.js
2. bootstrap.min.js
<script src="https://code.jquery.com/jquery-3.2.1.slim.min.js"
    integrity="sha384-KJ3o2DKtIkvYIK3UENzmM7KCkRr/rE9/Qpg6aAZGJwFDMVNA/GpGFF93hXpG5KkN" crossorigin="anonymous"></script>
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/4.0.0/css/bootstrap.min.css"
    integrity="sha384-Gn5384xqQ1aoWXA+058RXPxPg6fy4IWvTNh0E263XmFcJlSAwiGgFAW/dAiS6JXm" crossorigin="anonymous" />
<scriptsrc="https://maxcdn.bootstrapcdn.com/bootstrap/4.0.0/js/bootstrap.min.js"
    integrity="sha384-JZR6Spejh4U02d8jOt6vLEHfe/JQGiRRSQQxSfFWpi1MquVdAyjUar5+76PVCmYl" crossorigin="anonymous"></script>
<button type="button" class="btn btn-primary" data-toggle="modal" data-target="#exampleModal">Insert</button>
<div id="exampleModal" class="modal" tabindex="-1" role="dialog">
    <div class="modal-dialog" role="document">
        <div class="modal-content">
            <div class="modal-header">
                <h5 class="modal-title">Customer Details Form</h5>
                <button type="button" class="close" data-dismiss="modal" aria-label="Close">
                    <span aria-hidden="true">&times;</span>
                </button>
            </div>
            <div class="modal-body">
                <div class="form-group">
                    <label>Name:</label>
                    <asp:TextBox runat="server" ID="txtName" CssClass="form-control" />
                </div>
                <div class="form-group">
                    <label>Country:</label>
                    <asp:DropDownList runat="server" ID="ddlCountries" CssClass="form-control">
                        <asp:ListItem Text="Please select" Value="" />
                        <asp:ListItem Text="India" Value="India" />
                        <asp:ListItem Text="China" Value="China" />
                        <asp:ListItem Text="Australia" Value="Australia" />
                        <asp:ListItem Text="France" Value="France" />
                        <asp:ListItem Text="Unites States" Value="Unites States" />
                        <asp:ListItem Text="Russia" Value="Russia" />
                        <asp:ListItem Text="Canada" Value="Canada" />
                    </asp:DropDownList>
                </div>
                <div class="modal-footer">
                    <asp:Button Text="Save changes" runat="server" CssClass="btn btn-primary" OnClick="OnInsert" />
                    <button type="button" class="btn btn-secondary" data-dismiss="modal">Close</button>
                </div>
            </div>
        </div>
    </div>
</div>
 
 
Namespaces
You will need to import the following namespaces.
C#
using DTO;
using Data_Layer;
 
VB.Net
Imports DTO
Imports Data_Layer
 
 
Button Click event
When Insert button is clicked, an object of Customer class is created and the Name and Country values fetched from the TextBox and DropDownList are set respectively.
Then, the InsertCustomer method is called using the ICustomerBL interface and the data of Customer is inserted into the database using Three Tier Architecture.
Finally, the CustomerId of the inserted record is displayed in JavaScript Alert Message Box using RegisterStartupScript method.
C#
protected void OnInsert(object sender, EventArgs e)
{
    Customer customer = new Customer
    {
        Name = txtName.Text,
        Country = ddlCountries.SelectedItem.Text
    };
    ICustomerBL customerBL = new CustomerBL();
 
    //Insert Customer data.
    int customerId = customerBL.InsertCustomer(customer);
    ClientScript.RegisterStartupScript(this.GetType(), "alert", "alert('Inserted Customer ID: " + customerId + "');", true);
}
 
VB.Net
Protected Sub OnInsert(ByVal sender As Object, ByVal e As EventArgs)
    Dim customer As Customer = New Customer With {
        .Name = txtName.Text,
        .Country = ddlCountries.SelectedItem.Text
    }
    Dim customerBL As ICustomerBL = New CustomerBL()
 
    'Insert Customer data.
    Dim customerId As Integer = customerBL.InsertCustomer(customer)
    ClientScript.RegisterStartupScript(Me.GetType(), "alert", "alert('Inserted Customer ID: " & customerId & "');", True)
End Sub
 
 
Screenshots
The Form
Insert data into database using Stored Procedure in Three Tier Architecture in ASP.Net
 
Record after Insert in database
Insert data into database using Stored Procedure in Three Tier Architecture in ASP.Net
 
 
Downloads
Download Code