Introduction
I have implemented this class library during the development of several database-driven projects. Some of these projects utilized large amounts of data, and it was needed to implement a fast working and easy to use data access layer. I will not describe my reasons why to not use typed DataSets, NHibernate, DLINQ, or something similar (but if you are interested, here I've described some of them). I think that if you are reading this article you have your own ones.
Instead, I'll describe what this class library provides:
Strongly typed SQL commands
I've implemented support for two SQL command types: select queries (EntityReaderCommand<TEntity>) and scripts (EntityScriptCommand<TEntity>).
With EntityReaderCommand<TEntity>, you can read entities from the database by executing a specified SQL query. It works by analogy with IDataReader implementations, but when the IDataReader with each fetch returns an IDataRecord, the EntityReaderCommand<TEntity> returns an instance of the TEntity class.
This code sample will fill List<Person> from the database table "Person":
var persons = new List<Person>();
var reader = new EntityReaderCommand<Person>(
args => persons.Add(args.Entity),
@"
SELECT
Id <Id>,
FirstName <FirstName>,
LastName <LastName>
FROM
Person");
reader.Execute();
This code sample will read a Rectangle instance from the database:
var rectangle = new Rectangle();
new EntityReaderCommand<Rectangle>(
delegate(EntityReaderArguments<Rectangle> args)
{
rectangle = args.Entity;
args.Terminate = true;
},
@"
SELECT
1 <Location.X>,
2 <Location.Y>,
3 <Width>,
4 <Height>").Execute();
This code sample does the same, but using EntityScriptCommand<TEntity>:
var command = new EntityScriptCommand<Rectangle>(
new Rectangle(),
@"
SET <Location.X> = 1
SET <Location.Y> = 2
SET <Width> = 3
SET <Height> = 4");
command.Execute();
var rectangle = command.Entity;
The following code sample inserts a new Person instance in the database table "Person". Note, the Person object will have a new Id after insert.Execute().
var person = new Person{
FirstName = "Boris",
LastName = "Nadezhdin"
};
var insert = new EntityScriptCommand<Person>(
person,
@"
SET <Id> = newid()
INSERT INTO
Person
(
Id,
FirstName,
LastName
)
VALUES
(
<Id>,
<FirstName>,
<LastName>
)");
insert.Execute();
So, as you can see from the samples above, the query syntax is native to the SQL client, the only difference is mapping between the entity properties and the column aliases. Commands support composite properties (like Rectangle.Location). Entities and their properties can be reference or value types. There is no need for special XML or attribute based O/R mappings, all mappings are specified directly in the queries.
There are only two restrictions on the types of entities and their properties:
- The type should have a default constructor.
- If the property is used in a command, it should have a public getter and setter.
Disconnected entities
There is no proxy generation for entities in the command execution logic, so if you are using an EntityReaderCommand<TEntity>, you will read the TEntity, not a generic ancestor.
There is no constraints on entities and session scopes, you can read an entity from the database in one session and update it back in another one.
There is no entity cache, so you can have several instances of the same database entity.
Simple syntax for command parameters
Sometimes it needs to pass some additional arguments into the command. For example, to find a Person by Id:
public Person FindOne(Guid id)
{
Person person = null;
var reader = new EntityReaderCommand<Person>(
delegate(EntityReaderArguments<Person> args)
{
person = args.Entity;
args.Terminate = true;
},
@"
SELECT
Id <Id>,
FirstName <FirstName>,
LastName <LastName>
FROM
Person
WHERE
Id = {0}", id);
reader.Execute();
return person;
}
As you can see from the sample above, the syntax is similar to String.Format(String, params Object[] args), but in that case, id is not substituted by its string representation, it is passed into the command as a parameter.
Here is another sample in which you can see how to pass an argument and get its modified value back after the command execution:
const int one = 1;
var command = new SimpleCommand(
"SET {0} = {0} + 1", one);
command.Execute();
Assert.AreEqual(one + 1, command.GetArgs()[0]);
Transparent session and transaction management
Each command needs an open database session; if there is no open session in the current thread, the command opens a new session and closes it after execution.
To open new session manually, you should create a new instance of SessionScope; to close it, call Dispose() on the instance of the SessionScope. So, all commands in the scope of the SessionScope object will use one session, created by the SessionScope object.
If you are working on a web application and you want to have a database session per web request, you can create an instance of the SessionScope (and store it in items of HttpContext, for example) in the BeginRequest event handler and dispose it in the EndRequest event handler.
In addition to SessionScope, there is another scope of command execution, TransactionScope. It is used for transaction management.
To start a new transaction, you should create a new instance of TransactionScope. To end it, call Dispose() on the instance of the TransactionScope. So, all command in scope of that TransactionScope object will work in the transaction created by the TransactionScope object. Transactions are marked to rollback by default. So, to commit a transaction, you should call Commit() on the instance of the TransactionScope. Note, that no actual commit is performed before the disposal of TransactionScope.
Another interesting feature of command scopes is that they are local to thread there they were instantiated. So commands in different threads will always work in different database sessions.
Native database client abstraction layer
Objects from this class library are not coupled with any concrete database client. Session and transaction management, command execution, all work with native database clients only through the IDatabaseProvider interface. All methods specific to native database clients are extracted in this interface. Currently, there is only one implementation of IDatabaseProvider, for Microsoft SQL Server, but it is really simple to implement ones for Oracle, PgSQL, or MySQL.
The concrete IDatabaseProvider implementation and database connection string can be defined declaratively in the application configuration file, or programmatically via Scope.Configuration. Application configuration file is used by default, for example:
="1.0"
<configuration>
<configSections>
<section
name="dataAccess"
type="Yap.Data.Client.ConfigurationSectionHandler, Yap.Data.Client"/>
</configSections>
<dataAccess
providerType="Yap.Data.Client.Sql.SqlProvider, Yap.Data.Client.Sql"
providerAssembly="Yap.Data.Client.Sql.dll"
connectionString="Data Source=.\SQLEXPRESS; Database=YAP;Integrated Security=True;"/>
</configuration>
Sources
The solution was created in Visual Studio 2008, and consists of three projects:
- Yap.Data.Client
- Yap.Data.Client.Sql
- Yap.Data.UnitTest
The main object model is defined in Yap.Data.Client. The IDatabaseProvider implementation for Microsoft SQL Server is defined in Yap.Data.Client.Sql. Yap.Data.UnitTest contains test fixtures for the Yap.Data.Client assembly. Yap.Data.UnitTest uses MSTest, but can be easily converted to NUnit.
To run Unit Tests, it needs to create a database, execute create-script.sql from the folder SQL, and modify the connection string in the app.config file.
Binaries
You need only three things to use this class library in your project:
- Modify the application configuration file as it was shown in the sample above
- In your data access layer project, add a reference to Yap.Data.Client
- Copy Yap.Data.Client.Sql to the application output folder
History
