This is the multi-page printable view of this section. Click here to print.

Return to the regular view of this page.

ADO.NET client driver

The Vertica ADO.NET driver lets you access with C#.

The Vertica ADO.NET driver lets you access Vertica with C# .

1 - Installing the ADO.NET client driver

The ADO.NET client driver requires the following:.

Prerequisites

The ADO.NET client driver requires the following:

Installation

For a sample application that uses and demonstrates all of these installation methods, see the client-application-examples repository.

The ADO.NET client driver is available on NuGet and should be installed with a package reference.

To reference the package, add the following to your .csproj. For an example .csproj file, see SampleApp.csproj:

  <ItemGroup>
    <PackageReference Include="Vertica.Data" Version="23.4.0" />
  </ItemGroup>

Reference a local NuGet package

You can also download the Vertica.Data package and reference it locally:

  1. Download Vertica.Data.23.4.0.nupkg from NuGet to project_directory/packages/Vertica.Data.23.4.0.nupkg.
  2. Add the following to nuget.config to instruct NuGet to get the package from your local directory:
    <?xml version="1.0" encoding="utf-8" ?>
    <configuration>
      <packageSources>
        <add key="LocalPackages" value="packages" />
      </packageSources>
    </configuration>
    
  3. Reference the driver with a standard package reference in your .csproj file:
    <ItemGroup>
      <PackageReference Include="Vertica.Data" Version="23.4.0" />
    </ItemGroup>
    

Reference a local .dll

You can also download the NuGet package, extract the lib/net40/Vertica.Data.dll file, and then reference Vertica.Data.dll:

  1. Download Vertica.Data.23.4.0.nupkg from NuGet.org.
  2. Extract Vertica.Data.dll to project_directory/lib/Vertica.Data.dll.
  3. Reference Vertica.Data.dll in your .csproj file. For example:
  <ItemGroup>
    <Reference Include="Vertica.Data">
      <HintPath>lib\Vertica.Data.dll</HintPath>
    </Reference>
  </ItemGroup>

  <ItemGroup>
    <PackageReference Include="Microsoft.Win32.Registry" Version="5.0.0" />
    <PackageReference Include="System.Configuration.ConfigurationManager" Version="6.0.0" />
  </ItemGroup>

2 - Log properties

The following parameters control how messages between the client and server are logged. If they are not set, then the client library does not log any messages.

Config-level Settings

The following parameters control how messages between the client and server are logged. If they are not set, then the client library does not log any messages.

To set these parameters, edit the configuration file Vertica.Data.dll.config located in the same directory as Vertica.Data.dll. If Vertica.Data.dll.config does not exist, the driver creates it when it is first used.

LogLevel
The minimum severity of a message for it to be logged, one of the following:
  • 0: No logging
  • 1: Fatal errors
  • 2: Errors
  • 3: Warnings
  • 4: Info
  • 5: Debug
  • 6: Trace (all messages)

For example, a LogLevel of 3 means that the client driver logs messages with severities 1, 2, and 3.

LogPath
The absolute path of the log file. For example: /var/log/verticaadonet.log.
LogNamespace
Limits logging to messages generated by certain objects in the client driver.
CheckedRegistrySettings
Boolean, in Windows environments, whether the driver has performed the on-time check on the Windows Registry key HKEY_LOCAL_MACHINE\SOFTWARE\Vertica\ADO.NET\Driver. The driver checks the registry once when it is first run to retrieve settings, if any, from the Windows Registry, and write them to Vertica.Data.dll.config. CheckedRegistrySettings does not need to be set or modified by the user.

Example configuration file

The following example configuration file uses the default values for each configuration setting:

<?xml version="1.0" encoding="utf-8"?>
<configuration>
    <appSettings>
        <add key="Logging.LogLevel" value="None" />
        <add key="Logging.LogPath" value="" />
        <add key="Logging.LogNamespace" value="" />
    </appSettings>
</configuration> 

VerticaLogProperties

You can set the log properties of the ADO.NET driver with the VerticaLogProperties class, which includes the following methods:

  • SetLogPath(String path, bool persist)
  • SetLogNamespace(String lognamespace, bool persist)
  • SetLogLevel(VerticaLogLevel loglevel, bool persist)

Logs are created when the first connection is opened, so you cannot change the log path with SetLogPath() after the connection starts. You can change the log level and log namespace at any time.

The persist parameter controls whether the setting is written to the client's Vertica.Data.dll.config, where it will be used for all subsequent connections. If set to false, then the setting only applies to the current session.

SetLogPath()

The SetLogPath() method takes as an argument a String path containing the path to the log file and the persist argument. If the path string contains only a directory path, then the log file is created with the name vdp-driver-MM-dd_HH.mm.ss.log (where MM-dd_HH.mm.ss is the date and time the log was created). If the path ends in a filename, such as log.txt or log.log, then the log is created with that filename.

If SetLogPath() is called with an empty string for the path argument, then the client executable's current directory is used as the log path.

If SetLogPath() is not called and entry exists for the log path in Vertica.Data.dll.config, and you have called any of the other VerticaLogProperties methods, then the client executable's current directory is used as the log path.

When the persist argument is set to true, the path specified is copied to Vertica.Data.dll.config. If no filename is specified, then the filename is not saved to Vertica.Data.dll.config.

For example:

//set the log path
string path = "C:\\log";
VerticaLogProperties.SetLogPath(path, false);

SetLogNamespace()

The SetLogNamespace() method takes as an argument a String lognamespace containing the namespace to log and the persist argument. The namespace string to log can be one of the following:

  • Vertica
  • Vertica.Data.VerticaClient
  • Vertica.Data.Internal.IO
  • Vertica.Data.Internal.DataEngine
  • Vertica.Data.Internal.Core

Namespaces can be truncated to include child namespaces. For example, you can specify Vertica.Data.Internal to log for all of the Vertica.Data.Internal namespaces.

If a log namespace is not set, and no value is stored in Vertica.Data.dll.config, then the Vertica namespace is used for logging.

For example:

//set namespace to log
string lognamespace = "Vertica.Data.VerticaClient";
VerticaLogProperties.SetLogNamespace(lognamespace, false);

SetLogLevel()

The SetLogLevel() method takes as an argument a VerticaLogLevel loglevel, one of the following:

  • VerticaLogLevel.None
  • VerticaLogLevel.Fatal
  • VerticaLogLevel.Error
  • VerticaLogLevel.Warning
  • VerticaLogLevel.Info
  • VerticaLogLevel.Debug
  • VerticaLogLevel.Trace

If a log level is not set, and no value is stored in Vertica.Data.dll.config, then VerticaLogLevel.None is used.

For example:

//set log level
VerticaLogLevel level = VerticaLogLevel.Debug;
VerticaLogProperties.SetLogLevel(level, false);

Getting log properties

You can retrieve the values for the following properties with the VerticaLogProperties class:

  • LogPath
  • LogNamespace
  • LogLevel

For example:

//get current log settings
string logpath = VerticaLogProperties.LogPath;
VerticaLogLevel loglevel = VerticaLogProperties.LogLevel;
string logns = VerticaLogProperties.LogNamespace;
Console.WriteLine("Current Log Settings:");
Console.WriteLine("Log Path: " + logpath);
Console.WriteLine("Log Level: " + loglevel);
Console.WriteLine("Log Namespace: " + logns);

Examples

This complete example shows how to get and set log properties:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using Vertica.Data.VerticaClient;
namespace ConsoleApplication
{
    class Program
    {
        static void Main(string[] args)
        {
            //configure connection properties
            VerticaConnectionStringBuilder builder = new VerticaConnectionStringBuilder();
            builder.Host = "192.168.1.10";
            builder.Database = "VMart";
            builder.User = "dbadmin";
            
            //get current log settings
            string logpath = VerticaLogProperties.LogPath;
            VerticaLogLevel loglevel = VerticaLogProperties.LogLevel;
            string logns = VerticaLogProperties.LogNamespace;
            Console.WriteLine("\nOld Log Settings:");
            Console.WriteLine("Log Path: " + logpath);
            Console.WriteLine("Log Level: " + loglevel);
            Console.WriteLine("Log Namespace: " + logns);

            //set the log path
            string path = "C:\\log";
            VerticaLogProperties.SetLogPath(path, false);

            // set log level
            VerticaLogLevel level = VerticaLogLevel.Debug;
            VerticaLogProperties.SetLogLevel(level, false);

            //set namespace to log
            string lognamespace = "Vertica";
            VerticaLogProperties.SetLogNamespace(lognamespace, false);
            
            //open the connection
            VerticaConnection _conn = new VerticaConnection(builder.ToString());
            _conn.Open();

            //get new log settings
            logpath = VerticaLogProperties.LogPath;
            loglevel = VerticaLogProperties.LogLevel;
            logns = VerticaLogProperties.LogNamespace;
            Console.WriteLine("\nNew Log Settings:");
            Console.WriteLine("Log Path: " + logpath);
            Console.WriteLine("Log Level: " + loglevel);
            Console.WriteLine("Log Namespace: " + logns);

            //close the connection
            _conn.Close();
        }
    }
}

The example produces the following output:

Old Log Settings:
Log Path:
Log Level: None
Log Namespace:
New Log Settings:
Log Path: C:\log
Log Level: Debug
Log Namespace: Vertica