Click here to Skip to main content
13,596,324 members
Click here to Skip to main content
Add your own
alternative version


57 bookmarked
Posted 5 Jan 2005

Database Independent Access

, 5 Jan 2005
Rate this:
Please Sign up or sign in to vote.
Use different database providers without tying implementation to one.


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.


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");


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);
  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));
catch (Exception exception)

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;");

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;", 

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;", 

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.


This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here


About the Author

Jaryl Sim
Singapore Singapore
Jaryl Sim is a student at Singapore Management University with a fervent passion for all things programming.

You may also be interested in...

Comments and Discussions

Questioncan I use your way to connect to ACCESS database? Pin
Southmountain15-Mar-17 15:25
memberSouthmountain15-Mar-17 15:25 
GeneralMy vote of 4 Pin
Armando de la Torre29-Jun-11 14:21
memberArmando de la Torre29-Jun-11 14:21 
GeneralCool Code ! thx Pin
downmoon200826-Aug-08 20:19
memberdownmoon200826-Aug-08 20:19 
GeneralRe: Cool Code ! thx Pin
Jaryl Sim3-Nov-08 16:36
memberJaryl Sim3-Nov-08 16:36 
GeneralSqlcommand parameters order Pin
Nore17-Aug-07 12:27
memberNore17-Aug-07 12:27 
Hello, i'm currently testing your code in a c#-Access application.
I never used Sqlcommand parameters but it seems that there is a problem in your code or the sqlcommand class

First, i don't understand why in your code you do not need to put @ when using Add()

cmd.Add("param", foo)
instead of
sqlcmd.Parameters.AddWithValue("@param", foo)

Second, it seems addition order is important (same order as parameter apparition) and when you copy the internal hashtable to oledbparamtercollection, the order is broken

DataProvider.Command newProperty <br />
  = dp.CreateCommand(<br />
    @"INSERT INTO ItemProperties <br />
      (fk_iItemLabelId, fk_iItemTypeId, fk_iItemPropertyFieldId, oItemPropertyValue) <br />
      VALUES(@a_label, @b_type, @c_prop, @d_val);<br />
      ");<br />
            <br />
newProperty.AddParameter("a_label", ik.LabelId);<br />
newProperty.AddParameter("b_type", ik.TypeId);<br />
newProperty.AddParameter("c_prop", property);<br />
newProperty.AddParameter("d_val", value);<br />

in my example, values should be (11,25,1,"Movies/22")
But in the database i get a record with values (1,25,11,"Movies/22")

In the foreach(key of hashtable), keys are sorted like that
prop,type,label,val (not abnormal if sorted by hash)
and inserted in oledbparameterCollection as is...

I could use a simple array with oleDbParameter as value to replace the hash table but i'd like to have your opinion about this "bug"

AnswerRe: Sqlcommand parameters order Pin
Jaryl Sim18-Aug-07 6:25
memberJaryl Sim18-Aug-07 6:25 
GeneralI'll take Simplicity Versus Complex Pin
vextant1-May-05 6:58
membervextant1-May-05 6:58 
GeneralRe: I'll take Simplicity Versus Complex Pin
Jaryl Sim1-May-05 20:52
memberJaryl Sim1-May-05 20:52 
GeneralADP Pin
Fábio Batista15-Jan-05 13:27
memberFábio Batista15-Jan-05 13:27 
GeneralDetermining type... Pin
Arjan Einbu6-Jan-05 11:19
memberArjan Einbu6-Jan-05 11:19 
GeneralDAAB 3 (Data Access Application Block ) Pin
Yitzhak Gootvilig6-Jan-05 4:16
memberYitzhak Gootvilig6-Jan-05 4:16 
GeneralRe: DAAB 3 (Data Access Application Block ) Pin
Jaryl Sim6-Jan-05 4:42
memberJaryl Sim6-Jan-05 4:42 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

Permalink | Advertise | Privacy | Cookies | Terms of Use | Mobile
Web01 | 2.8.180621.3 | Last Updated 5 Jan 2005
Article Copyright 2005 by Jaryl Sim
Everything else Copyright © CodeProject, 1999-2018
Layout: fixed | fluid