At the Norwegian .NET User Group Vestfold (NNUG Vestfold) meeting that we hosted on the 29th of March, I did a presentation on how to develop a lightweight O/R mapper in .NET. During the meeting I promised to post the source code for the DataStore API that I had written, and here it is (download link at the end of this post)!

The API is mostly finished, but there may still be some bugs to iron out, and there are a few improvements here and there that I hope to get around to doing. I have already deployed it in the backend of the blog engine that is running on this site however, and it is doing its job perfectly so far :)

In this first post, I'll be giving a quick introduction to how it works, concentrating on the basic features exposed by it. In the coming weeks, I will be writing some focused posts on interresting parts of the implementation behind the API, so watch out for that :)

Preparing Your Types

At the core of the API sits the DataStore class. It contains all the methods for saving and retrieving objects to/from the data base. In order to use the DataStore however, you need to adorn the types in your object model with some attributes first. Basically, any property that you want to be persisted in the database needs to be decorated with a ColumnDefinitionAttribute. Optionally, the class itself may be decorated with a TableDefinitionAttribute, but this is only necessary if you wish to map the type to a table whose name does not match the class name (if so, use the Alias property of the attribute). An example type definition would look like this:

public class Item
{
private Int64 _id;
private string _name;
[ColumnDefinition(IsPrimaryKey=true, IsAutoIncrement=true)]
public Int64 Id
{
get { return _id; }
private set { _id = value; }
}
[ColumnDefinition]
public string Name
{
get { return _name; }
set { _name = value; }
}
}

This type then, will be able to store and retrieve data to/from a table named "Item" in your database that looks like this:

(Note: The Id property is configured as an auto-incrementing identity).

Currently, the API does not have any support for creating the database, so you need to create all the tables manually. The thing to keep in mind is that either the table name and colum names must match the class name and property names (case and all), or you must specify the table/column names using the alias properties. And of course the type of the column must match the type of the property (unless you have specified a TypeMapper and DbType in the ColumnDefinition - more on that in a later post).

Using the DataStore

With both our type and tables defined, we can then use the DataStore to do the usual SQL stuff, for example saving a new object to the database:

DbProviderFactory factory = DbProviderFactories.GetFactory("System.Net.SqlClient");
DataStore store = new DataStore(factory, "Data Source=.\SQLEXPRESS; Initial Catalog=MyDatabase; Integrated Security=True");
Item item = new Item();
item.Name = "My item";
item.Id = Convert.ToInt64(store.Save(item));

Two things to pay attention to here: Currently, the DataStore is only compatible with Sql Server 2005, which means that if you try to pass it a DbProviderFactory other than the one above, it will throw an exception. Secondly, notice how the Save method returns the id that the database assigned to the newly created row in the Item table, since I specified the primary key as an auto-incrementing identity.

While saving is fairly straight-forward, selecting has a few more options. The API has support for both filtering and paging, and can even run a custom SQL statement that you provide. Lets look at a few select examples:

foreach (Item item in store.Select<Item>("Name", Operator.Like, "f%"))
{
Console.WriteLine(item.Name);
}

In the example above, we are selecting all Items that have a Name that starts with the letter 'f'. Basically, this Select translates into the query "SELECT * FROM Item WHERE [Name] LIKE '%f'. Notice that the Select method returns a custom iterator - someone raised a concern that this would leave the connection to the database open, but as long as the iterator is disposed of after use - which the foreach construct does automatically - this is not an issue. I'll go into detail on this in a later post, as it is quite interresting and obviously has an impact on best-practices for the use of Select<T>.

List<Item> results = new List<Item>(store.Select<Item>(new Action<IDbCommand>(
delegate(IDbCommand command)
{
command.CommandText = "SELECT * FROM Item WHERE Id IN(1,2,3)";
})));

In this example, I've used the Select<T> overload that acceps an Action<IDbCommand> delegate, which lets me prepare the command object manually. This is a powerfull method where I can (as above) set a custom SQL statement to be executed, register parameters and so forth. Notice that I only need to prepare the command - the DataStore will then internally execute my command and read out the data it returns and populate the respective properties. This method truly shows the power of both Generics and anonymous delegates :)

In addition to the Save and Select methods, there are several other members of the DataStore that expose all the usual CRUD functionality that I wont go into detail explaining here, as they all function more or less in the same way.

The Source Code

So I bet you are itching to get your hands on the code... get it here :)