I will explain with an example, how to perform LIKE search using Dapper library with Stored Procedure and SQL Server in ASP.Net using C# and VB.Net.
 
 

Database

Here I am making use of Microsoft’s Northwind Database. You can download it from here.
Download and install Northwind Database
 
 

Stored Procedure

The following Stored Procedure will be used to perform LIKE search.
The Stored Procedure accepts ContactName parameter which is used to perform a search in Customers Table.
CREATE PROCEDURE Customers_SearchCustomers
      @ContactName NVARCHAR(30)
AS
BEGIN
      SET NOCOUNT ON;
      SELECT TOP 10 *
      FROM Customers
      WHERE ContactName LIKE'%'+ @ContactName +'%'
END
 
 

HTML Markup

The HTML markup consists of following controls:
TextBox – For capturing text to be searched in Table.
Button – For searching records.
GridView – For displaying data.
Columns
The GridView consists of four BoundField columns.
Search Customer:
<asp:TextBox ID="txtSearch" runat="server"></asp:TextBox>
<asp:Button ID="btnSubmit" runat="server" Text="Submit" OnClick="OnSubmit" />
<hr />
<asp:GridView ID="gvCustomers" runat="server"AutoGenerateColumns="false">
    <Columns>
        <asp:BoundField DataField="CustomerID" HeaderText="Customer Id" />
        <asp:BoundField DataField="ContactName" HeaderText="Contact Name" />
        <asp:BoundField DataField="City" HeaderText="City" />
        <asp:BoundField DataField="Country" HeaderText="Country" />
    </Columns>
</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
 
 

Performing LIKE Search using Stored Procedure in C# and VB.Net

When the Submit button is clicked, first the connection is read 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, a connection to the database is established using the SqlConnection class.
A DynamicParameters class object is created and Add method is called where we pass the searched text as parameter.
After that, the name of theStored Procedure and an object of DynamicParameter class with CommandType as Stored Procedure are passed as parameter to ExecuteReader method and the records are fetched from the Customers Table.
Note: For more details on how to use ExecuteReader, please refer my article Using Dapper ExecuteReader in MySQL in C# and VB.Net.
 
Finally, the fetched records are copied to DataTable object using Load method and is assigned to the DataSource property of GridView and the GridView is populated.
C#
protected void OnSubmit(object sender, EventArgs e)
{
    string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
    string spName = "Customers_SearchCustomers";
    using (SqlConnection con = new SqlConnection(constr))
    {
        DynamicParameters dynamicParameters = new DynamicParameters();
        // Adding parameter.
        dynamicParameters.Add("@ContactName", txtSearch.Text.Trim());
        using (IDataReader sdr = con.ExecuteReader(spName, dynamicParameters, commandType: CommandType.StoredProcedure))
        {
            using (DataTable dtCustomers = new DataTable())
            {
                dtCustomers.Load(sdr);
                gvCustomers.DataSource = dtCustomers;
                gvCustomers.DataBind();
            }
        }
    }
}
 
VB.Net
Protected Sub OnSubmit(ByVal sender As Object, ByVal e As EventArgs)
    Dim constr As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
    Dim spName As String = "Customers_SearchCustomers"
    Using con As SqlConnection = New SqlConnection(constr)
        Dim dynamicParameters As DynamicParameters = New DynamicParameters()
        dynamicParameters.Add("@ContactName", txtSearch.Text.Trim())
        Using sdr As IDataReader = con.ExecuteReader(spName, dynamicParameters, 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
 
 

Screenshot

Perform Like Search using Dapper and SQL Server in ASP.Net
 
 

Demo

View Demo
 
 

Downloads

Download Code