Get Started for Firebird

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

Support ships as a single package:

RepoDb.Firebird — the core provider, built on FirebirdSql.Data.FirebirdClient.

Targets Firebird 3.0 and later. Identity-column introspection relies on RDB$RELATION_FIELDS.RDB$IDENTITY_TYPE/RDB$GENERATOR_NAME, which do not exist on Firebird 2.5 and earlier — tables whose auto-increment behavior is implemented the pre-3.0 way (a BEFORE INSERT trigger plus a bare generator/sequence) are not detected as identity columns.

Installation

Install the library via NuGet using the Package Manager Console.

> Install-Package RepoDb.Firebird

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

GlobalConfiguration
    .Setup()
    .UseFirebird();

Create a Table

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

RECREATE TABLE "Person"
(
    "Id" BIGINT GENERATED BY DEFAULT AS IDENTITY NOT NULL,
    "Name" VARCHAR(128) NOT NULL,
    "Age" INTEGER NOT NULL,
    "CreatedDateUtc" TIMESTAMP NOT NULL,
    PRIMARY KEY ("Id")
);

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 int Age { get; set; }
    public DateTime CreatedDateUtc { get; set; }
}

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 FbConnection(ConnectionString))
{
    var id = connection.Insert(person);
}

To insert multiple rows, use the InsertAll operation.

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

The Insert method returns the generated key via Firebird’s RETURNING clause, and both Insert and InsertAll set it back onto the identity/primary property automatically (if present). Unlike SQL Server or Db2, Firebird’s ADO.NET provider (FbCommand) cannot execute multiple statements in one round trip, so InsertAll issues one INSERT per row rather than a single batched statement — passing an explicit batchSize greater than 1 throws a NotSupportedException.

Querying a Record

To query a row, use the Query method.

using (var connection = new FbConnection(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 FbConnection(ConnectionString))
{
    var people = connection.QueryAll<Person>();
    /* Process the results here */
}

Firebird 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 FbConnection(ConnectionString))
{
    var id = connection.Merge(person);
}

By default, the primary 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 FbConnection(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 FbConnection(ConnectionString))
{
    var affectedRecords = connection.MergeAll<Person>(people);
}

Merge compiles to Firebird’s native UPDATE OR INSERT INTO ... MATCHING (...) RETURNING ... upsert statement. The same one-round-trip-per-row caveat as InsertAll applies to MergeAll.

Deleting a Record

To delete a row, use the Delete method.

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

Other columns can also be used as qualifiers.

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

To delete all rows, use the DeleteAll method.

using (var connection = new FbConnection(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 FbConnection(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 FbConnection(ConnectionString))
{
    var updatedRows = connection.UpdateAll<Person>(people);
}

Both the Update and UpdateAll methods return the number of rows affected during the execution. As with InsertAll/MergeAll, UpdateAll with a batchSize greater than 1 throws a NotSupportedException.

Executing a Query

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

using (var connection = new FbConnection(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 FbConnection(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 FbConnection(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 FbConnection(ConnectionString))
{
    var sql = "SELECT * FROM \"Person\"";
    using (var reader = connection.ExecuteReader(sql))
    {
        /* Process the data reader here */
    }
}

Typed Result Execution

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

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

The result of this operation is an IEnumerable object.