Creating basic ADO.NET data objects with SqlClient

Get typical Visual Basic 2005 code for defining and opening an SqlConnection object, specifying a SqlCommand object and invoking the command's ExecuteReader and ExecuteXmlReader.

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.


Figure 1-3

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.

This was first published in May 2006

Dig deeper on Microsoft SQL Server 2005

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

SearchBusinessAnalytics

SearchDataCenter

SearchDataManagement

SearchAWS

SearchOracle

SearchContentManagement

SearchWindowsServer

Close