Paschal points to a article by Philip Miseldine on how to use the Factory Pattern to achieve database independence in your .NET code.
The article starts off with an explanation on how the Factory pattern works. The fact that the author felt this needed explaining shows a difference between the .NET world (where many developers are still relatively new to patterns) and the Java world (where the vast majority know patterns, and many tend to go overboard with them). Unfortunately, his description and ultimate implementation is wrong, achieving the drawbacks of Design Patterns (added complexity) without any of their advantages.
A factory is supposed to abstract from the actual object-class being created, yet his factory takes a "type" parameter which has a one-to-one mapping to this class. This defeats the purpose of using a Factory in the first place. If a developer needs to switch from a SQLConnection to an ODBCConnection, he is still forced to go through the entire code-base and change the parameters of every call to MakeConnection(). How is this better that changing a constructor-call? It's not.
Instead of creating this factory-class and writing
IDbConnection conn = ConnectionFactory.MakeConnection(
(int)ConnectionFactory.connectionTypes.SQLServer, connectionString);
he may as well have skipped the whole ConnectionFactory class and have simply written
IDbConnection conn = new SQLConnection(connectionString);
Does this mean the connectionFactory idea is wrong? No it's not. I actually use a Factory-pattern for this myself. The difference is that the details of which IDbConnection class and connectionString to use should be contained in the connection-factory class. That way, a developer can simply write
IDbConnection conn = ConnectionFactory.MakeConnection();and the ConnectionFactory itself takes care of the details of which connection-class and connection-string to use. Ideally this logic would even be abstracted out of the ConnectionFactory code by using config-files.
Unfortunately, a factory and interface based approach to data-access is still not quite that simple in .NET. Yes, you can use IDbConnection.CreateCommand() to get an IDbCommand, but there's no way you can for instance create an IDbDataAdapter based on a generic IDbConnection. To get around this, you'll need to create another factory-method to abstract this as well. When you do this though, make sure you declare it to return a DbDataAdapter-class instead of a IDbDataAdapter-interface, as the interface unfortunately misses a lot of convenience methods implemented in the class and DbDataAdapter is the base-class for all DataAdapters that come with the framework anyway.
Actually the PetShop 3.0 http://msdn.microsoft.com/library/en-us/dnbda/html/petshop3x.asp implements the Factory Pattern to achieve at some degree database independence.
Posted by Alberto Borbolla at November 19, 2003 10:10 PM"The fact that the author felt this needed explaining shows a difference between the .NET world (where many developers are still relatively new to patterns) and the Java world (where the vast majority know patterns, and many tend to go overboard with them)."
You may be right about this but don't forget that most .net developers come from the Microsoft world of COM where Design Patterns are used extensively. The Factory Pattern is the very base of COM. If you have always used Visual Basic for implementing COM components you may not know this but I am sure all C++ COM authors have had their fun with Design Patterns.
:-)
Oh, how I wish my colleges where up to design patterns. I am still trying to persuade them that global variables are bad and functions should not be 1,000 lines long!
Posted by Martin at November 20, 2003 6:18 AMgood point. I created a object factory for data access as one of my first projects in C# that takes an enum type in it's create() method. The difference being that I return a concrete data object class rather than the individual data access classes.
abstract public class cDataObject
{
//maybe make this a static property?
private string _sConnString;
//need to be implemented by the concrete classes
abstract public IDbCommand getCommand();
abstract public IDbConnection getConnection();
abstract public IDbDataAdapter getAdapter();
{snip}
public class SQLServerDataObject : cDataObject
{
public SQLServerDataObject()
{
}
public override IDbConnection getConnection()
{
return new System.Data.SqlClient.SqlConnection();
}
{snip}
I came from VB development, spent a little time doing Java work, and had never used any patterns but I instantly recognized the need for this pattern in ADO.NET. It made me wonder why they didn't have it in the first place, JDBC is very database independant. I'm glad that "Whidbey" is going to have a factory namespace in it as well as the relational - logical object mapper.
P.S. Good job on the last 2 versions of SharpReader. I've watch it's memory useage on my system go from 50KB, to 39KB, now down to 35KB and I've added new feeds with each version.
There's another factory to worry about. One for Parameters used with the DataAdapters.
Won't generics coming in the next rev of C# vastly simplify this problem?
Posted by BillSaysThis at November 20, 2003 1:25 PMHans: Can't you use IDbDataAdapter.SelectCommand.CreateParameter() for that?
Bill: No
BillSaysThis:
No, but the new Common object namespaces (DbConnection, DbCommand, and the factory) coming in the next version of the CLR (Whidbey) will help. The new ObjectSpaces namespace will make OOP with a relational database a little less painful as well.
I just created an interface and objects to wrap the implementation specific CommandBuilders...
like so:
interface IDbCommandBuilder
and then classes for each provider:
public class CommandBuilderOleDb : myNamespace.IDbCommandBuilder, IDisposable
{
private OleDbCommandBuilder oleDbCommandBuilder;
public CommandBuilderOleDb()
{
oleDbCommandBuilder = new OleDbCommandBuilder();
}
....
public void DeriveParameters(System.Data.IDbCommand cmd)
{
OleDbCommandBuilder.DeriveParameters((OleDbCommand)cmd);
}
etc. etc.... be happy to pass along if you'd like...
Posted by chadb at November 20, 2003 10:53 PMThere is an open source project to provide a simple data access layer in .NET.
It is Thycotic.Data.
http://thycotic-data.sourceforge.net
It tackles the problem of needing an adapter by doing the DataTable creation itself using some code from Steve Smith. Since the only purpose of an adapter is to fill a DataTable, it isn't really needed if your data layer will do this for you.
Thycotic.Data also reimplements OleDb style ? parameters in your SQL since ADO.NET passes the SQL to particular parameter you otherwise need specific parameters based on your provider, for example:
SELECT * FROM a WHERE b=@b (Sql Server)
SELECT * FROM a WHERE b=:b (Oracle)
With Thycotic.Data you can always say:
SELECT * FROM a WHERE b=?
Ps. I am the maintainer of Thycotic.Data.
Posted by Jonathan Cogley at November 26, 2003 1:18 PMGood points.
I'd like to make a few points however, as author of the said article. SitePoint articles need to be written from the viewpoint of those who aren't going to be familiar with design patterns. I decided to explain what a factory pattern was (to the best of my understanding!) to introduce the concept. My understanding of the factory pattern is to define an interface for creating an object, but let the subclasses decide which class to instantiate. I assumed passing parameters would still satisfy this.
I do believe that the method I outlined is still better than a constructor call as the variable to yield the correct type can be set. I originally used this method in a program which allowed the end-user to select which type of database to connect to. Hence, I could simply pass the variable and connection string that held which database type to use with the makeConnection method, and work with the database independently of its type. I would be interested to know how to achieve this without parameters, as you highlighted "IDbConnection conn = ConnectionFactory.MakeConnection();".
Fair analysis though, and I thank you for highlighting them!
Posted by Philip Miseldine at December 2, 2003 2:34 PMPhilip: thank you for your comments.
You said "My understanding of the factory pattern is to define an interface for creating an object, but let the subclasses decide which class to instantiate" - this is correct, but this is not what you built. A factory-method like that is typically an abstract method in a base-class or interface with concrete implementations of the factory method in the sub-classes. Different implementations usually return different classes.
An example of a factory method that is appropriate to your article is IDbConnection.CreateCommand(). SqlConnection implements this method to return a SqlCommand instance while OdbcConnection implements it to return an OdbcCommand. The client is shielded from this though as they just call CreateCommand() on their IDbConnection object and get the appropriate IDbCommand class instance returned to them. Once a client has the correct IDbConnection object, he needs no further logic to decide what type of IDbCommand to create - the factory method will do this for him.
What you're trying to do does not call for the factory-method pattern though - you need a single method (no sub-class implementations) that will return the appropriate IDbConnection to the client while shielding him from having to know which IDbConnection-class he needs. Unfortunately your implementation does not shield the client from this at all as he now needs to pass a ConnectionType which maps 1-to-1 to the class that will be returned to him. If you call MakeConnection() many times across your code-base and you subsequently want to change from a SqlConnection to a OdbcConnection, you will still need to change the parameters to all these MakeConnection() calls, which kind of defeats the purpose of your ConnectionFactory class.
To prevent this, you would have to store these parameters (ConnectionType and connectionString) in a separate class, but what better class than ConnectionFactory itself? and in that case, why even have the client pass these parameters in the first place? The ConnectionFactory already knows their values.