How to write a simple ADO.NET ORM by using Linq and extension methods - in 10 minutes

Yesterdays post from Alex James inspired me to create a very simple “ORM” by using C# 3.0’s extension methods and Linq. I wanted the ORM to be very simple and to build upon the existing ADO.NET infrastructure. That means that the “framework” is going to use the existing ADO.NET classes and extend them to return persistent object instances.

The classic methods are still available. The new methods are implemented as extension methods and add therefore only something without removing something else - which is great!

Now let’s get started. First I thought of creating an attribute to create the mapping between the persistent object and the returned data. Note that in C# 3.0 private fields don’t need to be created for a property, if not accessed from somewhere else then through the property itself (that’s why the setter and getter are empty!)

/// <summary>
/// Attribute used to decorate the properties that are filled with
/// the result from the database.
/// </summary>
[AttributeUsage(AttributeTargets.Property)]
public sealed class ColumnAttribute : Attribute
{
        /// <summary>
        /// Name of the column in the database.
        /// </summary>
        public string Name { get; set; }
}

Next we need to define some extension methods. I’m going to put that code into a static class called “ADONETExtensions”.

public static class ADONETExtensions
{
        // Methods…
}

The first method that’s going to be added is a method that returns an enumerator for the classes that implement the IDataReader interface. I have the method called GetEnumerator.

/// <summary>
/// Gets an enumerator for the given datareader.
/// </summary>
public static IEnumerable<T> GetEnumerator<T>(this IDataReader reader) where T : new()
{
        // Ensure to close the reader when done.
        using (reader)
        {
                // Read until we reach the end of the result.m
                while (reader.Read())
                {
                        // Create a new instance of the object to return.
                        T instance = Activator.CreateInstance<T>();

                        // Get a enumerable of properties that have
                        // the ColumnAttribute specified.
                        // Return an anonymous type that holds the
                        // property and the ColumnAttribute.
                        var props = from c in typeof(T).GetProperties()
                                  where Attribute.GetCustomAttribute(c, typeof(ColumnAttribute)) != null
                                  select new { Property = c, Attribute = (ColumnAttribute)Attribute.GetCustomAttribute(c, typeof(ColumnAttribute)) };

                        // For each property returned set the value
                        // returned by the database.
                        props.ToList().ForEach(x => x.Property.SetValue(instance, reader.TryGetValue(x.Attribute.Name), null));

                        // Return the current instance.
                        yield return instance;
                }
        }
}

The method uses a Linq statement to get all properties of the specified persistent type that are decorated with the “ColumnAttribute” attribute. The property and attribute itself is returned as new anonymous type. They are going to be used later in the ForEach method. Foreach loops through all mapped properties and does something. In this case we set the returned value (from the query) as the property value in the persistent object instance. ForEach expects a delegate. We are using a lambda expression here. A lambda expression is another notation for an anonymous delegate.

TryGetValue is an extension method that is also implemented in the ADONETExtensions class and looks like this:

/// <summary>
/// Tries to get a value for the given column name.
/// </summary>
public static object TryGetValue(this IDataReader reader, string name)
{
        // Get the schema table for the data reader.
        DataTable table = reader.GetSchemaTable();

        // The first column of the schema table
        // contains the names of the returned columns.
        // Loop through all rows of the first column to
        // understand if the given column name (name
        // argument) is found in the schema table.
        for (int i = 0; i < table.Rows.Count; i++)
        {
                if (table.Rows[i][0].ToString().ToLower() == name.ToLower())
                {
                        // If found return the value at the index i.
                        return reader[i];
                }
        }
                       
        // Return null if the column is not in the result.
        return null;
}

What might be a little bit confusing is the “GetSchemaTable” call. This call returns a DataTable that holds the schema of the returned IDataReader’s data. The best way to understand what’s found in this table is to set a breakpoint into the code and have a look at the table.

What we need next is a way to get the objects from our database connection. That’s why we create a GetObjects extension method for IDbConnection. This method is very similar to the one that has been created by Alex. The differences are that the method expects a generic type (the type T of the persistent objects being returned), next it returns an IEnumerable and it allows an IDbConnection to be specified. That’s possible because each IDbConnection has a method “CreateCommand” to create a command from the connection.

/// <summary>
/// Gets objects from the database.
/// </summary>
/// <param name="sql">The SQL statement to get the objects.</param>
public static IEnumerable<T> GetObjects<T>(this IDbConnection connection, string sql) where T : new()
{
        // Create a new command.
        IDbCommand command = connection.CreateCommand();
        command.CommandText = sql;

        // Get the reader.
        return command.ExecuteReader().GetEnumerator<T>();
}

Now we are done with our “ORM”. Next is to create a persistent object that uses our “ColumnAttribute” to specify the mapping:

class User
{
        /// <summary>
        /// Property Name maps to column NAME.
        /// </summary>
        [Column(Name = “NAME”)]
        public string Name
        {
                get;
                set;
        }
}

The column name and property name equal only in this example.
We are finally done with creating classes. Now let’s use them!

class Program
{
        static void Main(string[] args)
        {
                // Create the connection to the database.
                SqlConnection connection = new SqlConnection(@“user id=sa;server=waldemar\sqlexpress;database=Test;password=xxxx”);
                connection.Open();

                // Filter the database results by using
                // a Linq expression.
                var result = from u in connection.GetObjects<User>(“select * from [USERS]“)
                         where u.Name == “Alex” || u.Name == “Christian”
                         orderby u.Name ascending
                         select u;

                // Loop over the results.
                foreach(var user in connection.GetObjects<User>(“select * from [USERS]“))
                {
                        // Do something.
                }

                // Return a list with the results.
                List<User> list = connection.GetObjects<User>(“select * from [USERS]“).ToList();
        }
}

That’s it. Our little framework allows us to load persistent objects from the database. The framework is very simple and leaves still a lot of open points. There is, for example, no way to handle database parameters at the moment and it needs also a way to save the persistent objects back to the database.

That’s now up to you… Happy coding :)

Published on May 12th, 2007 — Tags: , ,
   digg it!    kick it   

6 Comments ( Comments RSS TrackBack )

  1. A discussion on this post is found here: http://channel9.msdn.com/ShowPost.aspx?PostID=307032

    Comment by Christian Liensberger — May 13, 2007 @ 1:42 pm

  2. Hello! Good Site! Thank you!

    Comment by xmbxhrfzfv — July 3, 2007 @ 4:35 pm

  3. rule hoyle poker strip rule poker

    Comment by run rule poker — July 27, 2008 @ 11:34 pm

  4. You do realise that:
    var result = from u in connection.GetObjects(“select * from [USERS]“)
    where u.Name == “Alex” || u.Name == “Christian”
    orderby u.Name ascending
    select u;
    reads the entire contents of the “USERS” table from the data base. This is an extremely inefficient way of doing what you want to do..

    Comment by Jesper Kihlberg — November 13, 2009 @ 5:38 am

  5. I know… it’s just a sample :)

    Comment by Christian Liensberger — December 10, 2009 @ 10:25 pm

  6. baidu…

    baidu http://www.baidu.com...

    Trackback by baidu — May 17, 2013 @ 11:19 am

Leave a comment

You can use these tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong> [code][/code] [code lang="csharp"][/code].