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

Slow SQL Server? These SentryOne resources share tips and tricks for not only troubleshooting SQL Server performance issues, but also preventing them before they hit your production environment.

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.

Database monitoring tools letting you down? See how SentryOne empowers Enterprises to go faster.

Topics:

Published at DZone with permission of

Opinions expressed by DZone contributors are their own.

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

{{ parent.tldr }}

{{ parent.urlSource.name }}