Problem
You want to convert between .NET provider data types and .NET Framework data types.
Solution
You need to understand the .NET Framework data types; their mappings to SQL Server, OLE DB, ODBC, and Oracle data types; and how to properly cast them. The .NET Framework typed accessors and .NET Framework provider-specific typed accessors for use with the DataReader class are also important.
Discussion
The ADO.NET DataSet and contained objects are data source independent. The DataAdapter is used to retrieve data into the DataSet and to reconcile modifications made to the data to the data source at some later time. The implication is that data in the DataTable objects contained in the DataSet are .NET Framework data types rather than data types specific to the underlying data source or the .NET data provider used to connect to that data source.
While the DataReader object for a data source is specific to the .NET data provider used to retrieve the data, the values in the DataReader are stored in variables with .NET Framework data types.
The .NET Framework data type is inferred from the .NET data provider used to fill the DataSet or build the DataReader . The DataReader has typed accessor methods that improve performance by returning a value as a specific .NET Framework data type when the data type is known, thereby eliminating the need for additional type conversion. For more information about using typed accessors with a DataReader , see Recipe 9.6.
Some DataReader classes expose data source specific accessor methods as well. For example, the SqlDataReader exposes accessor methods that return SQL Server data types as objects of System.Data.SqlType .
The following example shows how to cast a value from a DataReader to a .NET Framework data type and how to use the .NET Framework typed accessor and the SQL Server-specific typed accessor:
// Create the connection and the command. SqlConnection conn = new SqlConnection( ConfigurationSettings.AppSettings["Sql_ConnectString"]); SqlCommand cmd = new SqlCommand( "SELECT CategoryID, CategoryName FROM Categories", conn); // Open the connection and build the DataReader. conn.Open( ); SqlDataReader dr = cmd.ExecuteReader( ); // Get the CategoryID from the DataReader and cast to int. int categoryId = Convert.ToInt32(dr[0]); // Get the CategoryID using typed accessor. int taCategoryId = dr.GetInt32(0); // Get the CategoryID using the SQL Server-specific accessor. System.Data.SqlTypes.SqlInt32 sqlCategoryId = dr.GetSqlInt32(0);
In all cases, a null value for a .NET Framework data type is represented by System.DBNull.Value .
Table 2-7 lists the inferred .NET Framework data type, the .NET Framework typed accessor for the DataReader , and the SQL Server-specific typed accessor for each SQL Server data type.
Table 2-7. Data types and accessors for SQL Server .NET data provider
SQL Server data type |
.NET Framework data type |
.NET Framework typed accessor |
SQLType typed accessor |
---|---|---|---|
bigint |
Int64 |
GetInt64( ) |
GetSqlInt64( ) |
binary |
Byte[] |
GetBytes( ) |
GetSqlBinary( ) |
bit |
Boolean |
GetBoolean( ) |
GetSqlBit( ) |
char |
StringChar[] |
GetString( )GetChars( ) |
GetSqlString( ) |
datetime |
DateTime |
GetDateTime( ) |
GetSqlDateTime( ) |
decimal |
Decimal |
GetDecimal( ) |
GetSqlDecimal( ) |
float |
Double |
GetDouble( ) |
GetSqlDouble( ) |
image |
Byte[] |
GetBytes( ) |
GetSqlBinary( ) |
int |
Int32 |
GetInt32( ) |
GetSqlInt32( ) |
money |
Decimal |
GetDecimal( ) |
GetSqlMoney( ) |
nchar |
StringChar[] |
GetString( )GetChars( ) |
GetSqlString( ) |
ntext |
StringChar[] |
GetString( )GetChars( ) |
GetSqlString( ) |
numeric |
Decimal |
GetDecimal( ) |
GetSqlDecimal( ) |
nvarchar |
StringChar[] |
GetString( )GetChars( ) |
GetSqlString( ) |
real |
Single |
GetFloat( ) |
GetSqlSingle( ) |
smalldatetime |
DateTime |
GetDateTime( ) |
GetSqlDateTime( ) |
smallint |
Int16 |
GetInt16( ) |
GetSqlInt16( ) |
smallmoney |
Decimal |
GetDecimal( ) |
GetSqlDecimal( ) |
sql_variant |
Object |
GetValue( ) |
GetSqlValue( ) |
text |
StringChar[] |
GetString( )GetChars( ) |
GetSqlString( ) |
timestamp |
Byte[] |
GetBytes( ) |
GetSqlBinary( ) |
tinyint |
Byte |
GetByte( ) |
GetSqlByte( ) |
uniqueidentifier |
Guid |
GetGuid( ) |
GetSqlGuid( ) |
varbinary |
Byte[] |
GetBytes( ) |
GetSqlBinary( ) |
varchar |
StringChar[] |
GetString( )GetChars( ) |
GetSqlString( ) |
Table 2-8 lists the inferred .NET Framework data type, the .NET Framework typed accessor for the DataReader for each OLE DB type, and the corresponding ADO type.
Table 2-8. Data types and accessors for OLE DB .NET data provider
OLE DB data type |
ADO type |
.NET Framework data type |
.NET Framework typed accessor |
---|---|---|---|
DBTYPE_BOOL |
adBoolean |
Boolean |
GetBoolean( ) |
DBTYPE_BSTR |
adBSTR |
String |
GetString( ) |
DBTYPE_BYTES |
adBinary |
Byte[] |
GetBytes( ) |
DBTYPE_CY |
adCurrency |
Decimal |
GetDecimal( ) |
DBTYPE_DATE |
adDate |
DateTime |
GetDateTime( ) |
DBTYPE_DBDATE |
adDBDate |
DateTime |
GetDateTime( ) |
DBTYPE_DBTIME |
adDBTime |
DateTime |
GetDateTime( ) |
DBTYPE_DBTIMESTAMP |
adDBTimeStamp |
DateTime |
GetDateTime( ) |
DBTYPE_DECIMAL |
adDecimal |
Decimal |
GetDecimal( ) |
DBTYPE_ERROR |
adError |
ExternalException |
GetValue( ) |
DBTYPE_FILETIME |
adFileTime |
DateTime |
GetDateTime( ) |
DBTYPE_GUID |
adGUID |
Guid |
GetGuid( ) |
DBTYPE_HCHAPTER |
adChapter |
See footnote 1 |
GetValue( ) |
DBTYPE_I1 |
adTinyInt |
Byte |
GetByte( ) |
DBTYPE_I2 |
adSmallInt |
Int16 |
GetInt16( ) |
DBTYPE_I4 |
adInteger |
Int32 |
GetInt32( ) |
DBTYPE_I8 |
adBigInt |
Int64 |
GetInt64( ) |
DBTYPE_IDISPATCH 2 |
adIDispatch |
Object |
GetValue( ) |
DBTYPE_IUNKNOWN 2 |
adIUnknown |
Object |
GetValue( ) |
DBTYPE_NUMERIC |
adNumeric |
Decimal |
GetDecimal( ) |
DBTYPE_PROPVARIANT |
adPropVariant |
Object |
GetValue( ) |
DBTYPE_R4 |
adSingle |
Single |
GetFloat( ) |
DBTYPE_R8 |
adDouble |
Double |
GetDouble( ) |
DBTYPE_STR |
adChar |
String |
GetString( ) |
DBTYPE_UI1 |
adUnsignedTinyInt |
Byte |
GetByte( ) |
DBTYPE_UI2 |
adUnsignedSmallInt |
UInt16 |
GetValue( ) |
DBTYPE_UI4 |
adUnsignedInt |
UInt32 |
GetValue( ) |
DBTYPE_UI8 |
adUnsignedBigInt |
UInt64 |
GetValue( ) |
DBTYPE_UDT |
adUserDefined |
Not supported |
Not supported |
DBTYPE_VARIANT |
adVariant |
Object |
GetValue( ) |
DBTYPE_VARNUMERIC |
adVarNumeric |
Not supported |
Not supported |
DBTYPE_WSTR |
adWChar |
String |
GetString( ) |
1 Supported using the DataReader . For more information, see Recipe 2.20.
2 The object reference is a marshaled representation of the pointer.
Table 2-9 lists the inferred .NET Framework data type and the .NET Framework typed accessor for the DataReader for each ODBC data type.
Table 2-9. Data types and accessors for ODBC .NET data provider
ODBC data type |
.NET Framework data type |
.NET Framework typed accessor |
---|---|---|
SQL_BIGINT |
Int64 |
GetInt64( ) |
SQL_BINARY |
Byte[] |
GetBytes( ) |
SQL_BIT |
Boolean |
GetBoolean( ) |
SQL_CHAR |
StringChar[] |
GetString( )GetChars( ) |
SQL_DECIMAL |
Decimal |
GetDecimal( ) |
SQL_DOUBLE |
Double |
GetDouble( ) |
SQL_GUID |
Guid |
GetGuid( ) |
SQL_INTEGER |
Int32 |
GetInt32( ) |
SQL_LONG_VARCHAR |
StringChar[] |
GetString( )GetChars( ) |
SQL_LONGVARBINARY |
Byte[] |
GetBytes( ) |
SQL_NUMERIC |
Decimal |
GetDecimal( ) |
SQL_REAL |
Single |
GetFloat( ) |
SQL_SMALLINT |
Int16 |
GetInt16( ) |
SQL_TINYINT |
Byte |
GetByte( ) |
SQL_TYPE_TIMES |
DateTime |
GetDateTime( ) |
SQL_TYPE_TIMESTAMP |
DateTime |
GetDateTime( ) |
SQL_VARBINARY |
Byte[] |
GetBytes( ) |
SQL_WCHAR |
StringChar[] |
GetString( )GetChars( ) |
SQL_WLONGVARCHAR |
StringChar[] |
GetString( )GetChars( ) |
SQL_WVARCHAR |
StringChar[] |
GetString( )GetChars( ) |
Table 2-10 lists the inferred .NET Framework data type, the .NET Framework typed accessor for the DataReader , and the Oracle-specific typed accessor for each Oracle data type.
Table 2-10. Data types and accessors for Oracle .NET data provider
Oracle data type |
.NET Framework data type |
.NET Framework typed accessor |
OracleType typed accessor |
---|---|---|---|
BFILE |
Byte[] |
GetBytes( ) |
GetOracleBFile( ) |
BLOB |
Byte[] |
GetBytes( ) |
GetOracleLob( ) |
CHAR |
StringChar[] |
GetString( )GetChars( ) |
GetOracleString( ) |
CLOB |
StringChar[] |
GetString( )GetChars( ) |
GetOracleLob( ) |
DATE |
DateTime |
GetDateTime( ) |
GetOracleDateTime( ) |
FLOAT |
Decimal |
GetDecimal( ) |
GetOracleNumber( ) 2 |
INTEGER |
Decimal |
GetDecimal( ) |
GetOracleNumber( ) 2 |
INTERVAL YEAR TO MONTH 1 |
Int32 |
GetInt32( ) |
GetOracleMonthSpan( ) |
INTERVAL DAY TO SECOND 1 |
TimeSpan |
GetTimeSpan( ) |
GetOracleTimeSpan( ) |
LONG |
StringChar[] |
GetString( )GetChars( ) |
GetOracleString( ) |
LONG RAW |
Byte[] |
GetBytes( ) |
GetOracleBinary( ) |
NCHAR |
StringChar[] |
GetString( )GetChars( ) |
GetOracleString( ) |
NCLOB |
StringChar[] |
GetString( )GetChars( ) |
GetOracleLob( ) |
NUMBER |
Decimal |
GetDecimal( ) |
GetOracleNumber( ) 2 |
NVARCHAR2 |
StringChar[] |
GetString( )GetChars( ) |
GetOracleString( ) |
RAW |
Byte[] |
GetBytes( ) |
GetOracleBinary( ) |
REF CURSOR |
n/a |
n/a |
n/a |
ROWID |
StringChar[] |
GetString( )GetChars( ) |
GetOracleString( ) |
TIMESTAMP 1 |
DateTime |
GetDateTime( ) |
GetOracleDateTime( ) |
TIMESTAMP WITH LOCAL TIME ZONE 1 |
DateTime |
GetDateTime( ) |
GetOracleDateTime( ) |
TIMESTAMP WITH TIME ZONE 1 |
DateTime |
GetDateTime( ) |
GetOracleDateTime( ) |
UNSIGNED INTEGER |
Decimal |
GetDecimal( ) |
GetOracleNumber( ) 2 |
VARCHAR2 |
StringChar[] |
GetString( )GetChars( ) |
GetOracleString( ) |
1 Available only when using both Oracle 9i client and server software.
2 The Oracle NUMBER type has a maximum of 38 significant digits while the .NET Framework decimal type has a maximum of 28. An OverflowException will be raised if the Oracle NUMBER type has more than 28 significant digits.
For details about inferred .NET Framework data types, .NET Framework typed accessors for the DataReader , and provider-specific typed accessors for other .NET data providers, consult the documentation for the specific .NET data provider.
Connecting to Data
Retrieving and Managing Data
Searching and Analyzing Data
Adding and Modifying Data
Copying and Transferring Data
Maintaining Database Integrity
Binding Data to .NET User Interfaces
Working with XML
Optimizing .NET Data Access
Enumerating and Maintaining Database Objects
Appendix A. Converting from C# to VB Syntax