Over a million developers have joined DZone.

Griffin data mapper and SQLite

· Database Zone

Build fast, scale big with MongoDB Atlas, a hosted service for the leading NoSQL database. Try it now! Brought to you in partnership with MongoDB.

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.

Now it's easier than ever to get started with MongoDB, the database that allows startups and enterprises alike to rapidly build planet-scale apps. Introducing MongoDB Atlas, the official hosted service for the database on AWS. Try it now! Brought to you in partnership with MongoDB.

Topics:

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

Opinions expressed by DZone contributors are their own.

The best of DZone straight to your inbox.

SEE AN EXAMPLE
Please provide a valid email address.

Thanks for subscribing!

Awesome! Check your inbox to verify your email so you can start receiving the latest in tech news and resources.
Subscribe

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

{{ parent.tldr }}

{{ parent.urlSource.name }}