I’ve met quite a few folks who knew what stored procedures were but never actually had to write code to call one themselves. So this is just a quick run-down of what you need to do to call a stored procedure from your database. You’ll see it’s not so bad once you get into it.

What we are going to do is create a simple button that when clicked will call a stored procedure and return results based of a parameter that we supplied through a textbox.

We have some basic elements on an aspx page like this:

<asp:TextBox ID="testData" runat="server"></asp:TextBox>

<asp:Button runat="server" ID="Submit" Text="Submit" 
    OnClick="CallStoredProcedure" />

In your C# file you need to remember to add these references at the top:

using System.Data;
using System.Data.SqlClient;

That way we have access to the SQL tools we need to do this.

Let me just throw out the entirety of the code we will be using and then we will go over each element.

public void CallStoredProcedure(Object sender, EventArgs e)
 {
 // Connection String to Test Database
 String connectionString = "Data Source=localhost;Initial Catalog=Web_Test;UID=testUsername;Password=testPassword";

SqlConnection myConnection = new SqlConnection(connectionString);
 myConnection.Open();
 SqlCommand myProcedureTest = new SqlCommand("my_Stored_Procedure", myConnection);
 myProcedureTest.CommandType = CommandType.StoredProcedure;

 // Add user variables to Stored Procedure Execution
 myProcedureTest.Parameters.Add(new SqlParameter("@Test_Data", testData.Text));

// Execute the Stored Procedure
 SqlDataReader rdr = myProcedureTest.ExecuteReader();
 while (rdr.Read())
 {
    var returnedValue = rdr[0];
    // Do something with returnedValue
 }
}

 String connectionString = "Data Source=localhost;Initial Catalog=Web_Test;UID=testUsername;Password=testPassword";

So the first bit we are looking at is where we define our connection string to our database. These credentials will have to be provided to you if you are not in charge of your databases. Instead of localhost you might receive an IP address or a number of different ways to reach your database, followed by the credentials.

SqlConnection myConnection = new SqlConnection(connectionString);
 myConnection.Open();

The next section is where we create the SQL connection using the connection string we just created. It uses the credentials and information you provided to actually establish a connection to said database. The Open command establishes a connection to the database.

 SqlCommand myProcedureTest = new SqlCommand("my_Stored_Procedure", myConnection);
 myProcedureTest.CommandType = CommandType.StoredProcedure;

Here we are defining the SQL query we want to run and then tell it which database connection we want to use (in case we have more than one). We then clarify that what we are using as a SQL command is actually a stored procedure on the server.

 myProcedureTest.Parameters.Add(new SqlParameter("@Test_Data", testData.Text));

In this example, we are assuming that our Stored Procedure takes a single parameter which we pass in from our text box. In a real application, we would want to be more careful with blindly accepting user input but since this is just an example, we will just roll with it.

 SqlDataReader rdr = myProcedureTest.ExecuteReader();

This creates an object to actually “listen” for data being returned from the stored procedure after we execute everything.

 while (rdr.Read())
 {
    var returnedValue = rdr[0];
    // Do something with returnedValue
    myConnection.Close();
 }

Finally we do something with the returned data. This bit of code essentially says “while there is data to work with, do whatever code we put here and then close the connection to the database.” We are assuming that the stored procedure is only returning a single piece of information (or that we only care about the first piece) based off the “rdr[0]” code. You are responsible for knowing what to do with the returned data and use it how you see fit.

Obviously this tutorial wasn’t exhaustive on the topic of stored procedures by any stretch of the imagination but if you need to begin working with them, this should at least help you get on the ground running.

If you have any questions or comments, feel free to leave them below!