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.

By Tim TrottC# ASP.Net MVC • April 20, 2013
SQL Server to .Net Data Type Mappings Table

A handy reference table for converting a SQL Server Data type to a .NET Framework data type and vice-versa. These data type mappings are most useful to developers working in .Net technology who directly use SQL Server database queries.

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 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.

About the Author

Tim Trott is a senior software engineer with over 20 years of experience in designing, building, and maintaining software systems across a range of industries. Passionate about clean code, scalable architecture, and continuous learning, he specialises in creating robust solutions that solve real-world problems. He is currently based in Edinburgh, where he develops innovative software and collaborates with teams around the globe.

Related ArticlesThese articles may also be of interest to you

CommentsShare your thoughts in the comments below

My website and its content are free to use without the clutter of adverts, popups, marketing messages or anything else like that. 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?

New comments for this post are currently closed.