dk2Repository

Getting Started

dk2Database.dll contains the access layer. If you want to make use of the or-mapper also reference to dk2Repository.dll in your project.

A default connection is created by the static method Databases.RegisterDefault().

Registering database

public static void Main()
{ 
  Databases.RegisterDefault(new SqLiteDatabase(@"c:\mydb.sqlite", ""));
}

Executing SQL Statements

Execute

Databases.Default.Execute("UPDATE Users SET Salary = ? WHERE  DepartmentId < ?", 40000, 2);  

Execute scalar

string date = Databases.Default.ExecuteScalar("SELECT Date()").ToString();

DataTable Query

DataTable dt =  Databases.Default.Query("SELECT * FROM Users");

Lambda Query

IList<User> users = Databases.Default.Query(rdr =>
    {
        IList<User> list = new List<User>();

        while (rdr.Read())
        {
            var user = new User();
            user.Id = (int)rdr[0];
            user.Name = rdr[0].ToString();

            list.Add(user);
        }
        return list;

    }, "SELECT Id, Name FROM Users");

Command Query

class SelectUserByAgeCommand : QueryEntityCommandBase<IList<User>>
{
    public SelectUserByAgeCommand(int age)
        : base("SELECT Id, Name, Age FROM Users WHERE Age > @age")
    {
        Parameters.Add("age", age);
    }

    public override IList<User> ProcessDataReader(DbDataReader rdr)
    {
        var users = new List<User>();

        while (rdr.Read())
        {
            var user = new User();

            user.Id = (int)rdr[0];
            user.Name = rdr[1].ToString();
            user.Age = (int)rdr[2];

            users.Add(user);
        }
        return users;
    }
}

Databases.Default.Query(new SelectUserByAgeCommand(40));

Using the Repository

Entitiy classes must contain attributes that provide the table definition

Entity User

[Table("Users")]
class User
{
    public enum enumGender
    {
        Male, Female
    }

    [PrimaryKey(true)]
    [Column]
    public int Id { get; set; }

    [Column("Name", 100)]
    public string Name { get; set; }

    [Column("DateOfBirth", true)]
    public DateTime? DateOfBirth { get; set; }

    [Column]
    public int Age { get; set; }

    [Column]
    public enumGender Gender { get; set; }
}

Selecting entities

IList<User> users = Repository<User>.SelectAll();

IList<User> users = Repository<User>.Select("Age > ?", "Age ASC", 35);

IList<User> users = Repository<User>.Query("Select Id, Name, Age FROM Users WHERE Age > ? ORDER BY Age ASC", 35);

Update, insert, delete

Repository<User>.Update(user);

Repository<User>.Insert(user);

Repository<User>.Delete(user);

Tracked Repository

If an entity class is derived from the Repository class it provides extended functionality

Entity User

[Table("Users")]
class User : Repository<User>
{
    public enum enumGender
    {
        Male, Female
    }

    [PrimaryKey(true)]
    [Column]
    public int Id { get; set; }

    [Column("Name", 100)]
    public string Name { get; set; }

    [Column("DateOfBirth", true)]
    public DateTime? DateOfBirth { get; set; }

    [Column]
    public int Age { get; set; }

    [Column]
    public enumGender Gender { get; set; }
}

Selecting entities

IList<User> users = User.SelectAll();

IList<User> users = User.Select("Age > ?", "Age ASC", 35);

IList<User> users = User.Query("Select Id, Name, Age FROM Users WHERE Age > ? ORDER BY Age ASC", 35);

Update, insert, delete

user.Update();

user.Insert();

user.Delete();

Status, Save()

User u = new User();

u.Name = "Pep";
u.Age = 30;
Console.WriteLine(u.Status.ToString() + " - " + u.Id.ToString()); // New - 0

u.Save();
Console.WriteLine(u.Status.ToString() + " - " + u.Id.ToString()); // Unchanged - 1

u.Age = 21;
Console.WriteLine(u.Status.ToString() + " - " + u.Id.ToString()); // Updated - 1

u.Save();
Console.WriteLine(u.Status.ToString() + " - " + u.Id.ToString()); // Unchanged - 1

u = User.SelectById(1);
Console.WriteLine(u.Status.ToString() + " - " + u.Id.ToString() ); // Unchanged - 1

u.Age = 25;
Console.WriteLine(u.Status.ToString() + " - " + u.Id.ToString()); // Updated - 1

u.Delete();
Console.WriteLine(u.Status.ToString() + " - " + u.Id.ToString()); // Deleted - 1

u = User.SelectById(1);
// u is null