Using T-SQL Stored Procedures with ASP.NET 2.0 - CREATE PROCEDURE T-SQL Statement
(Page 2 of 4 )
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;).
string connectionString = "Data Source=(local);Initial Catalog=Northwind;Integrated Security=True";
try
{
using (SqlConnection connection = new SqlConnection(connectionString))
{
string commandText = "GetBasicCustomerData";
SqlCommand command = new SqlCommand(commandText, connection);
command.CommandType = CommandType.StoredProcedure;
connection.Open();
using (SqlDataReader dataReader = command.ExecuteReader())
{
while (dataReader.Read())
{
Response.Write("<b>" + dataReader["CustomerID"] + "</b>" + ", " +
dataReader["CompanyName"] + ", " + dataReader["City"] +
"<br />");
}
}
}
}
catch (Exception ex)
{
Response.Write(ex.Message);
}
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.
Next: ALTER PROCEDURE T-SQL Statement >>
More ASP.NET Articles
More By Michael Youssef
