Mapping between sql and c# types

Numeric Mapping

SQL Server Type	Default CLR Type mapping used by O/R Designer and SQLMetal
BIT	System.Boolean
TINYINT	System.Int16
INT	System.Int32
BIGINT	System.Int64
SMALLMONEY	System.Decimal
MONEY	System.Decimal
DECIMAL	System.Decimal
NUMERIC	System.Decimal
REAL/FLOAT(24)	System.Single
FLOAT/FLOAT(53)	System.Double

CLR Type	Default SQL Server Type used by DataContext.CreateDatabase
System.Boolean	BIT
System.Byte	TINYINT
System.Int16	SMALLINT
System.Int32	INT
System.Int64	BIGINT
System.SByte	SMALLINT
System.UInt16	INT
System.UInt32	BIGINT
System.UInt64	DECIMAL(20)
System.Decimal	DECIMAL(29,4)
System.Single	REAL
System.Double	FLOAT

WARNING
The SQL Server MONEY and SMALLMONEY types, which are also paired with the CLR System.Decimal type by default, have a much smaller precision, which can result in overflow or data loss exceptions when saving data to the database.  

The default precision of SQL Server DECIMAL type (18 decimal digits to the left and right of the decimal point) is much smaller than the precision of the CLR T:System.Decimal type that it is paired with by default.This can result in precision loss when you save data to the database.


------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Text and XML Mapping
 

SQL Server Type	Default CLR Type mapping used by O/R Designer and SQLMetal
CHAR	System.String
NCHAR	System.String
VARCHAR	System.String
NVARCHAR	System.String
TEXT	System.String
NTEXT	System.String
XML	System.Xml.Linq.XElement

CLR Type	Default SQL Server Type used by DataContext.CreateDatabase
System.Char	NCHAR(1)
System.String	NVARCHAR(4000)
System.Char[]	NVARCHAR(4000)
Custom type implementing Parse() and ToString()	NVARCHAR(MAX)

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Date and Time Mapping

SQL Server Type	Default CLR Type mapping used by O/R Designer and SQLMetal
SMALLDATETIME	System.DateTime
DATETIME	System.DateTime
DATETIME2	System.DateTime
DATETIMEOFFSET	System.DateTimeOffset
DATE	System.DateTime
TIME	System.TimeSpan

CLR Type	Default SQL Server Type used by DataContext.CreateDatabase
System.DateTime	DATETIME
System.DateTimeOffset	DATETIMEOFFSET
System.TimeSpan	TIME

WARNING
The range and precision of the CLR System.DateTime type is greater than the range and precision of the SQL Server DATETIME type, which is the default type mapping for the DataContext.CreateDatabase method.To help avoid exceptions related to dates outside the range of DATETIME, use DATETIME2, which is available starting with Microsoft SQL Server 2008.DATETIME2 can match the range and precision of the CLR System.DateTime.

SQL Server dates have no concept of TimeZone, a feature that is richly supported in the CLR.TimeZone values are saved as is to the database without TimeZone conversion, regardless of the original DateTimeKind information.

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Binary Mapping

SQL Server Type	Default CLR Type mapping used by O/R Designer and SQLMetal
BINARY(50)	System.Data.Linq.Binary
VARBINARY(50)	System.Data.Linq.Binary
VARBINARY(MAX)	System.Data.Linq.Binary
VARBINARY(MAX) with the FILESTREAM attribute	System.Data.Linq.Binary
IMAGE	System.Data.Linq.Binary
TIMESTAMP	System.Data.Linq.Binary

CLR Type	Default SQL Server Type used by DataContext.CreateDatabase
System.Data.Linq.Binary	VARBINARY(MAX)
System.Byte	VARBINARY(MAX)
System.Runtime.Serialization.ISerializable	VARBINARY(MAX)

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

 Miscellaneous Mapping

SQL Server Type	Default CLR Type mapping used by O/R Designer and SQLMetal
UNIQUEIDENTIFIER	System.Guid
SQL_VARIANT	System.Object

CLR Type	Default SQL Server Type used by DataContext.CreateDatabase
System.Guid	UNIQUEIDENTIFIER
System.Object	SQL_VARIANT

 https://msdn.microsoft.com/pl-pl/library/bb386947%28v=vs.110%29.aspx

SQL Server and the .NET Framework are based on different type systems.

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 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 (SQL Server 2008 and later)	DateTime	Date	GetSqlDateTime	Date	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 *	Variant	GetSqlValue *	Object	GetValue *
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

https://msdn.microsoft.com/en-us/library/cc716729%28v=vs.110%29.aspx

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

SqlTypes

Each data type in SqlTypes has its equivalent data type in SQL Server, with the same underlying data representation. Many of them also have equivalent data types in the CLR. However, SqlDateTime, SqlDecimal, and SqlString have different underlying data structures with their corresponding .NET Framework data types.

The following table maps the members of the SqlTypes namespace to Microsoft SQL Server data types and to the members of the SqlDbType enumeration.

.NET Framework SqlTypes	Native SQL Server	.NET Framework SqlDbType
SqlBinary	binary, image, timestamp, varbinary	Binary, Image, TimeStamp, VarBinary
SqlBoolean	bit	Bit
SqlByte	tinyint	TinyInt
SqlBytes	binary, image, timestamp, varbinary	Binary, Image, TimeStamp, VarBinary
SqlChars	char, nchar, text, ntext, nvarchar, varchar	Char, NChar, Text, Ntext, NVarChar, VarChar
SqlDateTime	datetime, smalldatetime	DateTime, SmallDateTime
SqlDecimal	numeric, decimal	Decimal
SqlDouble	float	Float
SqlFileStream	varbinary	VarBinary
SqlGuid	uniqueidentifier	UniqueIdentifier
SqlInt16	smallint	SmallInt
SqlInt32	int	Int
SqlInt64	bigint	BigInt
SqlMoney	money, smallmoney	Money, SmallMoney
SqlSingle	real	Real
SqlString	char, nchar, text, ntext, nvarchar, varchar	Char, NChar, Text, Ntext, NVarChar, VarChar
SqlXml	xml	Xml

https://msdn.microsoft.com/en-us/library/system.data.sqltypes%28v=vs.110%29.aspx

https://gist.github.com/Romoku/5444566
https://msdn.microsoft.com/en-us/library/cc716729%28v=vs.110%29.aspx
https://msdn.microsoft.com/en-us/library/bb386947%28v=vs.110%29.aspx
https://msdn.microsoft.com/en-us/library/ms131092.aspx
https://social.msdn.microsoft.com/Forums/sqlserver/en-US/d496c385-e0d9-4b98-81a4-1181270ea030/sql-server-vs-c-data-types?forum=sqlexpress
http://stackoverflow.com/questions/1058322/anybody-got-a-c-sharp-function-that-maps-the-sql-datatype-of-a-column-to-its-clr
http://stackoverflow.com/questions/425389/c-sharp-equivalent-of-sql-server-datatypes
https://msdn.microsoft.com/en-us/library/cc716729%28v=vs.110%29.aspx
https://msdn.microsoft.com/pl-pl/library/system.data.sqltypes%28v=vs.110%29.aspx