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
Info

SQL Server 2016+: History tables of a temporal table pair are filtered out and are not included in schema data.

Info

If you're connecting to an Azure SQL Server instance using Azure Active Directory, be sure to check the checkbox 'Use azure active directory' in the database connection information dialog when connecting to the database. You can then use 'WindowsAuthentication' for Azure AD integrated connections and 'DatabaseSpecificAuthentication' for Azure AD Password connections.

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