Common code for oledb and slqConnection

[Axcontrols]

Imagine I've got two separate functions like this:-

void do_oledb(string strNewValue)
{
   // Do something with a JET database
   OleDbConnection con = new OleDbConnection();
   OleDbCommand cmd = new OleDbCommand();
   con.ConnectionString = JetConStr; // The JET DB Connection string
   cmd.Connection = con;


   con.Open();
   cmd.CommandText = "UPDATE myTable SET myField = '" + strNewValue + "'";
   cmd.ExecuteNonQuery();
   con.Close();
}

void do_sql(string strNewValue)
{
   // Do something with an SQL Server database
   SqlConnection con = new SqlConnection();
   SqlCommand cmd = new SqlCommand();
   con.ConnectionString = SqlConStr; // The SQL Svr DB Connection string
   cmd.Connection = con;


   con.Open();
   cmd.CommandText = "UPDATE myTable SET myField = '" + strNewValue + "'";
   cmd.ExecuteNonQuery();
   con.CLose();
}

You can see that most of the code is identical.
What I'd like to do is have this in one single function. Some conditional code that sets up the connection and command objects etc. so that when I get to using the common code it doesn't care what type of connection we have. Of course in the real world we'd get differences in syntax of th commandtext and things like that but please humour me and come up with the smallest amount of code for a common function that could replace both of the above.

Just curious - humour me.

[IanS]

Well this appears to work

void do_any(string strNewValue)
{
   IDBConnection con;
   IDBCommand cmd;
   strCon = "";

   if(useOlDb == true)     // Some conditional stuff here
    {
        con = new OleDbConnection();
        cmd = new OleDbCommand();
        strCon = JetConStr;
    }
    else
    {
        con = new System.Data.SqlClient.SqlConnection();
        cmd = new System.Data.SqlClient.SqlCommand();
        strCon = SqlConStr;
    }
// But below here it's all common
   con.ConnectionString = strCon;
   cmd.Connection = con;

   con.Open();

   cmd.CommandText = "UPDATE myTable SET myField = '" + strNewValue + "'";
   cmd.ExecuteNonQuery();

   con.Close();
}

But it's just using a simple nonquery. Not sure if it would work with a datareader.

[IanS]

Although switching the above code to use

System.Data.Common.DbConnection con;
System.Data.Common.DbCommand cmd;

would allow the use of System.Data.Common.DbDataReader

which solves the dataReader problem (IDBxxxxxxxx doesn't have a datareader)

[wakawaka]

Look into the System.Data.Common namespace and the DbProviderFactory. These allow you to create db specific objects in a generic way. With this, you would use the Db[Object] classes to interact, eg: DbCommand, DbConnection, DbParameter, etc....