Working with T-SQL Stored Procedures using ADO.NET
In this series, I shall try to remove all kinds of confusion about calling stored procedures from within ADO.NET. I shall also explain how to write your own stored procedures and execute them from within ADO.NET. I start with the simplest and end with the most advanced way of executing the stored procedures.
A downloadable file for this article is available here.
Every programmer knows that stored procedures play a great role in developing multi-tier applications. Most of the experienced programmers work with stored procedures in a traditional fashion, and I agree that it really works.
But the technology has greatly improved from our traditional methods. Now we face several of the latest standards like XML, web services, external routines, and so on. I bet not every programmer uses all of them in developing their applications.
In this series, I wanted to start with the simplest traditional way of working with stored procedures. Gradually, I will add a few more series (all tying into one another) to work with most advanced trends in working with stored procedures.
But at this moment, this article focuses on the basics of stored procedures and how to call them from ADO.NET. I included the source code in the form of a single file (".zip" file). You need to extract the folders from the zip and configure the virtual directories on your own.
For the sake of this demonstration, I created two tables, "emp" and "dept," within the "Northwind" database. The "emp" table has been created with the following column structure:
Empno (int) Ename (varchar) Sal (float) Deptno (int)
Similarly, I have the "dept" table with the following structure:
Deptno (int) Dname (varchar) Location (varchar)
I ask that you create those two tables, as all of the stored procedures in this article are heavily dependent on the above two tables.
Before going into the ADO.NET code, we need to create a simple stored procedure in SQL Server. Using the "Query Analyzer", execute the following script in the "Northwind" database.
CREATE PROCEDURE dbo.sp_IncrementSalaries
AS
UPDATE emp SET sal = sal + 500
RETURN
The above stored procedure shall simply increase all the salaries of the employees by 500. The stored procedure is named "sp_IncrementSalaries." Now, we need to have ADO.NET access the same thing in ASP.NET.
The following are the steps you need to take to execute a simple stored procedure with ADO.NET:
Create and open a SQL Server connection (using a "SQLConnection" object).
Create a SQL Server command (using a "SQLCommand" object).
Specify the properties to the "SQLCommand" object.
Assign the "SQLConnection" object to the "SQLCommand" object.
Execute the stored procedure using the "ExecuteNonQuery" method.
Close the "SQLConnection" and release all memory resources.
We will just follow the above steps. Start by creating your own web application using Visual Studio.NET (I named mine "SP" for this demonstration). Design your web form (call it "SimpleStoredProcedure") with a single button named "Execute Simple SP."
After designing the form, switch to the code and add the following line at the top.
Imports System.Data.SqlClient
I am importing the namespace "System.Data.SqlClient," as I would like to connect to the Microsoft SQL Server database natively. You can also use other .NET providers based on your requirements. Add the following code to your "Execute Simple SP" button:
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim cn As New SqlConnection("Data Source=.;initial catalog=Northwind;user id=sa")
Dim cmd As New SqlCommand
With cmd
.CommandType = CommandType.StoredProcedure
.CommandText = "sp_IncrementSalaries"
.Connection = cn
.Connection.Open()
.ExecuteNonQuery()
.Connection.Close()
.Dispose()
End With
End Sub
Once you complete the above, you execute your application (by pressing F5) and click on the button "Execute Simple SP." Once it executes successfully, check your table "emp," which should get updated with new salaries (incremented by 500).
You can simplify the above code as follows:
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim cmd As New SqlCommand("sp_IncrementSalaries", New SqlConnection("Data Source=.;initial catalog=Northwind;user id=sa"))
With cmd
.CommandType = CommandType.StoredProcedure
.Connection.Open()
.ExecuteNonQuery()
.Connection.Close()
.Dispose()
End With
End Sub
In this article, I shall give preference to clarity (rather than simplicity). So, I shall proceed with the first method from now on.
Before going into the ADO.NET code, we need to create a stored procedure with a single parameter in SQL Server. Using "Query Analyzer," execute the following script in the "Northwind" database.
CREATE PROCEDURE dbo.sp_IncrementSalariesBy
(
@increment int
)
AS
UPDATE emp SET sal = sal + @increment
RETURN
The above stored procedure shall simply increase all salaries of the employees by the value (or parameter) provided by the calling program. The stored procedure is named "sp_IncrementSalariesBy" and the parameter is named "@increment." Now, we need to have ADO.NET access the same in ASP.NET.
The following are the steps you need to take to execute a simple stored procedure with ADO.NET:
Create and open a SQL Server connection (using the "SQLConnection" object).
Create a SQL Server command (using the "SQLCommand" object).
Specify the properties to the "SQLCommand" object.
Create a new parameter to handle our communication (using the "SQLParameter" object).
Add the parameter to the "SQLCommand" object.
Assign the "SQLConnection" object to the "SQLCommand" object.
Execute the stored procedure using the "ExecuteNonQuery" method.
Close the "SQLConnection" and release all memory resources.
We will just follow the above steps. Add a new web form (call it "StoredProcedureWithSingleParam") to the already created project in the previous section. Add a button named "Execute SP with Single Parameter."
After designing the form, switch to the code and add the following line at the top.
Imports System.Data.SqlClient
Add the following code to your "Execute SP with Single Parameter" button:
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim cn As New SqlConnection("Data Source=.;initial catalog=Northwind;user id=sa")
Dim cmd As New SqlCommand
With cmd
.CommandType = CommandType.StoredProcedure
.CommandText = "sp_IncrementSalariesBy"
Dim paramIncrement As New SqlParameter
With paramIncrement
.ParameterName = "@increment"
.SqlDbType = SqlDbType.Int
.Value = 300
End With
.Parameters.Add(paramIncrement)
.Connection = cn
.Connection.Open()
.ExecuteNonQuery()
.Connection.Close()
.Dispose()
End With
End Sub
Set the start page, execute your application (by pressing F5), and click on the button "Execute SP with Single Parameter." Once it executes successfully, check your table "emp," which should get updated with new salaries (incremented by 300).
In the above code, I simply hard coded the value 300. You can get the user specified value using a text box and assign it directly, to make it really dynamic.
You can proceed to the next section with tips on simplifying the above code.
Before going into the ADO.NET code, we need to create a stored procedure with multiple parameters in SQL Server. Using "Query Analyzer," execute the following script in the "Northwind" database.
CREATE PROCEDURE dbo.IncrementSalaryFor
(
@empno int,
@increment float
)
AS
UPDATE emp SET sal = sal + @increment WHERE empno = @empno
RETURN
The above stored procedure will simply increase the salary of a particular employee based on the values sent through parameters. The stored procedure is named "sp_IncrementSalariesFor" and the parameters are named "@empno" and "@increment." Now, we need to have ADO.NET access the same in ASP.NET.
The following are the steps you need to take to execute a simple stored procedure with ADO.NET:
Create and open a SQL Server connection (using a "SQLConnection" object).
Create a SQL Server command (using a "SQLCommand" object).
Specify the properties to the "SQLCommand" object.
Create two parameters to handle our communication (using the "SQLParameter" object).
Add the two parameters to the "SQLCommand" object.
Assign the "SQLConnection" object to "SQLCommand" object.
Execute the stored procedure using the "ExecuteNonQuery" method.
Close the "SQLConnection" and release all memory resources.
We will just follow the above steps. Add a new web form (call it "StoredProcedureWithMultipleParam") to the already created project in the previous section. Add a button named "Execute SP with Multiple Parameter."
After designing the form, switch to the code and add the following line at the top.
Imports System.Data.SqlClient
Add the following code to your "Execute SP with Multiple Parameter" button:
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim cn As New SqlConnection("Data Source=.;initial catalog=Northwind;user id=sa")
You can observe the above code for simplifying the "parameter" objects.
I developed the application using Microsoft Windows Server 2003 Standard Edition with Microsoft Visual Studio.NET 2003 Enterprise Architect and Microsoft SQL Server 2000. Even though I didn't test on all versions, the code in this article should work with any Windows OS (supporting .NET) and any .NET version with any SQL Server version. If anything could not work, please drop me line so that I can guide you.
In the next article, we shall deal with more interesting topics like OUTPUT parameters, returning values from database to ADO.NET, and so on. Watch for it or sign up for a newsletter to get notified.
Any comments, suggestions, feedback, bugs, errors, enhancements are highly appreciated at jag_chat@yahoo.com
MS SQL Server 
Pulling Information using DataAdapter with ADO.NET
