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

Return to the regular view of this page.

Inserting data (ADO.NET)

Inserting data can done using the VerticaCommand class.

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

1 - Using parameters

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

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

2 - Creating and rolling back transactions

Transactions in Vertica are atomic, consistent, isolated, and durable.

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

2.1 - Setting the transaction isolation level

You can set the transaction isolation level on a per-connection and per-transaction basis.

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