Generated code - Database specific features

Preface

This small section illustrates the different specific features which are available to you through configuration, either through the .config file of your application or through code. This section is more or less an aggregation of what's been discussed elsewhere as well so you won't miss a detail which could be of great benefit in your project. Also be sure to check the Application configuration through .config files for more details about features like catalog- and schema-name overwriting.

SqlServer specific: NEWSEQUENTIALID() support

When you're using unique_identifier types for primary keys on SqlServer 2005, you can benefit from the new feature of SqlServer 2005 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've 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. Before you save the entity set the SqlServer Dynamic Query Engine (DQE) in the SqlServer 2005 compatibility mode (see below). The DQE will then figure out to let the database insert the NEWSEQUENTIALID() produced value and it's automatically retrieved for you into the entity's PK.

SqlServer specific: compatibility mode

With the introduction of SqlServer 2005, it became necessary to signal the SqlServer DQE that it should use SqlServer 2005 specific features. This was a more appropriate step than to use a new codebase with solely SqlServer 2005 features as that would make running your code on SqlServer 2005 when it was first created for SqlServer 2000 a bit problematic.

You can set the SqlServer 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. For using the code method, please see for SelfServicing: Generated code - DbUtils functionality and for Adapter: Generated code - DataAccessAdapter functionality.

SqlServer 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've to set ARITHABORT ON before the particular insert statement is executed. To signal that the SqlServer DQE has to emit the ARITHABORT statement prior to an insert statement, you can use the ArithAbort flag implemented on the DbUtils class (SelfServicing) or DataAccessAdapter class (Adapter). Please see for SelfServicing: Generated code - DbUtils functionality and for Adapter: Generated code - DataAccessAdapter functionality.

SqlServer specific: User Defined Types support

SqlServer 2005 supports User Defined Types (UDTs) written in a CLR language like C# or VB.NET. The SqlServer 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 then 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 inside them which refer to the UDT type as the type of the property, as the UDT is a normal CLR type. In such a situation you've to reference the assembly which contains the UDT in your generated code 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.

SqlServer specific: SqlServer CE Desktop support

LLBLGen Pro v2.6 supports SqlServer CE Desktop v3.1 or higher, on .NET 2.0 or higher. SqlServer CE Desktop is the win32 runnable version of the same database known from the compact framework, SqlServer CE 3.0. SqlServer CE Desktop is SqlServer CE v3.1 or higher, but embeds roughly the same features as SqlServer 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, v3.5, as it contains more features.

To be able to target SqlServer CE Desktop, you first has to create a SqlServer project, similar to what you have to do for CF.NET support. Then, you have to select .NET 2.0 or higher as the target platform at tab 1 on the Generator Configuration dialog. CF.NET 1.0/2.0 or .NET 1.x aren’t supported for SqlServer CE Desktop.

Stored procedures aren’t supported, although they might be generated into the generated code. LLBLGen Pro uses the normal SqlServer DQE assembly for query production for CE Desktop. This is a change from v2.5 where a special DQE was shipped. You also have to specify the compatibility level for the DQE, to signal it that it has to generate queries for SqlServer CE. For more information about this compatibility level, please see Generated code - Application configuration through .config files.

When loading the generated VS.NET projects, the references to the SqlServer DQE for .NET 2.0 should be checked. If it's not correct, please correct the reference. 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 SqlServer 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\Northwind.sdf;"/>

As SqlServer CE Desktop doesn’t support multiple catalogs nor multiple schemas, these features aren’t available. Also COM+/System.Transactions transactions aren’t supported. All other LLBLGen Pro native features, like dependency injection, validation, authorization etc. are supported.

Linq is supported with SqlServer CE Desktop v3.5 or higher, however there are limitations in SqlServer 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 SqlServer CE Desktop.

SqlServerCe provider registration

It's no longer 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 SqlServer CE Desktop documentation about deployment: via the .msi shipped with SqlServer CE Desktop. If you can’t run this .msi installer, be sure your application’s .config file contains the appropriate provider registration for the DbProviderFactory. (this information is installed in the machine.config file by the .msi installer of SqlServer CE Desktop). More details about this are available in the SqlServer CE Desktop documentation (the 'Books online' documents of SqlServer CE Desktop)

Oracle specific: Ansi joins

Among the Oracle versions supported is Oracle 8i. Oracle 8i doesn't support LEFT/INNER/RIGHT OUTER JOIN syntax, or better: ansi join syntaxis, and requires syntaxis like SELECT .. FROM A, B, C. There are three Oracle DQE's: one for ODP.NET for 8i/9i, one for ODP.NET for 10g and one for the MS Oracle provider for 8i/9i/10g. The DQE for ODP.NET and 10g is pre-configured to use ansi join syntax, the other two aren't, they'll default to non-ansi syntaxis as supported by Oracle 8i. To switch these DQEs to use ansi-joins, a setting in the application's .config file has to be added to make the DQE use ansi joins instead. 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.