ADO.NET 101: SqlDataReader, Part 2 - Configuring the Command Object for an SQL Statement
(Page 3 of 5 )
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
Next: Configuring the Command Object for Stored Procedure >>
More Database Articles
More By Jayaram Krishnaswamy
/ 15 
