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
- 10/7/2010 - Added Deprecation Note section
Deprecation Note
The code presented in this article is deprecated and is no longer maintained. It is recommended that a new version of this library be used instead. The new library is not compatible with code presented in this article. It is a major rewrite of the whole thing and should be much friendlier and easier to use and extend. It is available here.
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 interchangeable 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;
[Table]
public class Person
[Table(Name="users")]
public class User
[Table(Schema="people")]
public class Person
[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.
[Column(Name="order_id")]
private int orderId;
[Column(Name="customer_id")]
public int CustomerId { get; set; }
[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;
public class DbFactory
{
public static readonly DbFactory Instance = new DbFactory();
private SqlProvider provider;
private DbFactory()
{
string connectString = ...;
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);
Purchase p2 = (Purchase) db.Find(typeof(Purchase), p1.Id);
Console.WriteLine( p2.Equals(p1) );
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();
IQuery q = db.Query();
q.Constrain("id").Equal(1);
IList list = db.Select(typeof(Purchase), q);
if (list.Count > 0)
{
Purchase p = (Purchase) list[0];
...
}
IQuery q1 = db.Query();
q1.Constrain("customer").Equal(1);
list = db.Select(typeof(Purchase), q1);
IQuery q2 = db.Query();
q2.Constrain("customer").Equal(1).And()
.Constrain("product").Equal(2);
list = db.Select(typeof(Purchase), q2);
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);
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);
list = db.Select(typeof(Purchase),
db.Query().Constrain("comment").Like("%delivery%"));
int[] intarray = new int[] { 1,5,10 };
IQuery q9 = db.Query().Constrain("customer").In(intarray)
.Order("customer", true);
list = db.Select(typeof(Purchase), q9);
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);
IQuery q11 = db.Query().Constrain("quantity").Equal(null).And()
.Constrain("date").NotEqual(null);
list = db.Select(typeof(Purchase), q11);
IQuery q12 = db.Query().Constrain("customer").Equal(1).And()
.Constrain("quantity").Greater(200);
list = db.Delete(typeof(Purchase), q12);
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.
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
select @products = count(distinct product)
from dbo.purchase
where customer = @cust_id
select @items = sum(quantity)
from dbo.purchase
where customer = @cust_id
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;
values[1] = 2;
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);
}
object[] parameters = new object[] { 1, 0, 0, DateTime.MinValue };
int[] outputs = new int[] { 1, 2, 3 };
IResultSet rs = db.Exec("dbo.customer_summary", parameters, outputs);
Console.WriteLine("Should be zero: " + rs.Rows);
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:
="1.0"="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();
TraceObject.Instance.Enabled = true;
db.Insert(...);
db.Select(...);
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!
