Griffin Data Mapper and SQLite

I’ve created a small example project that uses the data mapper in Griffin.Framework to work with SQLite.

To start with, you have a business entity somewhere:

public class User
{
	public int Id { get; set; }
	public string FirstName { get; set; }
	public string LastName { get; set; }
	public DateTime CreatedAtUtc { get; set; }
}

.. which you create a mapper for ..

public class UserMapper : EntityMapper<User>
{
	private static readonly DateTime UnixDate = new DateTime(1970, 1, 1);

	public UserMapper() : base("Users")
	{
	}

	public override void Configure(IDictionary<string, IPropertyMapping> mappings)
	{
		base.Configure(mappings);
		mappings["Id"].ColumnToPropertyAdapter = i => Convert.ToInt32(i);
		mappings["CreatedAtUtc"].ColumnToPropertyAdapter = o => 
		UnixDate.AddSeconds(Convert.ToInt32(o));
		mappings["CreatedAtUtc"].PropertyToColumnAdapter = o => 
		((DateTime) o).Subtract(UnixDate).TotalSeconds;
	}
}

As you can see, the mapper supports conversions between column and property types. You could even store child aggregates as JSON in a column if you would like.

The mapping is discovered automatically by the library (thanks to the AssemblyScanningMappingProvider).

Now you have to tell the data mapper to speak SQLite:

CommandBuilderFactory.Assign(mapper => new SqliteCommandBuilder(mapper));

That’s it. Down to business:

class Program
{
	static void Main(string[] args)
	{
		CommandBuilderFactory.Assign(mapper => new SqliteCommandBuilder(mapper));

		string cs = "URI=file:test.db";
		var connection = new SQLiteConnection(cs);
		connection.Open();

		if (!connection.TableExists("Users"))
		{
			using (var uow = new AdoNetUnitOfWork(connection))
			{
				uow.Execute(
					"CREATE TABLE Users (Id INTEGER PRIMARY KEY AUTOINCREMENT, 
					FirstName TEXT, LastName text, CreatedAtUtc INTEGER)");
				uow.SaveChanges();
			}
		}

		var users = connection.ToList<User>(new {FirstName = "Gau%"});

		var first = connection.First<User>(new {Id = 1});

		// clear old data
		using (var uow = new AdoNetUnitOfWork(connection))
		{
			using (var cmd = uow.CreateCommand())
			{
				cmd.CommandText = "SELECT * FROM Users";
				cmd.AddParameter("id", "983498043903");
				foreach (var entity in cmd.ToEnumerable<User>())
				{
					Console.WriteLine(entity.FirstName);
				}
			}

			uow.Truncate<User>();
			for (int i = 0; i < 100; i++)
			{
				uow.Insert(new User { FirstName = "Arne" + i });
			}

			uow.SaveChanges();
		}
	}
}

A complete example can be found on Github.

The library has full support for asynchronous operations.