 |
 |
 |
|
 |
|
 |
|
 |
|
 |
|
 |
 |
|
|
|
 |
 |
||
|
 |
|
This article looks at how to use Reflection in C# to load and use ADO.NET providers at run time.
Background
Late binding is something you may remember from ASP or Visual Basic where you would use CreateObject
specifying the type of object to create using the string ProgId, for example: CreateObject("ADODB.Recordset").
This would create the specific object and put it into a generic object type variable.
It was a useful way to avoid versioning incompatibilities in Visual Basic and also fit well with
the typeless environment of ASP.
How can objects be late bound in .NET?
The System.Activator class is available for creating COM instances and other types but is mainly
focused on COM types and Remoting. All of its methods either return a System.Runtime.Remoting.ObjectHandle
(which is used to wrap objects that require marshalling) or accept a Type as input which can be
tricky if the assembly for the type hasn't been loaded yet.
Don't despair ... all the functionality to load assemblies and types and invoke their methods and
properties are made available to you in the System.Reflection namespace.
A simple class, LateBinder, with methods to create an instance using Reflection.
using System.Reflection;
public class LateBinder
{
/// <exception cref="System.TypeLoadException">The type couldn't be
/// loaded.</exception>
/// <exception cref="System.Exception">The assembly couldn't be loaded
/// or other errors.</exception>
/// <exception cref="System.ArgumentNullException">fullyQualifiedTypeName
/// is a null reference.</exception>
public static object CreateInstance(string assemblyName,string fullyQualifiedTypeName)
{
Type type = null;
if (assemblyName != null)
{
// this method will also search the GAC and will use the latest
// version if multiple versions are found.
Assembly assembly = Assembly.LoadWithPartialName(assemblyName);
if (assembly != null)
{
type = assembly.GetType(fullyQualifiedTypeName,true,true);
}
else
{
throw new Exception("Failed to load assembly \"" + assemblyName + "\".");
}
}
else
{
type = Type.GetType(fullyQualifiedTypeName,true,true);
}
// invoke the type's constructor to create an instance
object instance = type.InvokeMember("",
BindingFlags.CreateInstance | BindingFlags.Public | BindingFlags.Instance,
null,
null,
new object[] { });
return instance;
}
public static object CreateInstance(string fullyQualifiedTypeName)
{
return CreateInstance(null,fullyQualifiedTypeName);
}
}
This class could then be used to create instances on the fly, for example a DataSet. Note that the System.Data assembly name must be specified since the DataSet is defined in the assembly System.Data.dll. This will load the System.Data.dll assembly if it has not yet been loaded.
object o = LateBinder.CreateInstance("System.Data","DataSet");
Console.WriteLine(o.ToString());
Now what?! How can we take advantage of Late Binding?
In true developer fashion, we have achieved something that is clever but what could it be
used for? The one thing that springs to mind is eliminating the need to compile
the data access layer (DAL) against all the ADO.NET managed providers. Hopefully you have
invested the time and effort into building a data access layer that all your applications
use which abstract away the specific ADO.NET provider. Many of the examples on the web
offer code which directly use the OleDbConnection, SqlConnection, OracleConnection, etc. This prevents
your code from easily using another provider in the future which may be necessary should your
database platform change or if you are developing a product that must support multiple
database platforms. The providers all implement common interfaces defined in System.Data
and in particular the System.Data.IDbConnection interface. It is good practice to program
against interfaces where possible since it gives greater flexibility to your code.
Your current code for obtaining a specific connection inside your DAL may look like this ...
private static IDbConnection GetConnection(DatabaseProviderType providerType)
{
switch (providerType)
{
case DatabaseProviderType.OleDb:
return new OleDbConnection();
break;
case DatabaseProviderType.SqlClient:
return new SqlConnection();
break;
case DatabaseProviderType.OracleClient:
return new OracleConnection();
break;
// TODO: implement future ADO.NET managed providers here
default:
throw new Exception("Unable to determine the appropriate provider type for "
+ providerType);
}
}
public enum DatabaseProviderType {
OleDb,
SqlClient,
OracleClient
}
The above code uses the OleDbConnection, SqlConnection and OracleConnection types directly meaning that your DAL assembly will not compile unless they are referenced and will not load unless these types are present. Therefore all your applications using the DAL assembly will need to have the corresponding .dlls for each ADO.NET managed provider they support whether it is used or not. You will also need to extend your switch statement and enum as new managed providers are released or required by your application, for example: Microsoft Exchange provider, ODBC provider, MySQL provider etc. This is clearly not desirable but how can it be avoided? The answer obviously lies with late binding ...
Revised GetConnection method using late binding.
private static IDbConnection GetConnection(string assemblyName,
string providerConnectionTypeName)
{
object o = LateBinder.CreateInstance(assemblyName,providerConnectionTypeName);
if (!(o is IDbConnection))
{
throw new Exception("\"" + providerConnectionTypeName
+ "\" doesn't implement IDbConnection.");
}
return (IDbConnection) o;
}
Voila!
Using late binding to create our connection allows us to specify the ADO.NET managed
provider to use at runtime and will even allow us to any current or future managed providers!
The use of the System.Data.IDbConnection allows us to directly call the methods supported by
this interface avoiding all the tedious reflection calls to InvokeMember we would otherwise
be forced to use.
Performance
While late binding may seem like a silver bullet, it should not be overused since it
tends to yield poor performance. This is due to the compiler being unable to make any
optimizations since it doesn't know the type of the object at compile time. You will
also lack the benefit of compile time checking when using late binding since the compiler
doesn't know the type and therefore cannot ensure that methods are being correctly
referenced with the types matching their signatures, etc.
In order to assess the performance impact of using late binding to obtain a connection,
the following test was performed using early and late binding.
- Create an IDbConnection using the appropriate method.
- Simple query to return a reader on a table containing 3535 records.
- Repeat query 100 times with 10 iterations of the test and take the average times.
- Perform test with OleDb and OracleClient managed providers.
- Analyze results.
Performance Test Code
class Class1
{
[STAThread]
static void Main(string[] args)
{
string sql = "SELECT * FROM YOUR_TABLE";
// OleDb
string connectionString = "Provider=MSDAORA.1;Data Source=xxx;User Id=xxx;PASSWORD=xxx";
// OracleClient
//string connectionString = "Data Source=xxx;User Id=xxx;PASSWORD=xxx";
{
DateTime start = DateTime.Now;
double rows = 0;
for (int n=0; n < 100; n++)
{
// late - OracleClient
//IDbConnection connection = GetConnection("System.Data.OracleClient",
// "System.Data.OracleClient.OracleConnection");
// early - OracleClient
//IDbConnection connection = GetConnection(DatabaseProviderType.OracleClient);
// late - OleDb
//IDbConnection connection = GetConnection("System.Data",
// "System.Data.OleDb.OleDbConnection");
// early - OleDb
IDbConnection connection = GetConnection(DatabaseProviderType.OleDb);
connection.ConnectionString = connectionString;
using (connection)
{
connection.Open();
IDbCommand command = connection.CreateCommand();
command.CommandType = CommandType.Text;
command.CommandText = sql;
IDataReader reader = command.ExecuteReader();
while (reader.Read())
{
rows++;
}
}
}
DateTime end = DateTime.Now;
Console.WriteLine("Rows: " + rows + " Millis:"
+ end.Subtract(start).Ticks/TimeSpan.TicksPerMillisecond);
}
// wait for input
Console.ReadLine();
}
private static IDbConnection GetConnection(DatabaseProviderType providerType)
{
switch (providerType)
{
case DatabaseProviderType.OleDb:
return new OleDbConnection();
break;
case DatabaseProviderType.SqlClient:
return new SqlConnection();
break;
case DatabaseProviderType.OracleClient:
return new OracleConnection();
break;
default:
throw new Exception("Unable to determine the appropriate provider type for "
+ providerType);
}
}
public enum DatabaseProviderType
{
OleDb,
SqlClient,
OracleClient
}
private static IDbConnection GetConnection(string assemblyName,
string providerConnectionTypeName)
{
object o = LateBinder.CreateInstance(assemblyName,providerConnectionTypeName);
if (!(o is IDbConnection))
{
throw new Exception("\"" + providerConnectionTypeName
+ "\" doesn't implement IDbConnection.");
}
return (IDbConnection) o;
}
}
Performance Test Results
Note that these times in milliseconds are the averages for performing 100 retrievals of 3535 records from the database.
Two things can be concluded from the test results:
- Late Binding is 6-10% slower in these tests than Early Binding.
- The OracleClient provider is faster when using Oracle than the OleDb provider. (unrelated but interesting nonetheless)
Conclusion
While Late Binding is slower, it is only by a very small margin which probably more than
offsets the benefits in the majority of cases. Please mail me
with your experiences, benchmarks and thoughts on Late Binding in .NET.
Happy Late Binding! :-)
Downloads and Further Reading
Download the Performance Test Code (.zip)
System.Reflection namespace on MSDN
Copyright © 2002 Thycotic Software Ltd.