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