Learn How to Use Stored Procedures with ASP.NET 4.0 and C#
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.
To begin, create a new ASP.NET Empty Web Site and:
- Right click the project in your solution explorer.
- Select add ASP.NET folder.
- Select the App_Data folder.
- Right click the App_Data folder.
- Select add existing item.
- 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:
- Open up your server explorer.
- Expand the stored procedures folder.
- Double click ‘CustOrderHist’.
You should now be viewing a stored procedure that looks like the following:
ALTER PROCEDURE CustOrderHist @CustomerID nchar(5)
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:
SqlCommand cmd = new SqlCommand("CustOrderHist", new SqlConnection("Data Source=.\\SQLEXPRESS;AttachDbFilename=|DataDirectory|\\NORTHWND.mdf;Integrated Security=True;User Instance=True"));
cmd.CommandType = CommandType.StoredProcedure;
GridView1.DataSource = cmd.ExecuteReader();
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#.