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 theColumnAttribute(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
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
//