This is the third article in a series focusing on working with stored procedures in ADO.NET. In this article we mainly focus on retrieving single values from stored procedures, retrieving by using "ExecuteScalar," and retrieving an entire row from a stored procedure.
A downloadable file for this article is available here.
If you are new to stored procedures (or to accessing them using ADO.NET) in SQL Server, I strongly suggest you go through my first article before proceeding further.
To work with the stored procedures in this article, you need the simple extra tables “emp” and “dept” within the “Northwind” database. The structures of those two tables are available in the first article of this series.
How to retrieve a single value returned by a stored procedure using Data Reader in ADO.NET from ASP.NET: stored procedure
In my previous article (second in the series), I already explained the RETURN statement in SQL Server stored procedures. A RETURN statement can only return integer values. And it is always recommended for technical purposes (like no error, error number, not found, found and so on). It is not as strongly recommended for other purposes like number of rows, salary of an employee, and so forth.
In this section, I shall look into the same concept, but with any data type and without using RETURN anymore. Before going to the ADO.NET code, we need to create a simple stored procedure in SQL Server which returns some value. Using “Query Analyzer,” execute the following script in the “Northwind” database.
CREATE PROCEDURE dbo.sp_emp_getName
(
@empno int
)
AS
SELECT ename FROM emp
WHERE empno = @empno
RETURN
The above stored procedure simply uses a SELECT statement to retrieve an employee name from a table “EMP” based on the “EMPNO” we send through the parameter “@EMPNO.” The stored procedure is named “sp_Emp_getName.” Now, we need to go to ADO.NET to access the same in ASP.NET.
The following are the steps we'll need to take:
Create and open a SQL Server connection (using “SQLConnection” object).
Create a SQL Server command (using “SQLCommand” object).
Specify the properties to the “SQLCommand” object.
Assign the “SQLConnection” object to “SQLCommand” object.
Execute the stored procedure using “ExecuteNonQuery” method and assign the result to a “SQLDataReader” object.
Retrieve the values from the “SQLDataReader” object and place them into your cache.
Close “SQLConnection” and release all memory resources.
The next section shows you how to deal with the ADO.NET code.
Now, we shall just follow the steps in the preview section practically. Add a new web form (call it “StoredProcedureReturningSingleValue”) with a single button captioned “Retrieve Single Value” and a label named “lblEname.”
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 “Retrieve Single Value” button:
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim name As String
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_emp_getName"
.Parameters.Add("@empno", 1201)
.Connection = cn
.Connection.Open()
Dim dr As SqlDataReader = .ExecuteReader
If dr.Read Then
name = dr(0)
Else
name = "Not Found"
End If
'release resources
.Connection.Close()
.Dispose()
End With
Me.lblEname.Text = name
End Sub
Set the start page and execute your application (by pressing F5) and click on the button “Retrieve Single Value.” Once it executes successfully, you will see either the employee name or a simple message, “Not Found.”
From the above code, we need to concentrate only on the following code fragment:
Dim dr As SqlDataReader = .ExecuteReader
If dr.Read Then
name = dr(0)
Else
name = "Not Found"
End If
“ExecuteReader” is a method which returns a “DataReader” object to pull the information from the database (in this case it is stored procedure) continuously, in a forward only and read-only manner.
In the previous section, we already worked on returning single values and handling them using “DataReader.” There is one more method we need to learn. We can use the “ExecuteScalar” method of the “SQLCommand” object to do exactly the same thing.
We shall work with the same stored procedure we worked with in the previous section. But the steps will be a bit different.
The following are the steps required 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.
Assign the “SQLConnection” object to the “SQLCommand” object.
Execute the stored procedure using “ExecuteScalar” and place the result into your cache.
Close “SQLConnection” and release all memory resources.
Now we shall just follow the above steps practically. Add a new web form (call it “SPReturningValueUsingScalar”) with a single button captioned “Retrieve Single Value” and a label named “lblEname” (similar to the previous section).
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 “Retrieve Single Value” button:
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim name As String
Dim cn As New SqlConnection("Data Source=.;initial catalog=Northwind;user id=sa")
The second method is very easy and very efficient. But it works with only one value. A “DataReader” can be used to retrieve one value, one row or even more rows continuously.
The limitation with “ExecuteScalar” is that it can always retrieve only one value (but not a row or rows). The “ExecuteScalar” is specially designed and tuned to retrieve single values. It is the best in performance when compared with “DataReader” when retrieving single value.
In the previous section, we worked on returning single values and handling them using “DataReader.” We shall extend this concept to an entire row.
Before going into the ADO.NET code, we need to create a simple stored procedure in SQL Server which returns some value. Using “Query Analyzer,” execute the following script in the “Northwind” database.
CREATE PROCEDURE dbo.sp_emp_getEmployeeDetails
(
@empno int
)
AS
SELECT * FROM emp
WHERE empno = @empno
RETURN
The above stored procedure simply uses a SELECT statement to retrieve an entire row related to an employee from the table “EMP” based on the “EMPNO” we send through the parameter “@EMPNO.” The stored procedure is named “sp_Emp_getEmployeeDetails.” Now, we need to go for ADO.NET to access the same in ASP.NET.
The steps would be very similar to the ones I specified in the first section. I shall directly work with the code now. Add a new web form (call it “SPReturningRow”) with a single button captioned “Retrieve Employee Details” and a few labels to display employee information.
After designing the form (as described in the previous section), switch to the code and add the following line at the top.
Imports System.Data.SqlClient
Add the following code to your “Retrieve Employee Details” button:
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim empno, ename, sal, deptno As String
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_emp_getEmployeeDetails"
.Parameters.Add("@empno", 1201)
.Connection = cn
.Connection.Open()
Dim dr As SqlDataReader = .ExecuteReader
If dr.Read Then
empno = dr("empno")
ename = dr("ename")
sal = dr("sal")
deptno = dr("deptno")
Else
empno = "Not Found"
ename = ""
sal = ""
deptno = ""
End If
'release resources
.Connection.Close()
.Dispose()
End With
Me.lblEmpno.Text = empno
Me.lblEname.Text = ename
Me.lblSal.Text = sal
Me.lblDeptno.Text = deptno
End Sub
Set the start page and execute your application (by pressing F5) and click on the button “Retrieve Employee Details.” Once it executes successfully, you will see either all of the details for the employee (like ename, sal, deptno) or a simple message, “Not Found.”
From the above code, we need to concentrate only on the following code fragment:
Dim dr As SqlDataReader = .ExecuteReader
If dr.Read Then
empno = dr("empno")
ename = dr("ename")
sal = dr("sal")
deptno = dr("deptno")
Else
empno = "Not Found"
ename = ""
sal = ""
deptno = ""
End If
As I explained before, “ExecuteReader” is a method which returns a “DataReader” object to pull the information from the database (in this case it is stored procedure) continuously, in a forward only and read-only manner.
In fact, we are now trying to pull an entire row of information. The values of every column in the entire row can be retrieved by specifying the column name with the “DataReader” together and assigning them to a primitive variable.
I developed the application using Microsoft Windows Server 2003 Standard Edition with Microsoft Visual Studio.NET 2003 Enterprise Architect and Microsoft SQL Server 2000. If anything does not work, please drop me a line so that I can guide you. The entire solution for this article is freely available in the form of a zip downloadable at the beginning of this article.
In the next article, we shall work with data tables, data sets, and so forth using stored procedures. If possible, we shall also look into stored functions and compare both.
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
