12/16/2002
The Dream
The dream of transparent data access is to not have to care about where the data is coming from. You have more important things to worry about, like providing a nice user interface and getting the business logic right on time. Ideally, it should not matter if the data is coming from SQL Server, Oracle, Outlook, or anywhere else useful data may be stored. It should not matter if you choose to develop against Access at home, SQL Server in development, and use Oracle in production. In a perfect world, we shouldn't have to rewrite our code in order to use a more efficient data access provider, but we do have to work for this dream.
The Problem
Unfortunately, data sources are different and need to be treated differently. What works well for one data source may not necessarily work well for another, and coding to the lowest common denominator in database functionality does not allow us to take advantage of the strengths of any one platform. This causes our code to be less efficient than it needs to be.
In DotNet, we have SqlClient, OracleClient, and ODP.NET all providing database-specific, optimized classes that try to leverage the strengths of the respective databases and OleDb providing access to everything else through a database-neutral, non-optimized set of classes.
This allows us to have efficient optimized data access if we know that we will be using SQLServer or Oracle all the time, but switching data providers requires changing the data type for every Connection object, every Command object, every Data Reader object, every DataAdapter object, etc. If we are not using one of these two platforms, or not committed to using the same one in all settings, we would seem to be doomed to inefficient, non-optimized code. Fortunately, DotNet provides a solution to this problem.
The Solution
If you are not familiar with the object-oriented concept of polymorphism, this solution will endear the concept to you. Polymorphism simply refers to an object behaving differently in different circumstances. If we write our code in terms of a generic "class," and have this class behave like an SQLClient when connecting to an SQLServer Database, an OracleClient when connecting to an Oracle Database, and an OleDb class otherwise, and we won't have to get bogged down in the specifics of which provider we are using.
This generic class that we will be using is not really a class in the traditional sense of the word. We will be using the interfaces that all of the data providers implement. We will not delve much into the details of interfaces. For our purposes, think of an interface as a contract between the class and users of that class. The interface defines a set of methods and properties that any class implementing the interface will include. The interface does not implement any of these, so we cannot instantiate an instance of an interface. Instead we typecast an instance of a class that implements the interface to the interface. When we call one of the methods defined in the interface, the original class' implementation is being called without us having to keep track of the type of the original class.
The interfaces we will be concerned with here include IDbConnection, IDbCommand, IDataReader, and IDbDataAdapter. For a complete listing of the mehods and properties for these interfaces, refer to the MSDN documentation: IDbConnection, IDbCommand, IDataReader, and IDbDataAdapter.
Because all of the data providers implement these interfaces, we know that they will all include database-specific versions of the methods we will need to access our data.
The Code
This code is not intended as a guide for best coding practices. Many of the details needed in an enterprise solution are left out so that we can focus on the details of using interfaces to interact with ADO.NET.
We will start by defining a function that will read various settings from the web.config file to specify such things as the ConnectionString
, the Data Type
for the Connection
object, and the Assembly
containing this data type. To pull settings from the config file, we must include the System.Configuration
namespace. We will also be using some of the features of Reflection to dynamically build the base type that is implementing the interface. To use Reflection, we must include the System.Reflection
namespace.
public static IDbConnection GetConnection (string dbType)
{
The dbType
parameter will be concatenated with the Configuration setting name to get the settings specific to the database type that we are using. In this example, we will be looking for SQLAssembly or OracleAssembly, etc.:
Type connectionType = null;
IDbConnection newConnection = null;
// Get the assembly that owns the process
Assembly runningAssembly = Assembly.GetExecutingAssembly();
// Get all the assemblies that are referenced
AssemblyName [] referencedAssemblies =
runningAssembly.GetReferencedAssemblies();
// Find the current type of database you need
string strTargetAssembly =
ConfigurationSettings.AppSettings[dbType + "Assembly"];
We will use the currently-executing assembly to get a list of all of the assemblies referenced by the running app. We will then loop through this array of AssemblyName
s to find the assembly where the config file promised the classes for our database type could be found.
// Search through all referenced assemblies
foreach (System.Reflection.AssemblyName
currentAssemblyName in referencedAssemblies)
{
When we find the referenced Assembly
, we will load it giving us access to the non-static methods of the Assembly
class. We will use this Assembly
instance to get an instance of the Type
object corresponding to the data type for the Connection
object.
// Open the assembly is the one we're looking for
if (currentAssemblyName.Name == strTargetAssembly)
{
Assembly referencedAssembly = Assembly.
Load(currentAssemblyName.FullName);
connectionType = referencedAssembly.
GetType(ConfigurationSettings.
AppSettings[dbType + "Connection"]);
// Stop searching through the assemblies
break;
}
}
If we were able to find the referenced assembly and get an instance of the Type
for the data type referenced for the connection object, we will create an instance of this data type and pass that back as the IDbConnection
object. The Activator
object allows to instantiate instances of an class.
if (connectionType != null)
{
// Create a connection of the type
newConnection = (IDbConnection)Activator.
CreateInstance (connectionType);
// Retrieve the Connection String
newConnection.ConnectionString = ConfigurationSettings.
AppSettings[dbType + "ConnectionString"];
}
// Return the new connection
return newConnection;
}
For our purposes, the relevant sections from the config file include:
<appSettings>
<add key="SQLAssembly"
value= "System.Data"
/>
<add key= "SQLConnection"
value= "System.Data.SqlClient.SqlConnection"
/>
<add key= "SQLConnectionString"
value="User ID=sa;
Password=;
Database=Northwind;
Server=localhost;"
/>
<add key="OracleAssembly"
value="System.Data.OracleClient"
/>
<add key="OracleConnection"
value="System.Data.OracleClient.OracleConnection"
/>
<add key="OracleConnectionString"
value="User ID=scott;Password=tiger;Data Source=rddvl"
/>
</appSettings>
We will also define a method that will use our GetConnection
method to return an IDbConnection
for the database type specified.
static IDataReader DataReader(string dbType, string query)
{
// Get a connection Object
IDbConnection conn = GetConnection(dbType);
// Create a Command Object
IDbCommand cmd = conn.CreateCommand();
// Set the Query
cmd.CommandText = Queryquery;
// Open the connection
cmd.Open();
// Execute the Command to create a DataReader
IDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
// Return the DataReader
return rdr;
}
Once the interfaces are connected to instances of the appropriate objects, we can use the methods in the interface without regard for which data type they point to, and rest assured that the appropriate optimized function calls will be made.
To demonstrate using this function, we will define a Web form that includes two data grids side by side. We will display data from Oracle in the first and data from SQLServer in the second:
<body>
<form id="TransparentADOAccess" method="post" runat="server">
<table border="1">
<tr>
<td><asp:DataGrid ID="dgOracle"
Runat="server"></asp:DataGrid></td>
<td><asp:DataGrid ID="dgSQL"
Runat="server"></asp:DataGrid></td>
</tr>
</table>
</form>
</body>
In the Page_Load
for this page, we will call the method we defined earlier twice, to populate the two datagrids with the names of the tables from both database platforms' demonstation databases.
private void Page_Load(object sender, System.EventArgs e)
{
// Specify the Query
string query = @"SELECT table_name
FROM all_Tables
WHERE owner = 'SCOTT' and rownum < 11";
// Create the DataReader
IDataReader oraDR = DataReader ("Oracle", query);
// Setup DataBinding
dgOracle.DataSource = oraDR;
dgOracle.DataBind();
oraDR.Close();
// Get another
query = @"SELECT top 10 table_name
FROM INFORMATION_SCHEMA.TABLES";
IDataReader sqlDR = DataReader ("SQL", query);
// Bind again
dgSQL.DataSource = sqlDR;
dgSQL.DataBind();
sqlDR.Close();
}
The output produced should look similar to this:
Another Example
Here we will use the same .aspx page defined earlier, and the same GetConnection
method, but we will populate the datagrids using DataAdapters
instead of DataReaders
.
To use DataAdapters
, we will need to define a new function, GetDataAdapter
, similiar to the GetConnection
function. We will also extend the web.config file to include the details about the data type for the DataAdapters
.
public static IDbDataAdapter GetDataAdapter(string dbType)
{
Type adapterType = null;
IDbDataAdapter newAdapter = null;
// Like before, get the main assembly
Assembly runningAssembly = Assembly.GetExecutingAssembly();
// Find the referenced assemblies
AssemblyName [] referencedAssemblies =
runningAssembly.GetReferencedAssemblies();
// Get the Configuration setting for the specified
// Assembly
string strTargetAssembly = ConfigurationSettings.
AppSettings[dbType + "assembly"];
// Find the assembly
foreach (System.Reflection.AssemblyName
currentAssemblyName in referencedAssemblies)
{
// If we found the right assembly...
if (currentAssemblyName.Name == strTargetAssembly)
{
// Load the Assembly and the configuration
// information
Assembly newAssembly = Assembly.
Load(currentAssemblyName.FullName);
adapterType = newAssembly.
GetType(ConfigurationSettings.
AppSettings[dbType + "DataAdapter"]);
// Stop searching if we get this far
break;
}
}
// If we found an adapter type,
if (adapterType != null)
{
// Create a new instance of the adapter
newAdapter = (IDbDataAdapter)Activator.
CreateInstance (adapterType);
}
// return it
return newAdapter;
}
The following sections need to be added to the config file:
<add key="SQLDataAdapter"
value = "System.Data.SqlClient.SqlDataAdapter"/>
<add key="OracleDataAdapter"
value = "System.Data.OracleClient.OracleDataAdapter"/>
We will also define a method that will use the GetConnection
and GetDataAdapter
methods to return an IDbDataAdapter
ready for use.
static IDbDataAdapter DataAdapter(string dbType, string query)
{
// Get a connection for the type we want
IDbConnection conn = GetConnection(dbType);
// Get a new DataAdapter for the type we want
IDbDataAdapter dataAdapter = GetDataAdapter(dbType);
// Create a command from the connection
dataAdapter.SelectCommand = conn.CreateCommand();
// Specify the query
dataAdapter.SelectCommand = query;
// Return the new adapter
return dataAdapter;
}
In the Page_Load
, we can now use our new functions and bind the DataSets
filled from the DataAdapters
to the DataGrids
.
private void Page_Load(object sender, System.EventArgs e)
{
// Create the new DataAdapter
IDbDataAdapter oraDA = DataAdapter ("Oracle",
"select table_name from all_Tables where rownum < 11");
// Create a new DataSet
DataSet dsOra = new DataSet();
// Tell the adapter to fill it
oraDA.Fill (dsOra);
// Do Data Binding
dgOracle.DataSource = dsOra.Tables[0];
dgOracle.DataBind();
// Get another adapter
IDbDataAdapter sqlDA = DataAdapter ("SQL",
"SELECT top 10 table_name FROM INFORMATION_SCHEMA.TABLES");
// Create a new DataSet
DataSet dsSQL = new DataSet();
// Have the DataAdapter fill it
sqlDA.Fill (dsSQL);
// Do more binding
dgSQL.DataSource = dsSQL.Tables[0];
dgSQL.DataBind();
}
Conclusion
Nothing is ever constant. The database used today may not be the database used in the future. The best data provider available for your database of choice today may not be the best data provider to use in the future. This article provides some guidance on how to protect your code from such changes. This simple method can also be expanded to serve as a common data access layer, helping to ensure consistent connection strings, which will help in connection pooling and management.
Nick Harrison UNIX-programmer-turned-.NET-advocate currently working in Charlotte, North Carolina using .NET to solve interesting problems in the mortgage industry.