I am working on a C# project that is pulling data from SQL Server
, ODBC data-sources
and Oracle Databases
at different times.
At this point, I have created 3 different classes - Once for each type of DB access. However, I am seeing that 95% of the code is identical between each of the libraries - The main difference being in creating the Connection
and Command
objects.
As a VERY simple example of what's in the classes, here could be an example:
public class Oracle
{
static DataTable exec_DT(string query, string conn_str)
{
DataTable retVal = new DataTable();
using (OracleConnection conn = new OracleConnection(conn_str))
using (OracleCommand cmd = new OracleCommand(query, conn))
{
conn.Open();
using (OracleDataReader rdr = cmd.ExecuteReader())
{
retVal.Load(rdr);
rdr.Close();
}
}
return retVal;
}
}
and
public class SQLServer
{
static DataTable exec_DT(string query, string conn_str)
{
DataTable retVal = new DataTable();
using (SqlConnection conn = new SqlConnection(conn_str))
using (SqlCommand cmd = new SqlCommand(query, conn))
{
conn.Open();
using (SqlDataReader rdr = cmd.ExecuteReader())
{
retVal.Load(rdr);
rdr.Close();
}
}
return retVal;
}
}
Basically, I need to get the same kind of return data frmo each of my databases and the methods to do so are near-identical, the difference being the Connection
and Command
-type objects.
Now, when I look at both connection objects, I see that they both inherit from DbConnection
:
public sealed class SqlConnection : DbConnection, ICloneable
public sealed class OracleConnection : DbConnection, ICloneable
But I still can't think of a good way to create a generic / abstract parent class that can stop the need for this kind of code duplication as the DBConnection / DBCommand classes are abstract and, when I try something along the following lines:
using (DbConnection conn = new DbConnection(conn_str))
using (DbCommand cmd = new DbCommand(query, conn))
I get errors such as Cannot create an instance of the abstract class or interface 'System.Data.Common.DbCommand'
I'm very much a newbie and any help / sample code / links to ways this has been done well in the past would really be greatly appreciated!
THANKS!!!