ADO.NET 101: SqlDataReader, Part 2 - Stored Procedure that Requires an Input Parameter
(Page 5 of 5 )
The following picture shows the stored procedure called MySimple2 on the SQL 2000 Server. It takes one parameter(input) to execute and return the results.
As we have done before, add an SQLConnection. Then, drag and drop the named stored procedure MySimple2 from the Server Explorer to the Web form. This automatically updates the SQLCommand1's Properties window. Click on the (Collection) as before to review the SQL Parameter Editor Window as seen in the next picture. This window is also automatically populated. The focus is now on the input parameter @State. The properties of this parameter are listed in detail. It is evident that this editor can be used to configure the parameters, although in this case it was automatic.
Now is the time to write the code to run this procedure and display the row/rows returned by the SQLDataReader. In this example, the common server controls(unbound) will be used to display the results. The code example shows how to display in TextBox, ListBox and DropDownList controls. In the case of textbox, only the last item of the column is displayed because it is overwritten. In the case of DropDownList, only one column is displayed by choice.
Private Sub Button1_Click(ByVal sender As System.Object, _
ByVal e As System.EventArgs) Handles Button1.Click
ListBox1.Items.Clear()
DropDownList1.Items.Clear()
SqlConnection1.Open()
Dim dr As SqlClient.SqlDataReader
'declare the parameterDim parm1 As SqlClient.SqlParameter
'set parameter valueSqlCommand1.Parameters("@State").Value
= TextBox1.Text
Dim drMySimple2 As SqlClient.SqlDataReader
drMySimple2 = SqlCommand1.ExecuteReader _
(CommandBehavior.CloseConnection)
While drMySimple2.Read
ListBox1.Items.Add(drMySimple2.Item(0) & " , " _
& drMySimple2.Item(2))
DropDownList1.Items.Add(drMySimple2.Item(0))
TextBox2.Text = drMySimple2.Item(0)
End While
drMySimple2.Close()
SqlConnection1.Close()
End Sub
Build the solution and run. The name of the State which has to be entered as a parameter is entered into the text box at the top; the results are seen in the ListBox, the DropDownList, and the Textbox as shown in the next picture
Stored Procedure that Requires an Input Parameter and Produces an Output Parameter The sql script to create the stored procedure Ytdsales is shown in the picture. It requires an input parameter and the result is returned in the output parameter. The stored procedure can be copied and executed in the query analyzer(SQL 2000 Server Tool) in the context of a pubs database; and the stored procedure will be stored in the database.
Once the stored procedure is created, it should be available in the Server Explorer. If it is not visible, you may need to refresh the contents of the Server Explorer. Now drag and drop a SqlConnection, and then drag and drop the stored procedure YtdSales from the Server Explorer.
Note that the query builder cannot be used to create a stored procedure from within the VS IDE for the Standard edition of the software. The Enterprise edition will be able to create procedures from within the IDE. The SQLCommand's SQL Parameters will be automatically configured as shown in the next picture.
This stored procedure has one input and one output parameter to contend with, so you need to add two text boxes, one for the input and one for the output. Also add a command button (Caption:Get Year today Sales) to initiate events. The code is very simple -- compare it to the code in Part 1 for the same procedure.
For Textbox1 type in some of the sample values for the titles shown within comments, and Textbox2 will display the resulting YtrSales figures. In practice, however, you may use another datareader to show a sorted list of all titles, and for the selected one, you can display the Ytdsales. You pass the parameter from the dropdownlist to the '@title' 's value.
Private Sub Button1_Click(ByVal sender As System.Object, _
ByVal e As System.EventArgs) Handles Button1.Click
SqlConnection1.Open()
Dim dr As SqlClient.SqlDataReader
Response.Write(SqlCommand1.Parameters.Item(1).Value)
'try some of these input values
'SqlCommand1.Parameters.Item(1).Value = "The Gourmet Microwave"
'SqlCommand1.Parameters.Item(1).Value = "Net Etiquette"
'assign the input parameterSqlCommand1.Parameters("@title").Value
= TextBox1.Text
'Perform ExecuteReader()dr = SqlCommand1.ExecuteReader
TextBox2.Text = SqlCommand1.Parameters("@ytd_sales").Value
dr.close
SqlConnection1.Close()
End Sub
For the sample input, "The Gourmet Microwave," the following result is obtained.
In this tutorial, only the minimum code is shown for the examples. However, one may need to prepare for other eventualities -- what happens if the result returned is a NULL, for instance? The program will throw an exception. In this case it will be necessary to wrap the output code to address the issue. For example, the title 'Net Etiquette' has a NULL in its ytd_sales, column. In order to address this, you may rewrite the code as follows:
'------
If IsDBNull(SqlCommand1.Parameters("@ytd_sales").Value) Then
TextBox2.Text = "There were no sales for this item"
Else
TextBox2.Text = SqlCommand1.Parameters("@ytd_sales").Value
End If
'-------
Conclusions
Using the data controls SqlConnection and SqlCommand greatly reduces the amount of code that needs to be written. This simplification is taken a step further by Microsoft Data Access Application Blocks. These are part of Microsoft's .NET Assembly that encapsulates the best practices for data access. The byword here is minimum code and maximum performance.
A variety of display options were shown including server controls. Again, best practices dictate judicious use of server controls as they can be expensive. Use of HTML tables or simple rendering using Response.write() are the least expensive methods.
Data Reader is the fastest and the best choice for read-only, forward only type of access for non-cached data. In order to fill combo boxes, verifying user authentication against stored information on the server, the data reader can be put to good use. Using type specific data access the Data Reader performance can be further improved.
| 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. |
/ 15 
