In this article I will explain with an example, how to pass parameter to
Stored Procedure using
Dapper library in Windows Forms (WinForms) Application using C# and VB.Net.
Installing Dapper package using Nuget
Database
I have made use of the following table Customers with the schema as follows.
![Pass parameter to Stored Procedure using Dapper in C# and VB.Net]()
I have already inserted few records in the table.
![Pass parameter to Stored Procedure using Dapper in C# and VB.Net]()
Note: You can download the database table SQL by clicking the download link below.
Stored Procedure
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
Form Design
The Form consists of following controls:
Label – For labelling the controls.
ComboBox – For selecting Country name.
Button – For displaying records.
The Button has been assigned with a Click event handler.
DataGridView – For displaying data.
![Pass parameter to Stored Procedure using Dapper in C# and VB.Net]()
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
Pass Parameter with Stored Procedure using Dapper in C# and VB.Net
Inside the Form Load event handler, the ComboBox items are added.
When the Search button is clicked, the IDataReader class object is created where the ExecuteReader method is called.
Then, the 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.
Finally, DataTable is assigned to the DataSource property of DataGridView.
C#
private void Form1_Load(object sender, EventArgs e)
{
cbCountries.Items.Add("United States");
cbCountries.Items.Add("India");
cbCountries.Items.Add("France");
cbCountries.Items.Add("Russia");
}
private void OnSearch(object sender, EventArgs e)
{
string country = cbCountries.SelectedItem.ToString();
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);
dgvCustomers.DataSource = dtCustomers;
}
}
}
}
VB.Net
Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
cbCountries.Items.Add("United States")
cbCountries.Items.Add("India")
cbCountries.Items.Add("France")
cbCountries.Items.Add("Russia")
End Sub
Private Sub OnSearch(sender As Object, e As EventArgs) Handles btnSearch.Click
Dim country As String = cbCountries.SelectedValue.ToString()
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)
dgvCustomers.DataSource = dtCustomers
End Using
End Using
End Using
End Sub
Screenshot
![Pass parameter to Stored Procedure using Dapper in C# and VB.Net]()
Downloads
