DML Options

In This Topic

To increase a configurability of dotConnect for SQLite as Entity Framework provider and provide more flexibility in behavior, we have added a number of DML options that influence the INSERT/UPDATE/DELETE commands and stored procedure calls.

Currently this feature is not yet supported for Entity Framework Core. It is supported only for Entity Framework v1 - v6.

Except for the batch updates settings, which are described in the Batch Updates topic, the list of DML options includes:

ReuseParameters - enables reusing existing parameters instead of creating new ones. For example, if the batch contains 30 INSERT statements, inserting the number 1024, one :p1 parameter will be used instead of creating 30 :p1 - :p30 parameters. Set this property to true only if a large number of the same values is sent to the database. Otherwise, it may even cause performance loss, if there are many parameters in a batch.
ParametersAsLiterals - determines if parameter values are inserted into SQL statements as literals instead of parameters. This behaviour is implemented in Connector .NET and can improve performance a bit in some rare cases.
InsertNullBehaviour. NULL values can be inserted in different ways. This configuration property allows the developer to determine the behaviour suitable for his particular application. Here is the list of possible alternatives:
- InsertNull. In this case NULLs are inserted explicitly (for each column that do not have a non-NULL value specified) like in the following example:
```
INSERT INTO "Company"
  ("CompanyID","CompanyName","Web","Email","Address_AddressTitle","Address_Address",
  "Address_City","Address_Region","Address_PostalCode","Address_Country",
  "Address_Phone","Address_Fax","PrimaryContact_ContactID","PersonContact_ContactID")
VALUES
  (:p0,:p1,:p2,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL)
```
- Omit. In this case provider simply omits all columns that do not have non-NULL values like in the following example:
```
INSERT INTO "Company"("CompanyID","CompanyName","Web")VALUES(:p0,:p1,:p2)
```
- InsertDefaultOrNull. In this case provider determines if there is a default value specified in the SSDL part of the model, and if it is available, this value is persisted to the database. If there is no default value specified, the behaviour is identical to the InsertNull one.
- InsertDefaultOrOmit. In this case provider determines if there is a default value specified in the SSDL part of the model as well. However, if there is no default value specified for a column, it is omitted from the INSERT command completely.
Please note that the last two options work with both common DefaultValue SSDL attribute and the new custom devart:DefaultValue SSDL attribute. The latter attribute does not trigger the type consistency check, so you can use a wider range of default values, like date('now').

Here is a simple example:
```
<Property Name="CreatedDate" Type:TIMESTAMP Nullable="false" 
devart:DefaultValue="date('now')" StoreGeneratedPattern="Identity" />
```
The generated SQL command looks like the following:
```
INSERT INTO "Product"
  ("CreatedDate","ProductName","UnitScale","InStock","Price","DiscontinuedPrice")
  VALUES(date('now'),:p0,:p1,:p2,:p3,:p4)
```
If the InsertNullBehaviour is set to InsertNullBehaviour.InsertDefaultOrNull or InsertNullBehaviour.InsertDefaultOrOmit, and the column has DefaultValue, then the following rules are applied when executing INSERT:
- For Primary Key columns, DefaultValue is always inserted.
- For a column with StoreGeneratedPattern, DefaultValue is always inserted too.
- For a nullable column without StoreGeneratedPattern, if the value is not NULL, then this value is inserted, otherwise, the DefaultValue is inserted.
- For a not null column without StoreGeneratedPattern of .NET reference type (string, byte[]), if the value is not NULL, then this value is inserted, otherwise, the DefaultValue is inserted.
- For a not null column of value type (Int32, DateTime, etc) without StoreGeneratedPattern, the value cannot be NULL, so this value is inserted.

These options can be set either in code as the properties of the EntityProviderConfig.DmlOptions object or in the project config file as the attributes of the DmlOptions element of the Devart.Data.SQLite.Entity tag.

The example code that enables the ReuseParameters option:

[email protected]