In this article I will explain with an example, how to perform CRUD i.e. Create, Read, Update, Delete operation with Stored Procedure using Dapper library in ASP.Net using C# and VB.Net.
 
 

Installing Dapper package using Nuget

In order to install Dapper library using Nuget, please refer my article Install Dapper from Nuget in Visual Studio.
 
 

Database

I have made use of the following table Customers with the schema as follows.
ASP.Net Web Forms CRUD: Select Insert Edit Update and Delete with Stored Procedure using Dapper
 
I have already inserted few records in the table.
ASP.Net Web Forms CRUD: Select Insert Edit Update and Delete with Stored Procedure using Dapper  
 
Note: You can download the database table SQL by clicking the download link below.
         Download SQL file
 
 

Stored Procedure

SELECT

The following Stored Procedure is used to display the records from SQL Server database.
CREATE PROCEDURE [Customers_GetCustomers]
AS
BEGIN
    SET NOCOUNT ON;
    SELECT [CustomerId]
    ,[Name]
    ,[Country]
    FROM [Customers]
END
 

Insert

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)
 
    SELECTSCOPE_IDENTITY()
END
 

Update

The following Stored Procedure will be used to Update data into the SQL Server database table.
This Stored Procedure accepts CustomerId, Name and Country parameters, which are used to UPDATE the records in Customers Table.
CREATE PROCEDURE[dbo].[Customers_UpdateCustomer]
      @CustomerId INT
     ,@Name VARCHAR(100)
     ,@Country VARCHAR(50)
AS
BEGIN
    UPDATE Customers
    SET [Name]= @Name,
        [Country]= @Country
    WHERE [CustomerId]= @CustomerId
END
 

Delete

The following Stored Procedure will be used to Delete data from the SQL Server database table.
This Stored Procedure accepts CustomerId parameter, which is used to DELETE the records in Customers Table.
CREATE PROCEDURE [Customers_DeleteCustomer]
    @CustomerId INT
AS
BEGIN
    DELETE FROM [Customers]
    WHERE [CustomerId] = @CustomerId
END
 
 

HTML Markup

The HTML Markup consists of:
GridView – For displaying data.

Columns

GridView consists of two TemplateField columns and a CommandField column.
TemplateField – Name and Country fields are set in the TemplateField columns with ItemTemplate with a Label and EditItemTemplate with TextBox for displaying and editing data respectively.
 

Properties

DataKeyNames – For storing the unique key values such as Primary Key, ID fields, etc.
Note: For more details on DataKeys please refer my article DataKeyNames in GridView example in ASP.Net.
 
PageSize – For permitting maximum number of rows to be displayed per page.
AllowPaging – For enabling paging in the GridView control.
EmptyDataText – For displaying text when there is no data in the GridView.
 

Events

GridView has been assigned with the following event handlers i.e. OnRowDataBound, OnRowEditing, OnRowCancelingEdit, OnRowUpdating, OnPageIndexChanging and OnRowDeleting.
 
TextBox – For capturing records to be added.
Button – For adding records.
The Button has been assigned with an OnClick event handler.
<asp:GridView ID="gvCustomers" runat="server" AutoGenerateColumns="false" OnRowDataBound="OnRowDataBound"
    DataKeyNames="CustomerId" OnRowEditing="OnRowEditing" OnRowCancelingEdit="OnRowCancelingEdit"
    PageSize="3" AllowPaging="true" OnPageIndexChanging="OnPaging" OnRowUpdating="OnRowUpdating"
    OnRowDeleting="OnRowDeleting" EmptyDataText="No records has been added.">
    <Columns>
        <asp:TemplateField HeaderText="Name" ItemStyle-Width="150">
            <ItemTemplate>
                <asp:Label ID="lblName" runat="server" Text='<%# Eval("Name")%>'></asp:Label>
            </ItemTemplate>
            <EditItemTemplate>
                <asp:TextBox ID="txtName" runat="server" Text='<%# Eval("Name")%>' Width="140"></asp:TextBox>
            </EditItemTemplate>
        </asp:TemplateField>
        <asp:TemplateField HeaderText="Country" ItemStyle-Width="150">
            <ItemTemplate>
                <asp:Label ID="lblCountry" runat="server" Text='<%# Eval("Country")%>'></asp:Label>
            </ItemTemplate>
            <EditItemTemplate>
                <asp:TextBox ID="txtCountry" runat="server" Text='<%# Eval("Country")%>' Width="140"></asp:TextBox>
            </EditItemTemplate>
        </asp:TemplateField>
        <asp:CommandField ButtonType="Link" ShowEditButton="true" ShowDeleteButton="true" ItemStyle-Width="150" />
    </Columns>
</asp:GridView>
<table border="1" cellpadding="0" cellspacing="0" style="border-collapse: collapse">
    <tr>
        <td style="width: 150px">Name:<br />
            <asp:TextBox ID="txtName" runat="server" Width="140" />
        </td>
        <td style="width: 150px">Country:<br />
            <asp:TextBox ID="txtCountry" runat="server" Width="140" />
        </td>
        <td style="width: 150px">
            <asp:Button ID="btnAdd" runat="server" Text="Add" OnClick="Insert" />
        </td>
    </tr>
</table>
 
 

Namespaces

You will need to import the following namespaces.
C#
using Dapper;
using System.Data;
using System.Configuration;
using System.Data.SqlClient; 
 
VB.Net
Imports Dapper
Imports System.Data
Imports System.Configuration
Imports System.Data.SqlClient
 
 

Binding the GridView using Dapper

Inside the Page Load event handler, the BindGrid method is called.

BindGrid

Inside the BindGrid method, the records are fetched from the SQL Server database using ExecuteReader method of Dapper library and copied to DataTable object using Load method.
The ExecuteReader method accepts name of the Stored Procedure and CommandType set to Stored Procedure as parameter.
Note: For more details on how to use ExecuteReader, please refer my article Using Dapper ExecuteReader Example in ASP.Net with C# and VB.Net.
 
Finally, the GridView is populated.
C#
protected void Page_Load(object sender, EventArgs e)
{
    if (!this.IsPostBack)
    {
        this.BindGrid();
    }
}
 
private void BindGrid()
{
    string spName = "Customers_GetCustomers";
    string  constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
    using (SqlConnection con = new SqlConnection(constr))
    {
        using (IDataReader sdr = con.ExecuteReader(spName, commandType: CommandType.StoredProcedure))
        {
            using (DataTable dtCustomers = new DataTable())
            {
                dtCustomers.Load(sdr);
                gvCustomers.DataSource = dtCustomers;
                gvCustomers.DataBind();
            }
        }
    }
}
 
VB.Net
Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs) Handles Me.Load
    If Not Me.IsPostBack Then
        Me.BindGrid()
    End If
End Sub
 
Private Sub BindGrid()
    Dim spName As String = "Customers_GetCustomers"
    Dim constr As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
    Using con As SqlConnection =  New SqlConnection(constr)
        Using sdr As IDataReader = con.ExecuteReader(spName,commandType:= CommandType.StoredProcedure)
            Using dtCustomers As DataTable = New DataTable()
                dtCustomers.Load(sdr)
                gvCustomers.DataSource = dtCustomers
                gvCustomers.DataBind()
            End Using
        End Using
    End Using
End Sub
 
 

Inserting using Dapper in ASP.Net

When Insert button is clicked, first a connection string is fetched from Web.Config file.
Note: For more details on how to read connection string from Web.Config file, please refer my article Read or Write Connection Strings in Web.Config file using ASP.Net using C# and VB.Net.
 
Then, the ExecuteScalar method of Dapper library is used to insert data into the SQL Server database.
The ExecuteScalar method accepts name of the Stored Procedure and CommandType set to Stored Procedure as parameter.
Note: For more details on ExecuteScalar method, please refer my article Understanding Dapper ExecuteScalar in C# and VB.Net.
 
Finally, the GridView is populated with updated records.
C#
protected void Insert(object sender, EventArgs e)
{
    int customerId;
    string spName = "Customers_InsertCustomer";
    string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
    using (SqlConnection con = new SqlConnection(constr))
    {
        string name = txtName.Text;
        string country = txtCountry.Text;
        customerId = Convert.ToInt32(con.ExecuteScalar(spName, new { name, country }, commandType: CommandType.StoredProcedure));
    }
 
    txtName.Text = string.Empty;
    txtCountry.Text = string.Empty;
    this.BindGrid();
}
 
VB.Net
Protected Sub Insert(ByVal sender As Object, ByVal e As EventArgs)
    Dim customerId As Integer
    Dim spName As String = "Customers_InsertCustomer"
    Dim constr As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
    Using con As SqlConnection = New SqlConnection(constr)
        Dim name As String = txtName.Text
        Dim country As String = txtCountry.Text
        customerId  = Convert.ToInt32(con.ExecuteScalar(spName, New With {name, country
        }, commandType:= CommandType.StoredProcedure))
    End Using
 
    txtName.Text = String.Empty
    txtCountry.Text = String.Empty
    Me.BindGrid()
End Sub
 
 

Edit

When the Edit Button is clicked, the GridView’s OnRowEditing event handler is triggered, where the EditIndex of the GridView is updated with the Row Index of the GridView Row to be edited.
C#
protected void OnRowEditing(object sender, GridViewEditEventArgs e)
{
    gvCustomers.EditIndex = e.NewEditIndex;
    this.BindGrid();
}
 
VB.Net
Protected Sub OnRowEditing(ByVal sender As Object, ByVal e As GridViewEditEventArgs)
    gvCustomers.EditIndex = e.NewEditIndex
    Me.BindGrid()
End Sub
 
 

Updating using Dapper in ASP.Net

When Update button is clicked, first a connection string is fetched from Web.Config file.
Note: For more details on how to read connection string from Web.Config file please refer my article Read or Write Connection Strings in Web.Config file using ASP.Net using C# and VB.Net.
 
Then, the Execute method of Dapper library is used to update data into the SQL Server database.
The ExecuteScalar method accepts name of the Stored Procedure and CommandType set to Stored Procedure as parameter.
Note: For more details on Execute method, please refer my article Understanding Dapper Execute in C# and VB.Net.
 
Finally, the GridView is populated with updated records.
C#
protected void OnRowUpdating(object sender, GridViewUpdateEventArgs e)
{
    GridViewRow row = gvCustomers.Rows[e.RowIndex];
    int customerId = Convert.ToInt32(gvCustomers.DataKeys[e.RowIndex].Values[0]);
    string name = (row.FindControl("txtName")as TextBox).Text;
    string country = (row.FindControl("txtCountry")as TextBox).Text;
    string spName = "Customers_UpdateCustomer";
    string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
    using (SqlConnection con = new SqlConnection(constr))
    {
        int i = con.Execute(spName, new {customerId, name, country }, commandType:CommandType.StoredProcedure);
    }
    gvCustomers.EditIndex = -1;
    this.BindGrid();
}
 
VB.Net
Protected Sub OnRowUpdating(ByVal sender As Object, ByVal e As GridViewUpdateEventArgs)
    Dim row As GridViewRow = gvCustomers.Rows(e.RowIndex)
    Dim customerId As Integer = Convert.ToInt32(gvCustomers.DataKeys(e.RowIndex).Values(0))
    Dim name As String = (TryCast(row.FindControl("txtName"), TextBox)).Text
    Dim country As String = (TryCast(row.FindControl("txtCountry"), TextBox)).Text
    Dim spName As String = "Customers_UpdateCustomer"
    Dim constr As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
    Using con As SqlConnection = New SqlConnection(constr)
        Dim i As Integer = con.Execute(spName, New With {customerId, name, country
        }, commandType:= CommandType.StoredProcedure)
    End Using
 
    gvCustomers.EditIndex = -1
    Me.BindGrid()
End Sub
 
 

Cancel Edit

When the Cancel Button is clicked, the GridView’s OnRowCancelingEdit event handler is triggered and the EditIndex is set to -1 and the GridView is populated with data using the BindGrid method.
C#
protected void OnRowCancelingEdit(object sender, EventArgs e)
{
    gvCustomers.EditIndex = -1;
    this.BindGrid();
}
 
VB.Net
Protected Sub OnRowCancelingEdit(ByVal sender As Object, ByVal e As EventArgs)
    gvCustomers.EditIndex = -1
    Me.BindGrid()
End Sub
 
 

Deleting using Dapper in ASP.Net

When Delete button is clicked, first a connection string is fetched from Web.Config file.
Note: For more details on how to read connection string from Web.Config file please refer my article Read or Write Connection Strings in Web.Config file using ASP.Net using C# and VB.Net.
 
Then, the Execute method of Dapper library is used to delete data from the SQL Server database.
The Execute method accepts name of the Stored Procedure and CommandType set to Stored Procedure as parameter.
Note: For more details on Execute method, please refer my article Understanding Dapper Execute in C# and VB.Net.
 
Finally, BindGrid method is called.
C#
protected void OnRowDeleting(object sender, GridViewDeleteEventArgs e)
{
    int customerId = Convert.ToInt32(gvCustomers.DataKeys[e.RowIndex].Values[0]);
    string spName = "Customers_DeleteCustomer";
    string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
    using (SqlConnection con = new SqlConnection(constr))
    {
        int i = con.Execute(spName, new {customerId  }, commandType:CommandType.StoredProcedure);
    }
    this.BindGrid();
}
 
VB.Net
Protected Sub OnRowDeleting(ByVal sender As Object, ByVal e As GridViewDeleteEventArgs)
    Dim customerId As Integer = Convert.ToInt32(gvCustomers.DataKeys(e.RowIndex).Values(0))
    Dim spName As String = "Customers_DeleteCustomer"
    Dim constr As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
    Using con As SqlConnection = New SqlConnection(constr)
        Dim i As Integer = con.Execute(spName, New With {customerId
        }, commandType:= CommandType.StoredProcedure)
    End Using
 
    Me.BindGrid()
End Sub
 
 

Delete Confirmation Message

The delete operation will be confirmed using JavaScript Confirmation Box, thus inside the OnRowDataBound event handler, the Delete LinkButton is assigned with a JavaScript OnClick event handler.
C#
protected void OnRowDataBound(object sender, GridViewRowEventArgs e)
{
    if (e.Row.RowType == DataControlRowType.DataRow && e.Row.RowIndex != gvCustomers.EditIndex)
    {
        (e.Row.Cells[2].Controls[2]as LinkButton).Attributes["onclick"] = "return confirm('Do you want to delete this row?');";
    }
}
 
VB.Net
Protected Sub OnRowDataBound(ByVal sender As Object, ByVal e As GridViewRowEventArgs)
    If e.Row.RowType = DataControlRowType.DataRow AndAlso e.Row.RowIndex <> gvCustomers.EditIndex Then
        TryCast(e.Row.Cells(2).Controls(2), LinkButton).Attributes("onclick") = "return confirm('Do you want to delete this row?');"
    End If
End Sub
 
 

Paging

Inside the OnPageIndexChanging event handler, the PageIndex property of the GridView is updated with the new Page Number which was clicked.
Finally, the GridView is populated using the BindGrid method which in-turn displays the new GridView page.
C#
protected void OnPaging(object sender, GridViewPageEventArgs e)
{
    gvCustomers.PageIndex = e.NewPageIndex;
    this.BindGrid();
}
 
VB.Net
Protected Sub OnPaging(ByVal sender As Object, ByVal e As GridViewPageEventArgs)
    gvCustomers.PageIndex = e.NewPageIndex
    Me.BindGrid()
End Sub
 
 

Screenshot

ASP.Net Web Forms CRUD: Select Insert Edit Update and Delete with Stored Procedure using Dapper
 
 

Downloads

Download Code