Link Search Menu Expand Document
RepoDB

Get Started for PostgreSQL

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

Installation

The library can be installed via Nuget. In your Package Manager Console, type the command below.

> Install-Package RepoDb.PostgreSql

If you are to work with bulk operations (BinaryBulkDelete, BinaryBulkInsert, BinaryBulkMerge and BinaryBulkUpdate), then install the RepoDb.PostgreSql.BulkOperations package.

> Install-Package RepoDb.PostgreSql.BulkOperations

Once installed, call the globalized setup method to initialize all the dependencies for PostgreSql.

GlobalConfiguration
	.Setup()
	.UsePostgreSql();

For the users prior the version 1.13.0, use the bootstrapper code below.

RepoDb.PostgreSqlBootstrap.Initialize();

Or, visit our installation page for more information.

Create a Table

Let us say you have this table on your database.

CREATE TABLE IF NOT EXISTS "Person"
(
    "Id" bigint(20) NOT NULL AUTO_INCREMENT,
    "Name" text,
    "Age" int(11) DEFAULT NULL,
    "CreatedDateUtc" datetime DEFAULT NULL
);

Create a Model

And you have this model on your 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 create a row, use the Insert method.

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

To insert multiple rows, use the InsertAll operation.

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

The Insert method returns the value of identity/primary column, while the InsertAll method returns the number of rows inserted. Both methods are automatically setting back the value of identity/primary property to the entity model (if present).

Querying a Record

To query a row, use the Query method.

using (var connection = new NpgsqlConnection(ConnectionString))
{
    var person = connection.Query<Person>(e => e.Id == 1);
    /* Do the stuffs for the 'person' here */
}

To query all the rows, use the QueryAll method.

using (var connection = new NpgsqlConnection(ConnectionString))
{
    var people = connection.QueryAll<Person>();
    /* Do the stuffs for the 'people' here */
}

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

By default, the primary column is used as a qualifier. You can also customize the qualifiers with other columns.

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

To merge all the rows, use the MergeAll method.

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

The Merge method returns the identity/primary column value, while the MergeAll method returns the number of rows affected. Both methods are automatically setting back the value of identity/primary property if present.

Deleting a Record

To delete a row, use the Delete method.

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

By default, the primary column is used as a qualifier, but you can also use the other columns like below.

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

To delete all the rows, use the DeleteAll method.

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

You can also pass the list of primary keys to be deleted.

using (var connection = new NpgsqlConnection(connectionString))
{
    var primaryKeys = new [] { 10045, 11001, ..., 12011 };
    var deletedRows = connection.DeleteAll<Person>(primaryKeys);
}

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,
    DateInsertedUtc = DateTime.UtcNow
};
using (var connection = new NpgsqlConnection(ConnectionString))
{
    var updatedRows = connection.Update<Person>(person);
}

You can also update via dynamic by targeting certain columns.

using (var connection = new NpgsqlConnection(ConnectionString))
{
    var updatedRows = connection.Update("Person", new { Id = 1, Name = "James Doe" });
}

To update all the rows, use the UpdateAll method.

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

By default, the primary column is used as a qualifier, but you can also specify your custom qualifiers.

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

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

Executing a Query

To execute a query, use the ExecuteNonQuery method.

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

To execute a query while expecting a result of class object, use the ExecuteQuery method.

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

To execute a query while expecting a single result, use the ExecuteScalar method.

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

To execute a query while expecting a result of DbDataReader object, use the ExecuteReader method.

using (var connection = new NpgsqlConnection(ConnectionString))
{
    var sql = "SELECT * FROM \"Person\" ORDER BY \"Id\" ASC;";
    using (var reader = connection.ExecuteReader(sql))
    {
        /* Do the stuffs for the data reader here */
    }
}

Typed Result Execution

You can infer the single-column resultsets to any .NET CLR type via ExecuteQuery operation.

using (var connection = new NpgsqlConnection(ConnectionString))
{
    var sql = "SELECT \"Name\" FROM \"Person\" WHERE \"Id\" = @Id;";
    var name = connection.ExecuteQuery<string>(sql, new { Id = 10045 });
}

And even with the enumerations.

public enum Gender
{
    Male,
    Female
}

Then, call it like below.

using (var connection = new NpgsqlConnection(ConnectionString))
{
    var sql = "SELECT \"Gender\" FROM \"Person\" WHERE \"Id\" = @Id;";
    var name = connection.ExecuteQuery<Gender>(sql, new { Id = 10045 });
}

The resultset of this operation is an [IEnumerable](https://learn.microsoft.com/en-us/dotnet/api/system.collections.generic.ienumerable-1?view=net-7.0) object.