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 accessor | SqlType typed accessor |
|---|---|---|---|
| bigint | Int64 | GetInt64() | GetSqlInt64() |
| binary | Byte[] | GetBytes() | GetSqlBinary() |
| bit | Boolean | GetBoolean() | GetSqlBit() |
| char | String
Char[] |
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 | String
Char[] |
GetString()
GetChars() |
GetSqlString() |
| ntext | String
Char[] |
GetString()
GetChars() |
GetSqlString() |
| numeric | Decimal | GetDecimal() | GetSqlDecimal() |
| nvarchar | String
Char[] |
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 | String
Char[] |
GetString()
GetChars() |
GetSqlString() |
| timestamp | Byte[] | GetBytes() | GetSqlBinary() |
| tinyint | Byte | GetByte() | GetSqlByte() |
| uniqueidentifier | Guid | GetGuid() | GetSqlGuid() |
| varbinary | Byte[] | GetBytes() | GetSqlBinary() |
| varchar | String
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 type | OLE DB type | .NET Framework type | .NET Framework typed accessor |
|---|---|---|---|
| adBigInt | DBTYPE_I8 | Int64 | GetInt64() |
| adBinary | DBTYPE_BYTES | Byte[] | GetBytes() |
| adBoolean | DBTYPE_BOOL | Boolean | GetBoolean() |
| adBSTR | DBTYPE_BSTR | String | GetString() |
| adChapter | DBTYPE_HCHAPTER | Supported through the DataReader. See Retrieving Data Using the DataReader. | GetValue() |
| adChar | DBTYPE_STR | String | GetString() |
| adCurrency | DBTYPE_CY | Decimal | GetDecimal() |
| adDate | DBTYPE_DATE | DateTime | GetDateTime() |
| adDBDate | DBTYPE_DBDATE | DateTime | GetDateTime() |
| adDBTime | DBTYPE_DBTIME | DateTime | GetDateTime() |
| adDBTimeStamp | DBTYPE_DBTIMESTAMP | DateTime | GetDateTime() |
| adDecimal | DBTYPE_DECIMAL | Decimal | GetDecimal() |
| adDouble | DBTYPE_R8 | Double | GetDouble() |
| adError | DBTYPE_ERROR | ExternalException | GetValue() |
| adFileTime | DBTYPE_FILETIME | DateTime | GetDateTime() |
| adGUID | DBTYPE_GUID | Guid | GetGuid() |
| adIDispatch | DBTYPE_IDISPATCH * | Object | GetValue() |
| adInteger | DBTYPE_I4 | Int32 | GetInt32() |
| adIUnknown | DBTYPE_IUNKNOWN * | Object | GetValue() |
| adNumeric | DBTYPE_NUMERIC | Decimal | GetDecimal() |
| adPropVariant | DBTYPE_PROPVARIANT | Object | GetValue() |
| adSingle | DBTYPE_R4 | Single | GetFloat() |
| adSmallInt | DBTYPE_I2 | Int16 | GetInt16() |
| adTinyInt | DBTYPE_I1 | Byte | GetByte() |
| adUnsignedBigInt | DBTYPE_UI8 | UInt64 | GetValue() |
| adUnsignedInt | DBTYPE_UI4 | UInt32 | GetValue() |
| adUnsignedSmallInt | DBTYPE_UI2 | UInt16 | GetValue() |
| adUnsignedTinyInt | DBTYPE_UI1 | Byte | GetByte() |
| adVariant | DBTYPE_VARIANT | Object | GetValue() |
| adWChar | DBTYPE_WSTR | String | GetString() |
| adUserDefined | DBTYPE_UDT | not supported | |
| adVarNumeric | DBTYPE_VARNUMERIC | not 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_BIGINT | Int64 | GetInt64() |
| SQL_BINARY | Byte[] | GetBytes() |
| SQL_BIT | Boolean | GetBoolean() |
| SQL_CHAR | String
Char[] |
GetString()
GetChars() |
| SQL_DECIMAL | Decimal | GetDecimal() |
| SQL_DOUBLE | Double | GetDouble() |
| SQL_GUID | Guid | GetGuid() |
| SQL_INTEGER | Int32 | GetInt32() |
| SQL_LONG_VARCHAR | String
Char[] |
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 | String
Char[] |
GetString()
GetChars() |
| SQL_WLONGVARCHAR | String
Char[] |
GetString()
GetChars() |
| SQL_WVARCHAR | String
Char[] |
GetString()
GetChars() |
.NET Framework Data Provider for Oracle
| Oracle type | .NET Framework type | .NET Framework typed accessor | OracleType typed accessor |
|---|---|---|---|
| BFILE | Byte[] | GetBytes() | GetOracleBFile() |
| BLOB | Byte[] | GetBytes() | GetOracleLob() |
| CHAR | String
Char[] |
GetString()
GetChars() |
GetOracleString() |
| CLOB | String
Char[] |
GetString()
GetChars() |
GetOracleLob() |
| DATE | DateTime | GetDateTime() | GetOracleDateTime() |
| FLOAT | Decimal | GetDecimal() | GetOracleNumber() ** |
| INTEGER | Decimal | GetDecimal() | GetOracleNumber() ** |
| INTERVAL YEAR TO MONTH * | Int32 | GetInt32() | GetOracleMonthSpan() |
| INTERVAL DAY TO SECOND * | TimeSpan | GetTimeSpan() | GetOracleTimeSpan() |
| LONG | String
Char[] |
GetString()
GetChars() |
GetOracleString() |
| LONG RAW | Byte[] | GetBytes() | GetOracleBinary() |
| NCHAR | String
Char[] |
GetString()
GetChars() |
GetOracleString() |
| NCLOB | String
Char[] |
GetString()
GetChars() |
GetOracleLob() |
| NUMBER | Decimal | GetDecimal() | GetOracleNumber() ** |
| NVARCHAR2 | String
Char[] |
GetString()
GetChars() |
GetOracleString() |
| RAW | Byte[] | GetBytes() | GetOracleBinary() |
| REF CURSOR | |||
| ROWID | String
Char[] |
GetString()
GetChars() |
GetOracleString() |
| TIMESTAMP * | DateTime | GetDateTime() | GetOracleDateTime() |
| TIMESTAMP WITH LOCAL TIME ZONE * | DateTime | GetDateTime() | GetOracleDateTime() |
| TIMESTAMP WITH TIME ZONE * | DateTime | GetDateTime() | GetOracleDateTime() |
| UNSIGNED INTEGER | Decimal | GetDecimal() | GetOracleNumber() ** |
| VARCHAR2 | String
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.