Sideway
output.to from Sideway
Draft for Information Only

Content

ADO.NET Data Type Mappings
 In This Section
 See also
SQL Server Data Type Mappings
 SQL Server documentation
 See also
OLE DB Data Type Mappings
 See also
ODBC Data Type Mappings
 See also
Oracle Data Type Mappings
 See also
Floating-Point Numbers
 See also
 Source/Reference

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.

See also

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 1

(SQL Server 2008 and later)
DateTime Date 1 GetSqlDateTime Date 1 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 2 Variant GetSqlValue 2 Object GetValue 2
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

1 You cannot set the DbType property of a SqlParameter to SqlDbType.Date.
2 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).

See also

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.

See also

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

See also

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.

See also

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.

See also

Source/Reference


©sideway

ID: 201000017 Last Updated: 17/10/2020 Revision: 0 Ref:

close

References

  1. Active Server Pages,  , http://msdn.microsoft.com/en-us/library/aa286483.aspx
  2. ASP Overview,  , http://msdn.microsoft.com/en-us/library/ms524929%28v=vs.90%29.aspx
  3. ASP Best Practices,  , http://technet.microsoft.com/en-us/library/cc939157.aspx
  4. ASP Built-in Objects,  , http://msdn.microsoft.com/en-us/library/ie/ms524716(v=vs.90).aspx
  5. Response Object,  , http://msdn.microsoft.com/en-us/library/ms525405(v=vs.90).aspx
  6. Request Object,  , http://msdn.microsoft.com/en-us/library/ms524948(v=vs.90).aspx
  7. Server Object (IIS),  , http://msdn.microsoft.com/en-us/library/ms525541(v=vs.90).aspx
  8. Application Object (IIS),  , http://msdn.microsoft.com/en-us/library/ms525360(v=vs.90).aspx
  9. Session Object (IIS),  , http://msdn.microsoft.com/en-us/library/ms524319(8v=vs.90).aspx
  10. ASPError Object,  , http://msdn.microsoft.com/en-us/library/ms524942(v=vs.90).aspx
  11. ObjectContext Object (IIS),  , http://msdn.microsoft.com/en-us/library/ms525667(v=vs.90).aspx
  12. Debugging Global.asa Files,  , http://msdn.microsoft.com/en-us/library/aa291249(v=vs.71).aspx
  13. How to: Debug Global.asa files,  , http://msdn.microsoft.com/en-us/library/ms241868(v=vs.80).aspx
  14. Calling COM Components from ASP Pages,  , http://msdn.microsoft.com/en-us/library/ms524620(v=VS.90).aspx
  15. IIS ASP Scripting Reference,  , http://msdn.microsoft.com/en-us/library/ms524664(v=vs.90).aspx
  16. ASP Keywords,  , http://msdn.microsoft.com/en-us/library/ms524672(v=vs.90).aspx
  17. Creating Simple ASP Pages,  , http://msdn.microsoft.com/en-us/library/ms524741(v=vs.90).aspx
  18. Including Files in ASP Applications,  , http://msdn.microsoft.com/en-us/library/ms524876(v=vs.90).aspx
  19. ASP Overview,  , http://msdn.microsoft.com/en-us/library/ms524929(v=vs.90).aspx
  20. FileSystemObject Object,  , http://msdn.microsoft.com/en-us/library/z9ty6h50(v=vs.84).aspx
  21. http://msdn.microsoft.com/en-us/library/windows/desktop/ms675944(v=vs.85).aspx,  , ADO Object Model
  22. ADO Fundamentals,  , http://msdn.microsoft.com/en-us/library/windows/desktop/ms680928(v=vs.85).aspx
close

Latest Updated LinksValid XHTML 1.0 Transitional Valid CSS!Nu Html Checker Firefox53 Chromena IExplorerna
IMAGE

Home 5

Business

Management

HBR 3

Information

Recreation

Hobbies 8

Culture

Chinese 1097

English 337

Reference 67

Computer

Hardware 151

Software

Application 202

Digitization 25

Latex 9

Manim 123

Numeric 19

Programming

Web 285

Unicode 494

HTML 65

CSS 59

ASP.NET 194

OS 391

DeskTop 7

Python 23

Knowledge

Mathematics

Formulas 8

Algebra 29

Number Theory 206

Trigonometry 18

Geometry 21

Calculus 67

Complex Analysis 21

Engineering

Tables 8

Mechanical

Mechanics 1

Rigid Bodies

Statics 92

Dynamics 37

Fluid 5

Fluid Kinematics 5

Control

Process Control 1

Acoustics 19

FiniteElement 2

Physics

Electric 27

Biology 1

Geography 1


Copyright © 2000-2020 Sideway . All rights reserved Disclaimers last modified on 06 September 2019