Database Access, Background

As I mentioned previously, we have an ORM layer on the project I am working on.  In this layer we have a DatabaseProxy for each table in our database.  So for example, if I have a User table, I have a UserProxy object that is responsible for basic (and more advanced) operations, such as Create, Read, Update, and Delete. 

Here is a snippet for how the UserProxy works to create a user:

public int Create(User user)
{
    int result;
    using (SqlCommand cmd = new SqlCommand("usp_userCreate"))
    {
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.Add(new SqlParameter("username", user.userName));
        cmd.Parameters.Add(new SqlParameter("email", user.email));
        cmd.Parameters.Add(new SqlParameter("password", user.password));
        cmd.Parameters.Add(new SqlParameter("salt", user.salt));
        SqlParameter userIdParam = new SqlParameter();
        userIdParam.ParameterName = "userId";
        userIdParam.Direction = ParameterDirection.Output;
        userIdParam.SqlDbType = SqlDbType.Int;
        cmd.Parameters.Add(userIdParam);
        DatabaseExecutor.Instance.ExecuteSingleCommand(cmd, null, ConnectionStrings,
            IsolationLevel.None, null);
        result = (int)userIdParam.Value;
        user.userId = result;
    }
}

the User object is just a lightweight data structure.
The DatabaseExecutor static instance is essentially an encapsulation of the mirroring/dual rail logic (and it supports batching too) and it executes the command with the supplied isolation levels.

Next on the UserProxy we have a a Read statement. Essentially you hand in a delegate (DataReaderConsumer) that takes an IDataReader as an interface.

public void FindAllUsers(DataReaderConsumer consumer)
{
    using(SqlCommand cmd = new SqlCommand("usp_userFindAll"))
    {
        cmd.CommandType = CommandType.StoredProcedure;
        DatabaseExecutor.Instance.ExecuteSingleCommand(cmd, consumer,
                ConnectionStrings, IsolationLevel.None, null);
    }
}

The call to FindAll looks like this:

List users = new List();
userProxy.FindAllUsers(
delegate(IDataReader reader)
   {
     while (reader.Read())
     {
         User newUser = new User();
         newUser.userId = (int) reader["userid"];
         newUser.userName = (string) reader["username"];
         users.Add(newUser);
     }
});return users;

As an aside the objects that use the Proxies represent an interface into the database access components. So we have an IUserAccessor interface that also has something to a findAllUsers method and returns this list. The purpose of this was to supply an Interface to developers that didn’t have to touch any Database specific code (we also thought we might be sharing this code to other groups within the company).

So that’s roughly how our data access layer works. The IUserAccessor (which admittedly I glossed over here) is also the point at which we can separate the system from the database (for unit testing).

Anyway, next post I’ll get into the DatabaseExecutor and batch operations.