 | |||
|
|
||
| |||
| |||
| ||||||||
|
|
|||||||
|
|
|
|
| Book Excerpt: |
|
||
|
|||
|
|||
|
|||
|
|||
|
|||
|
|
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
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.
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.
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.
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.
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
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
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.
|
|
|
|
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
SqlDataReaders with Multiple Resultsets
|
|
 |  |  | |  | |  | |  | |  | |  | |  | |  | |
|
|
About Us | Contact Us | For Advertisers | For Business Partners | Site Index | RSS |
|
|
||
|
|||||||
