NHibernate 3 Data Type Mapping for SQL Server

When I recently tried to add a DOUBLE column in a project using NHibernate 3.2.0 GA to connect to a SQL Server database, the schema validation failed since the T-SQL DOUBLE/C# double mapping did not work out. So I wondered how the NHibernate schema validator decides which column data types are supported for each of the C# data types.

So I checked the following sources:

• the MsSql****Dialect.cs files in the \src\NHibernate\Dialect\ directory of 3.2.0GA

• the DbType mapping of C#/.Net types (MSDN)

• the history of DbType values (MSDN)

• SQL Server data type mappings (MSDN)

The result, sorted by the DbType enumeration, looks like this:

DbType	.Net version	C#	NH Mapping 7/2000	2005	2008
AnsiString	1.1		VARCHAR(255)
			VARCHAR(n)
			TEXT
				VARCHAR(MAX)
AnsiString- FixedLength	1.1		CHAR(255)
			CHAR(n)
Binary	1.1	byte[]	VARBINARY(8000)
			VARBINARY(n)
			IMAGE
				VARBINARY(MAX)
Boolean	1.1	bool	BIT
Byte	1.1	byte	TINYINT
Currency	1.1		MONEY
Date	1.1		DATETIME
					DATE
DateTime	1.1	DateTime	DATETIME
DateTime2	3.5				DATETIME2
DateTime- Offset	3.5	DateTime- Offset			DATETIME- OFFSET
Decimal	1.1	decimal	DECIMAL(19,5)
			DECIMAL(n,n)
Double	1.1	double	DOUPLE PRECISION (synonym for FLOAT(53))
Guid	1.1	Guid	UNIQUEIDENTIFIER
Int16	1.1	short	SMALLINT
Int32	1.1	int	INT
Int64	1.1	long	BIGINT
Single	1.1	float	REAL (synonym for FLOAT(24))
String	1.1	string	NVARCHAR(255)
			NVARCHAR(n)
			NTEXT
				NVARCHAR(MAX)
String- FixedLength	1.1		NCHAR(255)
			NCHAR(n)
Time	1.1	TimeSpan	DATETIME
					TIME
Xml	2.0			XML

Note that some data types are not supported:

• DbType: Object, Sbyte, UInt16, UInt32, UInt64, VarNumeric

• C# data types: char, object

• T-SQL data types: sql_variant, rowversion, smalldatetime, timestamp, and CLR types