GLoWORM - Generic LightWeight Object-Relational Mapper

Now that C# 2.0 is completely official and released to the public, I can finally start unleashing some of the code that I've had sitting around for a while. I wanted to make sure that some of the stuff I was coding was still going to work on the final release, so I didn't put up a blog on it.

If you're not familiar with the ORM (also referred to as Container Managed Persistance by the *ahem*Java*ahem* folks), here's the cliffnotes: The idea is that you need to uncouple the knowledge of the persistence medium from the business objects themselves. This allows you to re-use the business objects in a Windows Forms app, an ASP.NET app, a Web Service, etc. Ideally, the container in which the business object resides dictates how the object is persisted. For example, you can have a SQL 2005 relator (container) or an XML relator, or a Microsoft Access relator. Regardless of the persistence medium, the object itself remains the same and allows the developer to focus on creating a very strong business object that is responsible for enforcing its own business rules on its data, indicating validation status, etc.

The problem is that the above example is in the strange world known as "the perfect". The reality is that object purity is never quite as simple as you think, and its never quite as practical as it should be. So what I've done is created something of a hybrid. My business objects don't know anything about the persistence medium, but Attributes associated with the object do. So, in theory, if I want a business object to work in 2 different persistence mediums, I just decorate it with two sets of attributes.

I have two different concepts that are important for this library:

1. The Relational Operation - A relational operation is an operation in which a business object's data is related (to, from, or bi-directionally) with a relational back-end such as SQL Server, XML, or Access.

2. A property map - This property map is an attribute that provides the underlying relational framework with information on how to transfer a property on an object with a parameter to a stored procedure or query. For example, the engine needs to know that the property CustomerID in the "object world" is actually referred to as @id in the "relational world"

There are two different kinds of relational operations. The first is where you populate a business object, essentially hit the "relate" button, and the results of the scalar execution of a query or stored procedure are then translated back onto the object. So, for example, you might have a business object called BankAccount. You set the Balance property, call invoke the relational operation, and the new balance is then 'sent' to the database. This removes the reduncancy of constantly spending all your time instantiating SqlCommands and SqlConnections and SqlParameters blah blah... once your mapping is defined, you can look at your model in a purely relational way.

The second operation is where you supply a business object that provides the parameters for the query, and the result set (typically rows returned from a SELECT) comes back to you as a list. In previous incarnations of this library, I had to return an ArrayList of object. If you've spent any time with .NET, you know that working with lists of essentially untyped objects is slow and inefficient. That kind of operation creates a ton of typecasting.

With Generics, I can actually return a strongly-typed list from that operation. I can invoke the relational operation and get back a List<Customer> or a List<BankAccount>.

Before I show you the code for the library, take a look at some code that uses the library to perform all the standard operations against a Customers table: select all, select one, update, insert, and delete.

using System;
using System.Data;
using System.Data.SqlClient;
using System.Collections.Generic;
using System.Text;

using DotNetAddict.Tools.GLoWORM;

namespace Harness
{
    class Program
    {
        static void Main(string[] args)
        {
            SqlConnection conn = new SqlConnection();
            Customer customer = new Customer();
            conn.ConnectionString = "server=localhost; Initial Catalog=SampleDb; Integrated Security=SSPI;";
            SqlRelator<Customer> custRel = new SqlRelator<Customer>(conn);
            List<Customer> custList = custRel.RelateBusinessArray(customer, "selectall");
            foreach (Customer cust in custList)
            {
                Console.WriteLine("{0}: {1}, {2}", cust.ID, cust.LastName, cust.FirstName);
            }

            Console.WriteLine("creating new customer...");
            customer.FirstName = "New";
            customer.LastName = "Customer";

            custRel.RelateBusinessObject(customer, "new");
            Console.WriteLine("New Customer ID {0}", customer.ID);

            Console.WriteLine("Updating new Customer:");
            customer.LastName = "McChange";
            custRel.RelateBusinessObject(customer, "update");

            Console.WriteLine("Selecting newly created customer");
            Customer cust2 = new Customer();
            cust2.ID = customer.ID;
            custRel.RelateBusinessObject(cust2, "selectOne");
            Console.WriteLine("After selecting individual customer: {0} --> {1}, {2}",
                cust2.ID, cust2.LastName, cust2.FirstName);

            Console.WriteLine("Now deleting recently created Customer.");

            custRel.RelateBusinessObject(cust2, "delete");
            conn.Close();
            Console.ReadLine();
        }
    }
}

What you're looking at is quite possibly some of the simplest code that anyone has to write to persist business objects to an underlying data store. All you have to do is create an instance of the SqlRelator<> class, passing the type of the object you're relating as the Generic Type Parameter. From there, the SqlRelator<> class reads attributes off of the business object to figure out how to relate the object to the database and back again.

I'll show you the Customer class below, and then you can download the code to see how the rest of it works.

using System;
using System.Data;
using System.Collections.Generic;
using System.Text;

using DotNetAddict.Tools.GLoWORM;

namespace Harness
{
    [RelationalOperationDefinition(
        "selectall",
        CommandType.StoredProcedure,
        "sp_SelectAllCustomers")]

    [RelationalOperationDefinition(
        "selectOne",
        CommandType.Text,
        "SELECT @firstName = FirstName, @lastName = LastName FROM Customers WHERE ID = @id")]

    [RelationalOperationDefinition(
        "new",
        CommandType.Text,
        "INSERT INTO Customers(FirstName, LastName) VALUES(@firstName, @lastName) SET @id = @@IDENTITY")]

    [RelationalOperationDefinition(
        "delete",
        CommandType.Text,
        "DELETE Customers WHERE ID = @id")]

    [RelationalOperationDefinition(
        "update",
        CommandType.Text,
        "UPDATE Customers SET FirstName = @firstName, LastName = @lastName WHERE ID = @id")]
    public class Customer
    {
        private int id;
        private string firstName;
        private string lastName;

        [PropertyMap(SqlDbType.Int, "@id", 4)]
        [RelationalOperation("new", ParameterDirection.Output)]
        [RelationalOperation("delete", ParameterDirection.Input)]
        [RelationalOperation("update", ParameterDirection.Input)]
        [RelationalOperation("selectOne", ParameterDirection.Input)]
        public int ID
        {
            get { return id; }
            set { id = value; }
        }

        [PropertyMap(SqlDbType.VarChar, "@firstName", 50)]
        [RelationalOperation("new", ParameterDirection.Input)]
        [RelationalOperation("update", ParameterDirection.Input)]
        [RelationalOperation("selectOne", ParameterDirection.Output)]
        public string FirstName
        {
            get { return firstName; }
            set { firstName = value; }
        }

        [PropertyMap(SqlDbType.VarChar, "@lastName", 50)]
        [RelationalOperation("new", ParameterDirection.Input)]
        [RelationalOperation("update", ParameterDirection.Input)]
        [RelationalOperation("selectOne", ParameterDirection.Output)]
        public string LastName
        {
            get { return lastName; }
            set { lastName = value; }
        }
    }
}

What I want to stress here is that with just a tiny little bit of code contained in the GLoWORM library, you can drastically reduce the amount of redundant code you write, you can get back to the business of creating pure business objects and simply decorate those objects with persistence rules. The GLoWORM library works with in-line SQL statements or stored procedures, and can use connections created elsewhere - which is a lot more than I can say for a lot of other ORM/CMP libraries.

Hopefully the code from this library will teach you some things about Generics, and hopefully you'll find that using the library in your own code will drastically reduce the time you spend in your data layer and free up time working in your middle and GUI tiers.

You can download the code and accompanying harness here.

To create the database that was used in this sample, just create a database with a single table with the following properties:

Customers

ID (PK, Identity, Int(4), Not Null)

FirstName (Varchar(50))

LastName (Varchar(50))

I also created a stored procedure that selects all of the Customers for the purpose of proving that GLoWORM is capable of both s'proc execution as well as inline SQL. Feel free to play around with creating your own procedures and replacing the inline SQL with them.

Enjoy,

   The Addict.

links: digg this    del.icio.us    technorati    reddit