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.
