FairCom DB SQL ADO.NET Data Provider

This section provides details about using the FairCom DB SQL ADO.NET Data Provider. Here you will find information on the required format of the connection string, supported data types and brief examples to quickly connect and use FairCom DB SQL databases.

• Support for Entity Framework 6.x (EF6) is implemented in a DLL named Ctree.Data.EntityFramework.dll. See Entity Framework 6 Support.
• Support for EF2 through EF4 is available as the Ctree.Data.SqlClient.Entity namespace inside the ADO.NET provider DLL named Ctree.Data.SqlClient.dll. See ADO.NET Entity Framework V2 - V4 Support.

Features

The following are key features of the FairCom DB SQL ADO.NET Data Provider:

• Advanced design-time wizards for Microsoft Visual Studio providing complete Entity Framework 6 support
• Supports all current FairCom DB data types, including LVARCHAR and LVARBINARY
• Supports stored procedure calls
• High performance
• Composed of pure managed code and is CLS-compliant
• Supports Embarcadero CodeGear

All Exception() derived classes in the ADO.NET provider have the [SerializableAttribute] to make them serializable in V11 and later.

Architecture

The FairCom DB ADO.NET Data Provider is referenced with the Ctree.Data.SqlClient.dll assembly.

There are core classes composing the FairCom DB ADO.NET Data Provider. The following table describes these classes and their methods.

FairCom DB SQL ADO.NET Connection String

ADO.NET connection strings are passed into CtreeSqlConnection objects as semicolon-separated Name-Value pairs. The Name portion of the Name-Value pair is not case-sensitive.

The following table lists Name-Value pairs used in connection strings for the FairCom DB SQL ADO.NET Data Provider.

You can use any of the synonyms as a replacement for the Name portion of the pair to remain cross-compatible with usage of other database products. The most common synonyms are shown below.

The minimum required are User ID and Password. If other values are not specified, default values will be assigned as noted.
 

Example

A typical connection string for the FairCom DB ADO.NET Data Provider would be composed of:

"User ID=ADMIN;Password=ADMIN;database=ctreeSQL;server=localhost;port=6597”

A minimal connection string can be:

"User=ADMIN;Password=ADMIN"

TLS/SSL Examples

"UID=ADMIN;PWD=ADMIN;Database=ctreeSQL;Server=localhost;Service=6597;ssl=basic";
"UID=ADMIN;PWD=ADMIN;Database=ctreeSQL;Server=localhost;Service=6597;ssl=peerAuthentication;sslcert=support.faircom.com";
"Database=ctreeSQL;Server=localhost;Service=6597;ssl=peerAuthentication;sslcert=support.faircom.com;ClientSSLCert=ADMIN";

Timeout

The following connection string sets the timeout to never expire. In this example, "Server=faircom2" is a bad server name:

conn.ConnectionString = "ConnectionTimeout=0;UID=ADMIN;PWD=ADMIN;Database=ctreeSQL;Datasource=db1.company.com;Service=6597"; 

You might expect your ADO.NET application to wait indefinitely before it returns. However, several errors are not affected by the timeout setting in this string, such as:

• the server name is a bad name
• the server is down
• the server is unreachable

The actual error returned by the timeout setting in this string is generated only if the server can establish the connection, but, it is very busy or the network is very slow to respond.

In the case above, with a bad server name, an error is returned in 5 seconds. (This value is not configurable in the connect string.)

TCP Keepalive Interval

Enabling a TCP Keepalive provides periodic activity on a link to help achieve 2 goals:1) prevent network hardware from identifying a connection as idle and silently terminatiing it, 2) distinguish between server operations that take a long to respond and broken network links.

connectionidletimeout Connection Option for ADO.NET Provider

The connectionidletimeout connection-string option is available to indicate how long the connection may stay idle in the pool before getting closed and removed from the pool. This option is separate from the existing connection timeout option, which has a different meaning in the ADO connection pool.

ADO.NET Support for TLS

In V11.5 and later, the FairCom DB ADO.NET provider supports TLS/SSL connections per Microsoft specifications.

The ADO.NET provider uses the local certificate store to locate certificates when using peerAuthentication. The server's CA (or a self-signed) certificate ctsrvr.pem must be added to the trusted root certificate store on the client machine for the .NET framework's certificate authentication to succeed:

CertMgr.exe /add ctsrvr.pem /c /s /r localMachine root

Note that the Common Name specified in the server certificate is the name that the application must specify in the ADO.NET connection string for the TLS option.

For this certificate, we used support.faircom.com as the Common Name, and so the ADO.NET connection string must specify sslcert=support.faircom.com for the TLS authentication to succeed.

If client certificate authentication is desired (V13.1 and later), a certificate chain and private key for the client must first be added to the current user's personal certificate store. This can be done using a PKCS #12 file containing the desired certificate chain and private key.

Connection String

The ADO.NET connection string is similar to the JDBC string. The connection string accepts a new property:

ssl=<value>

which can have two values:

• basic - Basic SSL setting, no peer certificate authentication, only communication encryption as requested by server certificate
• peerAuthentication - Server certificate authentication.

In the case of peerAuthentication the server certificate Common Name must be provided by the new property:

sslcert=<value>

If this property is not specified, the value of the Server setting is used to match the certificate.

If peerAuthentication is enabled, client authentication may also be attempted. If X509_AUTHENTICATION is enabled by the server then a client certificate can replace the normal password based authentication. Identify the Common Name for the client certificate to use:

ClientSSLCert=<value>

The current user's personal certificate store is searched first for this certificate. The localmachine store will also be searched if a matching certificate is not found in the current user's store and the process has permission to access the localmachine's personal store.

If X509_AUTHENTICATION is enabled by the server and the ClientSSLCert is accepted, any UID and PWD values included in the connection string are ignored.

Examples:

"UID=ADMIN;PWD=ADMIN;Database=CtreeSQL;Server=localhost;Service=6597;ssl=basic";
"UID=ADMIN;PWD=ADMIN;Database=CtreeSQL;Server=localhost;Service=6597;ssl=peerAuthentication;sslcert=support.faircom.com";
"Database=CtreeSQL;Server=localhost;Service=6597;ssl=peerAuthentication;sslcert=support.faircom.com;ClientSSLCert=JohnDoe";

Data Type Mapping - C# Edition

The following table lists the mapping between FairCom DB SQL data types and the .NET Framework types. It also lists DbTypes and .NET Framework typed accessors:

1The time portion of DateTime is set to hour = 0, minute = 0, second = 0 and milliseconds = 0.

2The date portion of DateTime is set to year = 1, month = 1 and day = 1.

ADO.NET Entity Framework

The FairCom DB SQL ADO.NET Data Provider has support for Entity Framework through V7.

System Requirements

The minimum development system requirements for FairCom DB SQL ADO.NET Entity Framework support are listed below. Note that they require the complete version (e.g., the complete version, not just the "client" version):

• Visual Studio 2008 Service Pack 1 or greater
• FairCom DB V11.5 requires Microsoft .NET 4.0 Framework.
• FairCom DB V11.0 requires Microsoft .NET 4.0 Framework.
• FairCom DB V10.3 requires Microsoft .NET 4.0 Framework.
• FairCom DB V10 requires at least Framework Version 3.5 SP1

Auto Incrementing Field Type Restriction

Entity Framework Models allow Int16, Int32 or Int64 field types to be specified as Auto Incrementing in model design. Auto Incrementing fields are also known as Identity fields in some schemas.

FairCom DB SQL allows one user Auto Incrementing field type. Note that FairCom DB already supported a serial segment field, currently used by default as the ROWID value. As there is a limitation of one SRLSEG field type per data file (table), this precluded the addition of a user-defined field. An IDENTITY attribute is now available for this purpose.

Other Known Limitations

The following are other known FairCom DB SQL limitations that can be encountered when using Entity Framework support. These are in various stages of development. Contact your nearest FairCom office for the latest information concerning specific support for these items.

• The SKIP operator is not currently supported. The SKIP operator is commonly used with the TOP operator for “paging” purposes.
• The EXCEPT operator is not currently supported.
• Parameters are not currently supported in functions and in the TOP operator.
• BIT (Boolean) columns can currently only be tested against 1 or 0 (that is, if ( bitColumn == 1 ). Entity Framework requires a test against true/false (for example, if ( bitColumn == true ) or more simply if ( bitColumn )

Entity Framework 6 Support

Support for Entity Framework 6.x (EF6) is implemented in a separate DLL named Ctree.Data.EntityFramework.dll.

Support for previous versions, up to EF4, is available as the Ctree.Data.SqlClient.Entity namespace inside the ADO.NET provider DLL (Ctree.Data.SqlClient.dll). See ADO.NET Entity Framework V2 - V4 Support.

To test or use the EF6 support, the following steps must be taken:

Note: This support has been tested with Visual Studio 2012 and 2013.

Be sure your FairCom DB package is installed. If you used the .zip installer, be sure you have executed <faircom>\tools\SetUp\FairComConfig.exe to register the ADO.NET driver.

1. Start Visual Studio 2012 (or newer).
2. Create a new Console Application project.
3. Right-click the project node and select Manage NuGet Packages.
4. From the packages list, select Entity Framework. This step is required to install the latest EF6 package in Visual Studio 2012 (Visual Studio 2013 should already have this package built-in).
5. Open the app.config file and replace the following line:
   1. <provider invariantName="System.Data.SqlClient" type="System.Data.Entity.SqlServer.SqlProviderServices, EntityFramework.SqlServer" />
   2. with:
   3. <provider invariantName="Ctree.Data.SqlClient" type="Ctree.Data.EntityFramework.CtreeSqlProviderServices, Ctree.Data.EntityFramework" />
6. In Solution Explorer add a reference to the Ctree.Data.EntityFramework.dll located by default in the \FairCom\V10.4.0\win*\bin\sql.ado.net directory (you may also remove the reference to EntityFramework.SqlServer).
7. Click Build > Rebuild Solution.
8. Right-click the project node and select Add > New Item.
9. From the Data category select ADO.NET Entity Data Model.
10. Select Generate from Database.
11. If no previous connection is present, click New Connection.
12. The FairCom DB SQL datasource should appear. If it does not:
    1. Click Change.
    2. Select FairCom DB SQL from the list of available datasources.
    3. If the FairCom DB SQL datasource is not listed among the available datasources, you did not properly register the datasource for the version of Visual Studio you are using. Please refer to ADO.NET installation documentation for information on how to register the datasource. For a quick registration in Visual Studio 2012, locate the FairComDDEXProvider_v11_Config.reg file in the win*\bin\sql.ado.net directory, double-click it, and click OK on all confirmation dialogs.
13. Check the values in the Connection Properties dialog and adjust them if needed (you can click Advanced for additional connection string options).
14. Click Test Connection to verify that the provider can successfully connect to the FairCom Server.
15. Click Next and you should be able to select one or more tables from the list of available ones and click Finish.
    1. Remember that, to properly work in the EF6 environment, the tables should have a "primary key" defined.

If everything works as expected, a model (.edmx file) will be generated.

Visual Studio DDEX Provider for FairCom DB

Note: This topic explains how the FairCom installer integrates the ADO provider inside Visual Studio. You should not need to perform these procedures by hand. If you have problems, you can refer to this topic to check that things are properly set up.

The DDEX Provider for FairCom ACE provides integration of FairCom .NET Data Adapter into Visual Studio.

Before you can use it, the following steps must be completed:

1. The FairCom .NET Data Adapter DLL (Ctree.Data.SqlClient.dll) must be put in the GAC (simply copy it to the C:\Windows\Assembly folder or use the Microsoft .NET Configuration tool).
2. The DLL in Step 1 must be registered in the machine.config file. The file should be located in the following directory:
   1. C:\WINDOWS\Microsoft.NET\Framework\v4.0.30319\CONFIG
   2. To register the provider, add the following to the machine.config file:

   3. <configuration>
      
          <configSections>
      
              ...
      
          <section name="ctree.data.sqlclient" type="System.Data.Common.DbProviderConfigurationHandler, System.Data, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" />
      
              ...
      
          <configSections>
      
      
      
          <system.data>
      
              <DbProviderFactories>
      
      ...
      
            <add name="FairCom DB SQL Data Provider" invariant="Ctree.Data.SqlClient" description=".Net Framework Data Provider for FairCom SQL" type="Ctree.Data.SqlClient.CtreeSqlClientFactory, Ctree.Data.SqlClient, Version=10.5.0.0, Culture=neutral, PublicKeyToken=0ce73727dc1039a8" />
      
      ...
      
              </DbProviderFactories>
      
      </system.data>
      
      ....
      
      </configuration>

3. Information should be added to the Windows registry. You can find this information in the FairCom DB Professional ReadMe First in the FairCom DB Professional Installed Components chapter:

If needed, the FairComDDEXProvider.reg file must be modified to set the correct CodeBase for the DDEX provider files (replace the current DLL path in the file with the correct path).

Attention: The path separator must be a double backslash ( \\ )