C#

• 1: ADO.NET data types
• 2: Setting the locale for ADO.NET sessions
• 3: Connecting to the database
• 3.1: Configuring TLS for ADO.NET
• 3.2: Opening and closing the database connection (ADO.NET)
• 3.3: ADO.NET connection properties
• 3.4: Load balancing in ADO.NET
• 3.5: ADO.NET connection failover
• 4: Querying the database using ADO.NET
• 4.1: Inserting data (ADO.NET)
• 4.1.1: Using parameters
• 4.1.2: Creating and rolling back transactions
• 4.1.2.1: Setting the transaction isolation level
• 4.2: Reading data (ADO.Net)
• 4.3: Loading data through ADO.Net
• 4.3.1: Using the Vertica data adapter
• 4.3.2: Using batch inserts and prepared statements
• 4.3.3: Streaming data via ADO.NET
• 4.3.3.1: Streaming from the client via VerticaCopyStream
• 4.3.3.2: Using copy with ADO.NET
• 5: Canceling ADO.NET queries
• 6: Handling messages
• 7: Getting table metadata

1 - ADO.NET data types

This table details the mapping between Vertica data types and .NET and ADO.NET data types.

UUID backwards compatibility

Vertica version 9.0.0 introduced the UUID data type, including JDBC support for UUIDs. The Vertica ADO.NET, ODBC, and OLE DB clients added full support for UUIDs in version 9.0.1. Vertica maintains backwards compatibility with older supported client driver versions that do not support the UUID data type, as follows:

2 - Setting the locale for ADO.NET sessions

• ADO.NET applications use a UTF-16 character set encoding and are responsible for converting any non-UTF-16 encoded data to UTF-16. The same cautions as for ODBC apply if this encoding is violated.

• The ADO.NET driver converts UTF-16 data to UTF-8 when passing to the Vertica server and converts data sent by Vertica server from UTF-8 to UTF-16.

• ADO.NET applications should set the correct server session locale by executing the SET LOCALE TO command in order to get expected collation and string functions behavior on the server.

• If there is no default session locale at the database level, ADO.NET applications need to set the correct server session locale by executing the SET LOCALE TO command in order to get expected collation and string functions behavior on the server. See the SET LOCALE command.

3 - Connecting to the database

3.1 - Configuring TLS for ADO.NET

You can optionally use TLS to secure communication between your ADO.NET application and Vertica.

Prerequisites

Before you configure ADO.NET for TLS, you must configure client-server TLS, setting the TLSMODE to ENABLE. Mutual mode (TRY_VERIFY or higher) is not supported for ADO.NET.

Linux

The following procedure configures TLS on a Linux system:

1. On the client filesystem, create the file /etc/ssl/certs/server.crt with the certificate text of the server certificate. You can retrieve the certificate text from a certificate in Vertica by querying the CERTIFICATES system table.
2. Run the following command to verify that the certificate file is valid. If it is valid, the command outputs information about the certificate:

   $ openssl x509 -in /etc/ssl/certs/server.crt -text -noout
   
   Certificate:
   Data:
       Version: 3 (0x2)
       Serial Number:
           65:e7:fe:f9:0e:60:8a:79:ff:97:e2:c2:e4:e8:57:09:bd:f3:34:20
       Signature Algorithm: sha256WithRSAEncryption
       Issuer: C = US, ST = Massachusetts, L = Burlington, O = OpenText, OU = Vertica, CN = Vertica Root CA
       Validity
           Not Before: Aug  3 18:11:44 2023 GMT
           Not After : Aug 12 18:11:44 2024 GMT
       Subject: C = US, ST = Massachusetts, L = Burlington, O = OpenText, OU = Vertica, CN = *.example.com
       Subject Public Key Info:
           Public Key Algorithm: rsaEncryption
               RSA Public-Key: (2048 bit)
               Modulus:
                   00:9a:3a:83:5b:e7:73:c2:a4:15:c7:0a:81:a0:02:
                   f3:a6:6c:bb:aa:fb:fc:c8:9a:db:b9:41:21:2d:ca:
                   d9:07:1a:b1:07:35:39:0b:f3:62:08:1c:31:49:d4:
                   e2:b3:21:a8:84:eb:f4:43:5f:92:9e:c3:34:3d:4b:
                   4b:ab:ad:75:05:3c:c4:82:b5:21:45:a3:a5:c2:5c:
                   1d:c9:e3:d2:93:c1:40:b4:f6:07:f7:6c:47:68:9f:
                   9b:5d:41:4b:85:83:e0:f2:56:36:67:ee:ac:1e:08:
                   8c:6c:3a:af:b8:20:84:1d:7e:bb:d2:5e:45:d0:a8:
                   6d:ca:d8:46:5a:83:e6:d0:8d:00:fc:c1:bf:ce:d7:
                   95:4c:1d:ed:3a:45:82:d5:4d:1b:2c:d6:c4:17:5c:
                   aa:78:bc:e3:c2:2b:06:70:c3:1a:42:57:3e:19:5f:
                   7c:2f:0c:f2:d5:09:6a:ad:04:cd:95:33:92:20:56:
                   41:86:62:b2:fb:a5:d1:c5:65:cd:be:f9:31:6c:45:
                   79:a5:7f:10:7d:07:1d:26:eb:f3:18:42:14:3b:37:
                   84:81:f4:4f:c0:8d:93:b2:57:da:4f:64:53:b8:cc:
                   ed:ce:a7:c5:cc:af:5b:d1:4a:3f:fc:32:5a:f3:84:
                   89:cb:19:52:43:22:5c:9d:54:88:6b:41:3a:39:00:
                   86:bd
               Exponent: 65537 (0x10001)
       X509v3 extensions:
           X509v3 Basic Constraints:
               CA:FALSE
           X509v3 Extended Key Usage:
               TLS Web Server Authentication
           X509v3 Key Usage: critical
               Digital Signature, Key Encipherment
           X509v3 Subject Key Identifier:
               DA:39:A3:EE:5E:6B:4B:0D:32:55:BF:EF:95:60:18:90:AF:D8:07:09
           X509v3 Authority Key Identifier:
               keyid:DA:39:A3:EE:5E:6B:4B:0D:32:55:BF:EF:95:60:18:90:AF:D8:07:09
               DirName:/C=US/ST=Massachusetts/L=Burlington/O=OpenText/OU=Vertica/CN=Vertica Root CA
               serial:4C:92:49:E5:98:94:C3:9C:B9:3E:DE:30:39:ED:52:23:E6:A8:7E:D8
   
   Signature Algorithm: sha256WithRSAEncryption
        a7:f5:35:12:ef:f2:8e:7e:85:45:6a:a0:7a:64:7b:d7:82:62:
        fc:2b:b4:76:1c:5b:3e:73:f8:cb:a7:8a:07:e7:1a:f3:fc:bc:
        45:58:b0:3c:13:6f:29:fa:7b:1a:cc:7b:c7:79:bc:54:62:5c:
        3f:44:ae:7e:af:68:6d:bc:3a:38:93:3f:a6:c9:42:70:68:c3:
        39:fc:a4:1a:2f:d5:d6:5d:0f:e4:06:cb:53:61:a7:b3:44:a5:
        85:74:76:f7:b7:65:1b:74:bf:58:63:40:60:82:59:01:b7:0f:
        a4:8c:58:44:7e:41:c9:63:a2:da:92:64:0e:a0:a5:f7:ad:49:
        40:f9:e3:e4:21:f2:d3:9c:c9:06:03:d6:5d:61:ef:ef:31:49:
        e0:66:79:08:97:0e:20:ec:2f:03:6c:a1:6e:9e:3c:24:5d:da:
        cc:20:ec:29:10:92:28:b2:3d:af:fb:3a:46:7d:ca:e5:bb:48:
        57:93:ef:27:a4:4d:00:2d:6d:7c:3c:6b:55:83:af:11:ef:c3:
        2f:d2:16:09:f0:4e:45:64:8d:50:93:da:ab:07:33:fb:2b:6c:
        d2:12:16:f9:a7:3d:de:e7:b9:62:0c:c3:37:bc:51:24:e7:aa:
        64:6d:19:15:7e:f5:f0:31:e6:5c:14:56:3b:6f:f0:6b:e0:35:
        68:b1:fa:27
   

3. On the client filesystem, create the file /usr/local/share/ca-certificates/root.crt with the certificate text of the CA certificate.
4. Verify that the certificate was issued by the CA certificate:

   $ openssl verify -CAfile /usr/local/share/ca-certificates/root.crt /etc/ssl/certs/server.crt
   server.crt: OK
   

5. Update the certificate store:

   $ update-ca-certificates
   

Windows

The Vertica ADO.NET driver uses the TLS certificates in the default Windows key store.

To use TLS for ADO.NET connections to Vertica:

1. Import the server certificate into the Windows key store:
   1. Create a file server.crt with the certificate text of the server certificate.
   2. Double-click server.crt certificate file.
   3. Let Windows determine the key type and select Install.
2. Import the CA certificate into the Windows key store:
   1. Create a file root.crt with the certificate text of the CA certificate.
   2. Double-click root.crt certificate file.
   3. Select Place all certificates in the following store.
   4. Select Browse, Trusted Root Certification Authorities, and Next.
   5. Select Install.

Enable SSL in your ADO.NET applications

In your connection string, enable SSL by setting the SSL property in VerticaConnectionStringBuilder to true, for example:

    //configure connection properties
    VerticaConnectionStringBuilder builder = new VerticaConnectionStringBuilder();
    builder.Host = "192.168.17.10";
    builder.Database = "VMart";
    builder.User = "dbadmin";
    builder.SSL = true;
    //open the connection
    VerticaConnection _conn = new VerticaConnection(builder.ToString());
    _conn.Open();

3.2 - Opening and closing the database connection (ADO.NET)

Before you can access data in Vertica through ADO.NET, you must create a connection to the database using the VerticaConnection class which is an implementation of System.Data.DbConnection. The VerticaConnection class takes a single argument that contains the connection properties as a string. You can manually create a string of property keywords to use as the argument, or you can use the VerticaConnectionStringBuilder class to build a connection string for you.

To download the ADO.NET driver, go to the Client Drivers Downloads page.

This topic details the following:

• Manually building a connection string and connecting to Vertica

• Using VerticaConnectionStringBuilder to create the connection string and connecting to Vertica

• Closing the connection

To manually create a connection string:

See ADO.NET connection properties for a list of available properties to use in your connection string. At a minimum, you need to specify the Host, Database, and User.

1. For each property, provide a value and append the properties and values one after the other, separated by a semicolon. Assign this string to a variable. For example:

   String connectString = "DATABASE=VMart;HOST=v_vmart_node0001;USER=dbadmin";
   

2. Build a Vertica connection object that specifies your connection string.

   VerticaConnection _conn = new VerticaConnection(connectString)
   

3. Open the connection.

   _conn.Open();
   

4. Create a command object and associate it with a connection. All VerticaCommand objects must be associated with a connection.

   VerticaCommand command = _conn.CreateCommand();
   

To use the VerticaConnectionStringBuilder class to create a connection string and open a connection:

1. Create a new object of the VerticaConnectionStringBuilder class.

   VerticaConnectionStringBuilder builder = new VerticaConnectionStringBuilder();
   

2. Update your VerticaConnectionStringBuilder object with property values. See ADO.NET connection properties for a list of available properties to use in your connection string. At a minimum, you need to specify the Host, Database, and User.

   builder.Host = "v_vmart_node0001";
   builder.Database = "VMart";
   builder.User = "dbadmin";
   

3. Build a Vertica connection object that specifies your connection VerticaConnectionStringBuilder object as a string.

   VerticaConnection _conn = new VerticaConnection(builder.ToString());
   

4. Open the connection.

   _conn.Open();
   

5. Create a command object and associate it with a connection. All VerticaCommand objects must be associated with a connection.

   VerticaCommand command = _conn.CreateCommand;
   

To close the connection:

When you're finished with the database, close the connection. Failure to close the connection can deteriorate the performance and scalability of your application. It can also prevent other clients from obtaining locks.

 _conn.Close();

Example usage:

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)
        {
            VerticaConnectionStringBuilder builder = new VerticaConnectionStringBuilder();
            builder.Host = "192.168.1.10";
            builder.Database = "VMart";
            builder.User = "dbadmin";
            VerticaConnection _conn = new VerticaConnection(builder.ToString());
            _conn.Open();
        //Perform some operations
            _conn.Close();
        }
    }
}

3.3 - ADO.NET connection properties

To download the ADO.NET driver, go to the Client Drivers Downloads page.

You use connection properties to configure the connection between your ADO.NET client application and your Vertica database. The properties provide the basic information about the connections, such as the server name and port number, needed to connect to your database.

You can set a connection property in two ways:

• Include the property name and value as part of the connection string you pass to a VerticaConnection.

• Set the properties in a VerticaConnectionStringBuilder object, and then pass the object as a string to a VerticaConnection.

3.4 - Load balancing in ADO.NET

Native connection load balancing

Native connection load balancing helps spread the overhead caused by client connections on the hosts in the Vertica database. Both the server and the client must enable native connection load balancing. If enabled by both, then when the client initially connects to a host in the database, the host picks a host to handle the client connection from a list of the currently up hosts in the database, and informs the client which host it has chosen.

If the initially-contacted host does not choose itself to handle the connection, the client disconnects, then opens a second connection to the host selected by the first host. The connection process to this second host proceeds as usual—if SSL is enabled, then SSL negotiations begin, otherwise the client begins the authentication process. See About native connection load balancing for details.

To enable native load balancing on your client, set the ConnectionLoadBalance connection parameter to true either in the connection string or using the ConnectionStringBuilder(). The following example demonstrates connecting to the database several times with native connection load balancing enabled, and fetching the name of the node handling the connection from the V_MONITOR.CURRENT_SESSION system table.

using System;
using System.Text;
using System.Data;
using Vertica.Data.VerticaClient;

namespace ConsoleApplication1 {
    class Program {
        static void Main(string[] args) {
            VerticaConnectionStringBuilder builder = new VerticaConnectionStringBuilder();
            builder.Host = "v_vmart_node0001.example.com";
            builder.Database = "VMart";
            builder.User = "dbadmin";
            // Enable native client load balancing in the client,
            // must also be enabled on the server!
            builder.ConnectionLoadBalance = true;

            // Connect 3 times to verify a new node is connected
            // for each connection.
            for (int i = 1; i <= 4; i++) {
                try {
                    VerticaConnection _conn = new VerticaConnection(builder.ToString());
                    _conn.Open();
                    if (i == 1) {
                        // On the first connection, check the server policy for load balance
                        VerticaCommand sqlcom = _conn.CreateCommand();
                        sqlcom.CommandText = "SELECT LOAD_BALANCE_POLICY FROM V_CATALOG.DATABASES";
                        var returnValue = sqlcom.ExecuteScalar();
                        Console.WriteLine("Status of load balancy policy
             on server: " + returnValue.ToString() + "\n");
                    }
                    VerticaCommand command = _conn.CreateCommand();
                    command.CommandText = "SELECT node_name FROM V_MONITOR.CURRENT_SESSION";
                    VerticaDataReader dr = command.ExecuteReader();
                    while (dr.Read()) {
                        Console.Write("Connect attempt #" + i + "... ");
                        Console.WriteLine("Connected to node " + dr[0]);
                    }
                    dr.Close();
                    _conn.Close();
                    Console.WriteLine("Disconnecting.\n");
                }
                catch(Exception e) {
                    Console.WriteLine(e.Message);
                }
            }
        }
    }
}

Running the above example produces the following output:

Status of load balancing policy on server: roundrobin

Connect attempt #1... Connected to node v_vmart_node0001
Disconnecting.

Connect attempt #2... Connected to node v_vmart_node0002
Disconnecting.

Connect attempt #3... Connected to node v_vmart_node0003
Disconnecting.

Connect attempt #4... Connected to node v_vmart_node0001
Disconnecting.

Hostname-based load balancing

You can also balance workloads by resolving a single hostname to multiple IP addresses. The ADO.NET client driver load balances by automatically resolving the hostname to one of the specified IP addresses at random.

For example, suppose the hostname verticahost.example.com has the following entries in C:\Windows\System32\drivers\etc\hosts:

192.0.2.0 verticahost.example.com
192.0.2.1 verticahost.example.com
192.0.2.2 verticahost.example.com

Specifying the hostname verticahost.example.com randomly resolves to one of the listed IP addresses.

3.5 - ADO.NET connection failover

If a client application attempts to connect to a host in the Vertica cluster that is down, the connection attempt fails when using the default connection configuration. This failure usually returns an error to the user. The user must either wait until the host recovers and retry the connection or manually edit the connection settings to choose another host.

Due to Vertica Analytic Database's distributed architecture, you usually do not care which database host handles a client application's connection. You can use the client driver's connection failover feature to prevent the user from getting connection errors when the host specified in the connection settings is unreachable. The JDBC driver gives you several ways to let the client driver automatically attempt to connect to a different host if the one specified in the connection parameters is unreachable:

• Configure your DNS server to return multiple IP addresses for a host name. When you use this host name in the connection settings, the client attempts to connect to the first IP address from the DNS lookup. If the host at that IP address is unreachable, the client tries to connect to the second IP, and so on until it either manages to connect to a host or it runs out of IP addresses.

• Supply a list of backup hosts for the client driver to try if the primary host you specify in the connection parameters is unreachable.

• (JDBC only) Use driver-specific connection properties to manage timeouts before attempting to connect to the next node.

For all methods, the process of failover is transparent to the client application (other than specifying the list of backup hosts, if you choose to use the list method of failover). If the primary host is unreachable, the client driver automatically tries to connect to other hosts.

Failover only applies to the initial establishment of the client connection. If the connection breaks, the driver does not automatically try to reconnect to another host in the database.

Choosing a failover method

You usually choose to use one of the two failover methods. However, they do work together. If your DNS server returns multiple IP addresses and you supply a list of backup hosts, the client first tries all of the IPs returned by the DNS server, then the hosts in the backup list.

The DNS method of failover centralizes the configuration client failover. As you add new nodes to your Vertica Analytic Database cluster, you can choose to add them to the failover list by editing the DNS server settings. All client systems that use the DNS server to connect to Vertica Analytic Database automatically use connection failover without having to change any settings. However, this method does require administrative access to the DNS server that all clients use to connect to the Vertica Analytic Database cluster. This may not be possible in your organization.

Using the backup server list is easier than editing the DNS server settings. However, it decentralizes the failover feature. You may need to update the application settings on each client system if you make changes to your Vertica Analytic Database cluster.

Using DNS failover

To use DNS failover, you need to change your DNS server's settings to map a single host name to multiple IP addresses of hosts in your Vertica Analytic Database cluster. You then have all client applications use this host name to connect to Vertica Analytic Database.

You can choose to have your DNS server return as many IP addresses for the host name as you want. In smaller clusters, you may choose to have it return the IP addresses of all of the hosts in your cluster. However, for larger clusters, you should consider choosing a subset of the hosts to return. Otherwise there can be a long delay as the client driver tries unsuccessfully to connect to each host in a database that is down.

Using the backup host list

To enable backup list-based connection failover, your client application has to specify at least one IP address or host name of a host in the BackupServerNode parameter. The host name or IP can optionally be followed by a colon and a port number. If not supplied, the driver defaults to the standard Vertica port number (5433). To list multiple hosts, separate them by a comma.

The following example demonstrates setting the BackupServerNode connection parameter to specify additional hosts for the connection attempt. The connection string intentionally has a non-existent node, so that the initial connection fails. The client driver has to resort to trying the backup hosts to establish a connection to Vertica.

using System;
using System.Text;
using System.Data;
using Vertica.Data.VerticaClient;

namespace ConsoleApplication1
{
    class Program
    {
        static void Main(string[] args)
        {
            VerticaConnectionStringBuilder builder =
        new VerticaConnectionStringBuilder();
            builder.Host = "not.a.real.host:5433";
            builder.Database = "VMart";
            builder.User = "dbadmin";
            builder.BackupServerNode =
        "another.broken.node:5433,v_vmart_node0002.example.com:5433";
            try
            {
                VerticaConnection _conn =
            new VerticaConnection(builder.ToString());
                _conn.Open();
                VerticaCommand sqlcom = _conn.CreateCommand();
                sqlcom.CommandText = "SELECT node_name FROM current_session";
                var returnValue = sqlcom.ExecuteScalar();
                Console.WriteLine("Connected to node: " +
            returnValue.ToString() + "\n");
                _conn.Close();
                Console.WriteLine("Disconnecting.\n");
            }
            catch (Exception e)
            {
                Console.WriteLine(e.Message);
            }
        }
    }
}

Notes

• When native connection load balancing is enabled, the additional servers specified in the BackupServerNode connection parameter are only used for the initial connection to a Vertica host. If host redirects the client to another host in the database cluster to handle its connection request, the second connection does not use the backup node list. This is rarely an issue, since native connection load balancing is aware of which nodes are currently up in the database. See Load balancing in ADO.NET.

• Connections to a host taken from the BackupServerNode list are not pooled for ADO.NET connections.

4 - Querying the database using ADO.NET

This section describes how to create queries to do the following:

• Inserting data into the database

• Read data from the database

• Load data into the database
  

4.1 - Inserting data (ADO.NET)

Inserting data can done using the VerticaCommand class. VerticaCommand is an implementation of DbCommand. It allows you to create and send a SQL statement to the database. Use the CommandText method to assign a SQL statement to the command and then execute the SQL by calling the ExecuteNonQuery method. The ExecuteNonQuery method is used for executing statements that do not return result sets.

To insert a single row of data:

1. Create a connection to the database.

2. Create a command object using the connection.

   VerticaCommand command = _conn.CreateCommand();
   

3. Insert data using an INSERT statement. The following is an example of a simple insert. Note that is does not contain a COMMIT statement because the Vertica ADO.NET driver operates in autocommit mode.

   command.CommandText =
        "INSERT into test values(2, 'username', 'email', 'password')";
   

4. Execute the query. The rowsAdded variable contains the number of rows added by the insert statement.

   Int32 rowsAdded = command.ExecuteNonQuery();
   

   The ExecuteNonQuery() method returns the number of rows affected by the command for UPDATE, INSERT, and DELETE statements. For all other types of statements it returns -1. If a rollback occurs then it is also set to -1.

Example usage:

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)
        {
            VerticaConnectionStringBuilder builder = new VerticaConnectionStringBuilder();
            builder.Host = "192.168.1.10";
            builder.Database = "VMart";
            builder.User = "dbadmin";
            VerticaConnection _conn = new VerticaConnection(builder.ToString());
            _conn.Open();
        VerticaCommand command = _conn.CreateCommand();
        command.CommandText =
               "INSERT into test values(2, 'username', 'email', 'password')";
        Int32 rowsAdded = command.ExecuteNonQuery();
        Console.WriteLine( rowsAdded + " rows added!");
            _conn.Close();
        }
    }
}

4.1.1 - Using parameters

You can use parameters to execute similar SQL statements repeatedly and efficiently.

Using parameters

VerticaParameters are an extension of the System.Data.DbParameter base class in ADO.NET and are used to set parameters in commands sent to the server. Use Parameters in all queries (SELECT/INSERT/UPDATE/DELETE) for which the values in the WHERE clause are not static; that is for all queries that have a known set of columns, but whose filter criteria is set dynamically by an application or end user. Using parameters in this way greatly decreases the chances of a SQL injection issue that can occur when simply creating a SQL query from a number of variables.

Parameters require that a valid DbType, VerticaDbType, or System type be assigned to the parameter. See Data types and ADO.NET data types for a mapping of System, Vertica, and DbTypes.

To create a parameter placeholder, place either the at sign (@) or a colon (:) character in front of the parameter name in the actual query string. Do not insert any spaces between the placeholder indicator (@ or :) and the placeholder.

For example, the following typical query uses the string 'MA' as a filter.

SELECT customer_name, customer_address, customer_city, customer_state
FROM customer_dimension WHERE customer_state = 'MA';

Instead, the query can be written to use a parameter. In the following example, the string MA is replaced by the parameter placeholder @STATE.

SELECT customer_name, customer_address, customer_city, customer_state
FROM customer_dimension WHERE customer_state = @STATE;

For example, the ADO.net code for the prior example would be written as:

VerticaCommand command = _conn.CreateCommand();
command.CommandText = “SELECT customer_name, customer_address, customer_city, customer_state
    FROM customer_dimension WHERE customer_state = @STATE”;
command.Parameters.Add(new VerticaParameter( “STATE”, VerticaType.VarChar));
command.Parameters["STATE"].Value = "MA";

4.1.2 - Creating and rolling back transactions

Creating transactions

Transactions in Vertica are atomic, consistent, isolated, and durable. When you connect to a database using the Vertica ADO.NET Driver, the connection is in autocommit mode and each individual query is committed upon execution. You can collect multiple statements into a single transaction and commit them at the same time by using a transaction. You can also choose to rollback a transaction before it is committed if your code determines that a transaction should not commit.

Transactions use the VerticaTransaction object, which is an implementation of DbTransaction. You must associate the transaction with the VerticaCommand object.

The following code uses an explicit transaction to insert one row each into to tables of the VMart schema.

To create a transaction in Vertica using the ADO.NET driver:

1. Create a connection to the database.

2. Create a command object using the connection.

   VerticaCommand command = _conn.CreateCommand();
   

3. Start an explicit transaction, and associate the command with it.

   VerticaTransaction txn = _conn.BeginTransaction();
   command.Connection = _conn;
   command.Transaction = txn;
   

4. Execute the individual SQL statements to add rows.

   command.CommandText =
        "insert into product_dimension values( ... )";
   command.ExecuteNonQuery();
   command.CommandText =
        "insert into store_orders_fact values( ... )";
   

5. Commit the transaction.

   txn.Commit();
   

Rolling back transactions

If your code checks for errors, then you can catch the error and rollback the entire transaction.

VerticaTransaction txn = _conn.BeginTransaction();
VerticaCommand command = new
        VerticaCommand("insert into product_dimension values( 838929, 5, 'New item 5' )", _conn);
// execute the insert
command.ExecuteNonQuery();
command.CommandText = "insert into product_dimension values( 838929, 6, 'New item 6' )";
// try insert and catch any errors
bool error = false;
try
{
    command.ExecuteNonQuery();
}
catch (Exception e)
{
    Console.WriteLine(e.Message);
    error = true;
}
if (error)
{
    txn.Rollback();
    Console.WriteLine("Errors. Rolling Back.");
}
else
{
    txn.Commit();
    Console.WriteLine("Queries Successful. Committing.");
}

Commit and rollback example

This example details how you can commit or rollback queries during a transaction.

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)
        {
            VerticaConnectionStringBuilder builder = new VerticaConnectionStringBuilder();
            builder.Host = "192.168.1.10";
            builder.Database = "VMart";
            builder.User = "dbadmin";
            VerticaConnection _conn = new VerticaConnection(builder.ToString());
            _conn.Open();
            bool error = false;
                VerticaCommand command = _conn.CreateCommand();
                VerticaCommand command2 = _conn.CreateCommand();
                VerticaTransaction txn = _conn.BeginTransaction();
                command.Connection = _conn;
                command.Transaction = txn;
                command.CommandText =
                "insert into test values(1, 'test', 'test', 'test' )";
                Console.WriteLine(command.CommandText);
                try
                {
                    command.ExecuteNonQuery();
                }
                catch (Exception e)
                {
                    Console.WriteLine(e.Message);
                    error = true;
                }
                command.CommandText =
                "insert into test values(2, 'ear', 'eye', 'nose', 'extra' )";
                Console.WriteLine(command.CommandText);
                try
                {
                    command.ExecuteNonQuery();
                }
                catch (Exception e)
                {
                    Console.WriteLine(e.Message);
                    error = true;
                }
                if (error)
                {
                    txn.Rollback();
                    Console.WriteLine("Errors. Rolling Back.");
                }
                else
                {
                    txn.Commit();
                    Console.WriteLine("Queries Successful. Committing.");
                }
            _conn.Close();
        }
    }
}

The example displays the following output on the console:

insert into test values(1, 'test', 'test', 'test' )
insert into test values(2, 'ear', 'eye', 'nose', 'extra' )
[42601]ERROR: INSERT has more expressions than target columns
Errors. Rolling Back.

See also

• Setting the transaction isolation level

4.1.2.1 - Setting the transaction isolation level

You can set the transaction isolation level on a per-connection and per-transaction basis. See Transaction for an overview of the transaction isolation levels supported in Vertica. To set the default transaction isolation level for a connection, use the IsolationLevel keyword in the VerticaConnectionStringBuilder string (see Connection String Keywords for details). To set the isolation level for an individual transaction, pass the isolation level to the VerticaConnection.BeginTransaction() method call to start the transaction.

To set the isolation level on a connection-basis:

1. Use the VerticaConnectionStringBuilder to build the connection string.

2. Provide a value for the IsolationLevel builder string. It can take one of two values: IsolationLevel.ReadCommited (default) or IsolationLevel.Serializeable. For example:

       VerticaConnectionStringBuilder builder = new VerticaConnectionStringBuilder();
       builder.Host = "192.168.1.100";
       builder.Database = "VMart";
       builder.User = "dbadmin";
       builder.IsolationLevel = System.Data.IsolationLevel.Serializeable
       VerticaConnection _conn1 = new VerticaConnection(builder.ToString());
       _conn1.Open();
   

To set the isolation level on a transaction basis:

1. Set the IsolationLevel on the BeginTransaction method, for example

   VerticaTransaction txn = _conn.BeginTransaction(IsolationLevel.Serializable);
   

Example usage:

The following example demonstrates:

• getting the connection's transaction isolation level.

• setting the connection's isolation level using connection property.

• setting the transaction isolation level for a new transaction.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
Vertica.Data.VerticaClient;
namespace ConsoleApplication
{
    class Program
    {
        static void Main(string[] args)
        {
            VerticaConnectionStringBuilder builder = new VerticaConnectionStringBuilder();
            builder.Host = "192.168.1.10";
            builder.Database = "VMart";
            builder.User = "dbadmin";
            VerticaConnection _conn1 = new VerticaConnection(builder.ToString());
             _conn1.Open();
            VerticaTransaction txn1 = _conn1.BeginTransaction();
            Console.WriteLine("\n Transaction 1 Transaction Isolation Level: " +
             txn1.IsolationLevel.ToString());
            txn1.Rollback();
            VerticaTransaction txn2 = _conn1.BeginTransaction(IsolationLevel.Serializable);
            Console.WriteLine("\n Transaction 2 Transaction Isolation Level: " +
             txn2.IsolationLevel.ToString());
            txn2.Rollback();
            VerticaTransaction txn3 = _conn1.BeginTransaction(IsolationLevel.ReadCommitted);
            Console.WriteLine("\n Transaction 3 Transaction Isolation Level: " +
             txn3.IsolationLevel.ToString());
            _conn1.Close();
        }
    }
}

When run, the example code prints the following to the system console:

 Transaction 1 Transaction Isolation Level: ReadCommitted
 Transaction 2 Transaction Isolation Level: Serializable
 Transaction 3 Transaction Isolation Level: ReadCommitted

4.2 - Reading data (ADO.Net)

To read data from the database use VerticaDataReader, an implementation of DbDataReader. This implementation is useful for moving large volumes of data quickly off the server where it can be run through analytic applications.

To read data from the database using VerticaDataReader:

1. Create a connection to the database.

2. Create a command object using the connection.

       VerticaCommand command = _conn.CreateCommand();
   

3. Create a query. This query works with the example VMart database.

           command.CommandText =
           "SELECT fat_content, product_description " +
           "FROM (SELECT DISTINCT fat_content, product_description" +
           "      FROM product_dimension " +
           "      WHERE department_description " +        "      IN ('Dairy') " +
           "      ORDER BY fat_content) AS food " +
           "LIMIT 10;";
   

4. Execute the reader to return the results from the query. The following command calls the ExecuteReader method of the VerticaCommand object to obtain the VerticaDataReader object.

   VerticaDataReader dr = command.ExecuteReader();
   

5. Read the data. The data reader returns results in a sequential stream. Therefore, you must read data from tables row-by-row. The following example uses a while loop to accomplish this:

    Console.WriteLine("\n\n Fat Content\t  Product Description");
        Console.WriteLine("------------\t  -------------------");
        int rows = 0;
        while (dr.Read())
        {
           Console.WriteLine("     " + dr[0] + "    \t  " + dr[1]);
           ++rows;
        }
        Console.WriteLine("------------\n  (" + rows + " rows)\n");
   

6. When you're finished, close the data reader to free up resources.

       dr.Close();
   

Example usage:

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)
        {
            VerticaConnectionStringBuilder builder = new VerticaConnectionStringBuilder();
            builder.Host = "192.168.1.10";
            builder.Database = "VMart";
            builder.User = "dbadmin";
            VerticaConnection _conn = new VerticaConnection(builder.ToString());
            _conn.Open();
        VerticaCommand command = _conn.CreateCommand();
            command.CommandText =
                "SELECT fat_content, product_description " +
                "FROM (SELECT DISTINCT fat_content, product_description" +
                "      FROM product_dimension " +
                "      WHERE department_description " +
                "      IN ('Dairy') " +
                "      ORDER BY fat_content) AS food " +
                "LIMIT 10;";
          VerticaDataReader dr = command.ExecuteReader();

         Console.WriteLine("\n\n Fat Content\t  Product Description");
         Console.WriteLine("------------\t  -------------------");
         int rows = 0;
         while (dr.Read())
         {
                Console.WriteLine("     " + dr[0] + "    \t  " + dr[1]);
                ++rows;
         }
         Console.WriteLine("------------\n  (" + rows + " rows)\n");
              dr.Close();
            _conn.Close();
        }
    }
}

4.3 - Loading data through ADO.Net

This section details the different ways that you can load data in Vertica using the ADO.NET client driver:

• Using the Vertica Data Adapter

• Using batch inserts and prepared statements
• Streaming data via ADO.NET

4.3.1 - Using the Vertica data adapter

The Vertica data adapter (VerticaDataAdapter) enables a client to exchange data between a data set and a Vertica database. It is an implementation of DbDataAdapter. You can use VerticaDataAdapter to simply read data, or, for example, read data from a database into a data set, and then write changed data from the data set back to the database.

Batching updates

When using the Update() method to update a dataset, you can optionally use the UpdateBatchSize() method prior to calling Update() to reduce the number of times the client communicates with the server to perform the update. The default value of UpdateBatchSize is 1. If you have multiple rows.Add() commands for a data set, then you can change the batch size to an optimal size to speed up the operations your client must perform to complete the update.

Reading data from Vertica using the data adapter:

The following example details how to perform a select query on the VMart schema and load the result into a DataTable, then output the contents of the DataTable to the console.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
Vertica.Data.VerticaClient;
namespace ConsoleApplication
{
    class Program
    {
        static void Main(string[] args)
        {
            VerticaConnectionStringBuilder builder = new VerticaConnectionStringBuilder();
            builder.Host = "192.168.1.10";
            builder.Database = "VMart";
            builder.User = "dbadmin";
            VerticaConnection _conn = new VerticaConnection(builder.ToString());
            _conn.Open();

            // Try/Catch any exceptions
                   try
            {
                using (_conn)
                {
                    // Create the command
                    VerticaCommand command = _conn.CreateCommand();
                    command.CommandText = "select product_key, product_description " +
                        "from product_dimension where product_key < 10";

                        // Associate the command with the connection
                        command.Connection = _conn;

                        // Create the DataAdapter
                        VerticaDataAdapter adapter = new VerticaDataAdapter();
                        adapter.SelectCommand = command;

                        // Fill the DataTable
                        DataTable table = new DataTable();
                        adapter.Fill(table);

                        //  Display each row and column value.
                        int i = 1;
                        foreach (DataRow row in table.Rows)
                        {
                            foreach (DataColumn column in table.Columns)
                            {
                                Console.Write(row[column] + "\t");
                            }
                            Console.WriteLine();
                            i++;
                        }
                    Console.WriteLine(i + " rows returned.");
                }
            }
            catch (Exception e)
            {
                Console.WriteLine(e.Message);
            }
            _conn.Close();
        }
    }
}

Reading data from Vertica into a data set and changing data:

The following example shows how to use a data adapter to read from and insert into a dimension table of the VMart schema.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using Vertica.Data.VerticaClient
namespace ConsoleApplication
{
    class Program
    {
        static void Main(string[] args)
        {
            VerticaConnectionStringBuilder builder = new VerticaConnectionStringBuilder();
            builder.Host = "192.168.1.10";
            builder.Database = "VMart";
            builder.User = "dbadmin";
            VerticaConnection _conn = new VerticaConnection(builder.ToString());
            _conn.Open();

                  // Try/Catch any exceptions
                    try
            {
                using (_conn)
                {

                            //Create a data adapter object using the connection
                            VerticaDataAdapter da = new VerticaDataAdapter();

                            //Create a select statement that retrieves data from the table
                            da.SelectCommand = new
                        VerticaCommand("select * from product_dimension where product_key < 10",
                        _conn);
                            //Set up the insert command for the data adapter, and bind variables for some of the columns
                            da.InsertCommand = new
                        VerticaCommand("insert into product_dimension values( :key, :version, :desc )",
                        _conn);
                    da.InsertCommand.Parameters.Add(new VerticaParameter("key", VerticaType.BigInt));
                    da.InsertCommand.Parameters.Add(new VerticaParameter("version", VerticaType.BigInt));
                    da.InsertCommand.Parameters.Add(new VerticaParameter("desc", VerticaType.VarChar));
                    da.InsertCommand.Parameters[0].SourceColumn = "product_key";
                    da.InsertCommand.Parameters[1].SourceColumn = "product_version";
                    da.InsertCommand.Parameters[2].SourceColumn = "product_description";
                    da.TableMappings.Add("product_key", "product_key");
                    da.TableMappings.Add("product_version", "product_version");
                    da.TableMappings.Add("product_description", "product_description");

                            //Create and fill a Data set for this dimension table, and get the resulting DataTable.
                            DataSet ds = new DataSet();
                    da.Fill(ds, 0, 0, "product_dimension");
                    DataTable dt = ds.Tables[0];

                            //Bind parameters and add two rows to the table.
                            DataRow dr = dt.NewRow();
                    dr["product_key"] = 838929;
                    dr["product_version"] = 5;
                    dr["product_description"] = "New item 5";
                    dt.Rows.Add(dr);
                    dr = dt.NewRow();
                    dr["product_key"] = 838929;
                    dr["product_version"] = 6;
                    dr["product_description"] = "New item 6";
                    dt.Rows.Add(dr);
                    //Extract the changes for the added rows.
                            DataSet ds2 = ds.GetChanges();

                            //Send the modifications to the server.
                            int updateCount = da.Update(ds2, "product_dimension");

                           //Merge the changes into the original Data set, and mark it up to date.
                            ds.Merge(ds2);
                    ds.AcceptChanges();
                    Console.WriteLine(updateCount + " updates made!");
                }
            }
            catch (Exception e)
            {
                Console.WriteLine(e.Message);
            }
            _conn.Close();
        }
    }
}

4.3.2 - Using batch inserts and prepared statements

You can load data in batches using a prepared statement with parameters. You can also use transactions to rollback the batch load if any errors are encountered.

If you are loading large batches of data (more than 100MB), then consider using a direct batch insert.

The following example details using data contained in arrays, parameters, and a transaction to batch load data.

The test table used in the example is created with the command:

=> CREATE TABLE test (id INT, username VARCHAR(24), email VARCHAR(64), password VARCHAR(8));

Example batch insert using parameters and transactions

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)
        {
            VerticaConnectionStringBuilder builder = new VerticaConnectionStringBuilder();
            builder.Host = "192.168.1.10";
            builder.Database = "VMart";
            builder.User = "dbadmin";
            VerticaConnection _conn = new VerticaConnection(builder.ToString());
            _conn.Open();
            // Create arrays for column data
                    int[] ids = {1, 2, 3, 4};
            string[] usernames = {"user1", "user2", "user3", "user4"};
            string[] emails = { "user1@example.com", "user2@example.com","user3@example.com","user4@example.com" };
            string[] passwords = { "pass1", "pass2", "pass3", "pass4" };
            // create counters for accepted and rejected rows
                    int rows = 0;
            int rejRows = 0;
            bool error = false;
            // Create the transaction
                    VerticaTransaction txn = _conn.BeginTransaction();
            // Create the parameterized query and assign parameter types
                    VerticaCommand command = _conn.CreateCommand();
            command.CommandText = "insert into TEST values (@id, @username, @email, @password)";
            command.Parameters.Add(new VerticaParameter("id", VerticaType.BigInt));
            command.Parameters.Add(new VerticaParameter("username", VerticaType.VarChar));
            command.Parameters.Add(new VerticaParameter("email", VerticaType.VarChar));
            command.Parameters.Add(new VerticaParameter("password", VerticaType.VarChar));
            // Prepare the statement
                    command.Prepare();

                    // Loop through the column arrays and insert the data
                    for (int i = 0; i < ids.Length; i++)            {
                command.Parameters["id"].Value = ids[i];
                command.Parameters["username"].Value = usernames[i];
                command.Parameters["email"].Value = emails[i];
                command.Parameters["password"].Value = passwords[i];
                try
                {
                    rows += command.ExecuteNonQuery();
                }
                catch (Exception e)
                {
                    Console.WriteLine("\nInsert failed - \n  " + e.Message + "\n");
                    ++rejRows;
                    error = true;
                }
            }
            if (error)
            {
                // Roll back if errors
                        Console.WriteLine("Errors. Rolling Back Transaction.");
                Console.WriteLine(rejRows + " rows rejected.");
                txn.Rollback();
            }
            else
            {
                // Commit if no errors
                        Console.WriteLine("No Errors. Committing Transaction.");
                txn.Commit();
                Console.WriteLine("Inserted " + rows + " rows. ");
            }
            _conn.Close();
        }
    }
}

4.3.3 - Streaming data via ADO.NET

There are two options to stream data from a file on the client to your Vertica database through ADO.NET:

• Use the VerticaCopyStream ADO.NET class to stream data in an object-oriented manner

• Execute a COPY LOCAL SQL statement to stream the data

The topics in this section explain how to use these options.

4.3.3.1 - Streaming from the client via VerticaCopyStream

The VerticaCopyStream class lets you stream data from the client system to a Vertica database. It lets you use the SQL COPY statement directly without having to copy the data to a host in the database cluster first by substituting one or more data stream(s) for STDIN.

Notes:

• Use Transactions and disable auto commit on the copy command for better performance.

• Disable auto commit using the copy command with the 'no commit' modifier. You must explicitly disable commits. Enabling transactions does not disable autocommit when using VerticaCopyStream.

• The copy command used with VerticaCopyStream uses copy syntax.

• VerticaCopyStream.rejects is zeroed every time execute is called. If you want to capture the number of rejects, assign the value of VerticaCopyStream.rejects to another variable before calling execute again.

• You can add multiple streams using multiple AddStream() calls.

Example usage:

The following example demonstrates using VerticaCopyStream to copy a file stream into Vertica.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.IO;
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";
            //open the connection
            VerticaConnection _conn = new VerticaConnection(builder.ToString());
            _conn.Open();
            try
            {
                using (_conn)
                {
                    // Start a transaction
                            VerticaTransaction txn = _conn.BeginTransaction();

                            // Create a table for this example
                            VerticaCommand command = new VerticaCommand("DROP TABLE IF EXISTS copy_table", _conn);
                command.ExecuteNonQuery();
                    command.CommandText = "CREATE TABLE copy_table (Last_Name char(50), "
                                    + "First_Name char(50),Email char(50), "
                                    + "Phone_Number char(15))";
                    command.ExecuteNonQuery();
                    // Create a new filestream from the data file
                            string filename = "C:/customers.txt";
                 Console.WriteLine("\n\nLoading File: " + filename);
                    FileStream inputfile = File.OpenRead(filename);
                    // Define the copy command
                            string copy = "copy copy_table from stdin record terminator E'\n' delimiter '|'" + " enforcelength "
                        + " no commit";
                    // Create a new copy stream instance with the connection and copy statement
                            VerticaCopyStream vcs = new VerticaCopyStream(_conn, copy);

                            // Start the VerticaCopyStream process
                            vcs.Start();
                            // Add the file stream
                            vcs.AddStream(inputfile, false);

                            // Execute the copy
                            vcs.Execute();

                            // Finish stream and write out the list of inserted and rejected rows
                            long rowsInserted = vcs.Finish();
                IList<long> rowsRejected = vcs.Rejects;
                // Does not work when rejected or exceptions defined
                    Console.WriteLine("Number of Rows inserted: " + rowsInserted);
                    Console.WriteLine("Number of Rows rejected: " + rowsRejected.Count);
                    if (rowsRejected.Count > 0)
                    {
                        for (int i = 0; i < rowsRejected.Count; i++)
                        {
                            Console.WriteLine("Rejected row #{0} is row {1}", i, rowsRejected[i]);
                        }
                    }

                            // Commit the changes
                            txn.Commit();
            }
            }
            catch (Exception e)
            {
                Console.WriteLine(e.Message);
            }


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

4.3.3.2 - Using copy with ADO.NET

To use COPY with ADO.NET, just execute a COPY statement and the path to the source file on the client system. This method is simpler than using the VerticaCopyStream class. However, you may prefer using VerticaCopyStream if you have many files to copy to the database or if your data comes from a source other than a local file (streamed over a network connection, for example).

The following example code demonstrates using COPY to copy a file from the client to the database. It is the same as the code shown in Bulk Loading Using the COPY Statement and the path to the data file is on the client system, rather than on the server.

To load data that is stored on a database node, use a VerticaCommand object to create a COPY command:

1. Create a connection to the database through the node on which the data file is stored.

2. Create a command object using the connection.

   VerticaCommand command = _conn.CreateCommand();
   

3. Copy data. The following is an example of using the COPY command to load data. It uses the LOCAL modifier to copy a file local to the client issuing the command.

   command.CommandText = "copy lcopy_table from '/home/dbadmin/customers.txt'"
     + " record terminator E'\n' delimiter '|'"
     + " enforcelength ";
   
   Int32 insertedRows = command.ExecuteNonQuery();
   Console.WriteLine(insertedRows + " inserted.");
   

Example usage:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.IO;
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";

                   // Open the connection
                    VerticaConnection _conn = new VerticaConnection(builder.ToString());
            _conn.Open();
            try
            {
                using (_conn)
                {

                            // Start a transaction
                            VerticaTransaction txn = _conn.BeginTransaction();

                            // Create a table for this example
                            VerticaCommand command = new VerticaCommand("DROP TABLE IF EXISTS lcopy_table", _conn);
                    command.ExecuteNonQuery();
                    command.CommandText = "CREATE TABLE IF NOT EXISTS lcopy_table (Last_Name char(50), "
                                    + "First_Name char(50),Email char(50), "
                                    + "Phone_Number char(15))";
                    command.ExecuteNonQuery();
                    // Define the copy command
                            command.CommandText = "copy lcopy_table from '/home/dbadmin/customers.txt'"
            + " record terminator E'\n' delimiter '|'"
                        + " enforcelength "
                + " no commit";
                            // Execute the copy
        Int32 insertedRows = command.ExecuteNonQuery();
Console.WriteLine(insertedRows + " inserted.");
                            // Commit the changes
                            txn.Commit();
                }
            }
            catch (Exception e)
            {
                Console.WriteLine("Exception: " + e.Message);
            }


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

5 - Canceling ADO.NET queries

You can cancel a running vsql query by calling the .Cancel() method of any Command object. The SampleCancelTests class demonstrates how to cancel a query after reading a specified number of rows. It implements the following methods:

• SampleCancelTest() executes the Setup() function to create a test table. Then, it calls RunQueryAndCancel() and RunSecondQuery() to demonstrate how to cancel a query after it reads a specified number of rows. Finally, it runs the Cleanup() function to drop the test table.
• Setup() creates a database for the example queries.
• Cleanup() drops the database.
• RunQueryAndCancel() reads exactly 100 rows from a query that returns more than 100 rows.
• RunSecondQuery() reads all rows from a query.

using System;
using Vertica.Data.VerticaClient;

class SampleCancelTests
{
    // Creates a database table, executes a query that cancels during a read loop,
    // executes a query that does not cancel, then drops the test database table.
    // connection: A connection to a Vertica database.

    public static void SampleCancelTest(VerticaConnection connection)
    {
        VerticaCommand command = connection.CreateCommand();

        Setup(command);

        try
        {
            Console.WriteLine("Running query that will cancel after reading 100 rows...");
            RunQueryAndCancel(command);
            Console.WriteLine("Running a second query...");
            RunSecondQuery(command);
            Console.WriteLine("Finished!");
        }
        finally
        {
            Cleanup(command);
        }
    }

    // Set up the database table for the example.
    // command: A Command object used to execute the query.
    private static void Setup(VerticaCommand command)
    {
        // Create table used for test.
        Console.WriteLine("Creating and loading table...");
        command.CommandText = "DROP TABLE IF EXISTS adocanceltest";
        command.ExecuteNonQuery();
        command.CommandText = "CREATE TABLE adocanceltest(id INTEGER, time TIMESTAMP)";
        command.ExecuteNonQuery();
        command.CommandText = @"INSERT INTO adocanceltest
        SELECT row_number() OVER(), slice_time
            FROM(
                    SELECT slice_time FROM(
                    SELECT '2021-01-01'::timestamp s UNION ALL SELECT '2022-01-01'::timestamp s
                    ) sq TIMESERIES slice_time AS '1 second' OVER(ORDER BY s)
            ) sq2";
        command.ExecuteNonQuery();
    }

    // Clean up the database after running the example.
    // command: A Command object used to execute the query.
    private static void Cleanup(VerticaCommand command)
    {
        command.CommandText = "DROP TABLE IF EXISTS adocanceltest";
        command.ExecuteNonQuery();
    }

    // Execute a query that returns many rows and cancels after reading 100.
    // command: A Command object used to execute the query.
    private static void RunQueryAndCancel(VerticaCommand command)
    {
        command.CommandText = "SELECT COUNT(id) from adocanceltest";
        int fullRowCount = Convert.ToInt32(command.ExecuteScalar());

        command.CommandText = "SELECT id, time FROM adocanceltest";
        VerticaDataReader dr = command.ExecuteReader();
        int nCount = 0;
        try
        {
            while (dr.Read())
            {
                nCount++;
                if (nCount == 100)
                {
                    // After reaching 100 rows, cancel the command
                    // Note that it is not necessary to read the remaining rows
                    command.Cancel();
                    return;
                }
            }
        }
        catch (Exception e)
        {
            Console.WriteLine(e.Message);
        }
        finally
        {
            dr.Close();
            // Verify that the cancel stopped the query
            Console.WriteLine((fullRowCount - nCount) + " rows out of " + fullRowCount + " discarded by cancel");
        }
    }

    // Execute a simple query and read all results.
    // command: A Command object used to execute the query.
    private static void RunSecondQuery(VerticaCommand command)
    {
        command.CommandText = "SELECT 1 FROM dual";
        VerticaDataReader dr = command.ExecuteReader();
        try
        {
            while (dr.Read())
            {
                ;
            }
        }
        catch (Exception e)
        {
            Console.WriteLine(e.Message);
            Console.WriteLine("Warning: no exception should be thrown on query after cancel");
        }
        finally
        {
            dr.Close();
        }
    }
}

6 - Handling messages

You can capture info and warning messages that Vertica provides to the ADO.NET driver by using the InfoMessage event on the VerticaConnection delegate class. This class captures messages that are not severe enough to force an exception to be triggered, but might still provide information that can benefit your application.

To use the VerticaInfoMessageEventHander class:

1. Create a method to handle the message sent from the even handler:

   static void conn_InfoMessage(object sender, VerticaInfoMessageEventArgs e)
   {
       Console.WriteLine(e.SqlState + ": " + e.Message);
   }
   

2. Create a connection and register a new VerticaInfoMessageHandler delegate for the InfoMessage event:

   _conn.InfoMessage += new VerticaInfoMessageEventHandler(conn_InfoMessage);
   

3. Execute your queries. If a message is generated, then the event handle function is run.

4. You can unsubscribe from the event with the following command:

   _conn.InfoMessage -= new VerticaInfoMessageEventHandler(conn_InfoMessage);
   

Examples

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using Vertica.Data.VerticaClient;
namespace ConsoleApplication {
  class Program {
    // define message handler to deal with messages
    static void conn_InfoMessage(object sender, VerticaInfoMessageEventArgs e) {
      Console.WriteLine(e.SqlState + ": " + e.Message);
    }
    static void Main(string[] args) {
      //configure connection properties
      VerticaConnectionStringBuilder builder = new VerticaConnectionStringBuilder();
      builder.Host = "192.168.1.10";
      builder.Database = "VMart";
      builder.User = "dbadmin";

      //open the connection
      VerticaConnection _conn = new VerticaConnection(builder.ToString());
      _conn.Open();

      //create message handler instance by subscribing it to the InfoMessage event of the connection
      _conn.InfoMessage += new VerticaInfoMessageEventHandler(conn_InfoMessage);

      //create and execute the command
      VerticaCommand cmd = _conn.CreateCommand();
      cmd.CommandText = "drop table if exists fakeTable";
      cmd.ExecuteNonQuery();

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

This examples displays the following when run:

00000: Nothing was dropped

7 - Getting table metadata

You can get the table metadata by using the GetSchema() method on a connection and loading the metadata into a DataTable:

• database_name, schema_name, and table_name can be set to null, a specific name, or use a LIKE pattern.

• table_type can be one of:

  • "SYSTEM TABLE"

  • "TABLE"

  • "GLOBAL TEMPORARY"

  • "LOCAL TEMPORARY"

  • "VIEW"

  • null

• If table_type is null, then the metadata for all metadata tables is returned.

For example:

DataTable table = _conn.GetSchema("Tables", new string[] { null, null, null, "SYSTEM TABLE" });

Examples

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";

            // open the connection
            VerticaConnection _conn = new VerticaConnection(builder.ToString());
            _conn.Open();

            // create a new data table containing the schema
            // the last argument can be "SYSTEM TABLE", "TABLE", "GLOBAL TEMPORARY",
            // "LOCAL TEMPORARY", "VIEW", or null for all types
            DataTable table = _conn.GetSchema("Tables", new string[] { null, null, null, "SYSTEM TABLE" });

            // print out the schema
            foreach (DataRow row in table.Rows) {
                foreach (DataColumn col in table.Columns)
                {
                    Console.WriteLine("{0} = {1}", col.ColumnName, row[col]);
                }
                Console.WriteLine("============================");
            }

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