In the first part of our SqlDataReader tutorial, you saw how to work with it using code. In this second part, you will learn how to work with it using the graphic user interface.
In Part 1, working with the SqlDataReader was described entirely based on code. The connection to the SQL Server, the configuring of the SQLCommand object -- whether it was for executing SQL statements, or stored procedures, were all accomplished by code. Further, the data was displayed to the user on the browser using mostly the Write() method of the Request object.
In this tutorial, working with SQLDataReader will be described mostly by using the Graphic User Interface. Resorting to GUI is helpful in prototyping, and invaluable in understanding how the code is generated in the design phase. A step-by-step approach is used and the tutorial is self-contained, and any referencing links are sparingly used to reduce distraction.
The tutorial follows the same pattern as Part 1.
In many forums, the frequently asked questions often center around the display of data retrieved from the database. The data retrieved from the database can be displayed by a variety of methods:
Using Response.Write() method as seen in Part 1
Populating a dynamically created HTML Table
Using Standard Server Controls
Textbox
ListBox
Drop Down List
Table
Using Databound Controls
In this tutorial several methods of displaying the data will be described. Using Databound controls as well as using data repeater control will be described in a separate tutorial.
Create a new ASP.NET Web application which adds the default WebForm1.aspx to the project. From the ToolBox->Data, drag an SQLConnection Control to the design pane of the WebForm1.aspx. This will add an instance SQLConnection1 of SqlConnection to the tray below the design pane. Right clicking this control will reveal its properties as shown in this picture.
Clicking on the empty space by the item ConnectionString will reveal the handle for the drop-down. Clicking on this handle pops up a drop-down list containing the existing SQLConnections as well as an option to make a new connection, if necessary, as seen in the above picture. You can see that there are four connections on the server XPHTEK(local), and a remote connection (Nechost). This sets up the ConnectionString necessary for the application to connect to the SQL Server.
Adding a SQLCommand is as easy as adding the SQLConnection. Simply drag the SQLCommand Control from Toolbox->Data and drop it on the design pane and an instance of this object, SQLCommand1 will make its way to the tray alongside the SQLConnection1.
The very first thing to do after adding an instance of SQLCommand is to set its Connection property. In order to do this, right click this control to view its properties as shown in the next picture. When newly added the item Connection will be showing none. Clicking inside this item pops up a drop-down list, where one could make a new connection or use an existing connection. To use an existing connection, just expand the item by clicking on the + sign.
As discussed in Part 1, the next property of the SQLCommand to be set is its CommandType property. Notice that the Connection is pointing to the SQLConnection1 instance created in the earlier step. For the native SQLConnection chosen there are basically two possible settings for CommandType property. It could be either the type Text, or the type Stored Procedure. The third option (TableDirect) is not supported by the SQL Native Data Provider. By clicking on the empty space in the CommandType line, one of these choices can be made.
As mentioned in the earlier section, if the CommandType property is set to Text, then the next property that needs to be set is the CommandText property. This represents the SQLStatement that the command will execute against the database. In the previous picture (screen), click on an empty space in the line item CommandText. This will spawn an ellipsis (...) button.
Upon clicking this button, the Query Builder pops up together with an Add Table modal window as shown in the next picture. This modal window shows all the objects from the pubs database that you would normally see in the View->Server Explorer menu item of the VS Studio IDE. In this particular case, you see the Table, View, and Functions, objects. If the Add Table were not to show up for some reason, right clicking in the design pane will bring up the Add Table dialogue.
If the Authors table were to be chosen, from which the query will be fashioned, then the Design pane will be populated by the column list for this table as shown in this picture. This designer is pervasive in most of Microsoft's products, including MS ACCESS, SQL Server, Visual Studio 6.0, and VB 6.0, with some variations in its implementation. More than one table can be added, and complicated queries with multiple joins can be made.
It may also be seen that a skeletal SQL statement is automatically added to the SQL pane of the Query Builder. The query is built by choosing the columns that need to be in the Select statement by checking the boxes alongside the column names in the table's column list in the design pane. Here you see the check mark for the au_lname column is checked, and this automatically updates the SQL statement in the SQL pane.
After making all the choices you need, you can do the sorting and filtering of the columns by appropriately making the choices in the Grid Pane. When the query is completed to your specification (satisfaction), you can right click in any of the panes and you will get a context sensitive drop down list of actions you can take. For example, you can test your sql statement for the correctness of the syntax, run the query and obtain results as shown in the next picture, or you can change the query type from being merely "select" to one of performing an action on the database.
Once the query is verified, displaying the query output can be accomplished in a number of ways. In this snippet, displaying the result using the Server Control Table is shown.
The Web Server Table control just adds the following tag to the HTML view of a Web form: <asp:Table id="Table2" style="Z-INDEX: 103; LEFT: 80px; POSITION: absolute; TOP: 416px" runat="server"></asp:Table> and an abbreviated image to the Design view as shown:
Rows and cells can be added either during design time by adding the necessary TableRow and TableCell ASP controls, or using code at run time.
The SQL Connection used is the same as before. The pubs and the SQL command text is: SELECT au_lname, au_fname, phone FROM authors
The following snippet shows how to get the data read by the SQLDataReader into the Server Table control. The table's rows and cells are dynamically constructed by calling new instances of rows and cells; and the cell contents are rendered with Literal Controls with the data output displayed in the literal controls.The Cells are added to the rows and finally the rows to the table.
Private Sub Button1_Click(ByVal sender As System.Object, _
ByVal e As System.EventArgs) Handles Button1.Click
SqlConnection1.Open()
Dim drSrv As SqlClient.SqlDataReader
drSrv = SqlCommand1.ExecuteReader
While drSrv.Read
Dim r As New TableRow
Dim k As Integer
Dim c As New TableCell
Table1.GridLines = GridLines.Both
c.Controls.Add(New LiteralControl(drSrv.Item(1)))
c.Controls.Add(New LiteralControl("..."))
c.Controls.Add(New LiteralControl(drSrv.Item(0)))
c.Controls.Add(New LiteralControl("..."))
c.Controls.Add(New LiteralControl(drSrv.Item(2)))
r.Cells.Add(c)
Table1.Rows.Add(r)
End While
The next screen shot shows a sample output from this code
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.
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.
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.
/ 16 
ADO.NET 101: SqlDataReader
