Over a million developers have joined DZone.
{{announcement.body}}
{{announcement.title}}

Griffin data mapper and SQLite

DZone's Guide to

Griffin data mapper and SQLite

· Database Zone
Free Resource

Finding a database that fits with a container-based deployment model can be frustrating. Learn what to look for in a Docker database

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 at github.

The library have full support for asynchronous operations.

When you're looking for a SQL database that can scale elastically, while still preserving ACID guarantees, you only have a few choices. Find out how these elastic SQL databases perform in thishead-to-head YCSB benchmark.

Topics:

Published at DZone with permission of Jonas Gauffin, DZone MVB. See the original article here.

Opinions expressed by DZone contributors are their own.

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}