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