ADO.NET Data Type Mappings

The .NET Framework is based on the common type system, which defines how types are declared, used, and managed in the runtime. It consists of both value types and reference types, which all derive from the Object base type. When working with a data source, the data type is inferred from the data provider if it is not explicitly specified. For example, a 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 by 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, instead of types specific to the .NET Framework data provider that is 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 value returned from the .NET Framework data provider.

Instead of relying on the inferred data type, you can use the typed accessor methods of the DataReader when you know the specific type of the value being returned. Typed accessor methods give you better performance by returning a value as a specific .NET Framework type, which eliminates the need for additional type conversion.

Note

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

In This Section

SQL Server Data Type Mappings
Lists inferred data type mappings and data accessor methods for System.Data.SqlClient.

OLE DB Data Type Mappings
Lists inferred data type mappings and data accessor methods for System.Data.OleDb.

ODBC Data Type Mappings
Lists inferred data type mappings and data accessor methods for System.Data.Odbc.

Oracle Data Type Mappings
Lists inferred data type mappings and data accessor methods for System.Data.OracleClient.

Floating-Point Numbers
Describes issues that developers frequently encounter when working with floating-point numbers.

SQL Server Data Type Mappings

SQL Server and the .NET Framework are based on different type systems. For example, the .NET Framework Decimal structure has a maximum scale of 28, whereas the SQL Server decimal and numeric data types have a maximum scale of 38. To maintain data integrity when reading and writing data, the SqlDataReader exposes SQL Server–specific typed accessor methods that return objects of System.Data.SqlTypes as well as accessor methods that return .NET Framework types. Both SQL Server types and .NET Framework types are also represented by enumerations in the DbType and SqlDbType classes, which you can use when specifying SqlParameter data types.

The following table shows the inferred .NET Framework type, the DbType and SqlDbType enumerations, and the accessor methods for the SqlDataReader.

SQL Server Database Engine type	.NET Framework type	SqlDbType enumeration	SqlDataReader SqlTypes typed accessor	DbType enumeration	SqlDataReader DbType typed accessor
bigint	Int64	BigInt	GetSqlInt64	Int64	GetInt64
binary	Byte[]	VarBinary	GetSqlBinary	Binary	GetBytes
bit	Boolean	Bit	GetSqlBoolean	Boolean	GetBoolean
char	String Char[]	Char	GetSqlString	AnsiStringFixedLength, String	GetString GetChars
date ¹ (SQL Server 2008 and later)	DateTime	Date ¹	GetSqlDateTime	Date ¹	GetDateTime
datetime	DateTime	DateTime	GetSqlDateTime	DateTime	GetDateTime
datetime2 (SQL Server 2008 and later)	DateTime	DateTime2	None	DateTime2	GetDateTime
datetimeoffset (SQL Server 2008 and later)	DateTimeOffset	DateTimeOffset	none	DateTimeOffset	GetDateTimeOffset
decimal	Decimal	Decimal	GetSqlDecimal	Decimal	GetDecimal
FILESTREAM attribute (varbinary(max))	Byte[]	VarBinary	GetSqlBytes	Binary	GetBytes
float	Double	Float	GetSqlDouble	Double	GetDouble
image	Byte[]	Binary	GetSqlBinary	Binary	GetBytes
int	Int32	Int	GetSqlInt32	Int32	GetInt32
money	Decimal	Money	GetSqlMoney	Decimal	GetDecimal
nchar	String Char[]	NChar	GetSqlString	StringFixedLength	GetString GetChars
ntext	String Char[]	NText	GetSqlString	String	GetString GetChars
numeric	Decimal	Decimal	GetSqlDecimal	Decimal	GetDecimal
nvarchar	String Char[]	NVarChar	GetSqlString	String	GetString GetChars
real	Single	Real	GetSqlSingle	Single	GetFloat
rowversion	Byte[]	Timestamp	GetSqlBinary	Binary	GetBytes
smalldatetime	DateTime	DateTime	GetSqlDateTime	DateTime	GetDateTime
smallint	Int16	SmallInt	GetSqlInt16	Int16	GetInt16
smallmoney	Decimal	SmallMoney	GetSqlMoney	Decimal	GetDecimal
sql_variant	Object ²	Variant	GetSqlValue ²	Object	GetValue ²
text	String Char[]	Text	GetSqlString	String	GetString GetChars
time (SQL Server 2008 and later)	TimeSpan	Time	none	Time	GetDateTime
timestamp	Byte[]	Timestamp	GetSqlBinary	Binary	GetBytes
tinyint	Byte	TinyInt	GetSqlByte	Byte	GetByte
uniqueidentifier	Guid	UniqueIdentifier	GetSqlGuid	Guid	GetGuid
varbinary	Byte[]	VarBinary	GetSqlBinary	Binary	GetBytes
varchar	String Char[]	VarChar	GetSqlString	AnsiString, String	GetString GetChars
xml	Xml	Xml	GetSqlXml	Xml	none

¹ You cannot set the DbType property of a SqlParameter to SqlDbType.Date.
² Use a specific typed accessor if you know the underlying type of the sql_variant.

SQL Server documentation

For more information about SQL Server data types, see Data types (Transact-SQL).

OLE DB Data Type Mappings

The following table shows the inferred .NET Framework type for data types from the .NET Framework Data Provider for ADO and OLE DB (System.Data.OleDb). The typed accessor methods for the OleDbDataReader are also listed.

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 a 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 the OLE DB types DBTYPE_IUNKNOWN and DBTYPE_IDISPATCH, the object reference is a marshaled representation of the pointer.

ODBC Data Type Mappings

The following table shows the inferred .NET Framework type for data types from the .NET Framework Data Provider for ODBC (System.Data.Odbc). The typed accessor methods for the OdbcDataReader are also listed.

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()

Oracle Data Type Mappings

The following table lists Oracle data types and their mappings to the OracleDataReader.

Oracle data type	.NET Framework data type returned by OracleDataReader.GetValue	OracleClient data type returned by OracleDataReader.GetOracleValue	Remarks
BFILE	Byte[]	OracleBFile
BLOB	Byte[]	OracleLob
CHAR	String	OracleString
CLOB	String	OracleLob
DATE	DateTime	OracleDateTime
FLOAT	Decimal	OracleNumber	This data type is an alias for the NUMBER data type, and is designed so that the OracleDataReader returns a System.Decimal or OracleNumber instead of a floating-point value. Using the .NET Framework data type can cause an overflow.
INTEGER	Decimal	OracleNumber	This data type is an alias for the NUMBER(38) data type, and is designed so that the OracleDataReader returns a System.Decimal or OracleNumber instead of an integer value. Using the .NET Framework data type can cause an overflow.
INTERVAL YEAR TO MONTH	Int32	OracleMonthSpan
INTERVAL DAY TO SECOND	TimeSpan	OracleTimeSpan
LONG	String	OracleString
LONG RAW	Byte[]	OracleBinary
NCHAR	String	OracleString
NCLOB	String	OracleLob
NUMBER	Decimal	OracleNumber	Using the .NET Framework data type can cause an overflow.
NVARCHAR2	String	OracleString
RAW	Byte[]	OracleBinary
REF CURSOR	The Oracle REF CURSOR data type is not supported by the OracleDataReader object.
ROWID	String	OracleString
TIMESTAMP	DateTime	OracleDateTime
TIMESTAMP WITH LOCAL TIME ZONE	DateTime	OracleDateTime
TIMESTAMP WITH TIME ZONE	DateTime	OracleDateTime
UNSIGNED INTEGER	Number	OracleNumber	This data type is an alias for the NUMBER(38) data type, and is designed so that the OracleDataReader returns a System.Decimal or OracleNumber instead of an unsigned integer value. Using the .NET Framework data type can cause an overflow.
VARCHAR2	String	OracleString

The following table lists Oracle data types and the .NET Framework data types (System.Data.DbType and OracleType) to use when binding them as parameters.

Oracle data type	DbType enumeration to bind as a parameter	OracleType enumeration to bind as a parameter	Remarks
BFILE	BFile	Oracle only allows binding a BFILE as a BFILE parameter. The .NET Data Provider for Oracle does not automatically construct one for you if you attempt to bind a non-BFILE value, such as byte[] or OracleBinary.
BLOB	Blob	Oracle only allows binding a BLOB as a BLOB parameter. The .NET Data Provider for Oracle does not automatically construct one for you if you attempt to bind a non-BLOB value, such as byte[] or OracleBinary.
CHAR	AnsiStringFixedLength	Char
CLOB	Clob	Oracle only allows binding a CLOB as a CLOB parameter. The .NET Data Provider for Oracle does not automatically construct one for you if you attempt to bind a non-CLOB value, such as System.String or OracleString.
DATE	DateTime	DateTime
FLOAT	Single, Double, Decimal	Float, Double, Number	Size determines the System.Data.DBType and OracleType.
INTEGER	SByte, Int16, Int32, Int64, Decimal	SByte, Int16, Int32, Number	Size determines the System.Data.DBType and OracleType.
INTERVAL YEAR TO MONTH	Int32	IntervalYearToMonth	OracleType is only available when using both Oracle 9i client and server software.
INTERVAL DAY TO SECOND	Object	IntervalDayToSecond	OracleType is only available when using both Oracle 9i client and server software.
LONG	AnsiString	LongVarChar
LONG RAW	Binary	LongRaw
NCHAR	StringFixedLength	NChar
NCLOB	NClob	Oracle only allows binding a NCLOB as a NCLOB parameter. The .NET Data Provider for Oracle does not automatically construct one for you if you attempt to bind a non-NCLOB value, such as System.String or OracleString.
NUMBER	VarNumeric	Number
NVARCHAR2	String	NVarChar
RAW	Binary	Raw
REF CURSOR	Cursor	For more information, see Oracle REF CURSORs.
ROWID	AnsiString	Rowid
TIMESTAMP	DateTime	Timestamp	OracleType is only available when using both Oracle 9i client and server software.
TIMESTAMP WITH LOCAL TIME ZONE	DateTime	TimestampLocal	OracleType is only available when using both Oracle 9i client and server software.
TIMESTAMP WITH TIME ZONE	DateTime	TimestampWithTz	OracleType is only available when using both Oracle 9i client and server software.
UNSIGNED INTEGER	Byte, UInt16, UInt32, UInt64, Decimal	Byte, UInt16, Uint32, Number	Size determines the System.Data.DBType and OracleType.
VARCHAR2	AnsiString	VarChar

The InputOutput, Output, and ReturnValue ParameterDirection values used by the Value property of the OracleParameter object are .NET Framework data types, unless the input value is an Oracle data type (for example, OracleNumber or OracleString). This does not apply to REF CURSOR, BFILE, or LOB data types.

Floating-Point Numbers

This topic describes some of the issues that developers frequently encounter when they work with floating-point numbers in ADO.NET. These issues are caused by the way that computers store floating-point numbers, and are not specific to a particular provider such as System.Data.SqlClient or System.Data.OracleClient.

Floating-point numbers generally do not have an exact binary representation. Instead, the computer stores an approximation of the number. At different times, different numbers of binary digits may be used to represent the number. When a floating point number is converted from one representation to another representation, the least significant digits of that number may vary slightly. Conversion typically occurs when the number is cast from one type to another type. The variation occurs whether the conversion occurs within a database, between types that represent database values, or between types. Because of these changes, numbers that would logically be equal may have changes in their least-significant digits that cause them to have different values. The number of digits of precision in the number may be larger or smaller than expected. When formatted as a string, the number may not show the expected value.

To minimize these effects, you should use the closest match between numeric types that is available to you. For example, if you are working with SQL Server, the exact numeric value may change if you convert a Transact-SQL value of real type to a value of float type. In the .NET Framework, converting a Single to a Double may also produce unexpected results. In both of these cases, a good strategy is to make all the values in the application use the same numeric type. You can also use a fixed-precision decimal type, or cast floating-point numbers to a fixed-precision decimal type before you work with them.

To work around problems with equality comparison, consider coding your application so that variations in the least significant digits are ignored. For example, instead of comparing to see whether two numbers are equal, subtract one number from the other number. If the difference is within an acceptable margin of rounding, your application can treat the numbers as if they are the same.