Link Search Menu Expand Document

Get Started for Vertica

RepoDB is a hybrid .NET ORM library for Vertica Database. The project is hosted at Github and is licensed with Apache 2.0.

Support ships as two packages:

Installation

Install the library via NuGet using the Package Manager Console.

> Install-Package RepoDb.Vertica

After installation, call the globalized setup method to initialize all dependencies for Vertica.

GlobalConfiguration
    .Setup()
    .UseVertica();

UseVertica() also forces the calling thread’s, and every subsequently-created thread’s, CultureInfo.CurrentCulture to CultureInfo.InvariantCulture. Vertica.Data formats/re-parses date-like parameter values using the ambient thread culture rather than CultureInfo.InvariantCulture — on a machine whose culture renders time with a non-colon separator (e.g. en-DK’s 13.45.30), this corrupts the value the driver actually sends. There is no per-call interception point available to a provider, so this is applied process-wide rather than scoped to Vertica calls specifically.

To use bulk operations (BulkDelete, BulkDeleteByKey, BulkInsert, BulkMerge and BulkUpdate — see Operations (Vertica)), install the RepoDb.Vertica.BulkOperations package.

> Install-Package RepoDb.Vertica.BulkOperations

Create a Table

The examples below assume the following table exists in the database.

CREATE TABLE "Person"
(
    "Id" IDENTITY(1, 1),
    "Name" VARCHAR(128),
    "Age" INTEGER,
    "CreatedDateUtc" TIMESTAMP
);

Create a Model

The examples below assume the following model exists in the application.

public class Person
{
    public long Id { get; set; }
    public string Name { get; set; }
    public long Age { get; set; }
    public DateTime CreatedDateUtc { get; set; }
}

Vertica has no distinct storage widths for its integer types — SMALLINT/INTEGER/BIGINT are all synonyms for one 8-byte integer, reported back to ADO.NET as long. Map integer-looking columns as long rather than int to avoid a cast failure when reading them back.

Creating a Record

To insert a row, use the Insert method.

var person = new Person
{
    Name = "John Doe",
    Age = 54,
    CreatedDateUtc = DateTime.UtcNow
};
using (var connection = new VerticaConnection(ConnectionString))
{
    var id = connection.Insert(person);
}

To insert multiple rows, use the InsertAll operation.

var people = GetPeople(100);
using (var connection = new VerticaConnection(ConnectionString))
{
    var rowsInserted = connection.InsertAll(people);
}

Unlike most providers, Vertica’s IsMultiStatementExecutable is false (VerticaCommand refuses a compound ;-separated statement once it carries a parameter) yet InsertAll still batches multiple rows into one genuine multi-row INSERT ... VALUES (...), (...), ... statement — the IsInsertAllBatchable database setting overrides IsMultiStatementExecutable specifically for this shape. MergeAll/UpdateAll, which have no equivalent single-statement shape, still issue one round trip per row; passing an explicit batchSize greater than 1 to either throws a NotSupportedException.

Querying a Record

To query a row, use the Query method.

using (var connection = new VerticaConnection(ConnectionString))
{
    var person = connection.Query<Person>(e => e.Id == 1);
    /* Process the result here */
}

To query all rows, use the QueryAll method.

using (var connection = new VerticaConnection(ConnectionString))
{
    var people = connection.QueryAll<Person>();
    /* Process the results here */
}

Vertica has no table-hint syntax (AreTableHintsSupported is false) — passing a hints argument to any operation throws a NotSupportedException.

Merging a Record

To merge a row, use the Merge method.

var person = new Person
{
    Id = 1,
    Name = "John Doe",
    Age = 57,
    CreatedDateUtc = DateTime.UtcNow
};
using (var connection = new VerticaConnection(ConnectionString))
{
    var id = connection.Merge(person);
}

By default, the primary or identity column is used as a qualifier. Custom qualifiers can also be specified.

var person = new Person
{
    Name = "John Doe",
    Age = 57,
    CreatedDateUtc = DateTime.UtcNow
};
using (var connection = new VerticaConnection(ConnectionString))
{
    var id = connection.Merge(person, qualifiers: (p => new { p.Name }));
}

To merge multiple rows, use the MergeAll method.

var people = GetPeople(100);
people
    .AsList()
    .ForEach(p => p.Name = $"{p.Name} (Merged)");
using (var connection = new VerticaConnection(ConnectionString))
{
    var affectedRecords = connection.MergeAll<Person>(people);
}

Vertica flatly refuses to run a MERGE statement against a table with an IDENTITY/AUTO_INCREMENT column at all (“Sequence or IDENTITY/AUTO_INCREMENT column in merge query is not supported”), and has no procedural fallback equivalent to Firebird’s EXECUTE BLOCK. Merge/MergeAll are instead always compiled as an UPDATE ... followed by an INSERT ... WHERE NOT EXISTS (...), joined by ; into a single command text — verify this against a live instance before relying on it in production, since VerticaCommand is documented elsewhere (see Operations (Vertica)) to refuse a compound statement that carries parameters.

Deleting a Record

To delete a row, use the Delete method.

using (var connection = new VerticaConnection(ConnectionString))
{
    var deletedRows = connection.Delete<Person>(1);
}

Other columns can also be used as qualifiers.

using (var connection = new VerticaConnection(ConnectionString))
{
    var deletedRows = connection.Delete<Person>(p => p.Name == "John Doe");
}

To delete all rows, use the DeleteAll method.

using (var connection = new VerticaConnection(ConnectionString))
{
    var deletedRows = connection.DeleteAll<Person>();
}

Both the Delete and DeleteAll methods return the number of rows affected during the execution.

Updating a Record

To update a row, use the Update method.

var person = new Person
{
    Id = 1,
    Name = "James Doe",
    Age = 55,
    CreatedDateUtc = DateTime.UtcNow
};
using (var connection = new VerticaConnection(ConnectionString))
{
    var updatedRows = connection.Update<Person>(person);
}

To update multiple rows, use the UpdateAll method.

var people = GetPeople(100);
people
    .AsList()
    .ForEach(p => p.Name = $"{p.Name} (Updated)");
using (var connection = new VerticaConnection(ConnectionString))
{
    var updatedRows = connection.UpdateAll<Person>(people);
}

Both the Update and UpdateAll methods return the number of rows affected during the execution.

Executing a Query

To execute a non-query statement, use the ExecuteNonQuery method.

using (var connection = new VerticaConnection(ConnectionString))
{
    var sql = "DELETE FROM \"Person\" WHERE \"Id\" = @Id";
    var affectedRecords = connection.ExecuteNonQuery(sql, new { Id = 1 });
}

To execute a query and return mapped objects, use the ExecuteQuery method.

using (var connection = new VerticaConnection(ConnectionString))
{
    var sql = "SELECT * FROM \"Person\" ORDER BY \"Id\" ASC";
    var people = connection.ExecuteQuery<Person>(sql);
    /* Process the results here */
}

To execute a query and return a scalar value, use the ExecuteScalar method.

using (var connection = new VerticaConnection(ConnectionString))
{
    var sql = "SELECT MAX(\"Id\") FROM \"Person\"";
    var maxId = connection.ExecuteScalar<long>(sql);
}

To execute a query and return a DbDataReader, use the ExecuteReader method.

using (var connection = new VerticaConnection(ConnectionString))
{
    var sql = "SELECT * FROM \"Person\"";
    using (var reader = connection.ExecuteReader(sql))
    {
        /* Process the data reader here */
    }
}

Executing a Stored Procedure

To execute a stored procedure, use any of the execute methods above and pass CommandType.StoredProcedure to the commandType argument.

using (var connection = new VerticaConnection(ConnectionString))
{
    var people = connection.ExecuteQuery<Person>("GetPeople",
        commandType: CommandType.StoredProcedure);
}

Beware of not putting a semi-colon at the end of the calls.

Alternatively, use the CALL command directly, which does not require the commandType argument.

using (var connection = new VerticaConnection(ConnectionString))
{
    var people = connection.ExecuteQuery<Person>("CALL GetPeople()");
}

CALL is Vertica’s syntax for invoking an actual stored procedure (available since Vertica 10). A scalar user-defined function, by contrast, is invoked like any other expression via SELECT function_name(...), not CALL.

You can also use the types defined at the Passing of Parameters section when passing a parameter.

Typed Result Execution

Single-column result sets can be mapped to any .NET CLR type via ExecuteQuery.

using (var connection = new VerticaConnection(ConnectionString))
{
    var sql = "SELECT \"Name\" FROM \"Person\"";
    var names = connection.ExecuteQuery<string>(sql);
}

The result of this operation is an IEnumerable object.