Recipe 2.8 Mapping .NET Data Provider Data Types to .NET Framework Data Types
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|
|
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|
|
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_IDISPATCH2
|
adIDispatch
|
Object
|
GetValue( )
| |
DBTYPE_IUNKNOWN2
|
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|
|
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|
|
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 MONTH1
|
Int32
|
GetInt32( )
|
GetOracleMonthSpan( )
| |
INTERVAL DAY TO SECOND1
|
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( )
| |
TIMESTAMP1
|
DateTime
|
GetDateTime( )
|
GetOracleDateTime( )
| |
TIMESTAMP WITH LOCAL TIME ZONE1
|
DateTime
|
GetDateTime( )
|
GetOracleDateTime( )
| |
TIMESTAMP WITH TIME ZONE1
|
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.
| 
