Using Stored Procedures with MSSQL and ASP.NET 2.0

Introduction

If you haven't used MSSQL stored procedures before with SQL Server and in your ASP.NET 2.0 pages then you need to read this article. In this article I will list down basic 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?

Benefits of Using Stored Procedures Compare to SQL Code.

A stored procedure is a script of SQL code that is stored in your database under a given name. The 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  using stored procedures instead of writing pure SQL code.
1. Reduce Network Usage between WebServer and Database Server
2. Code Reuse.
3. Faster in performance because they are precompiled and executed quickly
4. Enhance Security as you can only give your user execute permission on your stored procedures but 
    not to the underlying table.

Coding 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 GetCustomerData 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.

1. Create Stored Procedures
To create this stored procedure you need to open SQL Server Management Studio or Query analyzer and connect to your database server, then execute the following SQL code:

USE Northwind GO
CREATE PROC GetCustomerData
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 SQL query. To use this stored procedure you need to execute it using the keyword EXECUTE (or its shortcut EXEC) as follows:

EXEC GetCustomerData

If you try to run the statement above, you can see the result will be return just like how you execute the select statement, but rather than have to write the full select statement, you can just write exec the stored proc name. It saves you typing time and also code reuse. You can use this stored proc anytime without have to type it all over again. It is precompiled and saved into database.

And also imagine, if you try to run this code on your web server,and if you used to type the inline sql in your code, then you are advised to migrate all your inline sql into stored proc. It will save your network usage between the web server and database server. This is because you only send "Exec Stored proc text" from your web server to database server, rather than have to send the whole queries across the network. It will greatly enhance your web site performance.

2. Editing Your Stored Procedures
To Edit your Current Stored Procedures on your Database, you can run ALTER Statement. Below is the sample SQL Code.

USE Northwind GO
ALTER PROC GetCustomerData
AS
SELECT CustomerID, CompanyName, City FROM Customers

Sample Code in ASP.NET

Now Let's start create a web page to execute the GetCustomerData 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.

protected void Page_Load(object sender, EventArgs e) {
    string connectionString = "Data Source=(local);Initial    Catalog=Northwind;Integrated Security=True";
    SqlConnection connection= null;
    try
    {
        connection  = new SqlConnection(connectionString);
        string commandText = "GetCustomerData";
        SqlCommand command = new SqlCommand(commandText, connection);
        command.CommandType = CommandType.StoredProcedure;
        connection.Open();
	
        SqlDataReader dataReader = command.ExecuteReader();
        while (dataReader.Read())
        {
            Response.Write(" <B>" + dataReader["CustomerID"] + " </B>" + ", " +
            dataReader["CompanyName"] + ", " + dataReader["City"] + " <BR>");
        }
        dataReader.Close();
 
     }
     catch (Exception ex)
     {
        Response.Write(ex.Message);
     }
     finally {
         connection.Close();
     }
}

protected void Page_Load(object sender, EventArgs e) {
   string connectionString = "Data Source=(local);InitialCatalog=Northwind;Integrated Security=True";
   SqlConnection connection;
   try
   {
       connection = new SqlConnection(connectionString);
       string commandText = "InsertEmployee";
       SqlCommand command = new SqlCommand(commandText, connection);
       command.CommandType = CommandType.StoredProcedure;
       command.Parameters.AddWithValue("@LastName","Douglas");
       command.Parameters.AddWithValue("@FirstName","James");
       command.Parameters.AddWithValue("@Title", "IT Support");
       command.Parameters.AddWithValue("@City", "Texas");
       command.Parameters.AddWithValue("@Country","USA");
    
       SqlParameter outParameter = new SqlParameter();
       outParameter.ParameterName = "@EmployeeID";
       outParameter.Direction = ParameterDirection.Output;
       outParameter.DbType = DbType.Int32;
       command.Parameters.Add(outParameter);
       connection.Open();

       int rowsAffected = command.ExecuteNonQuery();
       if (rowsAffected == 1)
       {
           Response.Write("1 rows have been affected with this operation <BR><BR>");
       }
       Response.Write("Employee ID = " + command.Parameters["@EmployeeID"].Value);
    }
    catch (Exception ex)
    {
        Response.Write(ex.Message);
    }
    finally {
        connection.Close();
    }
}