HomeASP.NET Using T-SQL Stored Procedures with ASP.NET...
Using T-SQL Stored Procedures with ASP.NET 2.0
If you haven't used T-SQL stored procedures before with SQL Server and in your ASP.NET 2.0 pages then you need to read this article. Today I will answer questions like the following: what is a stored procedure? How can I create a stored procedure? How can I use it from within my ASP.NET 2.0 web pages? And how can I return an output parameter value to my ASP.NET web page?
Contributed by Michael Youssef Rating: / 35 October 17, 2007
A stored procedure is a script of T-SQL code that is stored in your database under a given name. The T-SQL code for the stored procedure may contain programming logic (using T-SQL IF and CASE statements for example) and SELECT, INSERT, UPDATE or DELETE statements as well. There are many benefits to using stored procedures instead of writing pure T-SQL code. As you have seen in our previous articles on ADO.NET we have used pure T-SQL code in our ASP.NET pages and we may have to use one of those T-SQL queries many times in different pages. What would happen if you want to change the query? You would modify all the pages searching for all the string values of that T-SQL code.
If you have a stored procedure then you would not do that, as we are going to see in our code examples. You simply use the name of the stored procedure as the command text and not the query itself. Thus, if you want to modify the query you will modify the stored procedure in the database and only once. I must say that this is not the case all the time because if you modified the stored procedure you may need to modify your ADO.NET code. For example you may add, or remove, SqlParameter objects from your SqlCommand object to match the modifications you have applied on the stored procedure.
One example of a change that you can do in the stored procedure code that doesn't require a modification in the ADO.NET code may be writing something like SELECT LastName, FirstName, Title from Employees instead of SELECT * From Employees, given that the Employees table contains only those three columns. Also you might change the programming logic of your stored procedure without changing the returning result set; thus we don't need to change any lines of our ADO.NET code.
One other benefit to using stored procedures instead of using direct T-SQL queries is that you don't give your users direct access to tables; instead you give them execution permissions on selected stored procedures, thus creating another layer of security. For example, you may prevent the user from selecting data from your Employees table but you give him the permission to execute a stored procedure named GetEmployeesRecords that selects the records from the Employees table.
So using stored procedures is very important for your development. Let's see how we can create T-SQL stored procedures. I say T-SQL stored procedures because with SQL Server 2005 and C# 2.0 you can write stored procedures in .NET managed code. In other words, you can write a stored procedure as a .NET class that uses the .NET Framework Class Library. I will discuss that in another article about using .NET managed stored procedures; for now, let's look at T-SQL stored procedures.
We create T-SQL stored procedure by using the CREATE PROCEDURE statement. To create a stored procedure you use the CREATE PROCEDURE keywords, followed by the name of the stored procedure, followed by the AS keyword. Now you can write the stored procedure's body. Also you may use the keyword PROC instead of PROCEDURE so you can write CREATE PROC.
Let's create a stored procedure called GetBasicCustomerData that returns data from the Customers table of the Northwind database. This procedure will be designed not to return all the columns; instead, it returns only basic information about the customers, namely CustomerID, CompanyName and City. To create this stored procedure you need to open SQL Server Management Studio and connect to your local server, then execute the following T-SQL code:
USE Northwind GO CREATE PROC GetBasicCustomerData AS SELECT CustomerID, CompanyName, City FROM Customers
It's as simple as this. The first line of code changes the current database to Northwind in order to create this stored procedure. Then we create the stored procedure by using the keywords CREATE PROC and give the name of the stored procedure followed by AS and then the T-SQL query. To use this stored procedure you need to execute it using the keyword EXECUTE (or its shortcut EXEC) as follows:
EXEC GetBasicCustomerData
You will get the resultset as shown next, with only the columns we have selected in the stored procedure's query.
Let's create a web page to execute the GetBasicCustomerData Stored Procedure. Start by creating a new website and add the following code to the Page_Load() event handler method of the Default.aspx.cs file. (Don't forget to reference the namespace of the SQL SERVER .NET Data Provider, using System.Data.SqlClient;).
Run the page and the result will be displayed as in the following screenshot.
As you can see, this code is similar to the code that we wrote before but with a couple of differences. First we used the name of the stored procedure we want to execute as a string value and passed it to the SqlCommand object's constructor instead of passing a T-SQL query. We also used the SqlCommand.CommandType property to tell the SqlCommand object that we want to execute a stored procedure instead of executing direct T-SQL statements like the SELECT statement for example. In that case we would use another value for the SqlCommand.CommandType property.
We did that by assigning the enumeration value CommandType.StoredProcedure to the SqlCommand.CommandType property. Note that the default value for this property is CommandType.Text which is used when executing direct T-SQL statements. We wrote the fields to the web page using a SqlDataReader object which provides a read-only forward-only access behavior to the database. We used the Response.Write() method to write the fields to the output stream.
Now let's do one more thing. We will modify the code to access the fields by index and not by name. Before that, however, let's change the code of the stored procedure using the ALTER PROCEDURE statement.
You can modify your stored procedure's code by using the ALTER PROCEDURE T-SQL statement. You need to specify the name of the stored procedure you want to ALTER along with the stored procedure's body. Let's modify our GetBasicCustomerData to include more columns. Run the following code in SQL Server Management Studio with the Northwind database as the current database:
ALTER PROCEDURE GetBasicCustomerData AS SELECT CustomerID, CompanyName, ContactName, City, Country FROM Customers
Of course you need to change your code to include the additional columns indicated and write their fields to the page. The following is the modified version of the Page_Load() event handler method that runs with any number of columns returned from the stored procedure. Replace the previous event handler method with the following:
Now run the page and you will get all the fields returned from the stored procedure.
What we have done in this example is access the fields using their column index instead of their column name. And because we can know how many columns have been retrieved in the result set from the database, through the SqlDataReader.FieldCount property, we can use a for loop to access every field and write its value to the web page. After the loop ends we can write a <br /> before the end of this loop iteration. The if statement inside the for loop tests to see if this field is the last one in the row or not; if it's the last field then it doesn't write the colon character to the page.
Now we can alter the stored procedure one more time to include the ContactTitle column as in the following code:
ALTER PROCEDURE GetBasicCustomerData AS SELECT CustomerID, CompanyName, ContactName, ContactTitle, City, Country FROM Customers
When you run the page, without any modifications to the code, you will see the new column values written to the page as in the following screenshot.
You can create more complicated stored procedures using both input parameters and output parameters, so let's see an example.
Stored procedures are similar to C# methods in that they define parameters; when you call them you pass values to those parameters. Stored procedures also pass values back to your code in the form of output parameters. One of the common uses of output parameters is with tables that are based on identity columns. In other words, you use an output parameter with a stored procedure that inserts a new row into a table, like the Northwind's Employees table, to return to the user (of your stored procedure) the new identity value for the EmployeeID column.
We will see how you can get the value of the OUTPUT parameter. This makes sense because you can't enter values for identity columns and, at the same time, you need to tell the user about the new identity value inserted into the table for this row. You normally will need this identity value for other operations in your code. Let's create a stored procedure called InsertEmployee that inserts a new row into the Employees table. The following is the stored procedure's code, which you need to execute in SQL Server Management Studio with Northwind as the current database.
CREATE PROCEDURE InsertEmployee (@LastName NVARCHAR(20), @FirstName NVARCHAR(10), @Title NVARCHAR(30), @City NVARCHAR(15), @Country NVARCHAR(15), @EmployeeID INT OUTPUT) AS INSERT INTO Employees(LastName,FirstName,Title,City,Country) VALUES(@LastName,@FirstName,@Title,@City,@Country) SET @EmployeeID = SCOPE_IDENTITY()
As you can see, the syntax for using input parameters with stored procedures is very easy. You simply define each parameter by preceding its name with the @ sign and define the data type for the parameter; you also separate the parameters using the colon. Note that we use one output parameter, which is the @EmployeeID, and we do that by using the keyword OUTPUT after the parameter name and its data type.
In the stored procedure's body we have used the INSERT statement to insert a new row based on the parameters passed to the stored procedure. Note how we assign the output parameter's value of the Stored Procedure. The SCOPE_IDENTITY() function is used to get the last IDENTITY value inserted at a column defined as IDENTITY in the current scope, and the scope in our case is the stored procedure. We simply assign the return value of this function to the output parameter @EmployeeID. Let's see how we can execute such a stored procedure.
We declare a variable to hold the output parameter's value and use the EXECUTE keyword to execute the stored procedure. Note how we pass the values of the input parameters, simply by using the syntax parameter-name = value. Also note that we have used the keyword OUTPUT when passing the variable that will hold the output parameter's value, after the stored procedure is executed, so the syntax is outout-parameter-name = variable-name OUTPUT. Then we print the new IDENTITY value using the SELECT statement.
Executing the stored procedure from an ASP.NET page is a different issue. Let's see how we can do it. We will use SqlParameter objects and the SqlCommand.ExecuteNonQuery() method to do that. The following is the code of the Default.aspx, which contains the text boxes and the button that executes the code.
When you run the page and enter values for the text boxes you will get a similar result to the one shown in the next screenshot.
I think by now you must be familiar with the SqlCommand and SqlParameter objects. In this event handler we have used the shortcut method AddWithValue() which accepts the name of the parameter, as a string value, and its value. Then it creates a SQLParameter object using this information and adds it to the Parameters collection of this command. we have passed the values that the user entered in the text boxes as values for those parameters. Note the code we have used to define the output parameter and get its value.
We created a SqlParameter object using the default constructor and set the parameter name using the SqlParameter.ParameterName property. Then we used the enumeration value ParameterDirection.Output as a value to the SqlParameter.Direction property to tell the command that this is an OUTPUT parameter that needs to be manipulated differently from input parameters; the default value for this property is ParameterDirection.Input. You must assign a data type for the output parameter or the command will not be executed.
We know that the @EmployeeID parameter is created as an INT data type on the server, so we used the enumeration value DbType.Int32 as the value for the SqlParameter.DbType property. We then added the output parameter to the SqlCommand.Parameters collection using the Add() method.
After calling the SqlCommand.ExecuteNonQuery() method the value of the output parameter is set and you can access it using the SqlCommand.Parameters[parametername].Value syntax as we did in the example.
ASP.NET 
Using the SQLCommand Object
