In this article I will explain with an example, how to call
MySQL Stored Procedure with Parameters in ASP.Net using C# and VB.Net.
The article explains with a simple example where a
Stored Procedure will be passed with a parameter and it returns the matching records. In similar way one can pass multiple parameters.
Download and Install the MySQL Connector
You will need to download and install the
MySQL Connector in order to connect to the
MySQL database in ASP.Net.
Database
I have made use of the following table Customers with the schema as follows.
![Call MySql Stored Procedure with Parameters in ASP.Net, C# and VB.Net]()
I have already inserted few records in the table.
![Call MySql Stored Procedure with Parameters in ASP.Net, C# and VB.Net]()
Note: You can download the database table SQL by clicking the download link below.
Stored Procedure
The following
Stored Procedure accepts a parameter
custId Integer parameter and is matched with the
CustomerId field of the
Customers Table of
MySQL database.
DELIMETER //
CREATE PROCEDURE Customers_GetCustomer(IN custId INT)
BEGIN
SELECT Name
,Country
FROM Customers
WHERE CustomerId = custId;
END //
DELIMITER ;
HTML Markup
The HTML Markup consists of following controls:
TextBox – For capturing CustomerId.
Button – For searching records.
The Button has been assigned with an OnClick event handler.
GridView – For displaying data.
Columns
The GridView consists of two BoundField columns.
Search:<asp:TextBox ID="txtCustomerId" runat="server" />
<asp:Button ID="btnSearch" Text="Search" runat="server" OnClick="OnSearch" />
<hr />
<asp:GridView ID="gvCustomers" runat="server" AutoGenerateColumns="false">
<Columns>
<asp:BoundField DataField="Name" HeaderText="Name" ItemStyle-Width="150" />
<asp:BoundField DataField="Country" HeaderText="Country" ItemStyle-Width="150" />
</Columns>
</asp:GridView>
Namespaces
You will need to import the following namespaces.
C#
using System.Data;
using System.Configuration;
using MySql.Data.MySqlClient;
VB.Net
Imports System.Data
Imports System.Configuration
Imports MySql.Data.MySqlClient
Calling MySql Stored Procedure with Parameters in ASP.Net
When the Search button is clicked, a TextBox value is fetched and GetCustomer method is called where first the CustomerId is passed as a parameter.
Finally, the GridView is populated.
C#
protected void OnSearch(object sender, EventArgs e)
{
this.GetCustomer(int.Parse(txtCustomerId.Text.Trim()));
}
private void GetCustomer(int customerId)
{
string spName = "Customers_GetCustomer";
string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
using (MySqlConnection con = new MySqlConnection(constr))
{
using (MySqlCommand cmd = new MySqlCommand(spName, con))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@CustId", customerId);
using (MySqlDataAdapter sda = new MySqlDataAdapter(cmd))
{
using (DataTable dt = new DataTable())
{
sda.Fill(dt);
gvCustomers.DataSource = dt;
gvCustomers.DataBind();
}
}
}
}
}
VB.Net
Protected Sub Search(sender As Object, e As EventArgs)
Me.GetCustomer(Integer.Parse(txtCustomerId.Text.Trim()))
End Sub
Private Sub GetCustomer(customerId As Integer)
Dim spName As String = "Customers_GetCustomer"
Dim constr As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
Using con As New MySqlConnection(constr)
Using cmd As New MySqlCommand(spName, con)
cmd.CommandType = CommandType.StoredProcedure
cmd.Parameters.AddWithValue("@CustId", customerId)
Using sda As New MySqlDataAdapter(cmd)
Dim dt As New DataTable()
sda.Fill(dt)
gvCustomers.DataSource = dt
gvCustomers.DataBind()
End Using
End Using
End Using
End Sub
Screenshot
![Call MySql Stored Procedure with Parameters in ASP.Net, C# and VB.Net]()
Demo
Downloads
