Today I would like to post a table listing the corresponding .Net Type to use with each SQL Data Type. This comes in handy when writing ADO.NET code.
The following link lists Microsoft SQL Server data types and their equivalent data type in .Net.
To be more exact it lists:
- Every SQL Server data types
- Their equivalent in the Common Language Runtime (CLR) for SQL Server in the System.Data.SqlTypes namespace
- Their native CLR equivalents in the Microsoft .NET Framework
SQL Server Data Types and Their .NET Framework Equivalents
I have also copy-pasted the SQL Server and .Net Data Type mapping table hereunder for my own convenience:
| SQL Server data type | CLR data type (SQL Server) | CLR data type (.NET Framework) |
| varbinary | SqlBytes, SqlBinary | Byte[] |
| binary | SqlBytes, SqlBinary | Byte[] |
| varbinary(1), binary(1) | SqlBytes, SqlBinary | byte, Byte[] |
| image | None | None |
| varchar | None | None |
| char | None | None |
| nvarchar(1), nchar(1) | SqlChars, SqlString | Char, String, Char[] |
| 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[] |
| nchar | SqlChars, SqlString | String, Char[] |
| text | None | None |
| ntext | None | None |
| uniqueidentifier | SqlGuid | Guid |
| rowversion | None | Byte[] |
| bit | SqlBoolean | Boolean |
| tinyint | SqlByte | Byte |
| smallint | SqlInt16 | Int16 (short in C#) |
| int | SqlInt32 | Int32 (int in C#) |
| bigint | SqlInt64 | Int64 (long in C#) |
| smallmoney | SqlMoney | Decimal (decimal in C#) |
| money | SqlMoney | Decimal (decimal in C#) |
| numeric | SqlDecimal | Decimal (decimal in C#) |
| decimal | SqlDecimal | Decimal (decimal in C#) |
| real | SqlSingle | Single (float in C#) |
| float | SqlDouble | Double (double in C#) |
| smalldatetime | SqlDateTime | DateTime |
| datetime | SqlDateTime | DateTime |
| sql_variant | None | Object |
| User-defined type(UDT) | None | Same class that is bound to the user-defined type in the same assembly or a dependent assembly. |
| table | None | None |
| cursor | None | None |
| timestamp | None | None |
| xml | SqlXml | None |
Regarding CLR Data Types, Data Types are basically divided in 3 categories:
- Value Types (CLR-defined structs, Enumerations, bool, user-defined structs)
- Reference Types
- Pointer Types
Furthermore, 3 types of CLR Data Type structures exist to hold numeric values:
- Integral types, holding integers
- Floating-point types, holding reals
- decimal, holding reals of a smaller range but higher precision
See Data Types (C# Reference) for a complete reference and correspondance between CLR types and C# types.