In this article I will explain how to call MySql Stored Procedure with Parameters in ASP.Net, 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.
 
Using MySQL with ASP.Net
This portion is extensively covered in my article Use and connect to MySQL Database in ASP.Net Application using MySQLConnector.
 
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.
         Download SQL file
 
 
Stored Procedure
The following MySql Stored Procedure accepts a parameter custId Integer parameter and is matched with the CustomerId field of the Customers Table.
The matching record is returned by the Stored Procedure.
DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `Customers_GetCustomer`(IN custId INT)
BEGIN
       SELECT Name
              ,Country
       FROM Customers
       WHERE CustomerId = custId;
END$$
DELIMITER ;
 
 
HTML Markup
The HTML Markup consists of an ASP.Net TextBox, a Button and a GridView control.
Search:
<asp:TextBox ID="txtCustomerId" runat="server" Text="" />
<asp:Button Text="Search" runat="server" OnClick="Search" />
<hr />
<asp:GridView ID="GridView1" 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, C# and VB.Net
When the Search Button is clicked, it calls the GetCustomer function which accepts the CustomerId entered in the TextBox and passes it as parameter to the MySql Stored Procedure.
The records returned by the Stored Procedure are displayed in the GridView.
C#
protected void Search(object sender, EventArgs e)
{
    this.GetCustomer(int.Parse(txtCustomerId.Text.Trim()));
}
 
private void GetCustomer(int customerId)
{
    string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
    using (MySqlConnection con = new MySqlConnection(constr))
    {
        using (MySqlCommand cmd = new MySqlCommand("Customers_GetCustomer", con))
        {
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@CustId", customerId);
            using (MySqlDataAdapter sda = new MySqlDataAdapter(cmd))
            {
                DataTable dt = new DataTable();
                sda.Fill(dt);
                GridView1.DataSource = dt;
                GridView1.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 constr As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
    Using con As New MySqlConnection(constr)
        Using cmd As New MySqlCommand("Customers_GetCustomer", con)
            cmd.CommandType = CommandType.StoredProcedure
            cmd.Parameters.AddWithValue("@CustId", customerId)
            Using sda As New MySqlDataAdapter(cmd)
                Dim dt As New DataTable()
                sda.Fill(dt)
                GridView1.DataSource = dt
                GridView1.DataBind()
            End Using
        End Using
    End Using
End Sub
 
 
Screenshot
Call MySql Stored Procedure with Parameters in ASP.Net, C# and VB.Net
 
 
Demo
View Demo
 
Downloads
Download Code