Tables with SQL Server and .NET data types
Posted by jpluimers on 2013/10/23
Thanks StackOverflow users George Stocker for asking, Örjan Jämte and Sir Crispalot for answering.
- SQL Server 2005:
SQL Server Data Types and Their .NET Framework Equivalents.
Data Type Conversion (Database Engine). - SQL Server 2008:
Mapping CLR Parameter Data.
Data Type Conversion (Database Engine). - SQL Server 2008 R2:
Mapping CLR Parameter Data.
Data Type Conversion (Database Engine). - SQL Server 2012:
Mapping CLR Parameter Data.
Data Type Conversion (Database Engine).
Below is the SQL Server 2012 table, in which I added links to the various data types.
I also added two columns with linked references to the types from the C# data types, C# Keywords, Reference Tables for Types (C# Reference) and Data Type Summary (Visual Basic).
One of the things I need to check is against the LINQ SQL-CLR Type Mapping.
It is very important to keep in mind that in SQL, each combination of precision and digits gets you a different decimal type, and all of them are different from the .NET decimal type. See for instance the answers on these questions:
- Unable to insert decimal to sql server table through c# code – Stack Overflow.
- Validating decimal in C# for storage in SQL Server – Stack Overflow.
SQL Server data type | Type (in System.Data.SqlTypes or Microsoft.SqlServer.Types) | CLR data type (.NET Framework) | C# data type | VB.net data type |
bigint | SqlInt64 | Int64, Nullable<Int64> | long | Long |
binary | SqlBytes, SqlBinary | Byte[] | byte | Byte |
bit | SqlBoolean | Boolean, Nullable<Boolean> | bool | Boolean |
char | None | None | ||
cursor | None | None | ||
date | SqlDateTime | DateTime, Nullable<DateTime> | Date | |
datetime | SqlDateTime | DateTime, Nullable<DateTime> | Date | |
datetime2 | None | DateTime, Nullable<DateTime> | Date | |
DATETIMEOFFSET | None | DateTimeOffset, Nullable<DateTimeOffset> | ||
decimal | SqlDecimal | Decimal, Nullable<Decimal> | decimal | Decimal |
float | SqlDouble | Double, Nullable<Double> | double | Double |
geography | SqlGeography SqlGeography is defined in Microsoft.SqlServer.Types.dll, which is installed with SQL Server and can be downloaded from the SQL Server 2012 feature pack. |
None | ||
geometry | SqlGeometry SqlGeometry is defined in Microsoft.SqlServer.Types.dll, which is installed with SQL Server and can be downloaded from the SQL Server 2012 feature pack. |
None | ||
hierarchyid | SqlHierarchyId SqlHierarchyId is defined in Microsoft.SqlServer.Types.dll, which is installed with SQL Server and can be downloaded from the SQL Server 2012 feature pack. |
None | ||
image | None | None | ||
int | SqlInt32 | Int32, Nullable<Int32> | int | Integer |
money | SqlMoney | Decimal, Nullable<Decimal> | ||
nchar | SqlChars, SqlString | String, Char[] | string, char[] | String, Char |
ntext | None | None | ||
numeric | SqlDecimal | Decimal, Nullable<Decimal> | ||
nvarchar | SqlChars, SqlString SQLChars is a better match for data transfer and access, and SQLString is a better match for performing String operations. |
String, Char[] | string, char[] | String, Char |
nvarchar(1), nchar(1) | SqlChars, SqlString | Char, String, Char[], Nullable<char> | string, char[] | String, Char |
real | SqlSingle (the range of SqlSingle, however, is larger than real) |
Single, Nullable<Single> | float | Single |
rowversion | None | Byte[] | byte | Byte |
smallint | SqlInt16 | Int16, Nullable<Int16> | short | Short |
smallmoney | SqlMoney | Decimal, Nullable<Decimal> | decimal | |
sql_variant | None | Object | object | Object |
table | None | None | ||
text | None | None | ||
time | None | TimeSpan, Nullable<TimeSpan> | ||
timestamp | None | None | ||
tinyint | SqlByte | Byte, Nullable<Byte> | byte | Byte |
uniqueidentifier | SqlGuid | Guid, Nullable<Guid> | ||
User-defined type(UDT) | None | The same class that is bound to the user-defined type in the same assembly or a dependent assembly. | ||
varbinary | SqlBytes, SqlBinary | Byte[] | byte | Byte |
varbinary(1), binary(1) | SqlBytes, SqlBinary | Byte, Byte[], Nullable<byte> | byte | Byte |
varchar | None | None | ||
xml | SqlXml | None |
Missing from the above table:
–jeroen
via: .net – C# Equivalent of SQL Server 2005 DataTypes – Stack Overflow.
Leave a Reply