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:
  1. 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:
  1. 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:
  1. public static string AdaptSqlStatement(string a_sqlStatement)
  2. {
  3. // it is assumed that source SQL statement uses named parameters(MS SQL Server
  4. //variant)
  5. if (s_defaultProviderType == ProviderType.USE_OLEDB_PROVIDER)
  6. {
  7. string l_result = a_sqlStatement;
  8. // first find all parameters
  9. Regex l_regex = new Regex(@"[@]\w+(?=\s|$|[,]|[)])");
  10. MatchCollection l_matches = l_regex.Matches(a_sqlStatement);
  11. foreach (Match l_match in l_matches)
  12. {
  13. string l_parameter = l_match.ToString();
  14. // make additional checks of the parameter(check for such things
  15. // as @@IDENTITY)
  16. if (l_result.IndexOf("@" + l_parameter) == -1)
  17. {
  18. l_result = l_result.Replace(l_parameter, "?");
  19. }
  20. }
  21. return l_result;
  22. }
  23. else
  24. {
  25. return a_sqlStatement;
  26. }
  27. }
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.