Introduction
This is the last article of this series. In the first article we've seen how to give descriptive information about a class using attributes. In the second article we've seen how to extract that information using the System.Reflection namespace. Now we'll create a DAL library that is capable of persisting any object that has a descriptive information about itself.
Designing the DAL library
During the creation of this DAL library I wanted to create it so it could be used either with a SQL Server data provider or an OleDb data provider. It could also be extended to other data providers. We can divide this library in the following parts:
Utility classes
class DALQueryBuilder
As mentioned in the first article this class helps in the construction of SQL statements to update an object in the database
class DALParameter
A generic parameter class to be used in stored procedures
class DALException
It's aSystem.Exception derived class. It's thrown whenever an exception occurs in our database operations. It provides more information about the error that happened.Attribute classes
All the classes that are derived from System.Attribute that were mention in the first article.
DAL itself
class DALEngine
The abstract base class that does database operations, making the database programming a lot easier. It has virtual and abstract methods were each data provider has a different implementation. More than methods to return DataSets, DataReaders from a database it has methods that handle objects marked with the DAL attributes.
class DALSqlEngine
A SQL Server implementation of DALEngine
class DALOleDbEngine
An OleDb implementation of DALEngine
A Closer Look at the DALEngine class
This is the declaration of the DALEngine class:
public abstract class DALEngine : IDisposable
{
//
// private data members
//
IDbConnection conn = null;
string connectionString = "";
ArrayList parameters = new ArrayList();
bool canClose = true;
// constructor
public DALEngine(string connectionString);
public bool CanClose;
public string ConnectionString;
protected IDbConnection Connection;
protected ArrayList Parameters;
public void Close();
public void Dispose();
//
// methods that must be override with a specific data provider
// implementation please see the implementation of DALSqlEngine
// or DALOleDbEngine
//
protected abstract IDbConnection GetConnection();
protected abstract IDbCommand CreateCommand(string spName);
public abstract void ExecSP_DataSet(string spName, DataSet dataSet,
string tableName);
public abstract void ExecQuery_DataSet(string query, DataSet dataSet,
string tableName);
//
// related to stored procedure parameters
//
public DALParameter GetParameter(string name);
void UpdateOutputParameters(IDbCommand cmd);
public void AddParameter(DALParameter param);
public void ClearParameters();
//
// for those that use stored procedures
//
public IDataReader ExecSP_DataReader(string spName);
public IDataReader ExecSP_DataReader(string spName,
CommandBehavior behavior);
public object ExecSP_Scalar(string spName);
public int ExecSP_NonQuery(string spName);
//
// methods for those that use plain SQL statements
//
public IDataReader ExecQuery_DataReader(string query,
CommandBehavior behavior);
public IDataReader ExecQuery_DataReader(string query);
public object ExecQuery_Scalar(string query);
public int ExecQuery_NonQuery(string query);
//
// Business objects methods
//
public static object CreateFromReader(IDataReader reader, Type objType);
public object RetrieveObject(object keyValue, Type objType);
public int RetrieveChildObjects(object foreignKeyValue, ArrayList objects,
Type childType);
void UpdateObjectSql(object o, DataTableAttribute dataTable);
void UpdateObjectStoredProcedure(object o, DataTableAttribute dataTable);
public void UpdateObject(object o);
public void UpdateObjects(IEnumerable enumObjects);
}
As you may be thinking we will only that a look at the last 6 methods. The other ones are easy to understand since there are a lot of similar implementations out there.
The CreateFromReader method returns an instance of the objType type. Since we use DataReaders to get data from the database, that's the method that's used to return business object instances. It's static since it doesn't need a database connection, only a DataReader that can be read.
The method RetrieveObject returns one instance of the objType type. This instance will have it's property values according to que unique table row that has the key value equals to the keyValue parameter. Under the hood this method creates a SELECT statement with a WHERE clause. The RetrieveChildObjects create object instances of type childType where the foreign key is equal to foreignKeyValue parameter value. Those instances created will be added to the objects parameter.
The methods UpdateObject and UpdateObjects make an update in the database. This update operation can happen using a stored procedure (in case the class has the DataTable attribute with an UpdateStoreProcedure property set) or a plain SQL statement. The private methods UpdateObjectSql and UpdateObjectStoredProcedure are in charge of doing this updates.
Benefits
First of all using the DAL itself will make your database programming a lot easier. Even if you don't intend to do database programming as I do, I strongly recommend using a DAL library that already have tedious and repetitive tasks implemented. You don't have to use this one, but at least use one.
But if you do use business objects to work with the database this library will save you a lot of time and effort. There's only a piece of source code that needs to be reviewed when a change in the database happens. And it can be done using a tool of your own, doing the opposite of the tool we created in the second part of this article.
To show you how easy it is to get/update objects from the database I included a sample application along with the full DAL library code. There's a code fragment of this sample application below so you can have a clue of it's strength.
For sure the DAL has a lot of bugs, and that's one of the reasons I posted this code here. Maybe you could have better solutions and code to accomplish the library goals. Please feel free to send me questions and contributions as I'm really interested in improving this library.
To develop the sample application I created my own DAL class deriving from DALSqlEngine, since I would be using SQL Server. That's the code of my DAL class:
public class DAL : DALSqlEngine
{
const string CONN_STRING = "server=localhost;uid=sa;pwd=;database=pubs";
public DAL() : base(CONN_STRING)
{
}
public ArrayList GetCustomerDependents(Customer customer)
{
ArrayList result = new ArrayList();
RetrieveChildObjects(customer.Id, result, typeof(CustomerDependent));
return result;
}
public void UpdateCustomerDependents(Customer customer)
{
UpdateObjects(customer.Dependents);
}
}
Simple, isn't it? Now the application code, it's just inserting/updating contact/customers and customer dependents.
public static void Main()
{
DAL dal = new DAL();
try
{
Contact contact = new Contact();
contact.Name = "Joao Cardoso";
contact.Age = 23;
contact.Address = "Av. Rio Branco, 202/121";
contact.Address2 = "Centro";
contact.PostalCode = "09029-901";
contact.City = "Sao Paulo";
contact.State = "SP";
contact.Country = "Brazil";
dal.UpdateObject(contact);
Console.WriteLine(contact);
Contact joaoCardoso = (Contact)dal.RetrieveObject(1, typeof(Contact));
joaoCardoso.Age++;
Console.WriteLine(joaoCardoso);
Console.WriteLine("");
Customer customer = new Customer();
customer.Name = "Paul Noyter";
customer.Age = 34;
customer.Address = "All St, 2202/2121";
customer.Address2 = "Downville";
customer.PostalCode = "90931";
customer.City = "Los Angeles";
customer.State = "CA";
customer.Country = "United States";
customer.TotalPurchased += 1900.87M;
customer.NumberOfPurchases++;
dal.UpdateObject(customer);
Customer paul = (Customer)dal.RetrieveObject(1, typeof(Customer));
Console.WriteLine(paul);
paul.TotalPurchased += 100M;
paul.NumberOfPurchases++;
dal.UpdateObject(paul);
if (paul.Dependents.Count == 0)
{
CustomerDependent dependent = paul.NewDependent();
dependent.Name = "Marie Noyter";
dependent.Age = 31;
paul.Dependents.Add(dependent);
dependent = paul.NewDependent();
dependent.Name = "Mark Noyter";
dependent.Age = 10;
paul.Dependents.Add(dependent);
dependent = paul.NewDependent();
dependent.Name = "Claudia Snorg";
dependent.Age = 32;
dependent.Relationship = CustomerRelationship.Friend;
paul.Dependents.Add(dependent);
dal.UpdateCustomerDependents(paul);
}
else
{
Console.WriteLine("Dependents of {0}", paul.Name);
foreach(CustomerDependent dependent in paul.Dependents)
{
Console.WriteLine("<Dependent>{0} - {1} [{2}]", dependent.Id,
dependent.Name, dependent.Relationship);
dependent.Relationship = CustomerRelationship.Family;
}
dal.UpdateCustomerDependents(paul);
}
}
finally
{
dal.Dispose();
}
}
Limitations
Of course it has limitations. Some can be solved by extending the DAL or the attributes. If you don't use an auto number key column the only way you can update your object is using stored procedures. The problem is that DAL can't figure out if an object should be inserted or updated in the database. You could extend the DAL so that it checks for a known attribute or interface that provides the information needed; Currently it doesn't support tables with more than one key column; Performance isn't fast as pushing SqlParameters down the SqlCommand, but you will have more time to do your business classes instead of database programming; It doesn't enable the use of stored procedures to retrieve objects from the database. But hey, it's easy to be done; There must be others, but those are the one I remember by the time I wrote this.
Conclusion
There a lot of ways to do database programming in the .NET world. Visual Studio .NET enables you to use Typed DataSet, which saves you a lot of time since it generates code. There's nothing wrong with DataSets, but I prefer using business objects. When I started developing such classes I started to notice how long it took me to write each class. With the library introduced here you could see how productive it can be to develop database applications using business objects.
