ADO.NET Data Provider

Exasol provides an ADO.NET Data Provider to connect .NET application to Exasol.

The data provider is tested with the following applications:

  • SQL Server Integration Services: SQL Server Integration Services are supported for Visual Studio 2015 and later versions. The SQL Server Integration Services work with the generic ADO.NET and ODBC drivers and don't need any extensions to be installed.

  • SQL Server Reporting Services (Data Processing Extension) and SQL Server Analysis Services (DDEX provider and pluggable SQL cartridge) are installed for:

    • Visual Studio 2015 (v 14.0) with SQL Server 2016 (v 13.0)

    • Visual Studio 2017 (v 15.0) with SQL Server 2017 (v 14.0)

    • Visual Studio 2019 (v 16.0) with SQL Server 2019 (v 15.0)

DDEX providers for SQL Server Analysis Services in Visual Studio 2017 and 2019 (v 14.0 and 15.0) are installed in EXAExtension.vsix by the Exasol ADO.NET setup. You must close all Visual Studio instances before this extension is installed.

System Requirements

  • You must have system administrator rights for the system where you are installing the data provider.
  • Microsoft .NET Framework 4.6.2 must be installed on your system.

The data driver is tested on the following operating systems:

  • Windows 7 (x86/x64)
  • Windows 10 (x86/x64)
  • Windows Server 2012 R2 (x86/x64)
  • Windows Server 2016 (x86/x64)
  • Windows Server 2019 (x86/x64)

Download and Install the ADO.NET Data Provider

  1. Download the latest driver from the Exasol Download Portal.

  2. Run the downloaded executable file and follow the setup wizard to complete the installation.

The data provider is installed in the global assembly cache and registered in the global configuration file of the .NET framework.

An additional tool Data Provider Metadata View (DPMV) is also installed. You can use this tool to check the connectivity and run simple metadata queries.

Use the Data Provider

  1. Select the data provider using either of the following two methods:
    • Select the entry Exasol Data Provider from the list of installed data providers.
    • Enter the invariant identifier Exasol.EXADataProvider to select the data provider.
  2. Connect to Exasol using a connection string with all the required information. The connection string is a sequence of keyword and value pairs separated by semicolons. For example, host=192.168.6.11..14/72975B4D331DEEB44FA41FB858A5417E163151BBFB45376574706800A8DCCE30:8563;UID=sys;PWD=exasol;Schema=test.

Supported keywords in connection strings

The following table describes all keywords that are supported in this version of Exasol. Keywords that are not listed in the table are not supported.

Keyword Description
authmethod

Specifies the authentication method for OpenID connections.

If the value for the parameter is accesstoken, the connection string will be "Server=<host>;Port=<port>;AuthMethod=accesstoken;PWD=<access_token>" and the password will be your OpenID Access Token.

If the value for the parameter is refreshtoken, the connection string will be "Server=<host>;Port=<port>;AuthMethod=refreshtoken;PWD=<refresh_token>" and the password will be your OpenID Refresh Token.

For more information, see Authentication using OpenID.
autocommit

Settings for autocommit: ON or OFF.

Default: ON
encryption

Settings for the automatic encryption: ON or OFF.

Default: ON
feedbackinterval

During a query the server sends feedback to the client at set intervals. This feedback:

  • Signals that the server is still there and executing the client command

  • Prevents the connection from being cut by the networks for inactivity

  • Checks whether a command is canceled

Default: 1 (second)

A feedback interval set too high may cause a long lag time before a command is canceled.
fingerprint

If TLS validation fails and you trust the server, use this parameter to include the server fingerprint in the connection string. For example:

Server=exadb1.example.com:8563;FingerPrint=BA7816BF8F01CFEA414140DE5DAE2223B00361A396177A9CB410FF61F20015AD;

To bypass TLS certificate checking, use the NOCERTCHECK option. NOCERTCHECK is case insensitive and can be placed after the host or port, or in a fingerprint argument. For example:

  • Server=exadb1.example.com/NOCERTCHECK:8563
  • Server=exadb1.example.com:8563/nocertcheck
  • Server=exadb1.example.com:8563;FingerPrint=NoCertCheck;

A connection string must only contain a single fingerprint, specified either after the host or port or in a fingerprint argument. If you provide multiple fingerprints, or if you specify the same fingerprint more than once in a connection string, the connection will fail.
hosttimeout

Specifies the connection timeout for each TCP connection attempt in milliseconds. If multiple hosts are specified in the connection string, each host will wait for the specified time for a successful connection.

Default: 2000

legacyencryption

If it is set to ON the driver will not use SSL to encrypt the client-server communication, instead it will use the ChaCha algorithm.

Possible values:

  • ON
  • OFF (default value)
logfile

Log file for the driver (for example, logfile='C:\tmp\ado.log'). The size of this log file can become large. Only switch on this option to analyze problems.
logintimeout

The maximum time in milliseconds that the driver will wait to establish a connection. This timeout is required to limit the overall login time, especially in the case of a large cluster with several reserve nodes.

Default: 0 (infinite)

onconnect

SQL string which is executed directly after the connection is established. If an error occurs, the connection will be aborted.
password or pwd Password
port

Port of Exasol. This port is used if you did not specify any port within the parameter host.
querytimeout

Defines how many seconds a statement may run before it is automatically aborted.

Default: 0 (infinite)
schema

Schema to be opened on login.
server or host

Defines the servers, fingerprint, and the port of the Exasol cluster (for example, 192.168.6.11..14/72975B4D331DEEB44FA41FB858A5417E163151BBFB45376574706800A8DCCE30:8563).

If host names are specified there, they can have DNS entries with multiple IP addresses.
snapshottransactions

Changes the snapshottransaction setting in the session.

Possible values:

  • ON
  • OFF

If not set, the default in the session is used.
sslcertificate

Specifies how ADO.NET driver will treat the server certificate for the connection. If the connection has VerifyServer the driver verifies the certificate in the SSL connection. VerifyNoneestablishes an SSL connection, but does not verify the server certificate. You can also specify a self signed certificate file name here for SSL verification.

Possible values:

  • <certificate_file_name>
  • VerifyServer (default value)
  • VerifyNone

If the SSL certificate is not verified or a client does not have the certificate, the connection fails. To overcome this problem, you could use a fingerprint in the connection string that allows the connection. For fingerprint based verification, the connection string is Server=exadb1.example.com/<fingerprint>;Port=8563. The fingerprint is automatically added to the database connection string when you upload a TLS certificate. For more information about TLS certificate upload, see Upload TLS Certificate.
superconnection

Enables the user to execute queries even if the limit for active sessions (executing a query) has been reached. Valid values are Y and N.

Default: database default

Only the SYS user can set the parameter.

superconnection should only be used in case of significant performance problems where it is impossible to log in and execute queries within a reasonable time. By that parameter you can analyze the system and kill certain processes which cause the problem.

user id, username, or uid

Username

Example for Exasol Data Provider using C#:


using System;
using System.Collections.Generic;
using System.Text;
using System.Data.Common;

namespace ConnectionTest
{
    class Program
    {
        static void Main(string[] args)
        {
            DbProviderFactory factory=null; try
            {
                factory = DbProviderFactories.GetFactory("Exasol.EXADataProvider"); 
                Console.WriteLine("Found Exasol driver");

                DbConnection connection = factory.CreateConnection(); 
                connection.ConnectionString =
                "Server=192.168.6.11..14;Port=8563;UID=sys;PWD=exasol;Schema=sys";

                connection.Open(); 
                Console.WriteLine("Connected to server"); 
                DbCommand cmd = connection.CreateCommand(); 
                cmd.Connection = connection; 
                cmd.CommandText = "SELECT * FROM CAT";

                DbDataReader reader = cmd.ExecuteReader();

                Console.WriteLine("Schema SYS contains:"); 
                while (reader.Read())
                {
                    Console.WriteLine("{0}, {1}", 
                    reader["TABLE_NAME"], 
                    reader["TABLE_TYPE"]);
                }

                reader.Close(); 
                connection.Close();
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.ToString());
            }
        }
    }
}

If the example is successfully executed the following output is displayed:

Found Exasol driver 
Connected to server 
Schema SYS contains: 
EXA_SQL_KEYWORDS, TABLE 
DUAL, TABLE 
EXA_PARAMETERS, TABLE
...

Schema Collections

Exasol ADO.NET driver supports the following Schema Collections:

  • MetaDataCollections
  • DataSourceInformation
  • DataTypes
  • Restrictions
  • ReservedWords
  • Tables
  • Columns
  • Procedures
  • ProcedureColumns
  • Views
  • Schemas

Best Practices

Objective Details
Inserting data into the database

Instead of using single insert statements like "INSERT INTO t VALUES 1, 2, ..." you should use more efficient interface of prepared statements and their parameters. Prepared statements achieve optimal performance when using parameter sets between 500 kB and 20 MB. Moreover you should insert the data by using the native data types.

For execution of prepared statements we recommend using the IParameterTable interface. You can create an instance of ParameterTable with a "Command" through CreateParameterTable() method.
Block-wise update by using prepared statements

Exasol ADO.NET provides a special interface to execute prepared statements (class ParameterTable) since it is not possible to update rows in Exasol through the ADO.NET specific interfaces DataSet and DataAdapter.

Executing parametrized queries is done by using SQL statements including question marks at the position of parameters. The values of the ParameterTable are used to replace the parameters when executing the statement.

Example for a parametrized query is given below:

using (EXACommand command = (EXACommand)connection.CreateCommand())
{
   command.CommandText = "CREATE OR REPLACE TABLE TEST (I INT, TS TIMESTAMP)";
   command.ExecuteNonQuery();
   command.CommandText = "INSERT INTO TEST VALUES (?, ?)"; 
   command.Prepare();
   IParameterTable tab = command.CreateParameterTable(); 
   tab.AddColumn(DbType.Int32); 
   tab.AddColumn(DbType.String);
   for (int i = 0; i < 10; i++)
   {
     int currentRow = tab.AppendRow(); 
     tab[0].setInt32(currentRow, i); 
     tab[1].setString(currentRow, "2017-06-30 11:21:13." + i);
   }
   command.Execute(tab);
}

Note: The two options to specify the decimal types are:

  • AddColumn(DbType.Decimal): When using this general method, the internal type DECIMAL(36,8) is used.
  • AddDecimalColumn(p,s): When using this specific method, the internal type DECIMAL(p,s) is used (36 digits as maximum).
Decimal types The ADO.NET DECIMAL data type can only store values with a precision of a maximum of 28 digits. Decimal columns in Exasol with greater precision do not fit in these data types and have to be provided as strings.
Column names Consider the case when using identifiers.
Batch execution

If you want to execute several statements in a batch, you can use the functions AddBatchCommandText() and ExecuteBatch() of the class EXACommand.

Example for a batch execution is given below.


using (EXACommand cmd = (EXACommand)connection.CreateCommand())
{
   cmd.AddBatchCommandText("CREATE OR REPLACE TABLE TEST (I INT)"); 
   cmd.AddBatchCommandText("INSERT INTO TEST VALUES (1)"); 
   cmd.AddBatchCommandText("SELECT * FROM TEST");
   DbDataReader reader = cmd.ExecuteBatch(); 
   do
   {
    if (reader.RecordsAffected>0)
        Console.Out.WriteLine("Records affected: " + reader.RecordsAffected);
   } while (reader.NextResult());
}

Exasol Data Processing Extension

The EXADataProcessingExtension implements interfaces for Microsoft SQL Server Reporting Services to create reports with Exasol. In the following example Visual Studio 2005 is used to create a report for the Reporting Services:

  1. Start Visual Studio 2005 and create a new report server project.
  2. Open the Project sheet explorer.
  3. Create a new data source and insert the connection string, user, and passwords.

    Exasol Data Processing Extension

  1. In the Project sheet explorer, add a new report using the wizard. When selecting the data source, choose the ExaDataSource that was previously configured.
  2. Publish the report as soon as it is ready. The functions are contained in Create menu.
  3. To view the report, point your browser to the address of the report server. For example, http://localhost/Reports