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();
        }
    }
}