The built in factory assumes a common (standard) syntax among the
different backend datastores.
So if all of your backend datastores can handle a query like:
Select
CustomerID,CompanyName,ContactName,ContactTitle,Address,City,Region,PostalCode,Country,Phone,Fax
from Customers
Then, yes, the built in factory will be sufficient.
Example:
System.Configuration.AppSettingsReader appReader = new
System.Configuration.AppSettingsReader();
string provider = appReader.GetValue("provider",
typeof(string)).ToString();
string connectionString = appReader.GetValue("connectionString",
typeof(string)).ToString();
System.Data.Common.DbProviderFactory factory =
System.Data.Common.DbProviderFactories.GetFactory(provider);
System.Data.Common.DbConnection con = factory.CreateConnection();
con.ConnectionString = connectionString;
System.Data.Common.DbCommand cmd = factory.CreateCommand();
// Here is the line
cmd.CommandText = "Select
CustomerID,CompanyName,ContactName,ContactTitle,Address,City,Region,PostalCode,Country,Phone,Fax
from Customers";
cmd.Connection = con;
con.Open();
IDataReader idr = cmd.ExecuteReader();
con.Close();//
So as long as the sql can be shared, then you're good. (<<Emphasis on the
"can be shared" comment)
However, I have found that when actually working with different RDBMS, the
simple sql statements don't actually play out all of the time.
Here are some questions:
What if you want inline sql for Access, but you want to call stored
procedures for Sql Server and stored procedure (in a package) for Oracle?
Another situation : Oracle supports stored procedure overloads, while Sql
Server does not.
//Brief description of stored procedure overloading
PL/SQL Packages : Overloading a procedure means creating multiple
procedures with the same name in the same package, each taking arguments
of different number or datatype.
//End Brief description of stored procedure overloading
Here is an oracle example I found on the web:
CREATE OR REPLACE PACKAGE stringer AS
FUNCTION stringify(p_field IN VARCHAR2) RETURN VARCHAR2;
FUNCTION stringify(p_field IN NUMBER) RETURN VARCHAR2;
FUNCTION stringify(p_field IN DATE) RETURN VARCHAR2;
END;
So in Sql Server, I have to write a different procedure for each type of
operation.
Create procedure dbo.StringifyWithVarchar( p_field varchar(max) )
Create procedure dbo.StringifyWithNumber( p_field number )
Create procedure dbo.StringifyWithDate( p_field datetime )
What my example (from my blog) does is a couple of things:
First, it has an abstract class
public abstract class CustomerDataBaseLine
If you write a simple concrete inheriting from this abstract class, then
you're done. This would be the "use as much common sql as you can"
method.
However, what if you're using Sql Server and you want to use a stored
procedure instead of inline sql?
You're already setup to do this, because you can override the virtual
method in the abstract class:
Here is my actual code from the sample:
private readonly string PROC_CUSTOMERS_GET_ALL =
"[dbo].[uspCustomersGetAll]";
public override IDataReader CustomersGetAllReader()
{
//Don't want to use inline sql with Sql Server.. .No Problem.
//Override the method. .. and use a stored procedure
IDataReader returnReader = null;
try
{
Database db = this.GetDatabase();
DbCommand dbc = db.GetStoredProcCommand(this.PROC_CUSTOMERS_GET_ALL);
returnReader = db.ExecuteReader(dbc);
return returnReader;
}
finally
{
}
}
So now you're able to call a stored procedure (instead of inline sql).
In the 2.0 abstract model (the sample above) .. you have this:
cmd.CommandText = "Select
CustomerID,CompanyName,ContactName,ContactTitle,Address,City,Region,PostalCode,Country,Phone,Fax
from Customers";
So my question is (and I'll be glad to hear other ideas) is .... if I have
a Jet (access.mdb) Database, and a Sql Server database, how do I set the
cmd.CommandText so that it uses inline sql for the Jet (access.mdb)
database and it uses a stored procedure for Sql Server?
.............
Some other thoughts:
The syntax for getting information from an Excel spreadsheet has its own
syntax.
Excel query:
"Select
[CustomerID],[CompanyName],[ContactName],[ContactTitle],[Address],[City],[Region],[PostalCode],[Country],[Phone],[Fax]
FROM [Sheet1$]";
With emphasis on the [Sheet1$]
.......
How about a txt file?
"SELECT [Customer ID] , [Company Name] , [Contact Name] , [Contact Title]
, [Address] , [City] , [Region] , [Postal Code] , [Country] , [Phone] ,
[Fax] FROM MyTextFile.txt";
So how do you get a single sql command to deal with Excel ("from
Sheet1$"), a text file ("from MyTextFile.txt") and then the baseline sql
"from Customers"?
This is what my example is trying to do. Instead of reacting, go ahead
and anticipate these issues, and have the code organized up front to deal
with these situations that might arise.
This article states almost the same thing (this is where I pulled my
sample code btw for the abstract factory) (aka, I'm not totally alone in
my conclusion that the ado.net abstract factory forces a common sql
syntax)
I put *** around the germane statement.
http://www.simple-talk.com/dotnet/.net-framework/ado.net-2.0-factory-classes/
//START QUOTE
** One disadvantage of using the factory classes and developing a common
data layer is that it limits us to standard SQL statements. This means we
cannot take advantage of the full functionality of a particular database
product. ***
One way to overcome this is to make a check on the type of ADO.NET object
created by a factory and execute some statements based on it. Though it's
not an elegant approach, it is useful when we need to execute database
product-specific SQL statements. For example:
C# Code
DbProviderFactory factory =
DbProviderFactories.GetFactory("System.Data.SqlClient");DbCommand cmd =
factory.CreateCommand();if(cmd is System.Data.SqlClient.SqlCommand){
//set command text to SQL Server specific statement } else if (cmd is
System.Data.OleDb.OleDbCommand) { //set command text to OleDb specific
statement } //END QUOTE
So that author tries to address the issue by checking the type on the
returned cmd and writing "if" logic. (Aka, I would agree with his
statement "Though it's not an elegant approach").
What I try to do is go ahead and separate them out up-front, so I can
encapsulate any special cases into a common concrete, instead of the "if"
method above.
I would add comments to his statement
"This means we cannot take advantage of the full functionality of a
particular database product."
with this list of gotchas:
Calling stored procedures instead of inline sql.
Calling overloaded stored procedures (Oracle) and non-overloaded stored
procedures(Sql Server).
Being able to deal with datastores with non standard syntax needs
("Sheet1$" and "MyTextFile.txt").
The ado.net abstract factory has its place for sure. I'm not saying
otherwise. If you can develop (only) standard sql and make it work and
perform well, then it is definately a good approach.
However, if I were supporting an enterprise application with multilple
backend rdbms support, and I actually had good dba's on each of those
rdbms systems, and I really needed to tweak out the performance for each
rdbms, I would (still) implement my approach above.
If I were support Sql Server and Oracle, I would have isolated code
fragments to try and find issues (maybe failing unit tests for example).
Each concrete would isolate my issues to a particuliar rdbms.
....
Another advantage of my approach would be that you could start out with
everything being standard sql (via the CustomerDataBaseLine abstract
class), but then as you hit 1 or 2 (or a few) performance trouble spots,
you could swap out a method for something more tuned for that particular
rdbms.
Aka, maybe your "GetAllOrders" procedures works great as standard sql for
Access(Jet) and Oracle. But for some reason, your Sql Server version
doesn't perform well. So you're able to write a stored procedure
(dbo.GetAllOrders) and override the method in the abstract class in your
SqlServer(concrete). So you have the ability to tweak certain procedures
(for a certain rdbms) when the need arises. And you didn't have to hack
it in, because you already setup your code to anticipate this need. (Yes,
I know there may be a missing index on the Sql Server version of your
datastore.....and finding that index might clear up the issue, but
sometimes I have found you just want to code up a stored procedure to take
advantage of some TSQL features)
(You can actually see this in my demo code,
public class CustomerJetData : CustomerDataBaseLine
This class inherits from CustomerDataBaseLine, and doesn't actually
override any of the methods for data operations.
......
All and all, where the author of the article above says (about what you
might do if you need a slightly different sql syntax):
"Though it's not an elegant approach"
My example is an attempt (emphasis on the "an attempt") to provide a more
elegant approach.
If there are other more elegant approaches out there, I would like to
listen to them. If there is a better appraoch, I'd like to know about
(which meets the issues described above).
But also take the time to code up an example........to show how you would
deal with the issues laid out above.
................
Scott M. said:
Why not just use the DBProvider Factory Pattern built right into ADO
.NET?
-Scott
