.NET Database Wrapper

Extension for IDbConnection that simplifies database communication. Currently just support PostgreSQL.

Simple querying database

Model used in example:

public class Person
{
    public virtual int Id { get; set; }
    public string FirstName { get; set; }
    public string LastName { get; set; }
    public int Age { get; set; }
    public Gender Gender { get; set; }
    public DateTime DateCreated { get; set; }
}

public enum Gender
{
    Unknown,
    Male,
    Female
}

Querying database and map to model, assuming c is of type IDbConnection:

Person[] persons = c.Query<Person>("SELECT * FROM persons").ToArray();

persons now contains an array of Person with data pulled from the database.

Quite simple, right? Check out more examples below. Don't forget to read the "Important!" notes further down.

Examples

Examples below will assume that c is of type IDbConnection.

Basic query

a list of the given type

c.Query<Person>("SELECT * FROM persons");

Assoc query

a list of dictionary where key matches columns name

c.QueryAssoc("SELECT id,first_name FROM persons");

Array query

a list of array where index matches columns index

c.QueryArray("SELECT id,first_name FROM persons");

Insert

inserts document into table documents

var document = new Document
{
    Id = Guid.NewGuid(),
    Name = "foo.txt",
    Data = Gender.Male,
    DateCreated = DateTime.Now
};

c.Insert("documents", document);

Or if you have a table containing an auto generated id

var person = new Person
{
    FirstName = "Dennis",
    LastName = "Ahlquist",
    Gender = Gender.Male,
    DateCreated = DateTime.Now
};

person.Id = c.Insert<int>("persons", person, "id");

Update

updates table persons and set person where id=1

c.Update("persons", person, "id=@Id", new { Id = 1 });

Delete

deletes from table persons where first_name="Dennis"

c.Delete("persons", "first_name=@FirstName", new { FirstName = "Dennis" });

Execute

execute anything, returning rows affected

c.Execute("TRUNCATE persons");

Decorated classes

You can do even simpler Inserts, Updates and Deletes by decorating your classes with Table, Key and Generated attributes.

[Table("persons")]
public class Person
{
    [Key]
    [Generated]
    public virtual long Id { get; set; }
    public string FirstName { get; set; }
    public string LastName { get; set; }
    public int? Age { get; set; }
    public Gender Gender { get; set; }
    [Generated]
    public DateTime? DateCreated { get; set; }
}

Get

c.Get<Person>(1);

will read table "persons" and return an object of Person from the database where id is 1.

Insert

c.Insert(person);

will insert into table "persons" and map properties marked with Generated to the object.

Update

c.Update(person);

updates table "persons" matching the key field(s).

Delete

c.Delete(person);

deletes from table "persons" matching the key field(s).

Important!

virtual and [Generated] properties is ignored on Insert and Update, but will be mapped with Query. Useful when there is an autogenerated id or you have a property in your model that doesn't match a column in the table.

yield is used in Query-methods. Make sure to read data before disposing the connection by calling .ToList() or .ToArray().

All methods will open the connection, if not opened yet.

Uppercase in properties names will assume that the column name has an underscore before e.g. FirstName matches first_name column in database.

No more DBNull! Seamless translates DBNull to null and null to DBNull.