SQL Server to .Net Data Type Mappings Table

Last Updated May 27, 2023 by . First Published in 2013.

SQL Server to .Net Data Type Mappings Table

SQL Server and the .NET Framework are based on different type systems. This handy table will help you to perform data type mappings.

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 <> .Net Data Type Mapping Conversion

SQL Server Database Engine type.NET Framework typeSqlDbType enumerationSqlDataReader SqlTypes typed accessorDbType enumerationSqlDataReader DbType typed accessor
bigintInt64BigIntGetSqlInt64Int64GetInt64
binaryByte[]VarBinaryGetSqlBinaryGetBytes
bitBooleanBitGetSqlBooleanBooleanGetBoolean
charString

Char[]

CharGetSqlStringAnsiStringFixedLength,

String

GetString

GetChars

date 1

(SQL Server 2008 and later)

DateTimeDate 1GetSqlDateTimeDate 1GetDateTime
datetimeDateTimeDateTimeGetSqlDateTimeDateTimeGetDateTime
datetime2

(SQL Server 2008 and later)

DateTimeDateTime2NoneDateTime2GetDateTime
datetimeoffset

(SQL Server 2008 and later)

DateTimeOffsetDateTimeOffsetnoneDateTimeOffsetGetDateTimeOffset
decimalDecimalDecimalGetSqlDecimalDecimalGetDecimal
FILESTREAM attribute (varbinary(max))Byte[]VarBinaryGetSqlBytesBinaryGetBytes
floatDoubleFloatGetSqlDoubleDoubleGetDouble
imageByte[]BinaryGetSqlBinaryBinaryGetBytes
intInt32IntGetSqlInt32Int32GetInt32
moneyDecimalMoneyGetSqlMoneyDecimalGetDecimal
ncharString

Char[]

NCharGetSqlStringStringFixedLengthGetString

GetChars

ntextString

Char[]

NTextGetSqlStringStringGetString

GetChars

numericDecimalDecimalGetSqlDecimalDecimalGetDecimal
nvarcharString

Char[]

NVarCharGetSqlStringStringGetString

GetChars

realSingleRealGetSqlSingleSingleGetFloat
rowversionByte[]TimestampGetSqlBinaryBinaryGetBytes
smalldatetimeDateTimeDateTimeGetSqlDateTimeDateTimeGetDateTime
smallintInt16SmallIntGetSqlInt16Int16GetInt16
smallmoneyDecimalSmallMoneyGetSqlMoneyDecimalGetDecimal
sql_variantObject 2VariantGetSqlValue 2ObjectGetValue 2
textString

Char[]

TextGetSqlStringStringGetString

GetChars

time

(SQL Server 2008 and later)

TimeSpanTimenoneTimeGetDateTime
timestampByte[]TimestampGetSqlBinaryBinaryGetBytes
tinyintByteTinyIntGetSqlByteByteGetByte
uniqueidentifierGuidUniqueIdentifierGetSqlGuidGuidGetGuid
varbinaryByte[]VarBinaryGetSqlBinaryBinaryGetBytes
varcharString

Char[]

VarCharGetSqlStringAnsiString, StringGetString

GetChars

xmlXmlXmlGetSqlXmlXmlnone
  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.
Was this article helpful to you?
 
Comments

If you enjoyed reading this article, or it helped you in some way, all I ask in return is you leave a comment below or share this page with your friends. Thank you.

There are no comments yet. Why not get the discussion started?

We respect your privacy, and will not make your email public. Hashed email address may be checked against Gravatar service to retrieve avatars. This site uses Akismet to reduce spam. Learn how your comment data is processed.