Oracle 12c Support and More New Features in New Versions of Devart Products

Devart releases new versions of its products: LinqConnect ORM solution, dotConnect ADO.NET data providers, and ORM designer – Entity Developer. The main new feature in dotConnect for Oracle, LinqConnect, and Entity Developer is the support for the latest release of Oracle Database – Oracle 12c. Additionally, dotConnect data providers offer you Sync Framework support, improved PgSqlDump component, load balancing support in dotConnect for MySQL, and other features. This article describes the new features in details.

Oracle 12c Support

Array Binding

dotConnect for Oracle now supports returning the numbers of affected rows for each array element when using array binding. For this, OracleCommand class offers an overload with the second out parameter:

public int ExecuteArray(int iters, out long[] rowsAffected)

This overload returns the numbers of affected rows for each array element in this out parameter.

This method requires Oracle 12c (both client and server) and works only in OCI mode. We will make a simple example of code using this method. This example uses the Dept table. Here is the DDL script of this table:

CREATE TABLE SCOTT.DEPT (
  DEPTNO NUMBER(2),
  DNAME VARCHAR2(14),
  LOC VARCHAR2(13),
  CONSTRAINT PK_DEPT PRIMARY KEY (DEPTNO)
);

The example executes an INSERT and UPDATE commands using Array Binding and outputs the numbers of rows affected for each array element. Here is the example code:

OracleConnection conn = new OracleConnection(
   "host=<HOST>;user id=<USER ID>;password=<PASSWORD>;"
);
conn.Open();

OracleCommand comm = new OracleCommand(
   "INSERT INTO dept VALUES(:deptno_p, :dname_p, :loc_p)", 
   conn
);

comm.Parameters.Add("deptno_p", OracleDbType.Integer);
comm.Parameters.Add("dname_p", OracleDbType.VarChar);
comm.Parameters.Add("loc_p", OracleDbType.VarChar);

// Set parameters values
comm.Parameters["deptno_p"].Value = new int[] { 10, 20, 30, 40 };
comm.Parameters["dname_p"].Value = 
   new string[] { "ACCOUNTING", "ACCOUNTING", "SALES", "OPERATIONS" };
comm.Parameters["loc_p"].Value = 
   new string[] { "NEW YORK", "DALLAS", "CHICAGO", "BOSTON" };

long[] rowAffected;

// Insert four records at one time
comm.ExecuteArray(4, out rowAffected);

Console.WriteLine("Row affected during insert command:");
if (rowAffected == null)
   Console.WriteLine("Unable to get the numbers of affected rows because Oracle " +
      "server or client are of version lower than 12c or Direct mode is used.");
else
{
   Console.WriteLine("Row affected during insert command:");
   foreach (long l in rowAffected)
       Console.WriteLine(l);
}
Console.WriteLine();

comm = new OracleCommand("Update dept set loc=:loc_p where dname=:dname_p", conn);
comm.Parameters.Clear();

comm.Parameters.Add("dname_p", OracleDbType.VarChar);
comm.Parameters.Add("loc_p", OracleDbType.VarChar);

comm.Parameters["dname_p"].Value = 
   new string[] { "ACCOUNTING", "RESEARCH", "SALES", "OPERATIONS" };
comm.Parameters["loc_p"].Value = 
   new string[] { "NEW YORK2", "DALLAS2", "CHICAGO2", "BOSTON2" };

comm.ExecuteArray(4, out rowAffected);

Console.WriteLine("Row affected during update command");
if (rowAffected == null)
   Console.WriteLine("Unable to get the numbers of affected rows because Oracle " + 
      "server or client are of version lower than 12c or Direct mode is used.");
else
{
   Console.WriteLine("Row affected during update command:");
   foreach (long l in rowAffected)
       Console.WriteLine(l);
}

Note that you should replace the connection string value with the actual connection string value for your server in order for this sample to work.

Auto Increment Identity Columns

Generation of Auto Increment identity columns is now supported when generating DDL code for ORM models using Model-First approach. It is supported for both Entity Framework and LinqConnect models. Code-First/Code-First Migrations also can generate columns with the identity clause ‘GENERATED AS IDENTITY’ for properties with ‘Store Generated Pattern=Identity’. This generation is supported in dotConnect for Oracle, LinqConnect, and Entity Developer.

For example, we create an Entity Framework model with a single entity class Department. The class will have the three properties: ID (an entity key property of int16 type having StoreGeneratedPattern=”Identity”), and two string properties Name and Location of the length 20 and 30 respectively. Older dotConnect for Oracle versions generate the following DDL (with default settings) for such a class:

-- 
-- Creating a table DEMOBASE."Departments" 
-- 
CREATE TABLE DEMOBASE."Departments" (
   "DeptID" NUMBER(5) NOT NULL,
   "Name" VARCHAR2(20 CHAR) NULL,
   "Location" VARCHAR2(30 CHAR) NULL,
   CONSTRAINT "PK_Departments" PRIMARY KEY ("DeptID")
);

-- 
-- Creating a sequence DEMOBASE."AI$Departments_DeptID_seq" 
-- 
CREATE SEQUENCE DEMOBASE."AI$Departments_DeptID_seq" START WITH 1 INCREMENT BY 1;

-- 
-- Creating a trigger DEMOBASE."AI$Departments" 
-- 
CREATE OR REPLACE TRIGGER DEMOBASE."AI$Departments"
   BEFORE INSERT ON DEMOBASE."Departments"
   FOR EACH ROW
   BEGIN
      SELECT DEMOBASE."AI$Departments_DeptID_seq".NEXTVAL INTO :NEW."DeptID" FROM DUAL;
   END;

The newer dotConnect for Oracle version generates the same DDL if the target Oracle server has a version lower than 12c. For Oracle 12c version it generate the following DDL, which is simpler and more clear:

-- 
-- Creating a table DEMOBASE."Departments" 
-- 

CREATE TABLE DEMOBASE."Departments" (
   "DeptID" NUMBER(5) GENERATED AS IDENTITY,
   "Name" VARCHAR2(20 CHAR) NULL,
   "Location" VARCHAR2(30 CHAR) NULL,
   CONSTRAINT "PK_Departments" PRIMARY KEY ("DeptID")
);

SQL Generation for LINQ Queries That Use Paging

LinqConnect and dotConnect for Oracle now use the new Oracle 12c language constructs when generating SQL queries for LINQ queries that use paging. For example, for the following query (using the same Department entity as in the previous example):

var query = ctx.Departments.OrderBy(d => d.DeptID).Skip(1).Take(3).ToList();

The following SQL is generated:

SELECT 
"Extent1"."DeptID",
"Extent1"."Name",
"Extent1"."Location"
FROM DEMOBASE."Departments" "Extent1"
ORDER BY "Extent1"."DeptID" ASC
OFFSET 1 ROWS FETCH NEXT 3 ROWS ONLY

Compare it with the SQL for Oracle servers of version lower than 12c:

SELECT 
"Extent1"."DeptID",
"Extent1"."Name",
"Extent1"."Location"
FROM (
	SELECT 
	"Extent1"."DeptID",
	"Extent1"."Name",
	"Extent1"."Location", row_number() OVER (ORDER BY "Extent1"."DeptID" ASC) AS "row_number"
	FROM DEMOBASE."Departments" "Extent1"
	ORDER BY "Extent1"."DeptID" ASC
) "Extent1"
WHERE "Extent1"."row_number" > 1 AND ROWNUM <= 3
ORDER BY "Extent1"."DeptID" ASC

New Connection Modes

Oracle 12c introduces more connection modes in addition to normal, SYSDBA, SYSASM, and SYSOPER. It adds SYSDG, SYSKM, and SYSDEBUG modes.

SYSBACKUP mode is intended for performing backup and restore operations and provides the necessary privileges for it. SYSDG mode allows managing Oracle Data Guard, and SYSKM mode facilitates Transparent Data Encryption keystore operations.

Support for New Size of Oracle String Types

dotConnect for Oracle, LinqConnect, and Entity Developer now generate DDL using the new maximal size of Oracle string types, introduced in Oracle 12c, when working with the corresponding server version. Suppose we have created the following entity class using Entity Framework Code First approach:

public class Item {

  [Key]
  [DatabaseGenerated(DatabaseGeneratedOption.None)]
  public int Id { get; set; }

  public string StringDefault { get; set; }

  [MaxLength(300)]
  public string String300 { get; set; }

  [MaxLength(12000)]
  public string String12K { get; set; }

  [Column(TypeName="nvarchar2")]
  public string StringNVarchar2 { get; set; }

  [Column(TypeName="varchar2")]
  public string StringVarchar2 { get; set; }

  public byte[] BinaryDefault { get; set; }

  [MaxLength(300)]
  public byte[] Binary300 { get; set; }

  [MaxLength(12000)]
  public byte[] Binary12K { get; set; }

  [Column(TypeName="raw")]
  public byte[] BinaryRaw { get; set; }   
}

When working with Oracle 12c, the following DDL will be generated for the corresponding table:

CREATE TABLE "Items" ( 
 "Id" NUMBER(10) NOT NULL,
 "StringDefault" NCLOB NULL,
 "String300" NVARCHAR2(300) NULL,
 "String12K" NVARCHAR2(12000) NULL,
 "StringNVarchar2" NVARCHAR2(16383) NULL,
 "StringVarchar2" VARCHAR2(32767 CHAR) NULL,
 "BinaryDefault" BLOB NULL,
 "Binary300" RAW(300) NULL,
 "Binary12K" RAW(12000) NULL,
 "BinaryRaw" RAW(32767) NULL,
 PRIMARY KEY ("Id")
);

Compare it with the DDL for the previous Oracle versions:

CREATE TABLE "Items" ( 
 "Id" NUMBER(10) NOT NULL,
 "StringDefault" NCLOB NULL,
 "String300" NVARCHAR2(300) NULL,
 "String12K" NCLOB NULL,
 "StringNVarchar2" NVARCHAR2(2000) NULL,
 "StringVarchar2" VARCHAR2(4000 CHAR) NULL,
 "BinaryDefault" BLOB NULL,
 "Binary300" RAW(300) NULL,
 "Binary12K" BLOB NULL,
 "BinaryRaw" RAW(2000) NULL,
 PRIMARY KEY ("Id")
);

Sync Framework Support

dotConnect for Oracle, dotConnect for MySQL, dotConnect for PostgreSQL, dotConnect for SQLite, and dotConnect for DB2 now provide support for Sync Framework 2.1.

Sync Framework Overview

Microsoft Sync Framework is a technology for data synchronization between any applications, services or devices. Sync Framework uses synchronization services for connecting to each of the synchronizing databases. Sync Framework database providers, similar to ADO.NET providers, allow other Sync Framework components to work without the need to concern on the unerlying database implementation.

The main Sync Framework classes are the following:

• SyncOrchestrator – creates and manages synchronization sessions. This class is provided by Sync Framework.
• DbSyncProvider – implements the synchronization service for interaction with a database and shields SyncOrchestrator from the specific implementation of the database. Each of the dotConnect data providers provides its own implementation for the corresponding database with the corresponding prefix. For example, dotConnect for Oracle provides OracleSyncProvider class.
• DbSyncAdapter – maintains the interaction between the DbSyncProvider and the database. This class is provided by Sync Framework.

When the synchronization is performed, the data in the specified synchronization scope is synchronized between the specified two databases. Synchronization scope is one or more tables (with the specified columns to synchronize) that will be synchronized as a whole. The synchronization scope can include all columns in a table or just a subset of them. It can include all rows or only the rows that match some filter condition. You also can create a template for filters (a condition including a variable), and then create multiple synchronization scopes using filters based on this template.

Before the data can be synchronized, the database must be provisioned.

Database Provisioning, Deprovisioning, and Synchronization

To include database data to synchronization, first you need to provision the database. Provisioning means the creation of Sync Framework tables, stored procedures and functions (except for SQLite), and triggers for tracking data changes in the specified synchronization scope – a table or a group of tables to synchronize data from. Both databases must be provisioned to synchronize data between them.

dotConnect data providers have their own classes for provisioning a scope:

• OracleSyncScopeProvisioning in dotConnect for Oracle
• MySqlSyncScopeProvisioning in dotConnect for MySQL
• PgSqlSyncScopeProvisioning in dotConnect for PostgreSQL
• SQLiteSyncScopeProvisioning in dotConnect for SQLite
• DB2SyncScopeProvisioning in dotConnect for DB2

After the provisioning, the databases can be synchronised. When synchronizing databases, you may retrieve synchronization operation statistics. It includes the time synchronization took, the number of processed changes and failed changes.

In case there is no need to synchronise a database or any specific scope any more, the database (or scope) can be deprovisioned. This means deleting of the unneeded Sync Framework database objects and/or data. User tables and data are retained. dotConnect data providers have their own classes for database deprovisioning.

The parent class of dotConnect SyncProvider classes is the descendant of the standard Sync Framework DbSyncProvider class. Other synchronization classes of our providers are not inherited from the standard Sync Framework classes, but provide the same interface.

Our providers also offer DescriptionBuilder classes to retrieve the description of already provisioned scope from the database.

In order to synchronize two database you must provision the corresponding scopes in them, create an instance of the SyncOrchestrator and assign the instances of the corresponding SyncProvider classes, initialized with the required scope names and connections, to its RemoteProvider and LocalProvider properties. Then you just simply call the Synchronize method of the SyncOrchestrator class.

Provisioning, deprovisioning, and synchronization are described in more details in the documentation of our providers. You can see the detailed description with code examples in the tutorials:

• for Oracle
• for MySQL
• for PostgreSQL
• for SQLite
• for DB2

Support for Load Balancing in MySQL

dotConnect for MySQL now supports load balancing. It allows you to specify several hosts with their ports in the Host parameter of the connection string, like “Host = (localhost:3307,db:3308,db)“. If several hosts are specified, dotConnect for MySQL will switch to the next host in the list for each new SELECT or SHOW statement. The first host in the list is considered the master host. All statements other than SELECT and SHOW will be executed against it.

Note: If the Port is also specified as the separate connection string parameter, this port value will be used as default for all hosts in the Host parameter with no port specified.

In case of using LocalFailover if a connection lost, dotConnect for MySQL tries to implicitly reconnect and reexecute the operation. If load balancing is used, and the operation is the SELECT or SHOW query execution, dotConnect for MySQL tries to reconnect to the next server in the list, not to the same one.

If connection pooling is enabled when using load balancing, the pooling is used in usual way. When a connection to a specific host is closed, it is placed in the pool instead of closing, and when it is opened, if there is available connection in the pool with the same connection settings, it is taken from the pool instead of opening a new one.

PgSqlDump Improvements

The new version of dotConnect for PostgreSQL provides the improved PgSqlDump component. The PgSqlDump component now supports generating INSERT statements using the multirow VALUES syntax. You can enable or disable it using the UseMultirowSyntax property. If this property is set to true, you may limit the size of generated INSERT statements with the CommitBatchSize property. It specifies the max size of a generated INSERT statement in bytes.

Another new feature of the PgSqlDump component is support for asynchronous backup and restore operations. PgSqlDump provides the following methods for these operations: BeginBackup, BeginBackupQuery, BeginRestore, EndBackup, EndBackupQuery, and EndRestore. These method are used in the following way: the methods with the “Begin” prefix return a System.IAsyncResult interface representing the asynchronous operation started by calling the method. First you call this method, and it starts the asynchronous operation in another thread, and the program flow continues. When you are ready to finish the operation, call the corresponding method with the “End” prefix and pass the IAsyncResult to it. If at the moment you call this function the operation has not yet been finished, application stops and waits till the function returns.

SQLite Online Backup API Support

Previously dotConnect for SQLite offered the SQLiteDump component for backing up a SQLite database to a DDL/DML script. Now you can use also SQLite Online Backup API for backing up a database with dotConnect for SQLite. Backup is invoked with the BackupDatabase method of the SQLiteConnection class.