ScottLilly.com Posts

My latest experiment is writing an ORM for .Net.

The ones I’ve seen always have something I don’t like. Usually, it’s how they relate the business object classes to the database (you either need to do the configuration in a separate file, or do something else ‘non-obvious’).

So, I’ve been trying to build one of my own.

Here’s a sample of what I have working so far.

Defining the business class

Customer.cs

using System;
using IDNORM.Attributes;
namespace Domain.Models
{
    [TableName("Customer")]
    public class Customer
    {
        public const string COLUMN_ID = "ID";
        public const string COLUMN_NAME = "Name";
        public const string COLUMN_DATE_CREATED = "DateCreated";
        [UniqueIDColumn]
        [ColumnName(COLUMN_ID)]
        public Guid ID { get; set; }
        [ColumnName(COLUMN_NAME)]
        public string Name { get; set; }
        [ColumnName(COLUMN_DATE_CREATED)]
        public DateTime DateCreated { get; set; }
    }
}

The attributes in the business class are used by the persistence layer.

The constants in lines 10 – 12 don’t really need to be constants.  I could have put the column name values directly into the attributes on lines 15, 18, and 21.  However, they come in handy in the next class (CustomerController.cs), where I connect to the ORM persistence manager.

Currently, I only have the code written to handle properties that are native .Net datatypes.  As I progress, the ORM should be able to handle properties that are other business classes and collections (of both native .Net datatypes and other business classes).

Using the ORM

Here’s how to use the ORM persistence in a controller class.

CustomerController.cs

using System;
using System.Collections.Generic;
using Domain.Interfaces.Views;
using Domain.Models;
using IDNORM;
using IDNORM.ORM;
namespace Domain.Controllers
{
    public static class CustomerController
    {
        static CustomerController()
        {
            PersistenceManager.SetDataSource(PersistenceManager.DbTypes.MSSQL2008, "(local)", "IDNORM", "IDNORMUser", "IDNORMPassword");
        }
        public static void CreateCustomer(ICustomerMaintenance customerMaintenance)
        {
            if(string.IsNullOrWhiteSpace(customerMaintenance.CustomerName))
            {
                return;
            }
            Customer customer = new Customer();
            customer.ID = Guid.NewGuid();
            customer.Name = customerMaintenance.CustomerName;
            customer.DateCreated = DateTime.Now;
            PersistenceManager.Insert(customer);
            customerMaintenance.CustomerName = "";
        }
        public static void RetrieveIndividualCustomerByID(ICustomerRetriever customerRetriever)
        {
            if(String.IsNullOrWhiteSpace(customerRetriever.ID.ToString()))
            {
                return;
            }
            ConditionList conditions = new ConditionList();
            conditions.AddCondition(Customer.COLUMN_ID, Condition.Comparators.EqualTo, customerRetriever.ID);
            List<Customer> customers = PersistenceManager.RetrieveMatching<Customer>(conditions);
            if(customers.Count == 1)
            {
                customerRetriever.CustomerName = customers[0].Name;
                customerRetriever.DateCreated = customers[0].DateCreated;
            }
        }
        public static void RetrieveCustomersBySearchCriteria(ICustomerSearcher customerSearcher)
        {
            if(String.IsNullOrWhiteSpace(customerSearcher.SearchCriteria))
            {
                return;
            }
            ConditionList conditions = new ConditionList();
            conditions.AddCondition(Customer.COLUMN_NAME, Condition.Comparators.Contains, customerSearcher.SearchCriteria);
            conditions.AddCondition(Customer.COLUMN_DATE_CREATED, Condition.Comparators.LessThanOrEqualTo, DateTime.Now);
            customerSearcher.MatchingCustomers = PersistenceManager.RetrieveMatching<Customer>(conditions);
        }
        public static void DeleteCustomersBySearchCriteria(ICustomerSearcher customerSearcher)
        {
            if(String.IsNullOrWhiteSpace(customerSearcher.SearchCriteria))
            {
                return;
            }
            ConditionList conditions = new ConditionList();
            conditions.AddCondition(Customer.COLUMN_NAME, Condition.Comparators.EqualTo, customerSearcher.SearchCriteria);
            PersistenceManager.DeleteMatching<Customer>(conditions);
        }
    }
}

The PersistenceManager class is where the application connects with the database, through the ORM.

The setup required to connect to the database is on line 16, in the constructor.

One thing I’d like to add is a built-in way to easily mock the database.  Then I can eliminate the need to use dependency injection of the data access objects, just for unit tests.  But I expect that’s going to be an interesting problem – once I start working on it.

In the CreateCustomer method (line 19), I create a Customer business object from the view and insert it into the database on line 32.  IDNORM detects the table and column names from the business class and does an insert into the database.

I want to add the ability to do an “InsertOrUpdate”.  If the ORM detects that a record already exists in that table, with the same values for any column marked as “UniqueIDColumn” (see line 14 of Customer.cs), then it will perform an update of all other columns, instead of inserting a new record.

In RetrieveIndividualCustomerByID (line 37), I’m doing a read from the database.

On lines 44 and 45, I setup a list of conditions for the ORM to use when querying the database.  In this case, the only condition is that the ID value matches the one passed by the view.

Here you can see why I created the constants in the Customer class (see line 10 of Customer.cs).  If I didn’t have that constant, I’d need to use a string “ID”, which could potentially get out of sync if I ever changed the property’s column name in the Customer class.

I’m not 100% happy with this solution, but I’m going with it for now.  If you have any ideas on how to handle this better, I’d love to hear them.

In RetrieveCustomersBySearchCriteria (line 56), I added two conditions.  The second one, where I limit the results to DateCreated less than or equal to DateTime.Now (line 65), doesn’t really make much business sense.  It’s really just there so you can see how multiple conditions work.

One problem with the current handling of conditions is that the ORM only handles multiple “AND” conditions to use in the “WHERE” part of the query.  If you have a more complex query that uses “OR”s, or needs to group some conditions within parentheses, you can’t do that right now.

On line 70, there’s a call to the ORM’s DeleteMatching method.  It works the same way as the RetrieveCustomersBySearchCriteria method.

Future plans

If I can get this all working the way I want, I’ll also include a caching layer in the persistence manager.  I’ll have it do asynchronous inserts and updates into the database, while doing immediate updates in the cache.

I’m not sure about adding in lazy loading.  That’s one of those things which always seemed like an interesting idea.  However, I haven’t seen many real-life performance problems due to not having it.