C# Equivalent of SQL Server DataTypes

Question

For the following SQL Server datatypes, what would be the corresponding datatype in C#?

Exact Numerics

bigint
numeric
bit
smallint
decimal
smallmoney
int
tinyint
money

---

Approximate Numerics

float
real

---

Date and Time

date
datetimeoffset
datetime2
smalldatetime
datetime
time

---

Character Strings

char
varchar
text

---

Unicode Character Strings

nchar
nvarchar
ntext

---

Binary Strings

binary
varbinary
image

---

Other Data Types

cursor
timestamp
hierarchyid
uniqueidentifier
sql_variant
xml
table

(source: MSDN)

Answer 1

This is for SQL Server 2005. There are updated versions of the table for SQL Server 2008, SQL Server 2008 R2, SQL Server 2012 and SQL Server 2014.

SQL Server Data Types and Their .NET Framework Equivalents

The following table lists Microsoft SQL Server data types, their equivalents in the common language runtime (CLR) for SQL Server in the System.Data.SqlTypes namespace, and their native CLR equivalents in the Microsoft .NET Framework.

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	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
int	SqlInt32	Int32
bigint	SqlInt64	Int64
smallmoney	SqlMoney	Decimal
money	SqlMoney	Decimal
numeric	SqlDecimal	Decimal
decimal	SqlDecimal	Decimal
real	SqlSingle	Single
float	SqlDouble	Double
smalldatetime	SqlDateTime	DateTime
datetime	SqlDateTime	DateTime
sql_variant	None	Object
User-defined type(UDT)	None	user-defined type
table	None	None
cursor	None	None
timestamp	None	None
xml	SqlXml	None

Answer 2

SQL Server and .Net Data Type mapping

Answer 3

In case anybody is looking for methods to convert from/to C# and SQL Server formats, here goes a simple implementation:

private readonly string[] SqlServerTypes = { "bigint", "binary", "bit",  "char", "date",     "datetime", "datetime2", "datetimeoffset", "decimal", "filestream", "float",  "geography",                              "geometry",                              "hierarchyid",                              "image",  "int", "money",   "nchar",  "ntext",  "numeric", "nvarchar", "real",   "rowversion", "smalldatetime", "smallint", "smallmoney", "sql_variant", "text",   "time",     "timestamp", "tinyint", "uniqueidentifier", "varbinary", "varchar", "xml" };
private readonly string[] CSharpTypes    = { "long",   "byte[]", "bool", "char", "DateTime", "DateTime", "DateTime",  "DateTimeOffset", "decimal", "byte[]",     "double", "Microsoft.SqlServer.Types.SqlGeography", "Microsoft.SqlServer.Types.SqlGeometry", "Microsoft.SqlServer.Types.SqlHierarchyId", "byte[]", "int", "decimal", "string", "string", "decimal", "string",   "Single", "byte[]",     "DateTime",      "short",    "decimal",    "object",      "string", "TimeSpan", "byte[]",    "byte",    "Guid",             "byte[]",    "string",  "string" };

public string ConvertSqlServerFormatToCSharp(string typeName)
{
    var index = Array.IndexOf(SqlServerTypes, typeName);

    return index > -1
        ? CSharpTypes[index]
        : "object";
}

public string ConvertCSharpFormatToSqlServer(string typeName)
{
    var index = Array.IndexOf(CSharpTypes, typeName);

    return index > -1
        ? SqlServerTypes[index]
        : null;
}

Edit: fixed typo

Answer 4

SQL Server and the .NET Framework are based on different type systems. For example, 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. Click Here's a link! for detail

https://msdn.microsoft.com/en-us/library/cc716729(v=vs.110).aspx