ADO.NET 101: SqlDataReader, Part 2 - Configuring the Command Object for Stored Procedure
(Page 4 of 5 )
Stored procedures are named executable objects stored on the SQL 2000 Server. They bring significant benefits in the form of improved performance, allowing restricted access to database, reduced programming errors, reduced network traffic, and many others. In this tutorial, the same stored procedures used in Part 1 will be used. It should be remembered that in order to execute stored procedures, proper permissions to the object should be in place.
Stored Procedure Without any Parameters MySimple is a stored procedure on the connected SQL 2000 Server. This procedure displays a number of columns from the stores table and filters them so that stores from "'CA' or 'WA'" states are displayed. The SQL script to create this procedure is shown in the next picture.
MySimple.sql
 |
 |
Drag and drop an SQLConnection control instance and configure it to connect to the pubs database as discussed earlier. Now, from the Server Explorer, drag and drop the named stored procedure MySimple onto the Web form. This action effectively adds a pre-configured SQLCommand1 instance to the Web form, as shown in the next picture.
Stored procedures usually have parameters that are passed to the Parameters collection of the command object. Click by the side of (Collection) in the SQLCommand1 properties Parameters line item. This spawns an elipsis (...) button which, when clicked, brings up the following screen. Although there are no parameters explicitly going into or coming from this stored procedure, the paramters collection will have one parameter that represents the return value -- in this case, the number of columns returned. This parameter's index is 0 in the collection.
This completes the configuration of the SQLCommand1 instance.
Now the code to display the retrieved data using a user designed HTML table is examined. The stored procedure is returning four columns with as yet undetermined number of rows. In the design of the HTML table, you need to make sure that there is only one table, that will have multiple rows. Hence all the rows that are read must be contained within the <table></table> tags. The code for this follows here; notice that the datareader is reading the raw values at the ordinals:
Private Sub Button2_Click(ByVal sender As System.Object, _
ByVal e As System.EventArgs) Handles Button2.Click
'open SQLConnection
SqlConnection1.Open()
'declare SQLDataReaderDim dr As SqlClient.SqlDataReader
'Execute Readerdr = SqlCommand1.ExecuteReader
Response.Write("<table border='1' bgcolor=gold>")
'read rowsWhile dr.Read
Response.Write("<tr>")
Response.Write("<td>")
Response.Write(dr.Item(0))
Response.Write("</td>")
Response.Write("<td>")
Response.Write(dr.Item(1))
Response.Write("</td>")
Response.Write("<td>")
Response.Write(dr.Item(2))
Response.Write("</td>")
Response.Write("<td>")
Response.Write(dr.Item(3))
Response.Write("</td>")
Response.Write("</tr>")
End While
Response.Write("</table>")
'close readerdr.Close()
'close connectionSqlConnection1.Close()
End Sub
The following picture display the result of running this procedure in the table built by HTML tags for the table.
Next: Stored Procedure that Requires an Input Parameter >>
More Database Articles
More By Jayaram Krishnaswamy
/ 15 
