Pulling Information Using DataReader With ADO.NET - How to retrieve a single value returned by a stored procedure using ExecuteScalar
(Page 3 of 5 )
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")
Dim cmd As New SqlCommand
With cmd
.CommandType = CommandType.StoredProcedure
.CommandText = "sp_emp_getName"
.Parameters.Add("@empno", 1201)
.Connection = cn
.Connection.Open()
name = .ExecuteScalar & ""
'release resources
.Connection.Close()
.Dispose()
End With
Me.lblEname.Text = IIf(name.Length = 0, "Not Found",
name)
End Sub
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.
Next: How to retrieve a single row returned by a stored procedure using DataReader in ADO.NET from ASP.NET: stored procedure >>
More MS SQL Server Articles
More By Jagadish Chaterjee
/ 12 
