Working with typed DataReader and SqlResultSet data

Get code samples to extract column values to native .NET data types.

This Article Covers

.Net Development

+ Show More
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 preceding code examples use Reader.Item(ColumnIndex).ToString, Reader.GetString (ColumnIndex) and Reader.GetInt32(ColumnIndex) methods to extract column values to native .NET data types, which the System namespace defines. ADO.NET 2.0 provides the following data-specific enumerations:

  • System.Data.DbType is a generic enumeration for setting the data types of OleDb and Odbc parameters, fields, and properties.
  • System.Data.SqlDbType is an enumeration for use with SqlParameter objects only. VS 2005 automatically adds SqlParameters when you create typed DataSets from SQL Server tables in the following sections.
  • System.Data.SqlTypes is a namespace that contains structures for all SQL Server 2000 and 2005 data types, except timestamp, and related classes and enumerations. Using SqlTypes structures improves data-access performance by eliminating conversion to native .NET types, and assures that column values aren't truncated.

VS 2005's online help provides adequate documentation for DbType and SqlDbType enumerations, and SqlTypes structures, so this chapter doesn't provide a table to relate these enumerations and types.

The following OpenDataReaderSqlTypes listing shows examples of the use of typical GetSqlDataType(ColumnIndex) methods:

     Private Sub OpenDataReaderSqlTypes()
          '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 sdrReader As SqlDataReader
          Try
              cnnNwind.Open()

              'Define the SqlCommand
             Dim strSQL As String = "SELECT Orders.*, " + _
               "ProductID, UnitPrice, Quantity, Discount " + _
               "FROM Orders INNER JOIN [Order Details] ON " + _
               "Orders.OrderID = [Order Details].OrderID WHERE CustomerID = 'ALFKI'"
            Dim cmdReader As SqlCommand = New SqlCommand(strSQL, cnnNwind)

            'Create, and traverse the SqlDataReader, assigning SqlTypes to variables
            sdrReader = cmdReader.ExecuteReader(CommandBehavior.CloseConnection)
           With sdrReader
                 If .HasRows Then
                   While .Read
                          'Get typical SqlTypes
                         Dim s_intOrderID As SqlInt32 = .GetSqlInt32(0)
                         Dim s_strCustomerID As SqlString = .GetSqlString(1)
                         Dim s_datOrderDate As SqlDateTime = .GetSqlDateTime(3)
                         Dim s_curUnitPrice As SqlMoney = .GetSqlMoney(15)
                         Dim s_sngDiscount As SqlSingle = .GetSqlSingle(17)
                   End While
               End If
         End With
      Catch exc As Exception
           MsgBox(exc.Message + exc.StackTrace)
      Finally
           'Close the SqlDataReader and the SqlConnection
           sdrReader.Close()
      End Try
End Sub

You can update SqlResultSet object column values with strongly typed variables by invoking the SqlResultSet.SetSqlDataType(ColumnIndex) method. You'll see more examples of strongly typed SQL Server data retrieval and update operations that use these methods in later chapters.

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 .NET Development for SQL Server

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