Introduction
In a previous tutorial, we created an abstract class for basic SCRUD database operations. Today we’re going to build on that to create a generic database abstraction layer (DAL). The aim of this is to allow you to connect to any database type without having to worry about changing your Data Layer classes.
We’re also going to be introducing 2 of the most common design patterns.
- Factory
- Singleton
First download the previous tutorial source code from here. We’ll be creating 2 new classes for this tutorial
DatabaseProvider.cs
This class will serve as our configuration class for the database connection. It will define the basic properties and methods that we will need to successfully initialize and load our chosen connection dll using Reflection
DatabaseFactory.cs
The factory class will read our configuration and based on that locate and load our connection.
Step 1 – DatabaseProvider.cs
The main interface that we care about when creating our DAL is:
When using reflection to load our database provider, this is the interfaces we will be looking for.
private static readonly Type CONNECTION_TYPE = typeof(IDbConnection);
The first method we are going to implement will tell us if the dll we have loaded actually implements the methods we need. If it doesn’t we can move on to the next dll. This method will be called from inside the factory on initialization.
public static bool IsDatabaseProvider(Assembly assembly)
{
bool returnValue = false;
try
{
foreach (var type in assembly.GetTypes())
{
returnValue = (type != CONNECTION_TYPE && CONNECTION_TYPE.IsAssignableFrom(type));
if (returnValue)
break;
}
}
catch (Exception)
{
//We can ignore this
}
return returnValue;
}
Here we’re using reflection to confirm the interfaces that a particular dll implements, if we find a match for our CONNECTION_TYPE and if it can be initialized we can use it. If the dll does not have the required interface, we’ll ignore it.
Now that we can identify our database provider, we can complete the rest of our wrapper class. First up is the constructor and setting up our properties.
public string Name { get; private set; }
public string FullName { get; private set; }
public Assembly Assembly { get; private set; }
public Type ConnectionType { get; private set; }
public DatabaseProvider(Assembly assembly)
{
this.Assembly = assembly;
this.Name = Assembly.ManifestModule.Name.Replace(".dll", "");
this.FullName = Assembly.FullName;
foreach (var type in assembly.GetTypes())
{
if (type != CONNECTION_TYPE && CONNECTION_TYPE.IsAssignableFrom(type))
this.ConnectionType = type;
}
}
Next we have to provide methods to actually initialize our connection
public IDbConnection CreateConnection()
{
var returnValue = Activator.CreateInstance(this.ConnectionType);
if (returnValue == null)
throw new Exception("Unable to initialize connection for " + this.Name);
return (IDbConnection)returnValue;
}
Activator.CreateInstance(type) is equivalent to doing Class instance = new Class();
Step 2: DatabaseFactory.cs
The purpose of the factory is to allow us to create and initialize database connections and the provide access to them in an easy, generic way. To do this we’ll need to keep track of each of the providers we have initialized and the easiest way is to keep a Dictionary of them.
public Dictionary Providers { get; private set; }We also want to prevent more than one instance of the factory, the easiest way to do this is to make the constructor private and to create a static instance of the DatabaseFactory class (This is the singleton pattern)
private static readonly DatabaseFactory _factory = new DatabaseFactory();
public static DatabaseFactory GetInstance()
{
return _factory;
}
private DatabaseFactory()
{
}
Now that we have that out of the way, we have to locate and initialize our connection objects. For this tutorial we’ll be using the MySQL .Net provider MySql.Data.dll, in our app.config we can add a connection string and specify its provider as MySql.Data. We’ll also be using the normal System.Data dll for SQL Server.
<connectionStrings>
<add name="MySql" providerName="MySql.Data" connectionString="Server=[Server Name];Database=[Database Name];Uid=[Username];Pwd=[Password];" />
<add name="SqlServer" providerName="System.Data" connectionString=" Server=[Server Name];Database=[Database Name];Uid=[Username];Pwd=[Password];" />
</connectionStrings>
First we have to do in the constructor is loop through our connections strings and try and load the providers. The MySql.Data.dll and it’s required files should be located in your bin folder for this
private DatabaseFactory()
{
this.Providers = new Dictionary();
string location = System.Reflection.Assembly.GetExecutingAssembly().Location;
string path = Path.GetDirectoryName(location);
List dlls = new List();
foreach (ConnectionStringSettings connection in ConfigurationManager.ConnectionStrings)
dlls.AddRange(Directory.GetFiles(path, connection.ProviderName + ".dll"));
string dotNetPath = Path.Combine(Environment.GetFolderPath(Environment.SpecialFolder.Windows), @"Microsoft.NET\Framework" +
(Environment.Is64BitProcess ? "64" : "") + @"\v" + Environment.Version.ToString(3));
dlls.AddRange(Directory.GetFiles(dotNetPath, connection.ProviderName + ".dll"));//load .net sql server
foreach (string dll in dlls)
{
var assembly = Assembly.LoadFile(dll);
if (DatabaseProvider.IsDatabaseProvider(assembly))
{
DatabaseProvider provider = new DatabaseProvider(assembly);
this.Providers.Add(provider.Name, provider);
}
}
}
We should now have a collection of database providers. A quick note here, if you want to use the .Net Framework System.Data provider for SQL Server, you’ll have to add the .Net install path to be searched.
string dotNetPath = Path.Combine(Environment.GetFolderPath(Environment.SpecialFolder.Windows), @"Microsoft.NET\Framework" + (Environment.Is64BitProcess ? "64" : "") + @"\v" + Environment.Version.ToString(3));
dlls.AddRange(Directory.GetFiles(dotNetPath, connection.ProviderName + ".dll"));//load .net sql server
The above code will add the correct System.Data.dll for your configured .Net version and CPU architecture.
Now all that’s left is to create our database connection method
- CreateConnection
///
/// Database connection factory singleton
///
public class DatabaseFactory
{
private const int COMMAND_TIMEOUT = 30000;
private static readonly DatabaseFactory _factory = new DatabaseFactory();
public Dictionary Providers { get; private set; }
public static DatabaseFactory GetInstance()
{
return _factory;
}
private DatabaseFactory()
{
this.Providers = new Dictionary();
string location = System.Reflection.Assembly.GetExecutingAssembly().Location;
string path = Path.GetDirectoryName(location);
List dlls = new List();
foreach (ConnectionStringSettings connection in ConfigurationManager.ConnectionStrings)
dlls.AddRange(Directory.GetFiles(path, connection.ProviderName + ".dll"));
foreach (string dll in dlls)
{
var assembly = Assembly.LoadFile(dll);
if (DatabaseProvider.IsDatabaseProvider(assembly))
{
DatabaseProvider provider = new DatabaseProvider(assembly);
this.Providers.Add(provider.Name, provider);
}
}
}
public IDbConnection CreateConnection(string providerName, string connectionString, out Exception exError)
{
}
}
Next up is our CreateConnection method. First we need to check to see if the factory knows about the requested provider, if it does we can use the provider class to instantiate our connection object. All we need to do is set the connection string.
public IDbConnection CreateConnection(string providerName, string connectionString, out Exception exError)
{
IDbConnection returnValue = null;
exError = null;
try
{
if (!this.Providers.Keys.Contains(providerName))
throw new Exception("Unknown provider " + providerName);
DatabaseProvider provider = this.Providers[providerName];
returnValue = provider.CreateConnection();
returnValue.ConnectionString = connectionString;
}
catch (Exception ex)
{
exError = ex;
}
return returnValue;
}
Step 3: Using it!
Now that we can create and initialize out connection, let’s start using it. I’ve updated the previous tutorial source code to use this method. Have a look and compare the code yourself.
Exception exError = null;
string connectionString = ConfigurationManager.ConnectionStrings["SqlServer"].ConnectionString;
string provider = ConfigurationManager.ConnectionStrings["SqlServer"].ProviderName;
using (IDbConnection connection = DatabaseFactory.GetInstance().CreateConnection(provider, connectionString, out exError))
{
User_DataMapper user_mapper = new User_DataMapper(connection);
List users = user_mapper.Select(out exError);
if (exError != null)
{
//do something with the users list
}
else
//Report and handle the exception
MessageBox.Show("Unable to get list of users, " + exError.Message);
}
In the previous version, the User_DataMapper used the SqlConnection class, now it uses IDbConnection
public User_DataMapper(IDbConnection connection) : base(connection) { }The Select method now looks like this
public override List<User> Select(out Exception exError)
{
List<User> returnValue = new List<User>();
exError = null;
try
{
if (this.Connection.State != ConnectionState.Open)
this.Connection.Open();
using (IDbCommand command = this.Connection.CreateCommand())
{
command.CommandText = "SELECT [UserID], [Username] FROM dbo.[User]";
command.CommandType = CommandType.Text;
using (IDataReader reader = command.ExecuteReader())
{
while (reader.Read())
returnValue.Add(new User() { UserID = reader.GetInt32(0), Username = reader.GetString(1) });
}
}
}
catch (InvalidOperationException invalid)
{
exError = invalid;
}
catch (Exception ex)
{
exError = ex;
}
return returnValue;
}
As you can see the creation of the command instance is different. If you wanted to simplify this, you could create your own command class that implements the IDbCommand interface. I’ll leave that for another day.
And there you have it, with only minor changes to you SQL statements you can support any .Net database provider, whether it be Oracle, MySQL or any other. If you do find yourself having to support multiple databases in your product I would strongly suggest moving your SQL out of code and into Stored Procedures or Functions. Not only will this mean you have to make no code changes, it will allow you to quickly deploy SQL fixes without having to release a compile and install
*A quick note
This project was created using Visual Studio 2012 and SQL Server Express
Visual Studio Express can be downloaded for free from http://www.microsoft.com/visualstudio/eng/downloads#d-2012-express and SQL Server Express can be downloaded for free from http://www.microsoft.com/en-us/sqlserver/editions/2012-editions/express.aspx
The sql create statement for dbo.[User] (SQL Server)
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[User]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[User](
[UserID] [int] IDENTITY(1,1) NOT NULL,
[Username] [nvarchar](100) NOT NULL,
CONSTRAINT [PK_User] PRIMARY KEY CLUSTERED ( [UserID] ASC ) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON ) ON [PRIMARY]
) ON [PRIMARY]
END
GO
