Microsoft SQL Server

Requirements

Microsoft SQL Server support relies on the SQLClient ADO.NET provider included in .NET. To be able to create a model from an existing SQL Server schema, you have to have access to a SQL Server instance, version 2000 or higher, or SQL Azure.

Supported features

Feature	Supported
Minimal database version	SQL Server 2000
Database types	All built-in types
User defined types	Yes
Foreign key constraints	Yes
Primary key constraints	Yes
Unique constraints	Yes
Check constraints	No
Default values	Yes
Unique indexes	No
Tables	Yes
Views	Yes
Stored Procedures	Yes
Table Valued Functions	Yes
(System versioned) Temporal tables	Yes
Synonyms	Yes
Cascade delete rules	Yes
Cascade update rules	Yes
Identity fields	Yes
System sequences	Yes (Scope_identity() and @@identity)
Schema based sequences	Yes
Multiple catalogs per project	Yes (Not on SQL Azure)
Multiple schemas per catalog	Yes
Linked servers	No

Type mappings

Below you'll find the two type mapping tables used by the SQL Server driver of LLBLGen Pro. One is used for Database First development, the other is used for Model First development. In case of Model First development, you can add your own type shortcuts to define a different target database type, in case the default mappings aren't what you need in a particular case.

Global model-first type overruling

For Model first, there are often multiple database types defined per .NET Type and a given filter. To prefer one over the other on a global level, please look at the following settings in the Relational model data element construction section of the Project Settings:

• Prefer natural character types
• Prefer decimal over currency types
• Prefer variable length types

Database first type mappings

You can control the .NET type during reverse engineering in database first development by defining Type Conversions in the Project Settings, which are based on Type Converters.

Database type	.NET Type
BigInt	System.Int64
Binary	System.Byte[]
Bit	System.Boolean
Char	System.String
DateTime2	System.DateTime
DateTimeOffset	System.DateTimeOffset
DateTime	System.DateTime
Date	System.DateTime
Decimal	System.Decimal
Float	System.Double
Image	System.Byte[]
Int	System.Int32
Money	System.Decimal
NChar	System.String
NText	System.String
NVarChar	System.String
Numeric	System.Decimal
Real	System.Single
SmallDateTime	System.DateTime
SmallInt	System.Int16
SmallMoney	System.Decimal
Sql_Variant	System.Object
SysName	System.String
Text	System.String
TimeStamp	System.Byte[]
Time	System.TimeSpan
TinyInt	System.Byte
UniqueIdentifier	System.Guid
UserDefinedType	System.Object (or if available, the .NET type it represents)
VarBinary	System.Byte[]
VarChar	System.String
Xml	System.String

Model first type mappings

If specified, the filter is combined with the .NET type to determine the database type. The elements length, precision and scale are the max. length, precision and scale of an entity field with the particular .NET Type. For instance a field with type System.String, max. length set to 50, will with all defaults enabled, a database type of NVarChar(50).

If a controlling setting is specified, that setting has to be set to the specified value (in the Relational model data element construction section of the Project Settings) to make the designer select that database type.

.NET Type	Filter	Database type	Controlling setting and value
System.Bool		Bit
System.Byte		TinyInt
System.Byte[]	0 < length < 32768	Binary(length)	Prefer variable length types set to false
System.Byte[]	0 < length < 32768	VarBinary(length)	Prefer variable length types set to true
System.Byte[]	length==0 or length >= 32768	VarBinary(MAX)
System.DateTime		DateTime
System.DateTimeOffset		DateTimeOffset
System.Decimal	0 < precision <=19 and scale == 0	Money	Prefer decimal over currency types set to false
System.Decimal	0 < precision <=4 and 0 < scale <= 4	SmallMoney	Prefer decimal over currency types set to false
System.Decimal	5 < precision <=19 and 0 < scale <= 4	Money	Prefer decimal over currency types set to false
System.Decimal		Decimal(precision, scale)	Prefer decimal over currency types set to true
System.Double		Float
System.Guid		UniqueIdentifier
System.Int16		SmallInt
System.Int32		Int
System.Int64		BigInt
System.Object		Sql_Variant
System.Single		Real
System.String	0 < length < 8192	Char(length)	Prefer natural character types set to false and Prefer variable length types set to false
System.String	0 < length < 8192	NChar(length)	Prefer natural character types set to true and Prefer variable length types set to false
System.String	0 < length < 8192	NVarChar(length)	Prefer natural character types set to true and Prefer variable length types set to true
System.String	0 < length < 8192	VarChar(length)	Prefer natural character types set to false and Prefer variable length types set to true
System.String	length==0 or length >= 8192	NVarChar(MAX)	Prefer natural character types set to true
System.String	length==0 or length >= 8192	VarChar(MAX)	Prefer natural character types set to false
System.TimeSpan		Time

Supported target frameworks

The following target frameworks are supported with SQL Server:

• LLBLGen Pro Runtime Framework
• Linq to Sql
• Entity Framework
• Entity Framework Core
• NHibernate