Recipe 2.8 Mapping .NET Data Provider Data Types to .NET Framework Data Types

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

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.