Pulling Information Using DataReader With ADO.NET
(Page 1 of 5 )
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.
Next: How to retrieve a single value returned by a stored procedure using Data Reader in ADO.NET from ASP.NET: the code >>
More MS SQL Server Articles
More By Jagadish Chaterjee
/ 10 
