Pulling Information Using DataReader With ADO.NET - How to retrieve a single row returned by a stored procedure using DataReader in ADO.NET from ASP.NET: the code
(Page 5 of 5 )
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
| DISCLAIMER: The content provided in this article is not warranted or guaranteed by Developer Shed, Inc. The content provided is intended for entertainment and/or educational purposes in order to introduce to the reader key ideas, concepts, and/or product reviews. As such it is incumbent upon the reader to employ real-world tactics for security and implementation of best practices. We are not liable for any negative consequences that may result from implementing any information covered in our articles or tutorials. If this is a hardware review, it is not recommended to open and/or modify your hardware. |
/ 16 
