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.
The Wiert Corner - irregular stream of stuff 
Leave a comment