Lite ORM library (v2)

• Download the Lite library - 24.45 KB

Changes

• 7/7/2008 - code version 1.0
• 7/22/2008 - code version 1.2
  • Added support for stored procedure output parameters.
  • Fixed a bug in handling a public property with a private getter/setter.
  • The name of the trace switch changed from "liteSwitch" to "lite".
• 7/28/2008 - code version 1.3
  • Changed the handling of procedure output parameters. Now all parameters go into the same array. You provide an array of indices at which output parameters are. See the "Stored Procedures" section below.
  • Added generics.
  • Fixed a bug related to transactions.

Introduction

This is my second attempt at writing a little ORM library. The first one can be found here. The basic ideas are the same, but the code has been completely redesigned and written from scratch.

This little library allows to map a class or a struct to a single table or a view in the database. A mapped class can be used to execute simple Insert, Update, Delete, and Select statements against the mapped table. This is a very common thing to do nowadays, so I doubt it requires any more explanation. In addition, this library allows calling functions and stored procedures (of course, if your database supports them) in some useful ways.

The code can work with structs as well as classes. The only requirement is that a class or a struct has a default constructor (it could be private). In the article, class is interchagable with struct. Same thing goes for database tables and views. Also, the article assumes that we are working with the SQL Server database engine.

Class-to-Table Mapping

To map a class to a database table, we need to decorate the class with some attributes. They could be found in the root namespace, which is lite. The TableAttribute should be applied to the class. Here, we specify the name of the table and the schema it belongs to. The Schema property can be left blank, in which case the table name will not be qualified with the name of the schema. If the Name property is left blank, it is assumed that the name of the class is the same as the name of the table.

using lite;

// maps to table dbo.person
[Table]
public class Person

// maps to table dbo.users
[Table(Name="users")]
public class User

// maps to table people.person
[Table(Schema="people")]
public class Person

// maps to view people.transactView
[Table(Name="transactView",Schema="people")]
public class Purchase

To map a class member to a table column, we have several options. The most common and the obvious one would be to use the ColumnAttribute, which could be applied to either a field or a property. This attribute has two properties that we can specify, Name and Alias. The Name is the actual name of a column in the database table. The Alias is... well, exactly what it sounds like, an alias. We will use aliases instead of column names when querying the database. This allows for greater flexibility to change column names in the database without having to modify a lot of code. If the Name property is not specified, it is assumed that the name of the class member to which the attribute is applied is the same as the name of the column in the database. If the Alias property is not specified, then the alias is the same as the name of the column.

// maps to [order_id]
[Column(Name="order_id")]
private int orderId;

// maps to [customer_id]
[Column(Name="customer_id")]
public int CustomerId { get; set; }

// maps to [quantity]
[Column]
public int Quantity { get; set; }

We will see the Alias property in action when we get to querying the database.

Another way to map a class field to a table column is to use the MapAttribute, which is applied to the class. This attribute can map any field that is visible to the current class. Notice that the field does not have to be defined in the class to which MapAttribute applies. This allows mapping of inherited fields. In the code, this attribute extends the ColumnAttribute, and therefore inherits the Name and the Alias properties that act the same way. But, to use the MapAttribute, we must tell it the name of the class member we want to map. An example should make it clear.

public class Person
{
    protected string ssn;
}

[Table]
[Map("ssn")]
public class Student
{
    [Column, ID, PK]
    protected int studentNumber;
}

The IDAttribute marks the identity column, and PKAttribute specifies the primary key column. There can only be one identity, but more than one primary key columns. Note: these attributes are required for the proper functioning of Insert, Update, and Delete statements.

Just to clarify:

• Not all table columns are required to be mapped, and not all class members are required to be mapped.
• Read-write class members are used in both directions: class-to-database and database-to-class. Read-only members are only used when sending data from class to database. Write-only members are used when populating the class from the database.
• Class-to-database direction are Inserts and Updates. Database-to-class is the Select statement.
• Class fields (variables) are always considered as read-write.
• Properties with get and set methods are read-write. Properties with only the get method are read-only. Properties with only the set method are write-only.

Using the Mapped Classes

Before we can use the mapped classes, we need to have an object that can generate and run SQL statements using our classes. This would be an object implementing the IDb interface. So, what we need to do is setup a way to get at those objects. For now, we will assume that we are working with a SQL Server database. This library comes with the implementation for SQL Server, but you are more than welcome to write code that will work with the database of your choice. Now, we need to write a class that will serve as a factory that produces IDb objects. Here is a very simple one:

using lite;
using lite.sqlserver;

// Every call to GetDb() returns a brand new IDb object with a new database
// connection under it. Depending on your needs you can easily modify
// this class to make it always return the same instance of IDb object or
// different IDb objects sharing the same connection.
public class DbFactory
{
    public static readonly DbFactory Instance = new DbFactory();
    
    private SqlProvider provider;
    
    private DbFactory()
    {
        string connectString = ...; //maybe get it from config file
        provider = new SqlProvider(connectString);
    }
    
    public IDb GetDb()
    {
        return provider.OpenDb();
    }
}

At this point, we are ready to start using our mapped classes. So, let's fully define a class that we will use in our examples.

create table dbo.purchase (
    purchase_id bigint identity primary key,
    customer int,
    product int,
    quantity int,
    comment nvarchar(100),
    purch_date datetime not null default getdate()
)
go

[Table]
public class Purchase
{
    [Column(Name="purchase_id",Alias="id"), ID, PK]
    private long purchaseId;
    
    [Column] private int customer;
    [Column] private int product;
    [Column] private int quantity;
    [Column] private string comment;
    
    [Column(Name="purch_date",Alias="date")]
    private DateTime purchaseDate;
    
    public Purchase()
    {
        purchaseDate = DateTime.Now;
    }
    
    public long Id
    {
        get { return purchaseId; }
    }
    
    public int Customer
    {
        get { return customer; }
        set { customer = value; }
    }
    
    public int Product
    {
        get { return product; }
        set { product = value; }
    }
    
    public int Quantity
    {
        get { return quantity; }
        set { quantity = value < 0 ? 0 : value; }
    }
    
    public string Comment
    {
        get { return comment; }
        set { comment = value; }
    }
    
    public DateTime PurchaseDate
    {
        get { return purchaseDate; }
    }
    
    public override bool Equals(object other)
    {
        return id == other.id
            && customer == other.customer
            && product == other.product
            && quantity == other.quantity
            && comment == other.comment
            && purchaseDate == other.purchaseDate;
    }
    
    public override int GetHashCode()
    {
        return base.GetHashCode();
    }
    
    public override string ToString()
    {
        return "Purchase id is " + id.ToString();
    }
    
    [Trigger(Timing.All)]
    private void TriggerMethod1(object sender, TriggerEventArgs e)
    {
        bool truth = (this == sender);
        Console.WriteLine("Trigger timing is " + e.Timing.ToString());
    }
}

The above class defines a trigger method TriggerMethod1. This method will be called at the designated time specified by the Timing enum. The method signature is similar to the standard of .NET events and delegates, but it may very well change in the future releases. The idea here is to allow triggers to be defined some place else, but for that, of course, the way we define triggers would have to change. Something like this may be implemented in some later release.

using lite;

static void Main(string[] args)
{
    IDb db = DbFactory.Instance.GetDb();
    
    Purchase p1 = new Purchase();
    p1.Customer = 1;
    p1.Product = 2;
    p1.Quantity = 3;
    p1.Comment = "Fast delivery please!";
    
    int records = db.Insert(p1);
    Console.WriteLine(p1.Id); //should not be zero
    
    Purchase p2 = (Purchase) db.Find(typeof(Purchase), p1.Id);
    Console.WriteLine( p2.Equals(p1) ); //should be true
    
    p2.Quantity = p1.Quantity + 5;
    p2.Comment = p1.Comment + " And I added 5 more items to my order.";
    db.Update(p2);
    
    records = db.Delete(p2);
    Console.WriteLine(records);
    
    db.Dispose();
}

Querying

A more interesting part is the querying interface. It is very primitive, but it works. The IDb class has a factory method, Query() that returns an IQuery object. This object helps us define the Where clause of the Select statement. Note that when we constrain a column to a value, we do not use the name of the column. Remember that Alias property of the ColumnAttribute? This is where it comes in handy. We specify an alias of the column, and internally it will be resolved to the real column name. The advantage of this is that we can change the names of database columns without the need to modify any queries. An example should make it much easier to understand.

using lite;

static void Main(string[] args)
{
    IDb db = DbFactory.Instance.GetDb();
    
    // select * from dbo.purchase where id=1
    IQuery q = db.Query();
    // note that we are not using the "purchase_id" to reference the column
    // we are using "id" which is the alias for [purchase_id] column (see above)
    q.Constrain("id").Equal(1);
    IList list = db.Select(typeof(Purchase), q);
    if (list.Count > 0)
    {
        Purchase p = (Purchase) list[0];
        ...
    }
    
    // select * from dbo.purchase where customer=1
    IQuery q1 = db.Query();
    q1.Constrain("customer").Equal(1);
    list = db.Select(typeof(Purchase), q1);
    
    // select * from dbo.purchase where customer=1 and product=2
    IQuery q2 = db.Query();
    q2.Constrain("customer").Equal(1).And()
        .Constrain("product").Equal(2);
    list = db.Select(typeof(Purchase), q2);
    
    // select * from dbo.purchase where
    // quantity<=10 and (customer=1 or product=2)
    IQuery q3 = db.Query().Constrain("customer").Equal(1).Or()
        .Constrain("product").Equal(2);
    IQuery q4 = db.Query().Constrain("quantity").LessEqual(10).And()
        .Constrain(q3);
    list = db.Select(typeof(Purchase), q4);
    
    // select * from dbo.purchase where (customer=1 and product=2)
    // or (quantity>5 and purch_date>=dateadd(day,-10,getdate()))
    IQuery q5 = db.Query().Constrain("customer").Equal(1).And()
        .Constrain("product").Equal(2);
    IQuery q6 = db.Query().Constrain("quantity").Greater(5).And()
        .Constrain("date").GreaterEqual(DateTime.Now.AddDays(-10));
    IQuery q7 = db.Query().Constrain(q5).Or().Constrain(q6);
    list = db.Select(typeof(Purchase), q7);
    
    // select * from dbo.purchase where comment like '%delivery%'
    list = db.Select(typeof(Purchase), 
           db.Query().Constrain("comment").Like("%delivery%"));
    
    // select * from dbo.purchase where
    // customer in (1,5,10) order by customer asc
    int[] intarray = new int[] { 1,5,10 };
    // all arrays in .NET implement IList
    IQuery q9 = db.Query().Constrain("customer").In(intarray)
        .Order("customer", true);
    list = db.Select(typeof(Purchase), q9);
    
    // select * from dbo.purchase where product
    // not in (2,3,4) order by purch_date desc
    IList notin = new ArrayList();
    notin.Add(2);
    notin.Add(3);
    notin.Add(4);
    IQuery q10 = db.Query().Constrain("product").NotIn(notin)
        .Order("date", false);
    list = db.Select(typeof(Purchase), q10);
    
    // select * from dbo.purchase where quantity
    // is null and purch_date is not null
    IQuery q11 = db.Query().Constrain("quantity").Equal(null).And()
        .Constrain("date").NotEqual(null);
    // .Equal(null) and .NotEqual(null) will convert to SQL's "is null"

    // and "is not null" respectively
    list = db.Select(typeof(Purchase), q11);
    
    // delete from dbo.purchase where customer=1 and quantity>200
    IQuery q12 = db.Query().Constrain("customer").Equal(1).And()
        .Constrain("quantity").Greater(200);
    list = db.Delete(typeof(Purchase), q12);
    
    // delete from dbo.purchase
    int deleted = db.Delete(typeof(Purchase), (IQuery)null);
    
    db.Dispose();
}

The syntax is very primitive, but is also very similar to the actual SQL statements, so it should be fairly simple to grasp.

Stored Procedures and Functions

Let's move on to calling functions and stored procedures. To call a function, we use the Call method; to execute a stored procedure, we use the Exec method of the IDb class. Functions can only return a single value as a return value, hence the signature of the Call method. We can use this method to execute a stored procedure as well, but the only thing that will be returned is the return value of the procedure (usually an int value). The Exec method is more interesting. It can execute a specified stored procedure and return a list of items of a specified type. The only constraint on that procedure is that it should return a result set with columns that are defined in the mapped class or the table to which the desired type is mapped (an example will make it clear). Another overload of the Exec method executes a specified stored procedure and returns an IResultSet object. This object is like a DataSet object, but not as heavy. Underneath, it's just an array of arrays (rows) returned by the executed procedure. We can get individual values using the index or the name of the column. There is also a version of the Exec method that allows us to get the values of the output parameters of the executed stored procedure.

create procedure dbo.get_purchases
    @cust_id int,
    @prod_id int
as
begin
    select purchase_id, customer, product, quantity, comment, purch_date
    from dbo.purchase
    where customer = @cust_id and product = @prod_id
end
go

create procedure dbo.get_customer_purchases
    @cust_id int
as
begin
    select product, quantity, comment, purch_date
    from dbo.purchase
    where customer = @cust_id
end
go

create function dbo.get_purchase_quantity(@id bigint)
returns int
as
begin
    declare @quantity int
    select @quantity = quantity from dbo.purchase where purchase_id = @id
    return @quantity
end
go

create procedure dbo.customer_summary
    @cust_id int,
    @products int output,
    @items int output,
    @last_purch_date datetime output
as
begin
    -- total number of distinct products purchased
    select @products = count(distinct product)
    from dbo.purchase
    where customer = @cust_id
    
    -- total number of items
    select @items = sum(quantity)
    from dbo.purchase
    where customer = @cust_id
    
    -- last purchase date
    select @last_purch_date = max(purch_date)
    from dbo.purchase
    where customer = @cust_id
end
go

using lite;

static void Main(string[] args)
{
    IDb db = DbFactory.Instance.GetDb();
    
    object[] values = new object[2];
    values[0] = 1; // cust_id parameter
    values[1] = 2; // prod_id parameter
    IList list = db.Exec(typeof(Purchase), "dbo.get_puchases", values);
    foreach (Purchase p in list)
    {
        Console.WriteLine(p.ToString());
    }
    
    IResultSet rs = db.Exec("dbo.get_customer_purchases", new object[] { 1 });
    while (rs.Next())
    {
        object o = rs["product"];
        if (o != null)
            Console.WriteLine("product " + o.ToString());
            
        o = rs["quantity"];
        if (o != null)
            Console.WriteLine("quantity " + o.ToString());
            
        o = rs["comment"];
        if (o != null)
            Console.WriteLine("comment " + o.ToString());
            
        o = rs["purch_date"];
        if (o != null)
        {
            DateTime purchDate = (DateTime) o;
            Console.WriteLine("purch_date " + purchDate.ToShortDateString());
        }
        
        Console.WriteLine();
    }
    
    long purchaseId = 5;
    object quantity = db.Call("dbo.get_purchase_quantity",
        new object[] { purchaseId });
    if (quantity == null)
        Console.WriteLine("no purchase with id " + purchaseId + " found");
    else
    {
        int q = (int) quantity;
        Console.WriteLine("quantity is " + q);
    }
    
    // This array contains all parameters (input and output) to the procedure.
    // We initialize the output parameters with default values  so that lite
    // can figure out the correct type of the parameter (default is string).
    object[] parameters = new object[] { 1, 0, 0, DateTime.MinValue };
    // This array specifies the indices at which output parameters are.
    // The values at these indices will be overwritten by the Exec method.
    int[] outputs = new int[] { 1, 2, 3 };
    IResultSet rs = db.Exec("dbo.customer_summary", parameters, outputs);
    Console.WriteLine("Should be zero: " + rs.Rows);
    // Our procedure doesn't have any code to guarantee that only non-null
    // values are returned, so we need to check for nulls. The values given
    // during output array initialization are overwritten and could be null.
    int distinctProducts = (parameters[1] != null) ? (int) parameters[1] : 0;
    int totalItems = (parameters[2] != null) ? (int) parameters[2] : 0;
    DateTime? lastPurchase = (parameters[3] != null) ?
        (DateTime?) parameters[3] : (DateTime?) null;
    Console.WriteLine("Distinct products purchased: " + distinctProducts);
    Console.WriteLine("Total number of items: " + totalItems);
    Console.WriteLine("Last purchase: " +
        lastPurchase.HasValue ?
        lastPurchase.Value.ToShortDateString() :
        "Never"

    );
    
    db.Dispose();
}

SPResultAttribute

Consider the dbo.get_customer_purchases procedure defined above. Executing that procedure returns an IResultSet object. But, we could make it return a list of strongly typed objects. The catch here is that we cannot really map this object to a table or a view because no table with such columns exist. The answer is to use the SPResultAttribute. Apply this attribute to a class instead of the TableAttribute. This will allow the IDb object to create instances of that class in response to executing a stored procedure. Here is an example.

using lite;

[SPResult]
public class CustomerPurchase
{
    [Column] public int Product;
    [Column] public int Quantity;
    [Column] public string Comment;
    [Column(Name="purch_date")] public DateTime PurchaseDate;
}

static void Main(string[] args)
{
    using (IDb db = DbFactory.Instance.GetDb())
    {
        string procName = "dbo.get_customer_purchases";
        object[] parameters = new object[] { 1 };
        IList list = db.Exec(typeof(CustomerPurchase), procName, parameters);
        foreach (CustomerPurchase cp in list)
        {
            Console.WriteLine(string.Format("{0}, {1}, {2}, {3}",
                              cp.Product, cp.Quantity, 
                              cp.Comment, cp.PurchaseDate);
        }
    }
}

Transactions

All database calls occur within a transaction. We can explicitly start a transaction by calling the IDb.Begin method. We can commit and rollback a transaction that we previously started by using the IDb.Commit and IDb.Rollback methods, respectively. If we do not start a transaction explicitly, a transaction will be started automatically when we call methods that attempt to modify the database. A Commit will be called if the database call returns successfully, otherwise Rollback will be called and an exception re-thrown. During an explicit transaction (started by the client code), the client code is responsible for handling exceptions and calling a Rollback method when required.

Nullable Types

By the way, we can use nullable types (int?, DateTime?, etc.) as the type of the field or property that we are mapping. The code should be able to work with them as well as with regular primitive types.

Tracing

If we want to see which SQL statements were executed against the database, we can simply configure .NET tracing, and Lite will output all the generated SQL statements. Before executing any command, the code sends information about the command and its parameters to the System.Diagnostics.Trace object, by default. If tracing is not configured or is turned off, then we obviously will not see anything. To enable tracing, we need to modify the config file (and, of course, we need to have the TRACE compilation symbol defined at compilation time). Here is a sample config file:

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
    <system.diagnostics>
        <trace autoflush="true" indentsize="2">

            <listeners>
                <remove name="Default"></remove>
                <add name="console"
                    type="System.Diagnostics.ConsoleTraceListener"></add>

                <add name="liteTraceFile"
                    type="System.Diagnostics.TextWriterTraceListener"
                    initializeData="c:\sql.txt"></add>
            </listeners>

        </trace>

        <switches>
            <add name="lite" value="true"
                listener="liteTraceFile" dedicated="true"></add>

        </switches>
    </system.diagnostics>
</configuration>

We work with the system.diagnostics section of the config file. Everything here is just standard .NET configuration stuff (see the .NET documentation). All tracing occurs through the TraceObject. Trace is disabled, by default. There are a couple of ways to enable tracing. First is to set the Enabled property of the TraceObject.Instance to true. Disable it by setting it to false. Another way is to create a switch named "lite" in the config file and set its value attribute to "true" as shown above.

The configuration discussed in the previous paragraph will write to all defined listeners. The lite switch supports two more attributes: "listener" and "dedicated". The "listener" attribute allows us to specify the name of the listener to which trace information will be sent. If the "dedicated" attribute is set to "true", then the listener specified in the "listener" attribute will be removed from the list of all trace listeners and will only be used by this library. Without this attribute, other calls to the Trace object will be able to write data to our listener. We can also set a dedicated TraceListener in the code by setting the TraceObject.Instance.Listener property. This property can only be used to assign a dedicated listener. You are free to use your imagination about setting up these listeners - whatever works best for your scenario.

Sometimes, we may not want to see all the SQL statements sent to the database by our code. In that case, we could have the tracing disabled in the config file and use API to temporarily enable it for certain calls to the IDb object.

IDb db = DbFactory.Instance.GetDb();

// enable tracing (while debugging)
TraceObject.Instance.Enabled = true;

db.Insert(...);
db.Select(...);

// disable tracing again
TraceObject.Instance.Enabled = false;

db.Dispose();

Conclusion

Well, that's pretty much all this library has to offer. To write an implementation for a database other than SQL Server, you would have to implement all the interfaces in the lite namespace. See the lite.sqlserver namespace for an example. If you want to add some new attributes or come up with another way of defining tables, columns, triggers, and procedures, it should also be possible without modifying interfaces in the lite namespace. If you need to add some new functionality you can just extend the IDb interface and extend the IProvider to return objects of the new type. This should not break the compatibility with existing code.

Thanks for reading. Have fun!