From my point of view, the article A Generic Data Access Component using Factory Pattern provides a very good way of creating ADO.Net data provider independent applications, but there is one problem with it. Different ADO.Net data providers use different approaches in labeling of parameters in SQL statement. SQL Server provider supports named parameters only, so in order to use parameter someone should write something like this:
"SELECT * FROM Customers WHERE CustomerID = @CustomerID", where
@CustomerID will be a parameter name.
The same SQL statement, written for OLE DB data provider, would look like this:
"SELECT * FROM Customers WHERE CustomerID = ?", just because OLE DB provider supports the "?" placeholder only. That means that even if someone will create IDbCommand interface using some factory object, he still will have to take into account this difference in labeling of parameters. To avoid this problem it is possible to use the following trick - always use SQL Server provider parameters labeling convention (like @CustomerID) and at the moment of creating an appropriate IDbCommand interface in factory object just replace this named parameter by a "?" placeholder in the case if OLE DB data provider should be used, using for example class, implementing regular expressions. The sample code could look as the following:
public static string AdaptSqlStatement(string a_sqlStatement)
{
// it is assumed that source SQL statement uses named
parameters (MS SQL Server
//variant)
if(s_defaultProviderType == ProviderType.USE_OLEDB_PROVIDER)
{
string l_result = a_sqlStatement;
// first find all parameters
Regex l_regex = new Regex(@"[@]\w+(?=\s|$|[,]|[)])");
MatchCollection l_matches =l_regex.Matches(a_sqlStatement);
foreach(Match l_match in l_matches)
{
string l_parameter = l_match.ToString();
// make additional checks of the
parameter (check for such things
// as @@IDENTITY)
if(l_result.IndexOf("@" + l_parameter)
== -1)
{
l_result =l_result.Replace(l_parameter, "?");
}
}
return l_result;
}
else
{
return a_sqlStatement;
}
}
As a result, having such adapter function as part of factory class, it is possible to write generic SQL statements with using of parameters in a data provider independent way. The only constraint is that using this approach it is not possible to use parameter with the same name several times, so all parameter names should be unique.
This is maybe not the most elegant solution (it would be better to have both the named parameters and placeholder for parameters support, built-in in all data providers), but this approach also works fine.
See the attached source code for full code.