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