Easy Data Access

Introduction

Before we start talking about the subject, let us create a few examples of typical data accessor methods.

First, we will need a few stored procedures.
Here is our first sproc:

CREATE Procedure GetPersonListByName(
    @firstName  varchar(50),
    @lastName   varchar(50),
    @pageNumber int,
    @pageSize   int)
AS
  -- stored procedure implementation

This stored procedure takes filter and page parameters and returns recordset from the Person table.

The method implementing the stored procedure call can look like the following:

public List<Person> GetPersonListByName(
    string firstName,
    string lastName,
    int    pageNumber,
    int    pageSize)
{
    // method implementation.
}

The second example will return single Person record by id.
Stored procedure:

CREATE Procedure GetPersonByID(@id int)
AS
  -- stored procedure implementation

Data access method:

public Person GetPersonByID(int id)
{
    // method implementation.
}

The last example will delete a record from the database by id.
Stored procedure:

CREATE Procedure DeletePersonByID(@id int)
AS
  -- stored procedure implementation

Data access method:

public void DeletePersonByID(int id)
{
    // method implementation.
}

So, let's see what we can say if we compare the stored procedure and C# method signatures.

1. Stored procedure and method names match up.
2. Sequential order, method parameter types and names correspond to stored procedure parameters.
3. Methods' return values can give us an idea what Execute method we should utilize and what object type has to be used to map data from recordset if needed.

As demonstrated above method definition contains all the information we need to implement the method body. Actually, by defining the method signatures, we completed the most intelligent part of data accessor development. The rest of the work is definitely a monkey's job. Honestly, I got bored of being just a coding machine writing the same data access code over and over again, especially understanding that this process can be automated.

This article shows how to avoid the implementation step of data access development and how to reduce this routine process to the method declaration.

Abstract Classes

Unfortunately, mainstream .NET languages still do not have a compile-time transformation system like some functional or hybrid languages do. All we have today is pre-compile- and run-time code generation.

This article concentrates on run-time code generation and its support by Business Logic Toolkit for .NET.

Let us step back and bring the methods from the previous examples together in one class. Ideally, this data accessor class could look like the following:

using System;
using System.Collections.Generic;

public class PersonAccessor
{
    public List<Person> GetPersonListByName(
        string firstName, string lastName, int pageNumber, int pageSize);

    public Person GetPersonByID   (int id);
    public void   DeletePersonByID(int id);
}

The bad news about this sample is that we cannot use such syntax as the compiler expects the method’s body implementation.

The good news is we can use abstract classes and methods that give us quite similar, compilable source code.

using System;
using System.Collections.Generic;

public abstract class PersonAccessor
{
    public abstract List<Person> GetPersonListByName(
        string firstName, string lastName, int pageNumber, int pageSize);

    public abstract Person GetPersonByID   (int id);
    public abstract void   DeletePersonByID(int id);
}

This code is 100% valid and our next step is to make it workable.

Abstract DataAccessor

Business Logic Toolkit provides the DataAccessor class, which is used as a base class to develop data accessor classes. If we add DataAccessor to our previous example, it will look like the following:

using System;
using System.Collections.Generic;

using BLToolkit.DataAccess;

public abstract class PersonAccessor : DataAccessor<Person,PersonAccessor>
{
    public abstract List<Person> GetPersonListByName(
        string firstName, string lastName, int pageNumber, int pageSize);

    public abstract Person GetPersonByID   (int id);
    public abstract void   DeletePersonByID(int id);
}

That’s it! Now this class is complete and fully functional.

The code below shows how to use it:

using System;
using System.Collections.Generic;

namespace DataAccess
{
    class Program
    {
        static void Main(string[] args)
        {
            PersonAccessor pa = PersonAccessor.CreateInstance();

            List<Person> list = pa.GetPersonListByName("Crazy", "Frog", 0, 20);

            foreach (Person p in list)
                Console.Write("{0} {1}", p.FirstName, p.LastName);
        }
    }
}

The only magic here is the CreateInstance method. First of all, this method creates a new class inherited from the PersonAccessor class and then generates abstract method bodies depending on each method declaration. If we wrote those methods manually, we could get something like this:

using System;
using System.Collections.Generic;

using BLToolkit.Data;

namespace Example.BLToolkitExtension
{
    public sealed class PersonAccessor : Example.PersonAccessor
    {
        public override List<Person> GetPersonListByName(
            string firstName,
            string lastName,
            int    pageNumber,
            int    pageSize)
        {
            using (DbManager db = GetDbManager())
            {
                return db
                    .SetSpCommand("GetPersonListByName",
                        db.Parameter("@firstName",  firstName),
                        db.Parameter("@lastName",   lastName),
                        db.Parameter("@pageNumber", pageNumber),
                        db.Parameter("@pageSize",   pageSize))
                    .ExecuteList<Person>();
            }
        }

        public override Person GetPersonByID(int id)
        {
            using (DbManager db = GetDbManager())
            {
                return db
                    .SetSpCommand("GetPersonByID", db.Parameter("@id", id))
                    .ExecuteObject<Person>();
            }
        }

        public override void DeletePersonByID(int id)
        {
            using (DbManager db = GetDbManager())
            {
                db
                    .SetSpCommand("DeletePersonByID", db.Parameter("@id", id))
                    .ExecuteNonQuery();
            }
        }
    }
}

(The DbManager class is another BLToolkit class used for “low-level” database access).

Every part of the method declaration is important. Method's return value specifies one of the Execute methods in the following way:

Return Type	Execute Method
IDataReader interface	ExecuteReader
Subclass of DataSet	ExecuteDataSet
Subclass of DataTable	ExecuteDataTable
Class implementing the IList interface	ExecuteList or ExecuteScalarList
Class implementing the IDictionary interface	ExecuteDictionary or ExecuteScalarDictionary
void	ExecuteNonQuery
string, byte[] or value type	ExecuteScalar
In any other case	ExecuteObject

The method name explicitly defines the action name, which is converted to the stored procedure name.

The type, sequential order, and name of the method parameters are mapped to the command parameters. Exceptions from this rule are:

• A parameter of DbManager type. In this case generator uses provided DbManager to call the command.
• Parameters decorated with attributes: FormatAttribute, DestinationAttribute.

Generating Process Control

The PersonAccessor class above is a very simple example and, of course, it seems too ideal to be real. In real life, we need more flexibility and more control over the generated code. BLToolkit contains a bunch of attributes to control DataAccessor generation in addition to DataAccessor virtual members.

Method CreateDbManager

protected virtual DbManager CreateDbManager()
{
    return new DbManager();
}

By default, this method creates a new instance of DbManager that uses default database configuration. You can change this behavior by overriding this method. For example:

public abstract class OracleDataAccessor<T,A> : DataAccessor<T,A>
    where A : DataAccessor<T,A>
{
    protected override BLToolkit.Data.DbManager CreateDbManager()
    {
        return new DbManager("Oracle", "Production");
    }
} 

This code will use the Oracle data provider and Production configuration.

Method GetDefaultSpName

protected virtual string GetDefaultSpName(string typeName, string actionName)
{
    return typeName == null?
        actionName:
        string.Format("{0}_{1}", typeName, actionName);
}

As I mentioned, the method name explicitly defines the so-called action name. The final stored procedure name is created by the GetDefaultSpName method. The default implementation uses the following naming convention:

• If type name is provided, the method constructs the stored proc name by concatenating the type and action names. Thus, if the type name is "Person" and the action name is "GetAll", the resulting sproc name will be "Person_GetAll".
• If the type name is NOT provided, the stored procedure name will equal the action name.

You can easily change this behavior. For example, for the naming convention "p_Person_GetAll", the method implementation can be the following:

public abstract class MyBaseDataAccessor<T,A> : DataAccessor<T,A>
    where A : DataAccessor<T,A>
{
    protected override string GetDefaultSpName(string typeName, string actionName)
    {
        return string.Format("p_{0}_{1}", typeName, actionName);
    }
}

Method GetTableName

protected virtual string GetTableName(Type type)
{
    // ...
    return type.Name;
}

By default, the table name is the associated object type name (Person in our examples). There are two ways to associate an object type with an accessor. By providing generic parameter:

public abstract class PersonAccessor : DataAccessor<Person>
{
}

And by the ObjectType attribute:

[ObjectType(typeof(Person))]
public abstract class PersonAccessor : DataAccessor
{
}

If you want to have different table and type names in your application, you may override the GetTableName method:

public abstract class OracleDataAccessor<T,A> : DataAccessor<T,A>
    where A : DataAccessor<T,A>
{
    protected override string GetTableName(Type type)
    {
        return base.GetTableName(type).ToUpper();
    }
}

TableNameAttribute

Also, you can change the table name for a particular object type by decorating this object with the TableNameAttribute attribute:

[TableName("PERSON")]
public class Person
{
    public int    ID;
    public string FirstName;
    public string LastName;
}

ActionNameAttribute

This attribute allows changing the action name.

public abstract class PersonAccessor : DataAccessor<Person, PersonAccessor>
{
    [ActionName("GetByID")]
    protected abstract IDataReader GetByIDInternal(DbManager db, int id);

    public Person GetByID(int id)
    {
        using (DbManager   db = GetDbManager())
        using (IDataReader rd = GetByIDInternal(db, id))
        {
            Person p = new Person();

            // do something complicated.

            return p;
        }
    }
}

ActionSprocNameAttribute

This attribute associates the action name with a stored procedure name:

[ActionSprocName("Insert", "sp_Person_Insert")]
public abstract class PersonAccessor : DataAccessor<Person, PersonAccessor>
{
    public abstract void Insert(Person p);
}

This attribute can be useful when you need to reassign a stored procedure name for a method defined in your base class.

SprocNameAttribute

The regular way to assign different from default sproc name for a method is the SprocName attribute.

public abstract class PersonAccessor : DataAccessor<Person, PersonAccessor>
{
    [SprocName("sp_Person_Insert")]
    public abstract void Insert(Person p);
}

DestinationAttribute

By default, the DataAccessor generator uses method’s return value to determine which Execute method should be used to perform the current operation. The DestinationAttribute indicates that target object is a parameter decorated with this attribute:

public abstract class PersonAccessor : DataAccessor<Person, PersonAccessor>
{
    public abstract void GetAll([Destination] List<Person> list);
}

Direction Attributes

DataAccessor generator can map provided business object to stored procedure parameters. Direction attributes allow controlling this process more precisely.

public abstract class PersonAccessor : DataAccessor<Person, PersonAccessor>
{
    public abstract void Insert(
        [Direction.Output("ID"), Direction.Ignore("LastName")] Person person);
}

In addition, BLToolkit provides two more direction attributes: Direction.InputOutputAttribute and Direction.ReturnValueAttribute.

DiscoverParametersAttribute

Usually, BLToolkit expects method parameter names to match stored procedure parameter names. The sequential order of parameters is not important in this case. This attribute enforces BLToolkit to retrieve parameter information from the sproc and to assign method parameters in the order they go. Parameter names are ignored.

FormatAttribute

This attribute indicates that specified parameter should be used to construct the stored procedure name or SQL statement:

public abstract class PersonAccessor : DataAccessor<Person, PersonAccessor>
{
    [SqlQuery("SELECT {0} FROM {1} WHERE {2}")]
    public abstract List<string> GetStrings(
        [Format(0)] string fieldName,
        [Format(1)] string tableName,
        [Format(2)] string whereClause);
}

IndexAttribute

If you want your method to return a dictionary, you will have to specify fields to build the dictionary key. The Index attribute allows you to do that:

public abstract class PersonAccessor : DataAccessor<Person, PersonAccessor>
{
    [SqlQuery("SELECT * FROM Person")]
    [Index("ID")]
    public abstract Dictionary<int, Person>           SelectAll1();

    [SqlQuery("SELECT * FROM Person")]
    [Index("@PersonID", "LastName")]
    public abstract Dictionary<CompoundValue, Person> SelectAll2();
}

Note: if your key has more than one field, the type of this key should be CompoundValue.

If the field name starts from '@' symbol, BLToolkit reads the field value from data source, otherwise from an object property/field.

ParamNameAttribute

By default, the method parameter name should match the stored procedure parameter name. This attribute specifies the sproc parameter name explicitly.

public abstract class PersonAccessor : DataAccessor<Person, PersonAccessor>
{
    [ActionName("SelectByName")]
    public abstract Person AnyParamName(
        [ParamName("FirstName")] string name1,
        [ParamName("@LastName")] string name2);
}

ScalarFieldNameAttribute

If your method returns a dictionary of scalar values, you will have to specify the name or index of the field used to populate the scalar list. The Index attribute allows you to do that:

public abstract class PersonAccessor : DataAccessor<Person, PersonAccessor>
{
    [SqlQuery("SELECT * FROM Person")]
    [Index("@PersonID")]
    [ScalarFieldName("FirstName")]
    public abstract Dictionary<int, string>           SelectAll1();

    [SqlQuery("SELECT * FROM Person")]
    [Index("PersonID", "LastName")]
    [ScalarFieldName("FirstName")]
    public abstract Dictionary<CompoundValue, string> SelectAll2();
}

ScalarSourceAttribute

If a method returns a scalar value, this attribute can be used to specify how database returns this value. The ScalarSource attribute take a parameter of the ScalarSourceType type:

ScalarSourceType	Description
DataReader	Calls the DbManager.ExecuteReader method, and then calls IDataReader.GetValue method to read the value.
OutputParameter	Calls the DbManager.ExecuteNonQuery method, and then reads value from the IDbDataParameter.Value property.
ReturnValue	Calls the DbManager.ExecuteNonQuery method, and then reads return value from command parameter collection.
AffectedRows	Calls the DbManager.ExecuteNonQuery method, and then returns its return value.

SqlQueryAttribute

This attribute allows specifying SQL Statement.

public abstract class PersonAccessor : DataAccessor<Person, PersonAccessor>
{
    [SqlQuery("SELECT * FROM Person WHERE PersonID = @id")]
    public abstract Person GetByID(int @id);
}

Conclusion

I hope this brief tutorial demonstrates one of the simplest, quickest and most low-maintenance ways to develop your data access layer. In addition, you will get one more benefit, which is incredible object mapping performance. But that is a topic we will discuss later.

You can always find the latest version of BLToolkit source code on http://www.bltoolkit.net/.

Regards,
Igor.