Generic Data Access Component using Factory Pattern

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 the labeling parameters in the SQL statements. 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 the OLE DB provider supports the "?" placeholder only. That means that even if someone will create an IDbCommand interface using some factory object, he still will have to take into account this difference in the 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 like 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 an adapter function as part of the factory class, it is possible to write generic SQL statements using parameters in a data provider-independent way. The only constraint is that using this approach it is not possible to use parameters 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 the full code.

1 Comment

Join the conversation! Your thoughts help the community grow.

Tuhin Paul2y ago
I recently came across your article on "A Generic Data Access Component using Factory Pattern," and I wanted to express my sincere appreciation for the insightful content you've provided. Your approach to creating ADO.Net data provider-independent applications using the Factory Pattern is both clever and practical. I found your solution to the parameter labeling differences across ADO.Net providers particularly ingenious. The adaptation function you introduced not only addresses a common challenge but also demonstrates a pragmatic way to maintain flexibility in data provider choices.