In this article I will explain with an example, how to pass parameter to 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.
Pass parameter to Stored Procedure using Dapper in ASP.Net
 
I have already inserted few records in the table.
Pass parameter to Stored Procedure using Dapper 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 is used to display the records from SQL Server database.
This Stored Procedure accepts Country as a parameter, which is used to display the records from Customers Table.
CREATE PROCEDURE [Customers_GetCustomersByCountry]
      @Country VARCHAR(50)
AS
BEGIN
    SET NOCOUNT ON;  
    SELECT [CustomerId]
          ,[Name]
          ,[Country]
    FROM [Customers]
    WHERE [Country] = @Country
END
 
 

HTML Markup

The HTML Markup consists of following controls:
DropDownList – For selecting Country name.
Button – For displaying the records.
The Button has been assigned with an OnClick event handler.
GridView – For displaying data.
<table>
    <tr>
        <td>Country:</td>
        <td>
            <asp:DropDownList ID="ddlCountries" runat="server">
                <asp:ListItem Text="Please select" Value=" "></asp:ListItem>
                <asp:ListItem Text="United States" Value="United States"></asp:ListItem>
                <asp:ListItem Text="India" Value="India"></asp:ListItem>
                <asp:ListItem Text="France" Value="France"></asp:ListItem>
                <asp:ListItem Text="Russia" Value="Russia"></asp:ListItem>
            </asp:DropDownList>
        </td>
    </tr>
    <tr>
        <td></td>
        <td><asp:Button ID="btnSearch" runat="server" Text="Search" OnClick="OnSearch" /></td>
    </tr>
</table>
<br />
<asp:GridView ID="gvCustomers" runat="server"></asp:GridView>
 
 

Namespaces

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

Passing Parameter with Stored Procedure using Dapper in ASP.Net

When the Search button is clicked, he name of the Stored Procedure and the Country value is passed to the ExecuteReader method of the Dapper library which then copied to DataTable object using Load method.
Note: For more details on how to use ExecuteReader method, please refer my article Understanding Dapper ExecuteReader in C# and VB.Net.
 
Then, a DataTable is loaded with the records from the IDataReader.
Finally, the DataTable is assigned to the DataSource property of GridView and GridView is populated and DropDownList is set to empty.
C#
protected void OnSearch(object sender, EventArgs e)
{
    string country = ddlCountries.SelectedValue;
    string spName = "Customers_GetCustomersByCountry";
    string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
    using (SqlConnection con = new SqlConnection(constr))
    {
        using (IDataReader sdr = con.ExecuteReader(spName, new { country }, commandType: CommandType.StoredProcedure))
        {
            using (DataTable dtCustomers = new DataTable())
            {
                dtCustomers.Load(sdr);
                gvCustomers.DataSource = dtCustomers;
                gvCustomers.DataBind();
            }
        }
    }
    ddlCountries.SelectedValue = string.Empty;
}
 
VB.Net
Protected Sub OnSearch(ByVal sender As Object, ByVal e As EventArgs)
    Dim country As String = ddlCountries.SelectedValue
    Dim spName As String = "Customers_GetCustomersByCountry"
    Dim constr As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
    Using con As SqlConnection = New SqlConnection(constr)
        Using sdr As IDataReader = con.ExecuteReader(spName, New With {country}, commandType:=CommandType.StoredProcedure)
            Using dtCustomers As DataTable = New DataTable()
                dtCustomers.Load(sdr)
                gvCustomers.DataSource = dtCustomers
                gvCustomers.DataBind()
            End Using
        End Using
    End Using
    ddlCountries.SelectedValue = String.Empty
End Sub
 
 

Screenshot

Pass parameter to Stored Procedure using Dapper in ASP.Net
 
 

Downloads

Download Code