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   

8 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

  7. Yoou may even want to include measurement information regarding the product.

    Without a website you aree missing out on the opportunity to control what Googhle says about
    you. It’s thee exciting news in thhe social media marketing blogosphere.
    Another very effective way on hhow to advertuse a website for free is by having your website links listed oon high traffic websites.
    Tere are a few frrequently asked questions which needed to bbe answered before getting facebook like on your websites.
    There might be some probable and good reasons, if you
    are planning to redesign your portfolio websites.

    The Ugly are thhe answers you may or may not receive from some
    of the live help. If the price drops for their product, they
    will rexeive an email Price Protectr that will inform them of this
    fact. Besides this sampled writing, I also offer editorial, consulting, and translating Services.
    Our website design in India build your brand and generate the maximum
    revenue providing more customers for your services acrosws the world.
    So, aan updated browser can alert you against suchh websites.
    It is an ever-changing and dynamic medium, which keeps on evolving but there are certain guidelines, which will remain the
    same, even in the future.

    For example, if I want a listing of concerts in Brazil during any particular day, I simply visit the site and get a listing of the available concerts there.
    This is for the marketing part itself. There is nothing more frustrating, and amateur, than seeing slow updates creeping across the
    website. However, it is very important. Thus our website
    design inn India help you too target the audience and increase your business in the market place.
    A good website design team, therefore, will work directly with yyou
    to ensure complete satisfaction.

    This way you can check in to see how well you’re doing.
    The goal with successful Internet promotion is to get your business website known to as many
    people as possibl witout spamming. How the malware can affect you aand your visitors?
    Webinars are hosted at noon, 5:00 p.m. Make your blog or site eye appealing
    and easy too naigate through.

    In addittion to this Free Website Creator, you can simmply create
    a website, or you can promote your website inn a
    number of ways. Four: Affiliate Programs or Viral Marketing Another excellentt way
    of link uilding on thhe innternet is through a viral marketing
    offer or by running your own affiliate program.
    In flyers, list your website address, with a brief description. Remember, this iis website
    represents your business on a global scale.
    Now if you are wiklling too invest a few dollars you could place links tto your website on other very popular
    websites for moms.

    Wahmdrive is the one I would recommend if you are thinking of starting a website for moms.
    An attractive and well-designed storefront draws mall customers
    inside. So if you are not going to do your own scripts and things like that I
    recommend linking most your links outside of
    your site to your blog or act. Every website that means business hhas them and yours should too.
    This tip is aimed mainly towards bloggers ssince mahy of the networks may
    not allow you to register if you are nnot a legit blog.
    On the other hand if you choose a website meat for lower age group
    than your kid, hee or shhe may not get any interest in that.

    Now, after your website is clean, you or your
    web security expert can request Google to unblock your
    website as soon as possible. Thhey work wit a spirit to create an unmatchable
    websites in which new customers can eawsily find the answers of their query.
    It would decrease the popularity of the website up to a
    great extent. Next will come the name of the website,
    written in italics text. This will ensure that all the information on your pages over the internet will bee encrypted and almost
    immpossible tto read by any hackers.

    Audit process Aftrer you have ordered the audit we begin with a detailed analysis of
    youhr website and within 1 day you will bbe contacted
    by one of our Account Managers to discuss your keyword strategy.
    I havve spent many years on both the visitor side and the
    design side of web pages annd have seen my share oof annoying sites (and,
    in the beginning, probably created a few annoying ones
    too). Youu need to include all the prices on each item.

    On the web, you have a very limited amount of time to grab someone’s attention because they have a virtually
    unlimited number of other thinjgs they can bee looking at instead.
    If you’re trying to promote something everyone else is, I
    don’t cade what traffic exchuange service you’re using, your sales conversions are going to be very low,
    or noothing at all. Staqrt out slow and taoe your time, don’t ruswh to build the greatest website
    of all time in one day.

    Large blocks of texst should bbe split up into paragraphs containing 4-5 sentences.
    These wbsites pln activities or experiences for such couples to spend time
    together. Invest in professional website bouwen and let
    your website earn for you. We are specialized in corporate website design Delhi and help our clients to meet
    their objectives assuring them with our quality services and bringing
    popularity in the market. There is a games or activfities section in family
    websites to cater this need of the family members.

    If you are performing the rocedure for the fikrst time, tuere is no need to enter any password.
    Meanwhile, there are a few actions you ould take: (1)
    You should scan your computer, your webmaster’s computer,
    and any computers that have access to your website files, for viruses and malware.

    It is pretty good. Often, check your e-mails, and reppy back to aany inquires, within twenty-four hours.
    If you insist on constructing and maintaining your indiovidual
    website, then youu might be seemingly going to be neglecting more necessary areas of your business that need your focus.
    Isee through the stories what I’ve always said in my life -the storms will come and after the storm the sun shines again.

    Write the code for with your keywords accordingly.
    Put pictures on your website off your products. As I mentioned before, they should
    also heed yur suggestons and revisions. You both benefit from
    each others efforts to generate traffic. Keeping
    all these factors in mind and considering the wweb today and its progress, we ccan put orth somje
    compelling arguments for the responsive website design. If you
    are going to have yokur brochures, etc., available for downloading, then be sure that they keep
    up with the ‘look and feel’ of all your marketing collateral (including
    the site).

    This will save you time and effort especially when you have posted many ads at the same time.
    Do not confuse this with the nname oof the website. Always
    offer a reward to the first 100 visitors orr even the one visitor
    is that tthe 1000th visitor each month.

    Visit my blog :: Xfire.Com

    Comment by Xfire.Com — August 8, 2014 @ 8:33 am

  8. Au-delà des conclusions de l’étude et ses interprétations qui en seront faites, le
    travail effectué par l’institut Max-Planck met en lumière la plasticité du
    cerveau. ” Le cerveau a la capacité de se reprogrammer instantanément, en changeant son mode de fonctionnement “, expliquait James Olds à Nicholas Carr dans un entretien de 2008, cité dans son ouvrage “Internet rend-il bête”.
    On se souvient tous du CV Facebook d’Augustin, du CV Amazon de Philippe, du CV pancarte ou du CV web-série.
    Pour tous les revoir, c’est par ici LA vidéo sports extrêmes de ce début
    d’année 2012, Snow, Surf, Bmx, Skate tout y est, images sublimes et performances incroyables inside
    !

    Comment by Wilbert — August 24, 2014 @ 3:23 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].