Light ORM Library for .NET

• Download demo project - 229.5 KB
• Download source - 28.2 KB

Preface

There are many breaking changes in this version of the code. This version is not backward compatible with previous versions. The article text has been updated to reflect the changes and the code snippets provided here will work only with the latest version of Light.

Introduction

This article is about a small and simple ORM library. There are many good ORM solutions out there, so why did I decide to write another one? Well, the main reason is simple: I like to know exactly which code runs in my applications and what is going on in it. Moreover, if I get an exception I'd like to be able to pinpoint the location in the code where it could have originated without turning on the debugger. Other obvious reasons include me wanting to know how to write one of these and not having to code simple CRUD ADO.NET commands for every domain object.

Purpose and Goal

The purpose of this library is to allow client code (user) to run basic database commands for domain objects. The assumption is that an object would represent a record in the database table. I think it is safe to say that most of us who write object-oriented code that deals with the database have these objects in some shape or form. So the goal was to create a small library that would allow me to reuse those objects and not constrain me to any inheritance or interface implementations.

Also, I wanted to remain in control: I definitely did not want something to be generating the tables or classes for me. By the same token, I wanted to stay away from XML files for mapping information because this adds another place to maintain the code. I understand that it adds flexibility, but in my case it is not required.

Design

One of the things I wanted to accomplish was to leave the user in control of the database connection. The connection is the only resource that the user has to provide for this library to work. This ORM library (Light) allows users to run simple INSERT, UPDATE, DELETE and SELECT statements against a provided database connection. It does not even attempt to manage foreign keys or operate on multiple related objects at the same time. Instead, Light provides the so-called triggers (see the section about triggers below) that allow you to achieve similar results. So, the scope of the library is: single table/view maps to a single object type.

Using the Code

Light uses attributes and reflection to figure out which statements it needs to execute to get the job done. There are two very straightforward attributes that are used to describe a table that an object maps to:

• TableAttribute - This attribute can be used on a class, interface or struct. It defines the name of the table and the schema to which objects of this type map. It also lets you specify the name of a database sequence that provides auto-generated numbers for this table (of course, the target database has to support sequences).
• ColumnAttribute - This attribute can be used on a property or a field. It defines the column name, its database data type, size (optional for non-string types) and other settings such as precision and scale for decimal numbers.

There are two more attributes that aid with inheritance and interface implementation:

• TableRefAttribute - This attribute can be used on a class, interface or struct. It is useful if you need to delegate table definition to another type.
• MappingAttribute - This attribute can be used on a class, interface or struct. It extends the ColumnAttribute (therefore inheriting all its properties) and adds a property for a member name. This attribute should be used to map inherited members to columns. More on this later, in the code example.

There is another attribute that helps with such things as object validation and management of related objects:

• TriggerAttribute - This attribute can only be used on methods with a certain signature. In short, it marks a method as a trigger. These trigger methods are executed either before or after 1 of 4 CRUD operations. More on this later, in the code example.

The most useful class of the Light library is the Dao class. Dao here stands for Data Access Object. Instances of this class provide methods to perform inserts, updates, deletes and selects of given objects, assuming that objects have been properly decorated with attributes. If a given object is not properly decorated or is null, an exception will be thrown.

A word about exceptions is in order. There are couple exceptions that can be thrown by Light. The most important one is System.Data.Common.DbException, which is thrown if there was a database error while executing a database statement. If your underlying database is SQL Server, then it is safe to cast the caught DbException exception to SqlException. Other exceptions are: DeclarationException, which is thrown if a class is not properly decorated with attributes; TriggerException, which is thrown if a trigger method threw an exception; and LightException, which is used for general errors and to wrap any other exceptions that may occur.

Please note that both DeclarationException and TriggerException are subclasses of LightException, so the catch statement catch(LightException e) will catch all three exception types. If you want to specifically handle a DeclarationException or a TriggerException, their catch statements must come before the catch statement that catches the LightException. Here is an example:

try {
    T t = new T();
    dao.Insert<T>(t);
}
catch(DbException e) {
    SqlException sqle = (SqlException) e;
}
catch(DeclarationException e) {
    ...
}
catch(TriggerException e) {
    ...
}
catch(LightException e) {
    if(e.InnerException != null) //then the following is always true
        bool truth = e.Message.Equals(e.InnerException.Message);
}

You cannot create an instance of a Dao class directly, using its constructor, because Dao is an abstract class. Instead, you should create instances of Dao subclasses targeted for your database. So far, without any modifications, Light can work with SQL Server (SqlServerDao) and SQLite .NET provider (SQLiteDao) databases. If you need to target another database engine or would like to override the default implementations for SQL Server or SQLite, all you have to do is create a class that extends the Dao class and implements all its abstract methods.

All operations (except select) are performed within an implicit transaction unless an explicit one already exists and was started by the same Dao instance. In that case, the existing transaction is used. The user must either commit or rollback an explicit transaction. If the Dispose method is called on the Dao object while it is in the middle of a transaction, the transaction will be rolled back. An explicit transaction is the one started by the user by calling the Dao.Begin method. Implicit transactions are handled by Dao objects internally and are automatically committed upon successful execution of a command or rolled back if an exception was thrown during command execution.

Note that for all of this to work, the Dao object must be associated with an open database connection. This can be done via the the Dao.Connection property. SqlServerDao and SQLiteDao also provide constructors that accept connection as a parameter. Remember that it is your responsibility to manage database connections used by Light. This means that you are responsible for opening and closing all database connections. A connection must be open before calling any methods of the Dao object. The Dao object will NEVER call Open or Close methods on any connection, not even if an exception occurs. Here is some sample code to demonstrate the concept. Let's assume that we will be connecting to an SQL Server database that has the following table defined:

create table dbo.person (
    id int not null identity(1,1) primary key,
    name varchar(30),
    dob datetime
)
go

Now let's write some code. Note that this code has not been tested to compile; please use the demo project as a working sample:

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

using Light; // Light library namespace - this is all you need to use it.

//
// Defines a mapping of this interface type to the dbo.person table.
//
[Table("person", "dbo")]
public interface IPerson
{
    [Column("id", DbType.Int32, PrimaryKey=true, AutoIncrement=true)]
    Id { get; set; }

    [Column("name", DbType.AnsiString, 30)]
    Name { get; set; }

    [Column("dob", DbType.DateTime)]
    Dob { get; set; }
}

//
// Says that when operating on type Mother the table definition from
// type IPerson should be used.
//
[TableRef(typeof(IPerson))]
public class Mother : IPerson
{
    private int id;
    private string name;
    private DateTime dob;

    public Mother() {}

    public Mother(int id, string name, DateTime dob)
    {
        this.id = id;
        this.name = name;
        this.dob = dob;
    }

    public int Id
    {
        get { return id; }
        set { id = value; }
    }

    public string Name
    {
        get { return name; }
        set { name = value; }
    }

    public DateTime Dob
    {
        get { return dob; }
        set { dob = value; }
    }
}

//
// Notice that this class is identical to Mother but does not
// implement the IPerson interface, so it has to define its
// own mapping.
//
[Table("person", "dbo")]
public class Father
{
    private int id;
    private string name;
    private DateTime dob;

    public Father() {}

    public Father(int id, string name, DateTime dob)
    {
        this.id = id;
        this.name = name;
        this.dob = dob;
    }

    [Column("id", DbType.Int32, PrimaryKey=true, AutoIncrement=true)]
    public int Id
    {
        get { return id; }
        set { id = value; }
    }

    [Column("name", DbType.AnsiString, 30)]
    public string Name
    {
        get { return name; }
        set { name = value; }
    }

    [Column("dob", DbType.DateTime)]
    public DateTime Dob
    {
        get { return dob; }
        set { dob = value; }
    }
}

//
// Same thing but using a struct.
//
[Table("person", "dbo")]
public struct Son
{
    [Column("id", DbType.Int32, PrimaryKey=true, AutoIncrement=true)]
    public int Id;
    [Column("name", DbType.AnsiString, 30)]
    public string Name;
    [Column("dob", DbType.DateTime)]
    public DateTime Dob;
}

//
// Delegating with a struct.
//
[TableRef(typeof(IPerson))]
public struct Daughter : IPerson
{
    private int id;
    private string name;
    private DateTime dob;

    public int Id
    {
        get { return id; }
        set { id = value; }
    }

    public string Name
    {
        get { return name; }
        set { name = value; }
    }

    public DateTime Dob
    {
        get { return dob; }
        set { dob = value; }
    }
}

//
// Main.
//
public class Program
{
    public static void Main(string[] args)
    {
        string s = "Server=.;Database=test;Uid=sa;Pwd=";

        // We use a SqlConnection, but any IDbConnection should do the trick
        // as long as you are using the correct Dao implementation to
        // generate SQL statements.
        SqlConnection cn = new SqlConnection(s);

        // Here is the Data Access Object.
        Dao dao = new SqlServerDao(cn);

        // This would also work:
        // Dao dao = new SqlServerDao();
        // dao.Connection = cn;

        try
        {
            // The connection must be opened before using the Dao object.
            cn.Open();

            Mother mother = new Mother(0, "Jane", DateTime.Today);
            int x = dao.Insert(mother);
            Console.WriteLine("Records affected: " + x.ToString());
            Console.WriteLine("Mother ID: " + mother.Id.ToString());

            Father father = new Father(0, "John", DateTime.Today);
            x = dao.Insert(father);
            Console.WriteLine("Father ID: " + father.Id.ToString());

            // We can also force father to be treated as 
            // another type by the Dao.
            // This is not limited to Insert, but the object and type 
            // MUST be compatible.
            dao.Insert<IPerson>(father);

            // This will also work.
            dao.Insert(typeof(IPerson), father);

            // We now have 3 fathers. Let's get rid of the last one.
            // The 'father' variable has the last Father inserted because
            // its Id was set to the last inserted identity.
            x = dao.Delete(father);

            // Now we have 2 fathers. Let's get them from the database.
            IList<Father> fathers = dao.Select<Father>();
            Console.WriteLine(fathers.Count);

            // NOTICE: Dao.Select and Dao.Find methods instantiate objects
            // internally so you cannot use an interface type
            // as the type of objects to return. In other words,
            // the runtime must be able to create instance of given type
            // using reflection (Activator.CreateInstance method).
            // The safest approach you can take is to make
            // sure that every entity type has a default constructor
            // (it could be private).

            Son son;
            son.Name = "Jimmy";
            son.Dob = DateTime.Today;
            dao.Insert(son);

            // Daughter is a struct, so it cannot be null. 
            // If record with given id is not found and the type is a struct,
            // then an empty struct of given type is returned.
            // This, obviously, only works for the generic version
            // of the Find method. The other version returns an object,
            // so null will be returned.
            // The following is usually not a good idea,
            // but they are compatible by table definitions.
            Daughter daughter = dao.Find<Daughter>(son.Id);
            Console.WriteLine(daughter.Name); // should print "Jimmy"

            daughter.Name = "Mary";
            dao.Update(daughter);

            // Refresh the son.
            // Generics not used, so the return type is object, 
            // could be null if not found.
            object obj = dao.Find(typeof(Son), son.Id);
            if(obj != null)
            {
                son = (Son) obj;
                Console.WriteLine(son.Name); // should print "Mary"
            }
        }
        catch(LightException e)
        {
            Console.WriteLine(e.Message);
        }
        catch(Exception e)
        {
            Console.WriteLine(e.Message);
        }
        finally
        {
            dao.Dispose();

            try { cn.Close(); }
            catch {}
        }
    }
}

Delegating table definition to another type was fairly easy, in my opinion. You simply apply TableRefAttribute to a type. This feature was geared towards being able to use patterns similar to the strategy pattern. You can define an interface or an abstract class with all required data elements. You can also have implementing classes delegate their table definition to this interface or abstract class, but have their business logic in methods differ. Here is some code that shows the use of MappingAttribute, which should help with inheritance. Assume that we are using the same connection and that the same dbo.person table exists in the database.

using System;
using System.Data;
using Light;

public class AbstractPerson
{
    protected int personId;

    public int PersonId
    {
        get { return personId; }
        set { personId = value; }
    }

    public abstract string Name { get; set; }
    public abstract DateTime Dob { get; set; }

    public abstract void Work();
}

//
// Maps the inherited property "PersonId".
//
[Table("person", "dbo")]
[Mapping("PersonId", "id", DbType.Int32, PrimaryKey=true, AutoIncrement=true)]
public class Father : AbstractPerson
{
    private string name;
    private DateTime dob;

    public Father() {}

    [Column("name", DbType.AnsiString, 30)]
    public override string Name
    {
        get { return name; }
        set { name = value; }
    }

    [Column("dob", DbType.DateTime)]
    public override DateTime Dob
    {
        get { return dob; }
        set { dob = value; }
    }

    public override void Work()
    {
        // whatever he does at work...
    }
}

//
// Maps the inherited protected field "personId".
//
[Table("person", "dbo")]
[Mapping("personId", "id", DbType.Int32, PrimaryKey=true, AutoIncrement=true)]
public class Mother : AbstractPerson
{
    [Column("name", DbType.AnsiString, 30)]
    private string name;
    [Column("dob", DbType.DateTime)]
    private DateTime dob;

    public Mother() {}

    public override string Name
    {
        get { return name; }
        set { name = value; }
    }

    public override DateTime Dob
    {
        get { return dob; }
        set { dob = value; }
    }

    public override void Work()
    {
        // whatever she does at work...
    }
}

MappingAttribute allows you to map an inherited member to a column. It doesn't really have to be an inherited member; you can also use variables and properties defined in the same type. However, I like to see meta information along with the actual information, that is, attributes applied to class members. This makes it easier to change the attribute if you are changing class members, for example, the data type.

Notice that Father uses the inherited property, while Mother uses the inherited field. Also, notice the case of the member name parameter in MappingAttribute. Father starts the string PersonId with a capital letter, which hints Light to search through properties first. If a property with such a name is not found, the fields will be searched. If a field with such a name is not found, an exception will be thrown. Similarly, Mother has personId starting with a lower case letter, so fields will be searched first. I guess the order in which members are searched does not give you a lot and is not a huge performance gain, but I always wanted to implement something that could "take a hint" and actually use it.

Querying

Light provides a way to query the database. This comes in handy if you don't want Light to load all objects of any given type and then filter them yourself. I don't think you ever want to do that. The Light.Query object allows you to specify a custom WHERE clause so that the operation is performed only on a subset of records. This object can be used with the Dao.Select and Dao.Delete methods. When used with the Dao.Delete method, the WHERE clause of the Light.Query object will be used to limit the records that will be deleted.

The concept is identical to using a WHERE clause in an SQL DELETE statement. Using the Light.Query object with the Dao.Select method allows you to specify records that will be returned as objects of a given type. In addition, the Dao.Select method takes into account the ORDER BY clause (the Dao.Delete method ignores it), which can also be specified in the Light.Query object. Again, the concept is identical to using WHERE and ORDER BY in SQL SELECT statements.

The Light.Query object is a very simple object and it does not parse the WHERE and the ORDER BY statements you give it. This means two things. First, you must use the real names of table columns as they are defined in the database. You cannot use a name of a property of a class to query the database. Second, you must specify a valid SQL statement for both the WHERE and ORDER BY clauses. If you will be using a plain (not parameterized) WHERE clause, then it is also your responsibility to protect yourself from SQL injection attacks. I don't think this is a problem when using parameterized statements.

Parameterized SQL statements are a recommended way of querying the database. It allows the database to cache the execution plan for later reuse. This means that the database does not have to parse your SQL statements each time they are executed, which definitely helps the performance. The Light.Query object allows you to create a parameterized WHERE clause. To achieve this, you simply use a parameter syntax as you would when writing a stored procedure and then set the values of those parameters by name or order. The following example should make this clear (code not tested).

//
// We will use the Son struct defined previously in the article.
// Assume we have a number of records in the dbo.person 
// table to which Son maps.
//

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

IDbConnection cn = new SqlConnection(connectionString);
Dao dao = new SqlServerDao(cn);
cn.Open();

// This will return all Sons born in the last year 
// sorted from youngest to oldest.
// We will use the chaining abilities of the Query and Parameter objects.
IList<Son> bornLastYear = dao.Select<Son>(
    new Query("dob BETWEEN @a AND @b", "dob DESC")
        .Add(
            new Parameter()
                .SetName("@a")
                .SetDbType(DbType.DateTime)
                .SetValue(DateTime.Today.AddYear(-1)),
            new Parameter()
                .SetName("@b")
                .SetDbType(DbType.DateTime)
                .SetValue(DateTime.Today)
        )
    );

// This will return all Sons named John - non-parameterized version.
IList<Son> johnsNoParam = dao.Select<Son>(new Query("name='John'"));

// This will do the same thing, but using parameters.
IList<Son> johnsParam = dao.Select<Son>(
    new Query("name=@name", "dob ASC").Add(
        new Parameter("@name", DbType.AnsiString, 30, "John")
    ));

// This will return all Sons whose name starts with letter J.
Query query = new Query("name like @name").Add(
        new Parameter("@name", DbType.AnsiString, 30, "J%")
    );
IList<Son> startsWithJ = dao.Select<Son>(query);

//
// We can use the same, previously defined, queries to delete records.
//

// This will delete all Sons whose name starts with letter J.
int affectedRecords = dao.Delete<Son>(query);

dao.Dispose();
cn.Close();
cn.Dispose();

The creation of Query and Parameter objects (in the first query) may look a bit awkward. Both Query and Parameter classes follow the builder pattern that allows for such code. Classes that implement the builder pattern contain methods that, after performing required actions, return a reference to the object on which the method was called. This allows you to chain method calls on the same object. Query and Parameter classes also have regular properties that you can set in a well-known manner. Both approaches work equally well. I just thought it would be easier to use these classes with such methods and the code would be more compact.

Default Table and Column Names

You can omit the name of the table in TableAttribute and the name of a column in ColumnAttribute. Light will provide default names to tables and columns based on the class and field names to which the attributes are applied. The rules to figure out the default name are very simple. In fact, there are no rules. The name of the class or field is used as is if the name is not provided in the attribute. It is best to see an example:

[Table] //same as [Table("Person")]
//[Table(Schema="dbo")] if you need to specify a schema.
public class Person
{
    [Column(DbType.Int32, PrimaryKey=true, AutoIncrement=true)]
    // same as [Column("personId", DbType.Int32, PrimaryKey=true, AutoIncrement=true)]
    private int personId;
    
    private string myName;
    
    [Column(DbType.AnsiString, 30)]
    // same as [Column("Name", DbType.AnsiString, 30)]
    public string Name
    {
        get { return myName; }
        set { myName = value; }
    }
}

Triggers

The concept of triggers comes from the database. A database trigger is a piece of code that is executed when a certain action occurs on a table, on which the trigger is defined. Light uses triggers in a similar fashion. Triggers are methods marked with the Light.TriggerAttribute, have a void return type, and take a single parameter of type Light.TriggerContext. TriggerAttribute allows you to specify when the method is going to be called by the Dao object. The same method can be marked to be called for more than one action. To do this, simply use the bitwise OR operator on Light.Actions passed to TriggerAttribute.

Trigger methods can be called before and/or after the insert, update and delete operations. However, it can only be called after the select operation (denoted by Actions.AfterConstruct) because, before the select operation, there are simply no objects to call triggers on: they are being created in the Dao.Select or Dao.Find methods.

So, the point here is that triggers are only called on existing objects. Hence, another caveat. When calling Dao.Delete and passing it a Query object, no triggers will be called on objects representing the records to be deleted simply because there are no objects for Light to work with. Internally, Light will not instantiate an instance just so it can call its triggers. If such behavior is required, you should first Dao.Select objects that are to be deleted and then pass them to the Dao.Delete method.

Here is some code demonstrating the use of triggers. The code has not been tested to compile or run. Assume we have the following table in our SQL Server database:

create table parent (
    parentid int not null identity(1,1) primary key,
    name varchar(20)
)
go

create table child (
    childid int not null identity(1,1) primary key,
    parentid int not null foreign key references parent (parentid),
    name varchar(20)
)
go

Here are C# classes defining this fake parent/child relationship:

using System;
using System.Data;
using System.Collections.Generic;
using Light;

//
// Here is our parent.
//
[Table("parent")]
public class Parent
{
    [Column("parentid", DbType.Int32, PrimaryKey=true, AutoIncrement=true)]
    private int id = 0;
    [Column("name", DbType.AnsiString, 20)]
    private string name;
    
    private IList<Child> children = new List<Child>();
    
    public Parent()
    {}
    
    // No setter as it will be assigned by the database.
    public int ParentId
    {
        get { return id; }
    }
    
    public string ParentName
    {
        get { return name; }
        set { name = value; }
    }
    
    public int ChildCount
    {
        get { return children.Count; }
    }
    
    public Child GetChild(int index)
    {
        if(index > 0 && index < children.Count)
            return children[index];
        return null;
    }
    
    public void AddChild(Child child)
    {
        child.Parent = this;
        children.Add(child);
    }
    
    public void RemoveChild(Child child)
    {
        if(children.Contains(child))
        {
            child.Parent = null;
            children.Remove(child);
        }
    }
    
    //
    // Triggers
    //
    [Trigger(Actions.BeforeInsert | Actions.BeforeUpdate)]
    private void BeforeInsUpd(TriggerContext context)
    {
        // We can do validation here!!!
        // Let's say that the name cannot be empty.
        if(string.IsNullOrEmpty(name))
        {
            // This will cause Dao to throw an exception
            // and will abort the current transaction.
            context.Fail("Parent's name cannot be empty.");
        }
    }
    
    [Trigger(Actions.AfterInsert | Actions.AfterUpdate)]
    private void AfterInsUpd(TriggerContext context)
    {
        // Let's save all the children. The database is ready
        // for it because now this parent's id is in there
        // and referential integrity will not break.
        Dao dao = context.Dao;
        if(context.TriggeringAction == Actions.AfterUpdate)
        {
            // There may have been children already saved
            // so we need to delete them first.
            dao.Delete<Child>(new Query("parentid=@id").Add(
                new Parameter().SetName("@id").SetDbType(DbType.Int32)
                    .SetValue(this.id)
                ));
            
        }
        // And now we can insert the children.
        dao.Insert<Child>(children);
    }
    
    [Trigger(Actions.AfterActivate)]
    private void AfterActivate(TriggerContext context)
    {
        // Let's load all the children.
        Dao dao = context.Dao;
        children = dao.Select<Child>(new Query("parentid=@id").Add(
            new Paremter().SetName("@id").SetDbType(DbType.Int32)
                .SetValue(this.id)
            ));
        
        foreach(Child child in children)
            child.Parent = this;
    }
}

[Table("child")]
public class Child
{
    [Column("childid", DbType.Int32, PrimaryKey=true, AutoIncrement=true)]
    private int id = 0;
    [Column("name", DbType.AnsiString, 20)]
    private string name;
    
    private Parent parent;
    
    public Child()
    {}
    
    public int ChildId
    {
        get { return id; }
    }
    
    public string Name
    {
        get { return name; }
        set { name = value; }
    }
    
    public Parent Parent
    {
        get { return parent; }
        set { parent = value; }
    }
    
    [Column("parentid", DbType.Int32)]
    private int ParentId
    {
        get
        {
            if(parent != null)
                return parent.Id;
            return 0;
        }
    }
}

public class Program
{
    public static void Main(string[] args)
    {
        SqlConnection cn = new SqlConnection("Server=.; Database=test; Uid=sa; Pwd=");
        Dao dao = new SqlServerDao(cn);
        cn.Open();
        
        // Set up parent/child relationships.
        Parent jack = new Parent();
        jack.Name = "Parent Jack";
        
        Child bob = new Child();
        bob.Name = "Child Bob";
        
        Child mary = new Child();
        mary.Name = "Child Mary";
        
        jack.AddChild(bob);
        jack.AddChild(mary);
        
        // When we save the parent, its children will also be saved.
        dao.Insert<Parent>(jack);
        
        // This id was assigned by the database.
        int jacksId = jack.Id;
        
        // Let's now pull jack from the database.
        Parent jack2 = dao.Find<Parent>(jacksId);
        
        // All Jack's children should be loaded by now.
        Console.WriteLine("Jack's children are:");
        for(int i = 0; i < jack2.ChildCount; i++)
            Console.WriteLine(jack2.GetChild(i).Name);
        
        dao.Dispose();
        cn.Close();
    }
}

Be careful not to create triggers that load objects in circles. For example, say we would add a trigger to the Child class that would load its parent object on AfterActivate. This trigger would load the parent, which would start loading children, which in turn would start loading the parent again, and so on and so forth, until you run out of memory and your program crashes.

So, in a one-to-many relationship or cases where one object fully depends on another, triggers are very helpful. However, they will rarely be able to handle many-to-many relationships unless your code is disciplined enough to only access related objects from one side all the time. Of course, triggers don't solve all the issues of related objects, but in some cases they might help.

Stored Procedures

Light allows you to call stored procedures to select objects. This is useful to call procedures that perform searches based on multiple tables. Alternatively, you can create a view to deal with this, but in most cases it is easier to deal with a stored procedure. However, an even better use for it is to bypass an intermediate table in a many-to-many relationship defined in the database. An example should make this clear.

Example: the SQL

create table users (
    userid int identity(1,1) primary key,
    username varchar(30)
)
go

create table roles (
    roleid int identity(1,1) primary key,
    rolename varchar(30)
)
go

-- intermediate table: defines many-to-many relationship
create table userrole (
    userid int foreign key references users(userid),
    roleid int foreign key references roles(roleid),
    constraint pk_userrole primary key(userid, roleid)
)
go

create procedure getroles(@userid int) as
begin
    select roles.*
    from roles join userrole on roles.roleid = userrole.roleid
    where userrole.userid = @userid
end
go

Example: the C#

using System;
using System.Data;
using System.Data.SqlClient;
using System.Collection.Generic;
using Light;

[Table("roles")]
public class Role
{
    [Column(DbType.Int32, PrimaryKey=true, AutoIncrement=true)] private int roleid;
    [Column(DbType.AnsiString, 30)] private string rolename;
    
    public int Id {
        get { return roleid; }
        set { roleid = value; }
    }
    
    public string Name {
        get { return rolename; }
        set { rolename = value; }
    }
}

[Table("users")]
public class User
{
    [Column(DbType.Int32, PrimaryKey=true, AutoIncrement=true)] private int userid;
    [Column(DbType.AnsiString, 30)] private string username;
    
    private IList<Role> roles = new List<Role>();
    
    public int Id {
        get { return userid; }
        set { userid = value; }
    }
    
    public string Name {
        get { return username; }
        set { username = value; }
    }
    
    public IList<Role> Roles {
        get { return roles; }
    }
    
    [Trigger(Actions.AfterConstruct)]
    private void T1(TriggerContext ctx)
    {
        // Notice that we are not using the UserRole objects
        // here to pull the list of Role objects.
        
        Dao dao = ctx.Dao;
        roles = dao.Call<Role>(
            new Procedure("getroles").Add(
                new Parameter("@userid", DbType.Int32, this.userid)
            )
        );
    }
}

[Table]
public class UserRole
{
    [Column(DbType.Int32, PrimaryKey=true)] private int userid;
    [Column(DbType.Int32, PrimaryKey=true)] private int roleid;
    
    public int UserId {
        get { return userid; }
        set { userid = value; }
    }
    
    public int RoleId {
        get { return roleid; }
        set { roleid = value; }
    }
}

public class Program
{
    public static void Main(string[] args)
    {
        SqlConnection cn = new SqlConnection("Server=.; Database=test; Uid=sa; Pwd=");
        cn.Open();
        Dao dao = new SqlServerDao(cn);
        
        // add new user
        User user1 = new User();
        user1.Name = "john";
        dao.Insert(user1);
        
        // add some roles
        for(int i = 0; i < 3; i++)
        {
            // create role
            Role role = new Role();
            role.Name = "role " + (i+1).ToString();
            dao.Insert(role);
            
            // associate with user1
            UserRole userrole = new UserRole();
            userrole.UserId = user1.Id;
            userrole.RoleId = role.Id;
            dao.Insert(userrole);
        }
        
        // let's select the only user from the database
        // it should have all roles in its Roles property
        User user2 = dao.Find<User>(user1.Id);
        
        Console.WriteLine("Roles of " + user2.Name + ":");
        foreach(Role role in user2.Roles)
        {
            Console.WriteLine(role.Name);
        }
        
        dao.Dispose();
        cn.Close();
    }
}

Performance

Light is a wrapper around ADO.NET, so it is slower than ADO.NET by definition. On top of that, Light uses reflection to generate table models and create objects to be returned from the Dao.Select and Dao.Find methods. That is also slower than the creation of objects using the new operator. However, Light does attempt to compensate for these slowdowns.

Light generates only parameterized SQL statements. Every command that runs is prepared in the database (IDbCommand.Prepare is called before a command is executed). This forces the database to generate an execution plan for the command and cache it. Later calls to the same type of command (INSERT, SELECT, etc.) with the same type of object should be able to reuse the previously created execution plan from the database, unless the database removed it from its cache.

Light has a caching mechanism for generated table models, so it doesn't have to use reflection to search through a type of any given object every time. By default, it stores up to 50 table models, but this number is configurable (see Dao.CacheSize). Light uses the Least Recently Used algorithm to choose table models to be evicted from the cache when it becomes full.

Conclusion

The demo project provided is not really a demo project. It is just a bunch of NUnit tests that I ran against an SQL Server 2005 database. So, if you want to run the demo project, you will need to reference (or re-reference) the NUnit DLL that is on your system. Also, you will need to compile the source code and reference it from the demo project. No binaries are provided in the downloads, only source code. You don't need Visual Studio to use these projects; you can use a freely available SharpDevelop IDE (which was used to develop Light) or the good old command line.

Also included is an extension project by Jordan Marr. His code adds support for concurrency and introduces a useful business framework structure. It keeps track of object properties that were changed and only updates objects if anything was changed. This reduces the load on the database. The business framework also allows you to add validation rules to your objects.

The code is fully commented, so you may find some more useful information there. I hope this was, is or will be useful to somebody in some way...

Credits

Many thanks to Jordan Marr for his contribution, feedback, ideas and the extension project.

History

• 2007-10-18: First submission of this article (code version 2.0.0.0)
• 2007-11-01: Added the "Querying" article section; new version of the code (version 2.1.0.0)
  Bug fixes and improvements:
  • Fixed a bug with private properties; now mapping private property to a column works as expected
  • Now extracting values from a data reader uses an integer index instead of column names
• 2007-12-16: Modified the article to reflect API changes (code version 2.5.0.0) and added the "Triggers" article section
• 2008-02-05: Code version 2.5.3.0
  • Small bug fixes
  • Added support for stored procedures
  • Added the ability to figure out table and column names (if those were omitted from attributes)
  • Modified the article to reflect API changes
  • Added sections for Default Names and Stored Procedures