ADO.NET Data Provider
This article describes the Exasol 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 ADO.NET data provider is tested on the following operating systems:
Windows (64-bit)
-
Windows 10
-
Windows Server 2016
-
Windows Server 2019
-
Windows Server 2022
Download and install the ADO.NET data provider
-
Download the latest driver from the Exasol Download Portal.
- 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
- 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.
- 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/<fingerprint>: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.
TLS encryption is required by default for all connections. The legacy ChaCha encryption method is deprecated and is not recommended. Connections from drivers that disable encryption through the encryption
property or switch to ChaCha encryption through the legacyencryption
property are not accepted in Exasol 8.19.0 or later.
If TLS encryption cannot be used and you trust the server, you can include the server fingerprint in the connection string to bypass TLS validation. For more information, see fingerprint.
Keyword | Value type | Description |
---|---|---|
authmethod
|
string |
Specifies the authentication method for OpenID connections.
For more information, see CREATE USER. |
autocommit
|
boolean [ON | OFF] |
Enables/disables autocommit. Default: ON |
feedbackinterval
|
integer ≥0 |
The interval in seconds between the feedback messages that the server sends to the client during a query. This feedback does the following:
Default: 1 (second) Setting a very high feedback interval value may cause a long lag time before a command is canceled. |
fingerprint
|
string |
If TLS validation fails and you trust the server, you can use this parameter to include the server fingerprint in the connection string. For example:
To bypass TLS certificate checking, use the
IMPORTANT: A connection string must only contain a single fingerprint, specified either after the host or port or in a |
hosttimeout
|
integer ≥0 |
Specifies the connection timeout in milliseconds for each TCP connection attempt. If multiple hosts are specified in the connection string, each host will wait for the specified time for a successful connection. Default: 2000 (ms) |
logfile
|
string |
Path to a log file for the driver. For example: The size of the log file can become very large. Because of this, we do not recommend logging in a production environment. Default: empty (logging disabled) |
logintimeout
|
integer ≥0 |
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: no timeout (driver will wait indefinitely for the connection to be established) |
onconnect
|
string |
SQL string that is executed directly after the connection is established. If an error occurs, the connection will be aborted. |
password or pwd |
string | Password for the connection. |
port
|
numeric |
The TCP port for Exasol. This port is used if you did not specify a port in the |
querytimeout
|
integer ≥0 |
Defines the time in seconds for a statement to run before it is automatically aborted. Default: 0 (infinite) |
schema
|
string |
The schema to be opened after login. If the schema cannot be opened, the login fails. |
server or host |
string |
Defines the servers, fingerprint, and port of the Exasol cluster to connect to. For example: If host names are specified there, they can have DNS entries with multiple IP addresses. |
snapshottransactions
|
boolean [ON | OFF] |
Enables/disables snapshot transactions for the session. Default: session default |
sslcertificate
|
string |
Specifies how the driver will treat the server certificate for the connection. You can also specify a self signed certificate. Possible values:
|
superconnection
|
boolean [Y | N] |
When enabled, the user can execute queries even if the limit for active sessions (executing a query) has been reached.
Only the
Default: database default |
|
string |
Username for the connection. |
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
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:
- Start Visual Studio 2005 and create a new report server project.
- Open the Project sheet explorer.
- Create a new data source and insert the connection string, user, and passwords.
- In the Project sheet explorer, add a new report using the wizard. When selecting the data source, choose the ExaDataSource that was previously configured.
- Publish the report as soon as it is ready. The functions are contained in Create menu.
- To view the report, point your browser to the address of the report server. For example,
http://localhost/Reports