Generated code - Database specific features
This section illustrates the database specific features which are available to you through configuration, either through the .config file of your
application or through code. See the Application configuration through .config files
section for the details about the configuration settings defined through .config
SQL Server specific: NEWSEQUENTIALID() support
When you're using unique_identifier types for primary keys on SQL Server 2005
or higher, you can benefit from a feature called NEWSEQUENTIALID().
This feature allows you to auto-generate new GUIDs for your primary keys which are sequential, so they are friendly for clustered indexes. To use this feature
in LLBLGen Pro, you have to specify as the default
for the primary key field in the table definition: NEWSEQUENTIALID(). Furthermore, you shouldn't set
the PK field to a new GUID value when you're saving the entity.
The DQE will then let the database insert the NEWSEQUENTIALID() produced value and it's automatically retrieved for you into the entity's PK.
SQL Server specific: compatibility mode
The SQL Server DQE is usable for a variety of different SQL Server versions, from
7 to 2012 and from CE 3.1 to CE 4.0.
The SQL Server DQE uses a compatibility mode to determine for which database
to generate th SQL so the SQL matches the supported features for the SQL
Server version used. You can set the SQL Server DQE's compatibility mode in two different ways: using the application's .config file or use a code statement. For the application's
.config file method, please see Generated code - Application configuration through .config files
using the code method, please see for SelfServicing: Generated code - CommonDaoBase functionality
and for Adapter:
Generated code - DataAccessAdapter functionality
By default the compatibility mode is set to SQL
Server 2005 or higher
The SQL differences per compatibility mode
Setting the compatibility level has the following effect on the
- the sequence retrieval logic to use by default:
- @@IDENTITY with compatibility level SqlServer7, SqlServerCE3x,
- SCOPE_IDENTITY() with compatibility level SqlServer2000,
ability to use NEWSEQUENTIALID(), with compatibility level SqlServer2005,
- the SQL produced for a paging query
- using a temp table approach with compatibility level SqlServer7,
- a CTE
approach is used with compatibility level SqlServer2005
- an OFFSET/FETCH using query with compatibility level
- a client-side paging approach (skip rows on datareader/close
datareader when page is read) with compatibility level
SqlServerCE3x, SqlServerCE35, SqlServerCE40
- the TOP value in a SELECT query:
- a parameter is used with compatibility level SqlServer2005,
- an in-line constant without brackets (e.g. SELECT TOP 5 ... )
with compatibility level SqlServer7, SqlServer2000
- an in-line constant with brackets (e.g. SELECT TOP(5) ... ) with
compatibility level SqlServerCE35, SqlServerCE40
- a client-side limiter approach (read TOP rows from datareader,
close datareader afterwards) with compatibility level SqlServerCE3x
SQL Server specific: ArithAbort support
When you're using indexed views in your database, and you're inserting data into tables which are used in these indexed views, you'll run into the problem that
you have to set ARITHABORT ON before the particular insert statement is executed. To signal that the SQL Server DQE has to emit the ARITHABORT statement prior to an
insert statement, you can use the ArithAbort flag implemented on the CommonDaoBase class (SelfServicing) or DataAccessAdapter class (Adapter).
Please see for SelfServicing: Generated code - CommonDaoBase functionality
and for Adapter:
Generated code - DataAccessAdapter functionality
SQL Server specific: Spatial types / User Defined Types support
SQL Server 2005 or higher supports User Defined Types (UDTs) written in a CLR language like C# or VB.NET. The SQL Server driver can read these fields and if you're using
UDTs in your tables, the fields which have a UDT as their type will be read by the driver and their UDT type is considered their valid type.
Entities mapped onto these tables (or views) have fields which .NET type is equal to the UDT of the target field in the table/view they're mapped on. The
generated entity classes will have properties which refer to the UDT type as the type of the property, as the UDT is a normal CLR type.
In these situations you have to reference the assembly which contains the UDT in your generated Visual Studio.NET project. (For Adapter, the database generic project).
Usage of the field in .NET code is like any other code: you can set the field to an instance of the UDT type and normally save it and load it.
Saving a UDT requires that the UDT is serializable
to string, which is a requirement for SQL Server as well.
mechanism is used for loading and persisting Spatial Typed data.
SQL Server specific: SQL Server CE Desktop support
LLBLGen Pro supports SQL Server CE Desktop v3.1 or higher. SQL Server CE Desktop is the win32 runnable version of the same database known from the compact framework, SQL Server CE 3.0. SQL Server CE Desktop is SQL Server CE v3.1 or higher, but embeds roughly the same features as SQL Server CE 3.0 or higher for the compact framework: no stored procedures, a single schema and no meta-data retrieval.
It's recommended that you use the latest CE Desktop version, as it contains more features.
To be able to target SQL Server CE Desktop, you first has to create a SQL Server project.
Stored procedures aren’t supported on CE Desktop, although they might be generated into the generated code. LLBLGen Pro uses the normal SQL Server DQE assembly for query production for CE Desktop. You also have to specify the compatibility level for the DQE, to signal it that it has to generate queries for SQL Server CE. For more information about this compatibility level, please see Generated code - Application configuration through .config files.
To be able to connect to a SqlServerCE desktop database, one has to adjust the connection string, as this connection string is the one used to connect to the SQL Server catalog from which the LLBLGen Pro project was created. It has to have the format shown by the following example:
<add key="Main.ConnectionString" value="data source=c:\pathtodb\databasename.sdf;"/>
As SQL Server CE Desktop doesnt support multiple catalogs nor
multiple schemas, these features arent available. Also System.Transactions transactions aren’t supported. All other LLBLGen Pro native features, like dependency injection, validation, authorization etc. are supported.
Linq is supported with SQL Server CE Desktop v3.5 or higher, however there are limitations in SQL Server CE Desktop which make it a bit of a struggle. For example the lack of scalar query support can lead to a lot of errors at runtime because a scalar query in a projection or WHERE clause isn't supported by SQL Server CE Desktop.
SqlServerCe provider registration
It's not necessary to reference System.Data.SqlServerCe.dll
, however on the machine the application is ran which uses compatibility level 3 or 4, this dll has to be installed as documented in the SQL Server CE Desktop documentation about deployment: via the .msi shipped with SQL Server CE Desktop. If you can’t run this .msi installer, be sure your application’s .config file contains the appropriate provider registration for the
. (this information is installed in the machine.config file by the .msi installer of SQL Server CE Desktop).
More details about this are available in the SQL Server CE Desktop documentation (the 'Books online' documents of SQL Server CE Desktop)
SQL Server specific: SQL Azure support
LLBLGen Pro supports SQL Azure out of the box, at runtime. To make your
application run on SQL Azure you have to do the following:
- Use Catalog name overwriting to overwrite your catalog's name to "".
See Catalog Name
Overwriting in application config files for details how to do this.
This works for Selfservicing and adapter. As 'old' catalog name you
specify the name of the catalog you're using in the project, e.g. 'Northwind'.
As 'new' catalog you specify the empty string: "".
- Use only one catalog in your project. If you use multiple catalogs
in your project, they'll be seen as one catalog on SQL Azure, as there's
no catalog name allowed in SQL queries.
- It's recommended to stick to the default schema 'dbo'. If you have
used a different schema in your project and you use 'dbo' on SQL Azure,
use Schema Name Overwriting (similar to catalog name overwriting) in
your application's config file. If you want to use a different schema on
SQL Azure, use ALTER USER username WITH DEFAULT_SCHEMA =
schemaname; on the SQL Azure database to set it as the default
Oracle specific: Ansi joins
By default, the Oracle DQEs will use Ansi joins, i.e. LEFT/INNER/RIGHT
OUTER JOIN syntax. To switch this off, and use join syntaxis like SELECT .. FROM A, B, C,
you can switch this off by using a setting in the application's .config file. Please see:
Generated code - Application configuration through .config files for the details.
Oracle / Firebird specific: Trigger based sequence values
It can be that your project's Oracle or Firebird database schema is used by multiple applications, among them your LLBLGen Pro based software. This can give the situation that you've to
deal with the situation that the schema is configured to use triggers
to insert sequence values on row insert. To tell the Oracle DQE or Firebird DQE of choice that this
is the case, and thus that it shouldn't ask for a new sequence value when a new entity is inserted, you've to add a setting to the application's .config file.
Please see: Generated code - Application configuration through .config files
for the details.