Export (0) Print
Expand All

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

The ADO.NET DataSet object is independent of any specific data source. Data in a DataSet is retrieved from a data source, and changes are persisted back to the data source using a DataAdapter. This means that when a DataAdapter fills a DataTable in a DataSet with values from a data source, the resulting data types of the columns in the DataTable are .NET Framework types rather than types specific to the .NET Framework data provider used to connect to the data source.

Likewise, when a DataReader returns a value from a data source, the resulting value is stored in a local variable that has a .NET Framework type.

For both the Fill operations of the DataAdapter and the Get methods of the DataReader, the .NET Framework type is inferred from the type returned from the .NET Framework data provider. It is recommended that you use the typed accessor methods of the DataReader when you know the specific type of the value being returned. Typed accessor methods result in better performance by returning a value as a specific .NET Framework type, eliminating the need for additional type conversion. The SqlDataReader exposes SQL Server–specific typed accessor methods if a .NET Framework type does not meet the needs of the application. SQL Server–specific typed accessor methods return objects of System.Data.SqlType.

The following tables show the inferred .NET Framework type for data types from Microsoft SQL Server, OLE DB, and ODBC. The typed accessor methods for the DataReader are also listed.

Note   Null values for any .NET Framework data provider data types are represented by DBNull.Value.

.NET Framework Data Provider for SQL Server

SQL Server type.NET Framework type.NET Framework typed accessorSqlType typed accessor
bigintInt64GetInt64()GetSqlInt64()
binaryByte[]GetBytes()GetSqlBinary()
bitBooleanGetBoolean()GetSqlBit()
charString

Char[]

GetString()

GetChars()

GetSqlString()
datetimeDateTimeGetDateTime()GetSqlDateTime()
decimalDecimalGetDecimal()GetSqlDecimal()
floatDoubleGetDouble()GetSqlDouble()
imageByte[]GetBytes()GetSqlBinary()
intInt32GetInt32()GetSqlInt32()
moneyDecimalGetDecimal()GetSqlMoney()
ncharString

Char[]

GetString()

GetChars()

GetSqlString()
ntextString

Char[]

GetString()

GetChars()

GetSqlString()
numericDecimalGetDecimal()GetSqlDecimal()
nvarcharString

Char[]

GetString()

GetChars()

GetSqlString()
realSingleGetFloat()GetSqlSingle()
smalldatetimeDateTimeGetDateTime()GetSqlDateTime()
smallintInt16GetInt16()GetSqlInt16()
smallmoneyDecimalGetDecimal()GetSqlDecimal()
sql_variantObject *GetValue() *GetSqlValue() *
textString

Char[]

GetString()

GetChars()

GetSqlString()
timestampByte[]GetBytes()GetSqlBinary()
tinyintByteGetByte()GetSqlByte()
uniqueidentifierGuidGetGuid()GetSqlGuid()
varbinaryByte[]GetBytes()GetSqlBinary()
varcharString

Char[]

GetString()

GetChars()

GetSqlString()

* It is recommended that you use a specific typed accessor if you know the underlying type of the sql_variant.

.NET Framework Data Provider for OLE DB

ADO typeOLE DB type.NET Framework type.NET Framework typed accessor
adBigIntDBTYPE_I8Int64GetInt64()
adBinaryDBTYPE_BYTESByte[]GetBytes()
adBooleanDBTYPE_BOOLBooleanGetBoolean()
adBSTRDBTYPE_BSTRStringGetString()
adChapterDBTYPE_HCHAPTERSupported through the DataReader. See Retrieving Data Using the DataReader.GetValue()
adCharDBTYPE_STRStringGetString()
adCurrencyDBTYPE_CYDecimalGetDecimal()
adDateDBTYPE_DATEDateTimeGetDateTime()
adDBDateDBTYPE_DBDATEDateTimeGetDateTime()
adDBTimeDBTYPE_DBTIMEDateTimeGetDateTime()
adDBTimeStampDBTYPE_DBTIMESTAMPDateTimeGetDateTime()
adDecimalDBTYPE_DECIMALDecimalGetDecimal()
adDoubleDBTYPE_R8DoubleGetDouble()
adErrorDBTYPE_ERRORExternalExceptionGetValue()
adFileTimeDBTYPE_FILETIMEDateTimeGetDateTime()
adGUIDDBTYPE_GUIDGuidGetGuid()
adIDispatchDBTYPE_IDISPATCH *ObjectGetValue()
adIntegerDBTYPE_I4Int32GetInt32()
adIUnknownDBTYPE_IUNKNOWN *ObjectGetValue()
adNumericDBTYPE_NUMERICDecimalGetDecimal()
adPropVariantDBTYPE_PROPVARIANTObjectGetValue()
adSingleDBTYPE_R4SingleGetFloat()
adSmallIntDBTYPE_I2Int16GetInt16()
adTinyIntDBTYPE_I1ByteGetByte()
adUnsignedBigIntDBTYPE_UI8UInt64GetValue()
adUnsignedIntDBTYPE_UI4UInt32GetValue()
adUnsignedSmallIntDBTYPE_UI2UInt16GetValue()
adUnsignedTinyIntDBTYPE_UI1ByteGetByte()
adVariantDBTYPE_VARIANTObjectGetValue()
adWCharDBTYPE_WSTRStringGetString()
adUserDefinedDBTYPE_UDTnot supported   
adVarNumericDBTYPE_VARNUMERICnot supported   

* For OLE DB types DBTYPE_IUNKNOWN and DBTYPE_IDISPATCH, the object reference is a marshaled representation of the pointer.

.NET Framework Data Provider for ODBC

ODBC type.NET Framework type.NET Framework typed accessor
SQL_BIGINTInt64GetInt64()
SQL_BINARYByte[]GetBytes()
SQL_BITBooleanGetBoolean()
SQL_CHARString

Char[]

GetString()

GetChars()

SQL_DECIMALDecimalGetDecimal()
SQL_DOUBLEDoubleGetDouble()
SQL_GUIDGuidGetGuid()
SQL_INTEGERInt32GetInt32()
SQL_LONG_VARCHARString

Char[]

GetString()

GetChars()

SQL_LONGVARBINARYByte[]GetBytes()
SQL_NUMERICDecimalGetDecimal()
SQL_REALSingleGetFloat()
SQL_SMALLINTInt16GetInt16()
SQL_TINYINTByteGetByte()
SQL_TYPE_TIMESDateTimeGetDateTime()
SQL_TYPE_TIMESTAMPDateTimeGetDateTime()
SQL_VARBINARYByte[]GetBytes()
SQL_WCHARString

Char[]

GetString()

GetChars()

SQL_WLONGVARCHARString

Char[]

GetString()

GetChars()

SQL_WVARCHARString

Char[]

GetString()

GetChars()

.NET Framework Data Provider for Oracle

Oracle type.NET Framework type.NET Framework typed accessorOracleType typed accessor
BFILEByte[]GetBytes()GetOracleBFile()
BLOBByte[]GetBytes()GetOracleLob()
CHARString

Char[]

GetString()

GetChars()

GetOracleString()
CLOBString

Char[]

GetString()

GetChars()

GetOracleLob()
DATEDateTimeGetDateTime()GetOracleDateTime()
FLOATDecimalGetDecimal()GetOracleNumber() **
INTEGERDecimalGetDecimal()GetOracleNumber() **
INTERVAL YEAR TO MONTH *Int32GetInt32()GetOracleMonthSpan()
INTERVAL DAY TO SECOND *TimeSpanGetTimeSpan()GetOracleTimeSpan()
LONGString

Char[]

GetString()

GetChars()

GetOracleString()
LONG RAWByte[]GetBytes()GetOracleBinary()
NCHARString

Char[]

GetString()

GetChars()

GetOracleString()
NCLOBString

Char[]

GetString()

GetChars()

GetOracleLob()
NUMBERDecimalGetDecimal()GetOracleNumber() **
NVARCHAR2String

Char[]

GetString()

GetChars()

GetOracleString()
RAWByte[]GetBytes()GetOracleBinary()
REF CURSOR         
ROWIDString

Char[]

GetString()

GetChars()

GetOracleString()
TIMESTAMP *DateTimeGetDateTime()GetOracleDateTime()
TIMESTAMP WITH LOCAL TIME ZONE *DateTimeGetDateTime()GetOracleDateTime()
TIMESTAMP WITH TIME ZONE *DateTimeGetDateTime()GetOracleDateTime()
UNSIGNED INTEGERDecimalGetDecimal()GetOracleNumber() **
VARCHAR2String

Char[]

GetString()

GetChars()

GetOracleString()

* The specified Oracle type is only available when using both Oracle 9i client and server software.

** An Oracle NUMBER can have a maximum of 38 significant digits. The .NET Framework decimal type is limited to 28 digits. Reading an Oracle NUMBER into a .NET Framework decimal type results in an OverflowException for NUMBER values exceeding 28 digits. If you are reading an Oracle NUMBER value from an OracleDataReader, it is recommended that you call the GetOracleNumber typed accessor method to return Oracle NUMBER values as an OracleNumber. If you are filling a DataSet, you can use the FillError event to determine if an OverflowException has occurred and take appropriate action. For information on the FillError event, see Working with DataAdapter Events.

See Also

Populating a DataSet from a DataAdapter

Show:
© 2014 Microsoft