none
Is it possible to write "Generic" database commands? RRS feed

  • Question

  • I have an application that is designed to connect to several different database types ( sql server, oledb, odbc... )

     

    No matter what type of database I am connected to, there are many places where the function I want to perform is exactly the same,  the only difference being the database type.

     

    It seems silly to have to write a separate method for every database type. 

     

    Is the any way to construct a "generic" database method.  What I'm imagining would look something like this:

     

     

    public static void GetDistinctValues(string fieldName, string tableName, StringBuilder sb) {

    *** warning FANTASY EXAMPLE ALERT!!  THIS CODE WILL NOT COMPILE!!

    string sql = "Select Distinct " + fieldName + " from " + tableName;

    GENERIC_Command cmd = new GENERIC_Command(sql, MY_GENERIC_DbConnection);

    GENERIC_DbConnection.Cn.Open();

    try {

    GENERIC_DataReader reader = cmd.ExecuteReader();

    while (reader.Read()) {

    if (reader[0] != DBNull.Value) {

    // for the sake of simplifying this example,

    // we assume reader[0] is string field

    sb.AppendLine((string)reader[0]);

    }

    } finally {

    MY_GENERIC_DbConnection.Cn.Close();

    }

    }

     

     

    Can it be done?

    Thursday, February 14, 2008 5:31 PM

All replies

  •  

    Hi,

     

    Well, I wouldn't bother using generic objects but switching the data provider through the application configuration file. Look for classes available in the System.Data.Common (DbProviderFactory, DbProviderFactories etc.) namespace and System.Configuration.

     

    My prefrered way to access databases is by using Data Access Application Block that is shipped with Enterprise Library 3.1.

     

    Thursday, February 14, 2008 9:37 PM
  • I probably shouldn't have used the word "generic" - I meant it in its dictionary meaning, not as in "generic collections".

     

    The lack of response forces me to choose between 2 options:

     

    1) The idea is so wacky that no one will touch it with a 10 foot pole.

    or

    2) I am a mad genius, so far ahead of my time that no one can even understand my questions.

     

    I think the smart money is probably  on option #1.

     

    best wishes to all reader,

    Kirk

     

    Wednesday, February 20, 2008 6:17 PM
  • I believe Cristian_t pretty much answered your question. .NET 2.0 provides database factory that allows you to isolate your code and make it database independent. If .NET provider (does not matter which one) implemented as .NET 2.0
    compliant then it should work with this factory. If it is not enough then you need to create some interface or base
    class which will be implemented/inherited by all database specific class libraries, but would expose everything to outside caller (your application) as database-independent. Then you would need to create your own factory (it is one of OOP
    patterns) and load and instantiate appropriate database-specific class inside of the factory.

    Thursday, February 21, 2008 11:09 AM
    Moderator