Simple Data Access

Sometimes I want the "mapping" capabilities of an ORM, but I don't want the overhead of a full blown ORM like NHibernate or Entity Framework.

I have looked into micro-ORMs like Massive and SimpleData, and like where they are heading.

I have developed my own version of a simple data access library to help with CRUD operations and convention based mapping (map column names to object properties).

Here is some sample code to help you understand:

//Entity
public class Club
{
    public string Code { get; set; }
    public string Name { get; set; }
    public string HomeGround { get; set; }
    public DateTime EstablishedDate { get; set; }
}

//Create simple database
var db= new SimpleDatabase(DatabaseType.SqlServer, ConnectionString);

//Some data
var data = new Club { Code = "MFC", Name = "Melbourne Demons", HomeGround = "MCG", EstablishedDate = new DateTime(1858, 1, 1) };

//Insert
db.Insert<Club>()
  .Values(_data)
  .Go();

//Update
db.Update<Club>()
  .Set(data)
  .WhereEquals(c => c.Code, data.Code)
  .Go();

//Delete
db.Delete<Club>()
  .WhereEquals(c => c.Code, data.Code)
  .Go();

//Select
var results = db.Select<Club>()
                .WhereEquals(c => c.Code, "MFC")
                .AndEquals(c => c.HomeGround, "MCG")
                .ToList();

At first it may look like I'm reinventing LINQ to SQL, but I am not. The key difference with this is that you are not tied to an object structure or restricted to strongly typed properties. If at any point you need to just go back to plain old strings add some extra clauses, select from a table that is named different from your object, or if you need to insert some additional columns not on your object you have the flexibility:

db.Delete<ClubDTO>("Club")
  .WhereEquals("Name", data.DisplayName)  //Different property name 
  .Go();


db.Insert("Club")
  .Values(new
          {
              data.Code,
              data.Name,
              data.HomeGround,
              data.EstablishedDate,
              LastChanged = DateTime.Now //extra column
          })
  .Go();

Source code on GitHub. Note this probably doesn't compile just yet, because there are some missing dependencies - but you can see the general idea and shape of the code. I am slowly migrating an entire 'Toolkit' of libraries like this up to GitHub, so eventually it will be in a compiling state.