The following tip was excerpted from Chapter 1, 'Migrating from ADO to ADO.NET,' of the book Expert One-on-one Visual Basic 2005 Database Programming by Roger Jennings, courtesy of Wiley Publishers. Click here for the complete collection of book excerpts.
The following sections illustrate typical VB 2005 code for defining and opening an SqlConnection object, specifying an SqlCommand object, and invoking the command's ExecuteReader and ExecuteXmlReader methods. The procedures include code to display SqlDataReader column and XmlReader element values. All examples use a local SQL Server 2000 or 2005 Northwind sample database as their data source.
If you're using the default named instance of SQLX on your test machine, change localhost to .SQLEXPRESS in the strConn connection string. If you're using Access's MSDE 2000 instance as the local server, change Northwind to NorthwindCS. If you're using a remote SQL Server instance, replace localhost with the remote server's network name.
The VB2005DBChapter01BasicDataObjects folder, which you create by expanding the Chapter01.zip file from the Wrox Web site for the book, contains complete source code for the following procedures. However, you must install the Northwind sample database before running the sample projects. See the Introduction's "Source Code and Sample Databases" section for details.
| TABLE OF CONTENTS
SqlDataReaders with Multiple Resultsets
XmlReaders with FOR XML AUTO Queries
Fill a DataGridView with a DataReader
Return a Single Data Row
Return a Scalar Value
Execute Queries That Don't Return Data
|SqlDataReaders with Multiple Resultsets||Return to Table of Contents|
One of the most common uses of SqlDataReader objects is filling dropdown lists or list boxes with lookup data. You can use multiple resultsets from a single SQL batch query or stored procedure to fill multiple lists in the FormName_Load event handler. The following OpenDataReader procedure opens a connection to the Northwind sample database, specifies an SqlCommand object that returns two resultsets, and invokes its ExecuteReader method to generate the SqlDataReader instance. The CommandBehavior .CloseConnection argument closes the connection when you close the DataReader. All basic ADO.NET data objects follow this pattern; only the ExecuteObject method and DataReader iteration methods differ. The SqlDataReader.Read method, which replaces the often-forgotten RecordSet.MoveNext instruction, returns True while rows remain to be read. Similarly, the SqlDataReader.NextResult method is True if unprocessed resultsets remain after the initial iteration.
Only one resultset is open as you iterate multiple resultsets, which differs from SQL Server 2005's Multiple Active Resultsets (MARS) feature. Chapter 10, "Upgrading from SQL Server 2000 to 2005," describes how to enable the MARS feature.
Private Sub OpenDataReader() 'Define and open the SqlConnection object Dim strConn As String = "Server=localhost;Database=Northwind;" + _ "Integrated Security=SSPI" Dim cnnNwind As SqlConnection = New SqlConnection(strConn) cnnNwind.Open() 'Define the SqlCommand to return two resultsets Dim strSQL As String = "SELECT * FROM Shippers" strSQL += ";SELECT EmployeeID, FirstName, LastName FROM Employees" Dim cmdReader As SqlCommand = New SqlCommand(strSQL, cnnNwind) cmdReader.CommandType = CommandType.Text 'Define, create, and traverse the SqlDataReader 'Close the connection when closing the SqlDataReader Dim sdrReader As SqlDataReader = _ cmdReader.ExecuteReader(CommandBehavior.CloseConnection) sdrReader = cmdReader.ExecuteReader With sdrReader If .HasRows Then While .Read 'Fill a Shippers list box lstShippers.Items.Add(.Item(0).ToString + " - " + .Item(1).ToString) End While While .NextResult 'Process additional resultset(s) While .Read 'Fill an Employees list box lstEmployees.Items.Add(.Item(0).ToString + " - " + _ .Item(1).ToString + " " + .Item(2).ToString) End While End While End If 'Close the SqlDataReader and SqlConnection .Close() End With End Sub
Use of the HasRows property is optional because initial invocation of the Read method returns False if the query returns no rows. The SqlDataReader.Item(ColumnIndex) property returns an Object variable that you must convert to a string for concatenation. Structured error handling code is removed for improved readability.
|XmlReaders with FOR XML AUTO Queries||Return to Table of Contents|
Adding a FOR XML AUTO clause to an SQL Server SELECT query or stored procedure returns the resultset as an XML stream. The default XML document format is attribute-centric; add the Elements modifier to return an element-syntax document. Here's the XML document returned by a SELECT * FROM Shippers FOR XML AUTO, Elements query:
<?xml version="1.0" encoding="utf-8" ?> <root> <Shippers> <ShipperID>1 </ShipperID> <CompanyName>Speedy Express </CompanyName> <Phone>(503) 555-9831 </Phone> </Shippers> <Shippers> <ShipperID>2 </ShipperID> <CompanyName>United Package </CompanyName> <Phone> (503) 555-3199</Phone> </Shippers> <Shippers> <ShipperID>3 </ShipperID> <CompanyName>Federal Shipping </CompanyName> <Phone>(503) 555-9931 </Phone> </Shippers> </root>
ADO.NET 2.0's new SqlCommand.ExecuteXmlReader method loads a System.Xml.XmlReader object with the stream, as shown in the following OpenXmlReader procedure listing. XmlReader is an abstract class with concrete XmlTextReader, XmlNodeReader, and XmlValidatingReader implementations. ADO.NET 2.0's ExecuteXmlReader method returns a concrete implementation.
Private Sub OpenXmlReader() 'Define and open the SqlConnection object Dim strConn As String = "Server=localhost;Database=Northwind;" + _ "Integrated Security=SSPI" Dim cnnNwind As SqlConnection = New SqlConnection(strConn) Dim xrShippers As System.Xml.XmlReader Try cnnNwind.Open() 'Define the SqlCommand Dim strSQL As String = "SELECT * FROM Shippers FOR XML AUTO, Elements" Dim cmdXml As SqlCommand = New SqlCommand(strSQL, cnnNwind) xrShippers = cmdXml.ExecuteXmlReader With xrShippers .Read() Do While .ReadState <> Xml.ReadState.EndOfFile txtXML.Text += .ReadOuterXml Loop 'Format the result txtXML.Text = Replace(txtXML.Text, "><", ">" + vbCrLf + "<") End With Catch exc As Exception MsgBox(exc.Message + exc.StackTrace) Finally xrShippers.Close cnnNwind.Close() End Try End Sub
Substituting xrShippers.MoveToContent followed by xrShippers.ReadOuterXML (without the loop) returns only the first <Shippers> element group.
You must execute the XmlReader.Read method to move to the first element group, followed by a ReadOuterXml invocation for each element group, which represents a row of the resultset. The ExecuteXmlReader method doesn't support the CommandBehavior enumeration, so you must close the SqlConnection object explicitly. OleDbCommand doesn't support the ExecuteXmlReader method; Microsoft wants you to use SqlClient classes for all SQL Server data access applications, including SQLCLR code running in the SQL Server 2005 process.
Figure 1-3 shows the BasicDataObjects project's form after executing from the frmMain_Load event handler, which executes the preceding OpenDataReader and OpenXmlReader procedures, and the following LoadDataGridView procedure.
FOR XML AUTO queries or stored procedures in production applications cause a substantial performance hit compared with traditional data-access methods. The server must generate the XML stream, many more data bytes travel over the network, and the client or component must transform the XML stream to a usable format.
|Fill a DataGridView with a DataReader||Return to Table of Contents|
If your application needs to display only tabular data , a read-only grid control that's populated by code consumes the fewest resources. The DataGridView control replaces VS 2002 and VS 2003's DataGrid control, and is easy to fill programmatically. A read-only DataGridView populated by a DataReader behaves similarly to VB6's standard (unbound) Grid control, except that DataGridViews have sortable columns by default.
The following code defines the dgvCusts DataGridView control's columns and then populates each row with an instance of an objCells() Object array that contains cell values:
Private Sub LoadDataGridView() 'Populate a read-only DataGridView control with an SqlDataReader Dim cnnNwind As SqlConnection = New SqlConnection(strConn) Try Dim strSql As String = "SELECT * FROM Customers" Dim cmdGrid As New SqlCommand(strSql, cnnNwind) cmdGrid.CommandType = CommandType.Text cnnNwind.Open() Dim sdrGrid As SqlDataReader = cmdGrid.ExecuteReader Dim intCol As Integer With sdrGrid If .HasRows Then dgvCusts.Rows.Clear() 'Add column definition: FieldName, and ColumnName For intCol = 0 To .FieldCount - 1 dgvCusts.Columns.Add(.GetName(intCol), .GetName(intCol)) Next 'Base column width on header text width dgvCusts.AutoSizeColumnsMode = _ DataGridViewAutoSizeColumnsMode.ColumnHeader While .Read 'Get row data as an Object array Dim objCells(intCol) As Object .GetValues(objCells) 'Add an entire row at a time dgvCusts.Rows.Add(objCells) End While .Close() End If End With Catch exc As Exception MsgBox(exc.Message) Finally cnnNwind.Close() End Try End Sub
To sort the DataGridView control on column values, click the column header. Alternate clicks perform ascending and descending sorts.
|Return a Single Data Row||Return to Table of Contents|
Adding a CommandBehavior.SingleRow flag to the SqlDataReader object returns the first row of a resultset specified by an SQL query or stored procedure. The following code returns the first row of Northwind's Customers table, if you don't specify a WHERE clause. Otherwise the code returns the first row specified by WHERE criteria. Adding a CommandBehavior.CloseConnection flag closes the connection automatically when you close the SqlDataReader object.
Private Sub OpenExecuteRow() Dim cnnNwind As SqlConnection = New SqlConnection(strConn) Try cnnNwind.Open() 'Define the SqlCommand Dim strSQL As String = "SELECT * FROM Customers" 'Following is optional for the first record 'strSQL += " WHERE CustomerID = 'ALFKI'" Dim cmdRow As SqlCommand = New SqlCommand(strSQL, cnnNwind) cmdRow.CommandType = CommandType.Text Dim sdrRow As SqlDataReader = _ cmdRow.ExecuteReader(CommandBehavior.SingleRow Or _ CommandBehavior.CloseConnection) With sdrRow If .HasRows Then .Read() Dim intFields As Integer = .FieldCount Dim strCustID As String = .GetString(0) Dim strCompany As String = .GetString(1) End If 'Closes the DataReader and Connection .Close() End With Catch exc As Exception MsgBox(exc.Message + exc.StackTrace) Finally 'Close the SqlConnection, if still open cnnNwind.Close() End Try End Sub
|Return a Scalar Value||Return to Table of Contents|
The SqlCommand.ExecuteScalar method returns the value of the first column of the first row of a resultset. The most common use of ExecuteScalar is to return a single SQL aggregate value, such as COUNT, MIN, or MAX. The following OpenExecuteScalar procedure listing returns the number of Customers table records:
Private Sub OpenExecuteScalar() 'Return a single SQL aggregate value Dim strConn As String = "Server=localhost;Database=Northwind;" + _ "Integrated Security=SSPI" Dim cnnNwind As SqlConnection = New SqlConnection(strConn) cnnNwind.Open() 'Define the SqlCommand Dim strSQL As String = "SELECT COUNT(*) FROM Customers" Dim cmdScalar As SqlCommand = New SqlCommand(strSQL, cnnNwind) cmdScalar.CommandType = CommandType.Text Dim intCount As Integer = CInt(cmdScalar.ExecuteScalar) 'Close the SqlConnection cnnNwind.Close() End Sub
|Execute Queries That Don't Return Data||Return to Table of Contents|
You use the SqlCommand.ExecuteNonQuery method to execute SQL queries or stored procedures that update base table data—INSERT, UPDATE, and DELETE operations. As the following OpenExecuteNonQuery code demonstrates, ExecuteNonQuery rivals the simplicity of ExecuteScalar:
Private Sub RunExecuteNonQuery() 'Add and delete a bogus Customers record Dim strConn As String = "Server=localhost;Database=Northwind;" + _ "Integrated Security=SSPI" Dim cnnNwind As SqlConnection = New SqlConnection(strConn) Dim intRecordsAffected As Integer Try cnnNwind.Open() 'Define and execute the INSERT SqlCommand Dim strSQL As String = "INSERT Customers (CustomerID, CompanyName) " + _ "VALUES ('BOGUS', 'Bogus Company')" Dim cmdUpdates As SqlCommand = New SqlCommand(strSQL, cnnNwind) cmdUpdates.CommandType = CommandType.Text intRecordsAffected = cmdUpdates.ExecuteNonQuery 'Update and execute the UPDATE SqlCommand strSQL = "UPDATE Customers SET CompanyName = 'Wrong Company' " + _ "WHERE CustomerID = 'BOGUS'" cmdUpdates.CommandText = strSQL intRecordsAffected += cmdUpdates.ExecuteNonQuery 'Define and execute the DELETE SqlCommand strSQL = "DELETE FROM Customers WHERE CustomerID = 'BOGUS'" cmdUpdates.CommandText = strSQL intRecordsAffected += cmdUpdates.ExecuteNonQuery Catch exc As Exception MsgBox(exc.Message + exc.StackTrace) Finally 'Close the SqlConnection cnnNwind.Close() If intRecordsAffected <> 3 Then MsgBox("INSERT, UPDATE, DELETE, or all failed. " + _ "Check your Customers table.") End If End Try End Sub
Executing SQL update queries against production databases isn't a recommended practice and most DBAs won't permit direct updates to server base tables. The purpose of the preceding example is to provide a simple illustration of how the ExecuteNonQuery method works. In the real world, parameterized stored procedures usually perform table updates.
The above tip was excerpted from Chapter 1, 'Migrating from ADO to ADO.NET,' of the book Expert One-on-one Visual Basic 2005 Database Programming by Roger Jennings, courtesy of Wiley Publishers. Click here for the complete collection of book excerpts.