Database Independent Access

• Download source files - 2.41 Kb

Introduction

If you ever needed to port over a project using one database to another, you'd know what a pain that could be. By hiding all the implementation details inside the DataProvider, we are able to achieve implementation independence by specifying what type of database to be used.

Features

DataProvider currently supports Ole DB and SQL Server providers, but you can easily customise it to support your provider if you wish. You can execute SQL statements against the database with SQL parameters to guard against SQL injection. DataProvider is designed with connection pooling considerations in mind although I have yet to test it in a production environment. Using Reflection, there's no more need to write tedious code to populate a class object. Lastly, DataProvider supports database transactions for non-query SQL.

Getting Started

Copy over DataProvider.cs into your project, renaming the namespace if necessary for your project purposes. Using DataProvider is simple, declare it like this:

DataProvider dp = 
    new DataProvider(ConnectionString, DataProvider.DBType.OleDb);

If you want to use the SQL provider, change OleDb to SqlServer. Change ConnectionString to the database you are using. Now let's say we want to return the number of rows in the entire Test table:

DataProvider dp = 
    new DataProvider(ConnectionString, DataProvider.DBType.OleDb);
DataSet = dp.ExecuteScalar("SELECT COUNT(*) FROM Test;");

Pretty simple huh? It gets better. Using parameterised SQL is important in guarding against SQL injection. Furthermore, building strings makes for unsightly code. Here's another snippet that updates the database:

DataProvider dp = 
    new DataProvider(ConnectionString, DataProvider.DBType.OleDb);

DataProvider.Command cmd = 
    dp.CreateCommand("UPDATE Test SET TestString=@TestString;");
cmd.AddParameter("@TestString", "Test123");

dp.ExecuteNonQuery(cmd);

I've avoided allowing AddParameter() to specify the data type. While this may be useful, it made it exponentially harder to cater to the different data types supported by different providers. All parameters are added to the command as objects.

Retrieving DataReaders and DataSets

So what if you require the power offered by DataReaders and DataSets? DataProvider provides such support as easily as the codes above.

DataProvider dp = 
    new DataProvider(ConnectionString, DataProvider.DBType.OleDb);
try
{
  using (IDataReader reader = dp.ExecuteReader("SELECT * FROM Test;"))
  {
        while (reader.Read())
        {
          for (int i=0; i<reader.FieldCount; i++)
          {
            Response.Write(reader.GetName(i) + ": " + reader.GetValue(i));
          }
        }
        reader.Close();
  }
} 
catch (Exception exception)
{
          System.Diagnostics.Debug.WriteLine(exception.Message);
}

I've used the using keyword here to ensure the destruction of the IDataReader object, which in turn ensures the closing of the database connection, freeing up the server's resources. IDataReader objects can be used just like you've always used them so you should already be familiar with that. Getting and using DataGrids are even easier:

DataProvider dp = 
    new DataProvider(ConnectionString, DataProvider.DBType.OleDb);
DataSet = dp.FillDataSet("SELECT * FROM Test;");

Do note that all the functions we've used so far are overloaded to take strings and Command objects so you can easily switch between plain SQL strings or paramterised SQL.

Using Transactions

Using transactions have never been easier without the use of DataProvider. Simply create an array of command objects and pass it into ExecuteTransaction(). Again, you have a choice to use plain SQL or parameterised SQL.

DataProvider dp = 
    new DataProvider(ConnectionString, DataProvider.DBType.OleDb);
DataProvider.Command [] commands = new DataProvider.Command[2];

commands[0] = 
    dp.CreateCommand("INSERT INTO Test (TestNumber, TestString, TestDate) 
            VALUES (73, @TestString, @TestDate);");
commands[0].AddParameter("@TestString", "Testing");
commands[0].AddParameter("@TestDate", DateTime.Now);

commands[1] = dp.CreateCommand("UPDATE Test SET TestNumber=44 WHERE ID=2;");
dp.ExecuteTransaction(commands);

Using Reflection

If you don't already know, Reflection allows us to access and modify an object's class information programmatically. Hence, we can use it to populate an object from a database table.

DataProvider dp = 
    new DataProvider(ConnectionString, DataProvider.DBType.OleDb);

TestObject obj = new TestObject();
obj = (TestObjec) dp.ReflectObject("SELECT * FROM Test WHERE TestID=37;", 
                                                          obj.GetType());

Here, TestObject is a user-defined class. Make sure that the class member variable names match the field names in the database. Fields that do not match are ignored. I instantiate a new TestObject here because I require it to get its Type. However, this is unnecessary because ReflectObject() will return a new instance anyway.

I've yet to find out a way to determine a Type without going through the trouble of instantiating it (I assume that this is required for runtime identification purposes) or specifying assembly names.

You can also use DataProvider to reflect multiple instances of an object and return it as an ICollection.

DataProvider dp = 
    new DataProvider(ConnectionString, DataProvider.DBType.OleDb);
TestObject obj = new TestObject();
List = (ArrayList) dp.ReflectCollection("SELECT * FROM Test;", 
                                                obj.GetType());

Final Notes

Even if you do not require database independent access, DataProvider is easy to use. If you have any queries or suggestions, please email me.