A Small ADO.NET Library with Some ORM Capabilities

• Download source - 20.53 KB

Purpose

This is not meant to be a full blown ORM solution nor is it claimed as such. The purpose is to map one object to one table/view and allow the user to perform basic CRUD (Insert, Update, Delete, Select) tasks.

Overview

The idea is simple. Create a class to represent a record in one of the database tables or views. The class and its members are then mapped to the table. Note that class members must be visible within the scope of the class in order to be mapped. If the parent class mapped a private field, it will not be used when dealing with the child class.

Mapping

The mapping can be done using one of two ways: using attributes or implementing a static method. These are the two methods provided with the source code. Other mapping methods could easily be defined by implementing the Sqless.Mapping.ITableDefinitionBuilder interface and adding an instance of that class to Sqless.Mapping.TableDefinitionFactory.Builders list. You can also rearrange the items in this list to specify the order in which classes will be checked for mappings. (Note: Accessing this list is not thread safe.)

In order to map a class to a database table using attributes, the class needs to be decorated with a TableAttribute, where the user can specify the following:

• Name - Name of the mapped table
• Schema - Schema name to which the mapped table belongs
• Sequence - Sequence object associated with the mapped table (this only makes sense for databases, such as Oracle, that support sequences)

To map a class member to a table column, the class member must be decorated with a FieldAttribute where the user can specify the following:

• Name - Name of the database column
• Flags - Field flags which specify how and when the field will be accessed

The FieldFlags enum has the following values:

• Read - The field value is allowed to be read from database column and written into .NET object.
• Write - The field value is allowed to be read from .NET object and saved into database column.
• ReadWrite - Allows the field value to be passed in both directions. Equivalent to FieldFlags.Read | FieldFlags.Write. This should be considered the default if no other flags are specified.
• Key - Specifies that this field is part of the primary key on the table.
• Auto - Specifies that the value of this field is automatically generated by the database.

When specifying FieldFlags, the flags could be combined using the bitwise OR.

Alternatively, a static method named DefineTable could be implemented in the class. This method should return a Sqless.Mapping.TableDefinition object which describes the mapping for the containing class. TableDefinition objects implement the Builder pattern, so method calls can be chained, and they provide a way to define the same data elements as attributes described above. The advantage of this method is that it avoids the System.Reflection calls necessary to discover the attribute mapping, hence, it should be faster. The disadvantage is that it's more typing and field mappings are not inherited by child classes.

We will see examples of both mapping methods later.

API Overview

The main object is the IDatabase object. It is a wrapper over a database connection. It provides a way to handle transactions, execute raw SQL statements and access ITable objects. The ITable object is the one that provides ORM capabilities, that is, handles the mapping between .NET objects and database tables. Each ITable can create IQuery objects which provide a way to run select (and delete) statements with the where and order by clause against that table. All communication with the underlying database is handled by IStatement objects. IStatement is a wrapper around a database command object. You can use IStatement to execute raw SQL statements (which is not very safe) or parameterized SQL statements with parameterization handled by the IStatement object. Simple .NET formatting (e.g. WHERE id = {0}) is used to specify parameter placeholders in SQL strings. IQueryResult objects are returned from executing queries using the IStatement. IQueryResult provides convenient methods to iterate over the result set.

Example Mapping

Consider the following table in SQL Server:

create table dbo.People
(
	Id int not null identity,
	Fname varchar(20),
	Lname varchar(20),
	Dob datetime,
	constraint PK_People primary key (Id)
)

Create a class to represent records in this table and map it using attributes:

[Table("People", "dbo")]
public class Person
{
	private int? id;
	private string fname;
	private string lname;
	private DateTime? dob;

	[Field("Id", FieldFlags.Read|FieldFlags.Key|FieldFlags.Auto)]
	public int? ID
	{
		get { return id; }
		set { id = value; }
	}

	[Field("Fname")]
	public string FirstName
	{
		get { return fname; }
		set { fname = value; }
	}

	[Field("Lname")]
	public string LastName
	{
		get { return lname; }
		set { lname = value; }
	}

	[Field("Dob")]
	public DateTime? BirthDate
	{
		get { return dob; }
		set { dob = value; }
	}
}

Notice the flags for the ID field. The ID field in the database is an identity field and a primary key. Hence, the flags contain the FieldFlags.Key and FieldFlags.Auto. Fields marked as Auto will be populated with generated value after the record object is inserted. The FieldFlags.Read allows this field to only be read from the database, but never written. This is useful because an identity field should not be updated. It is also possible to make any other field, not only identity fields, read-only or write-only.

Here is how to map the same class using the static method:

// Now we don't specify any attributes.
public class Person
{
	/* Field declarations */
	/* Property declarations */
	
	public static TableDefinition DefineTable()
	{
		return new TableDefinition("People").Schema("dbo")
			.Field("Id").MapTo("ID").ReadOnly().Key().Auto().Add()
			.Field("Fname").MapTo("FirstName").Add()
			.Field("Lname").MapTo("LastName").Add()
			.Field("Dob").MapTo("BirthDate").Add();
	}
}

Object Triggers

Triggers are object methods that are invoked either before or after an object participates in a database operation. Trigger names define when they are called. Just like all other class members, trigger methods must be visible within the scope of the class in question. That is, a private trigger method defined in the parent class will not be called when dealing with a child class. Trigger methods have the signature of an EventHandler(object sender, EventArgs args). The first parameter (sender) is the IDatabase object firing the trigger. The second is an empty EventArgs object.

Here is a list of all possible triggers:

// Called before insert operation.
void BeforeInsert(object sender, EventArgs args);

// Called after insert operation.
void AfterInsert(object sender, EventArgs args);

void BeforeUpdate(object sender, EventArgs args);
void AfterUpdate(object sender, EventArgs args);

// Not called if deleting using a query.
void BeforeDelete(object sender, EventArgs args);
void AfterDelete(object sender, EventArgs args);

// No corresponding BeforeSelect.
void AfterSelect(object sender, EventArgs args);

Say, we need to modify the Person class so that the BirthDate defaults to DateTime.Today if it was not set. We can add the following methods to the class:

public class Person
{
	/* Declarations are not shown. */
	
	protected void BeforeInsert(object sender, EventArgs args)
	{
		EnsureDob();
	}
	
	protected void BeforeUpdate(object sender, EventArgs args)
	{
		EnsureDob();
	}
	
	private void EnsureDob()
	{
		if (!dob.HasValue)
			dob = DateTime.Today;
	}
}

Triggers are free to load and save other objects. Assume we have Customer and Purchase classes defined and properly mapped to corresponding tables. We also want each Customer to always have the list of his Purchases. We can accomplish this using an AfterSelect trigger.

[Table("Customers")]
public class Customer
{
	private int customerID;
	private IList purchases;
	
	protected void AfterSelect(object sender, EventArgs args)
	{
		purchases = (sender as IDatabase).Table(typeof(Purchase))
			.Query().Eq("CustomerID", this.customerID)
			.OrderBy("PurchaseDate", false)
			.Select();
	}
}

The Query Object

IQuery objects allow users to specify the search criteria (the WHERE and ORDER BY clause) for the Select, Find, and Delete operations. The method names should make it really easy to figure out what the method does. An example should make it trivial. The IQuery object implements the builder pattern so that calls to its methods could be chained.

ITable table = db.Table(typeof(Person));

// WHERE Id = 1 and Fname = 'John'
table.Query().Eq("Id", 1).And().Eq("Fname", "John");

// WHERE Fname like 'M%' and Lname = 'Smith'
table.Query().Like("Fname", "M%").And().Eq("Lname", "Smith");

// WHERE (Fname = 'John' and Lname = 'Doe') or (Fname = 'Jane' and Lname = 'Smith')
table.Query().Sub().Eq("Fname", "John").And().Eq("Lname", "Doe").EndSub()
	.Or().Sub().Eq("Fname", "Jane").And().Eq("Lname", "Smith").EndSub();

// WHERE Id IN (1,2,3)
table.Query().In("Id", new int[] { 1,2,3 });

// WHERE Lname = 'Smith' ORDER BY Fname ASC
table.Query().Eq("Lname", "Smith").Order("Fname", true);

// Using a template query object, the following will select all rows
//   WHERE FirstName = 'John' AND LastName = 'Smith'
// All non-null fields are used in the query.
Person p = new Person();
p.FirstName = "John";
p.LastName = "Smith";
IList johnSmiths = database.Table(p.GetType()).Query(p).Select();

Executing Raw SQL Statements

You can execute any non-query SQL statements including insert, update, and delete. When executing SQL queries using IStatement, IQueryResult objects are returned. From here, you can read the result set into a RowSet object or provide your own callbacks that will be called when iterating over the results.

string sql = "select Id, Dob, Fname, Lname from dbo.People";

RowSet rs = database.Prepare(sql).ExecQuery().ToRowSet();
while (rs.Next()) {
	Console.WriteLine("Id = {0}, Dob = {1}, Fname = {2}, Lname = {3}",
		rs.Get(0), rs.Get("Dob"), rs[2], rs["Lname"] );
}

// Get a list of objects
IList people = database.Prepare(sql).ExecQuery()
	.ToList(new ToListCallback(delegate(IRow row) {
		MyObject p = new MyObject();
		p.ID = row[0];
		p.Date = row[1];
		p.Text = row[2] + row[3];
		return p;
	}));

// Output names
database.Prepare(sql).ExecQuery()
	.ForEach(new ForEachCallback(delegate(IRow row) {
		Console.WriteLine( (string)row[2] + " " + (string)row[3] );
	}));

Null and DBNull Values

Conversions between null and DBNull are handled automatically. Whenever you need to insert a null value into a database field, you should pass the .NET null value or set the mapped object field to null. Any DBNull values selected from the database are converted to .NET null before they are assigned to an object field or added to the RowSet object. Nullable types are supported. The IQuery object also handles nulls correctly when it is passed to the Eq and Ne methods.

The Trace Event

IDatabase objects raise Trace events. The Trace event is raised before executing any command against the database. This event is usually used to write the generated SQL statements and parameter values to a file or console. A more common option is to configure application tracing and write to the Trace object. This is useful for debugging purposes.

Some More Examples

SqlConnection conn = new SqlConnection("my_connection_string");
IDatabase database = new Sqless.SqlServer.SqlDatabase(conn);

// How many Johns do we have
int count = database.Table(typeof(Person))
		.Query().Eq("FirstName", "John")
		.Count();

// Get Person with id = 5
Person p = (Person) database.Table(typeof(Person))
		.Query().Eq("Id", 5).Find();

// Execute stored procedure SelectCustomer
string sql = "exec SelectCustomer {0}";
RowSet rs = database.Prepare(sql).ExecQuery(5).ToRowSet();

// exec InsertCustomer @p0, @p1, @p2
string sql = "exec InsertCustomer {0}, {1}, {2}";
int rowcount = 0;
using (IStatement stmt = database.Prepare(sql))
	for (int i = 0; i < 10; ++i)
		rowcount += stmt.ExecNonQuery(i, "John", "Doe");

History

• 5^th April, 2010: Initial post
• 19^th September, 2010: Update
• 30^th November, 2011: Update
• 9^th December, 2011: Update