XML vs SQL Server

[Scott M.]

The built in factory assumes a common (standard) syntax among the
different backend datastores.

Just to be clear here, the DBProvider Factory pattern is an interface-based
programming paradigm. It provides a common set of class members that are
each *called* the same way, regardless of the actual DBProvider. It does
NOT assume that the *values* you supply to the common interface members will
remain constant.

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.

The factory will still be sufficient even if the new DBMS needs the query to
be written differently. You would simply change the value of the Command
object's CommandText property to the new syntax. But, you wouldn't have to
change the *type* of Command object you have or how you are using it.
That's the whole point of interfaces - - the data changes, but the interface
stays the same.

Example:

I wasn't asking for an example. I'm the one who suggested it to you.

[snip]

So as long as the sql can be shared, then you're good. (<<Emphasis on the
"can be shared" comment)

Wrong. Even if your SQL needs to be changed, you're still good. You just
change the value of the CommandText and everything else is fine. It is
unreasonable to expect that you'd never have to change a single item in your
code as your DBMS changes.

However, I have found that when actually working with different RDBMS, the
simple sql statements don't actually play out all of the time.

So, just change your SQL, but not the objects in use or how you are using
them.

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?

Then you change your CommandType and CommandText as necessary. BUT NOT YOUR
OBJECTS OR HOW YOU ARE USING THEM.

Another situation : Oracle supports stored procedure overloads, while Sql
Server does not.

Same answer as above.

//Brief description of stored procedure overloading

[snip: this has nothing to do with the answer]

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 )

Again, you are staying way off the point.

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:

Or, you could just change the CommandType to StoredProcedure and the
CommandText to the sproc name.

Here is my actual code from the sample: [snip]


So now you're able to call a stored procedure (instead of inline sql).

Seems more involved than just changing two property values.

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?

Answered already. You don't have one value that fits all circumstances.
Your very question implies that a change has been made to the data access
model, which using the ADO .NET built-in DBProvider Factory requires you to
make no custom anything and simply change the values of two properties if
needed.

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"?

You keep asking the same question over and over. The answer is that the
DBProvider Factory absolutely allows you to create ONE instance of a Command
object that just needs its CommandText property value altered when the
back-end data store requires it.

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.

Perhaps, but you've re-invented the wheel to solve a *problem* that isn't
really a problem that needs solving. Essentailly, you're tyring to make a
better mouse trap.

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)

Absolutely! That's what interfaces are all about! But, I suspect that when
you wrote *syntax*, you didn't actually mean it as "the syntax to use the
classes", which does reamin consistent. I suspect you meant "the syntax of
the CommandText", which is incorrect. The Factory pattern only says that
you have a Connection, a Command, and perhaps a DataReader whoes interfaces
do not change, regardless of the DBMS.

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. ***

Taken at face value, that is an incorrect statement.

-Scott

 

[shapper]

Hello,

Sorry for the delay on my answer ...

1. I agree that XML is specially to be used for transport data.
I use it often ... For example to get data from SQL Database and
send it to Flash movie, etc.

2. I know SQL Server is the better option. But if the client does not
want it ... What can I do?
I can't say a client "my way or no way".

3. "Why, e.g., did you not use Excel spreadsheets? Or text files?"
Just because I see XML files as the better of evils for this case.

4. About Access database:
I used it often in the past but I dropped it when Linq didn't
include it.
SQL Express seems the path to follow.

5. Other database options.
I think that the ideal solution for these scenarios would be to use
MySQL.
Usually is free on hosting servers.
I think Entity Framework is supporting it or it will support it.

Two months ago I was trying EF. I decided to wait for EF4.
The reason is that I found to many hacks to solve this and that.
And it seems the need for those hacks will disappear when EF4 comes
out.
I am waiting for ASP.NET 4.0 and EF. Any idea when it will come
out?

I checking also NHibernate ... But even if NHibernate seems to have
better reviews I would prefer to use EF.

Thanks,
Miguel




It sounds logic so I often

 

[Scott M.]

Hello,

Sorry for the delay on my answer ...

1. I agree that XML is specially to be used for transport data.
I use it often ... For example to get data from SQL Database and
send it to Flash movie, etc.

2. I know SQL Server is the better option. But if the client does not
want it ... What can I do?
I can't say a client "my way or no way".

No, but you can certainly try to educate your client as to why it is the
wrong fit. If you tell them that using XML this way:

1.does not include any built in security
2. will be prone to file corruption
3. has no built-in way to validate input
4. has no built-in way to enforce data constraints
5. will not provide the best performance
6. offers no multi-user support
7. offers no advantage to a DBMS and will be more complex in the end because
a. items 1, 3, and 4 (above) will have to be implemented with XSD and
homemade security
b. the architecutre won't scale when needed

If you make your case on each of these points, while also making the point
that using a DBMS doesn't have to add to the cost of the solution and will
overcome all of these items with a comprable amount of work, then you may
get them to see the light.

3. "Why, e.g., did you not use Excel spreadsheets? Or text files?"
Just because I see XML files as the better of evils for this case.

I did not recommend this as this would be a very bad solution.

4. About Access database:
I used it often in the past but I dropped it when Linq didn't
include it.
SQL Express seems the path to follow.

While it's true that there is no LINQ to Access, and it's true that Access
would only be a good choice for limited user activity, you seem to be very
much tied into using LINQ as an architectural requirment. Why?