Export (0) Print
Expand All

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

* Use a specific typed accessor if you know the underlying type of the sql_variant.

For more information about SQL Server data types, see Data Types (Database Engine).

Show:
© 2014 Microsoft