In this tutorial we will be demonstrating how to use a stored procedure with ASP.NET 4.0 and C#. For this particular example we will be using the Northwind sample database that will be available with the source of the project. The Northwind database is a great example because it already has plenty of data and stored procedures to select that data.

We migrated our web sites to Server Intellect over one weekend and the setup was so smooth that we were up and running right away. They assisted us with everything we needed to do for all of our applications. With Server Intellect’s help, we were able to avoid any headaches!

To begin, create a new ASP.NET Empty Web Site and:
  1. Right click the project in your solution explorer.
  2. Select add ASP.NET folder.
  3. Select the App_Data folder.
  4. Right click the App_Data folder.
  5. Select add existing item.
  6. Select NORTHWND.mdf.
Once we have added our database to the project, we can take a look at what is inside. To view the stored procedures of this database:
  1. Open up your server explorer.
  2. Expand the stored procedures folder.
  3. Double click ‘CustOrderHist’.
You should now be viewing a stored procedure that looks like the following:
Code Block
Ask a Question
Download Source
SQL
The CustOrderHist stored procedure.
ALTER PROCEDURE CustOrderHist @CustomerID nchar(5)
AS
SELECT ProductName, Total=SUM(Quantity)
FROM Products P, [Order Details] OD, Orders O, Customers C
WHERE C.CustomerID = @CustomerID
AND C.CustomerID = O.CustomerID AND O.OrderID = OD.OrderID AND OD.ProductID = P.ProductID
GROUP BY ProductName
This stored procedure will select all of the orders given a particular customer ID.

Next, we will want to add a new page to the project named ‘Default.aspx’. Add a grid view to the page leaving the default settings. Then in our code behind file, add the following code to the Page_Load event method:
Code Block
Ask a Question
Download Source
Default.aspx.cs
Our Page_Load event method.
SqlCommand cmd = new SqlCommand("CustOrderHist", new SqlConnection("Data Source=.\\SQLEXPRESS;AttachDbFilename=|DataDirectory|\\NORTHWND.mdf;Integrated Security=True;User Instance=True"));
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("CustomerID", "ALFKI");
 
using (cmd.Connection)
{
    cmd.Connection.Open();
 
    GridView1.DataSource = cmd.ExecuteReader();
    GridView1.DataBind();
}

We stand behind Server Intellect and their support team. They offer dedicated servers, and they are now offering cloud hosting! 

This code creates a new SQL command with a command string of ‘CustOrderHist’, our stored procedure, and a connection to our local Northwind database. Then, we pass in ‘ALFKI’ as the customer ID and bind the data we select to our grid view. Go ahead and load up the web site. Notice that some orders are being displayed in our grid view that were selected with our stored procedure. This demonstrates how you can easily call a stored procedure in a database using ASP.NET 4.0 and C#.
Using-Stored-Procs-asp4-cs.zip