Hello,

When a web application (site) is small in terms of data I usually use
XML files.

For example, at the moment I have a web site with the following XML
files (Note: I use XML as they would be SQL Server tables):

Users, UsersRoles, Roles, Texts, Albums and Photos.

To give you an idea I will have at its maximum:

4 Users, 2 Roles, (8 UsersRoles), 4 Texts, 10 Albums and 200 Photos.

Each photo will have a maximum of 40KB. Most of them only 10 or 20 KB.
The functionality I need is really simple: Create, Delete, Get and
Update. No really complex queries.

Do you think for this kind of project a XML structure to hold data
rather then SQL Server is ok?
Will I have a huge problem in performance?

I have this implemented and it is working quite fine.

Where do you think it is the "point" of using a SQL database instead
of XMl files?

Thanks,
Miguel

"shapper" <mdmoura@gmail.com> wrote in message 
news:709fb04a-767e-4691-ae09-b42a14f2712e@m25g2000yqc.googlegroups.com...
> Hello,
>
> When a web application (site) is small in terms of data I usually use
> XML files.
>
> For example, at the moment I have a web site with the following XML
> files (Note: I use XML as they would be SQL Server tables):
>
> Users, UsersRoles, Roles, Texts, Albums and Photos.
>
> To give you an idea I will have at its maximum:
>
> 4 Users, 2 Roles, (8 UsersRoles), 4 Texts, 10 Albums and 200 Photos.
>
> Each photo will have a maximum of 40KB. Most of them only 10 or 20 KB.
> The functionality I need is really simple: Create, Delete, Get and
> Update. No really complex queries.
>
> Do you think for this kind of project a XML structure to hold data
> rather then SQL Server is ok?
> Will I have a huge problem in performance?
>
> I have this implemented and it is working quite fine.
>
> Where do you think it is the "point" of using a SQL database instead
> of XMl files?
>
> Thanks,
> Miguel

Don't forget what the whole point of XML is...to have a non-proprietary and 
platform-independent way to send structured data to some other location. 
Even then, the XML is a representation of data stored in some other more 
permenent data store.  XML is not billed as being a good choice when it 
comes to performance, it's a good choice when you need to send data across a 
wire between systems.  Sure, there are other uses for XML, such as for 
configuraiton files.  But for pure data storage that will need to be read 
and written to, it may be more trouble than it's worth.

Having to read and write to a file on a file system over and over again is 
not going to be the better performing choice, nor would it work reliably if 
multiple file accesses occur.  There's no security on XML files either.

You haven't described anyting here about your app that would in any way make 
me want to include XML as part of the solution.  Put your data in SQL.

-Scott 

shapper wrote:
> When a web application (site) is small in terms of data I usually use
> XML files.
> 
> For example, at the moment I have a web site with the following XML
> files (Note: I use XML as they would be SQL Server tables):
> 
> Users, UsersRoles, Roles, Texts, Albums and Photos.
> 
> To give you an idea I will have at its maximum:
> 
> 4 Users, 2 Roles, (8 UsersRoles), 4 Texts, 10 Albums and 200 Photos.
> 
> Each photo will have a maximum of 40KB. Most of them only 10 or 20 KB.
> The functionality I need is really simple: Create, Delete, Get and
> Update. No really complex queries.
> 
> Do you think for this kind of project a XML structure to hold data
> rather then SQL Server is ok?
> Will I have a huge problem in performance?
> 
> I have this implemented and it is working quite fine.
> 
> Where do you think it is the "point" of using a SQL database instead
> of XMl files?

If your data is readonly data, then your XML files will work fine.
But if you need to update data, then supporting multiple concurrent
updates to files and recovery if somethings crashes in the middle
will be a lot of work with file and a piece of cake with
database.

Performance wise XML files will probably perform similar to database
tables without indexes. Which is OK for the data size you list. But
I am a bit skeptical about whether you really know that data would
never grow bigger.

Arne

"shapper" <mdmoura@gmail.com> wrote in message 
news:709fb04a-767e-4691-ae09-b42a14f2712e@m25g2000yqc.googlegroups.com...

> Do you think for this kind of project a XML structure to hold data
> rather then SQL Server is ok?

XML isn't a database. Trying to make XML files pretend to be a database is 
always the wrong solution.

If you need database functionality, use a database - that's what databases 
are for...

Why, e.g., did you not use Excel spreadsheets? Or text files?


> Where do you think it is the "point" of using a SQL database instead
> of XMl files?

See above.


-- 
Mark Rae
ASP.NET MVP
http://www.markrae.net 

I would take things the other way round. Why use XML rather than a DBMS ?

If you meant "SQL Server", the product you could use 
http://www.microsoft.com/Sqlserver/2005/en/us/compact.aspx.

Else try :
http://en.wikipedia.org/wiki/Comparison_of_relational_database_management_systems

You'll likely find a DBMS product that requires not specific installation 
and that will provide a bit more functionality than XML files...

--
Patrice 

"Patrice" <http://scribe-en.blogspot.com/> wrote in message 
news:%23OJ0VIGcKHA.1640@TK2MSFTNGP06.phx.gbl...

>> When a web application (site)

> If you meant "SQL Server", the product you could use 
> http://www.microsoft.com/Sqlserver/2005/en/us/compact.aspx.

Does SQL Compact work with ASP.NET apps now...?


-- 
Mark Rae
ASP.NET MVP
http://www.markrae.net 

>>> When a web application (site)

> Does SQL Compact work with ASP.NET apps now...?

Ooops, sorry, I missed it was a web app as the argument generally invoqued 
for using XML files with a small amount of data is avoiding installing a 
DBMS as part of the application setup.

For a web app, I really don't see what advantage the OP expects from XML 
files even for small amounts of data, especially as it seems he does use 
DBMS otherwise...

--
Patrice 

"Patrice" <http://scribe-en.blogspot.com/> wrote in message 
news:uEJKCkHcKHA.2188@TK2MSFTNGP04.phx.gbl...

> For a web app, I really don't see what advantage the OP expects from XML 
> files even for small amounts of data, especially as it seems he does use 
> DBMS otherwise...

Me neither...


-- 
Mark Rae
ASP.NET MVP
http://www.markrae.net 

Mark Rae [MVP] wrote:
> "Patrice" <http://scribe-en.blogspot.com/> wrote in message 
> news:%23OJ0VIGcKHA.1640@TK2MSFTNGP06.phx.gbl...
>>> When a web application (site)
> 
>> If you meant "SQL Server", the product you could use 
>> http://www.microsoft.com/Sqlserver/2005/en/us/compact.aspx.
> 
> Does SQL Compact work with ASP.NET apps now...?

It does not?

Arne

>> Does SQL Compact work with ASP.NET apps now...?
>
> It does not?

SQL Server Compact uses few resources and is easily installable (even with 
no admin rights), it is intended for Mobile or Desktop application as an 
easily manageable DBMS.

As a web application is installed on a server, requires other products and 
more scalability, connecting to SQL Server Compact from an ASP.NET has been 
explicitely blocked by MS (actually I never tried).

Sorry for polluting this thread, I missed it was a web app (using XML files 
is mostly seen when one don't want to install a DBMS on a PC as part of his 
setup, in which case I suggest to check if not already done this edition (or 
any other easily installable DBMS product that is precisely intended for 
this usage)).

For a web app, I don't see the point of not using a "true" DBMS (even free 
hosting likely provides this).

--
Patrice

On Nov 28, 10:20=A0pm, "Patrice" <http://scribe-en.blogspot.com/> wrote:

> For a web app, I really don't see what advantage the OP expects from XML
> files even for small amounts of data, especially as it seems he does use
> DBMS otherwise...

It's not me ... It's them! lol

90% of the cases I use a SQL Express database.

However, a few times a client sees the hosting company asking a little
bit more for the SQL database.
Doesn't matter how much more it is ... For the client is more. Period.

Usually are clients with small web sites where all they need is to
edit a text, a few photos, etc.

The data size is always small and the Edit/Create/Delete is always
taken by a single access.
I only have multiple Read accesses to the XML files. Never Edit/Create/
Delete.

So I think in these cases using a few XML files it is ok ... At least
is working fine.
And by using XML files I have some structure on the data and I am able
to use LINQ.

In relation to other database options ...
The true is if the web application requires something more I prefer to
jump right to SQL Server which I am more familiar with.

For example, I have a similar project in terms of complexity but they
require the user to submit the CV's.
So I might have more than one user submiting data to the database at a
given moment.
In this case I went for SQL database and explained exactly that to the
client.

Sometime ago I was looking to the database engine, I think used by
Firefox, that is file based.
I don't remember the name and I think there were some LINQ
implementation for that but not supporting everything.

But as I said, when there is the need for that I jump immediately for
SQL Server.


Well, this is my idea ...

"shapper" <mdmoura@gmail.com> wrote in message 
news:3007cfc1-1644-4f6b-9036-312826ac6c56@a21g2000yqc.googlegroups.com...
On Nov 28, 10:20 pm, "Patrice" <http://scribe-en.blogspot.com/> wrote:

>So I think in these cases using a few XML files it is ok ... At least
>is working fine.
>And by using XML files I have some structure on the data and I am able
>to use LINQ.

Did you read my reply?  While it may be *ok*, there's no compelling reason 
to use it and only reasons no to.  You can still use LINQ if the data is in 
SQL.  You don't gain any performance (and most likely hurt it), you have no 
security, and effectively leave yourself with no scalability.  This is not 
what XML was designed for.

I'll never forget my first formal introduction to XML at a developer 
conference back in 2000.  The speaker (who's name escapes me, but was 
already well versed in XML) said "Just because you can use XML doesn't mean 
you should.".

-Scott 

"shapper" <mdmoura@gmail.com> wrote in message 
news:3007cfc1-1644-4f6b-9036-312826ac6c56@a21g2000yqc.googlegroups.com...

> So I think in these cases using a few XML files it is ok ...

So why did you even ask the question in the first place...???

You seem determined that you are right, no matter what anyone else tells 
you...


-- 
Mark Rae
ASP.NET MVP
http://www.markrae.net 

On Nov 29, 4:19=A0pm, "Mark Rae [MVP]" <m...@markNOSPAMrae.net> wrote:
> "shapper" <mdmo...@gmail.com> wrote in message
>
> news:3007cfc1-1644-4f6b-9036-312826ac6c56@a21g2000yqc.googlegroups.com...
>
> > So I think in these cases using a few XML files it is ok ...
>
> So why did you even ask the question in the first place...???
>
> You seem determined that you are right, no matter what anyone else tells
> you...
>
> --
> Mark Rae
> ASP.NET MVPhttp://www.markrae.net

I said "I think" ... And some of the answers I get confirm some things
I wasn't sure about.

Scott's and Arne's first answers confirmed my ideas which I wasn't
sure.

If I would be sure I wouldn't post ... of course

"shapper" <mdmoura@gmail.com> wrote in message 
news:cca364f1-26ef-41fd-b907-884009e77be4@c3g2000yqd.googlegroups.com...
On Nov 29, 4:19 pm, "Mark Rae [MVP]" <m...@markNOSPAMrae.net> wrote:
> "shapper" <mdmo...@gmail.com> wrote in message


>Scott's and Arne's first answers confirmed my ideas which I wasn't
>sure.

What did I say that made you think "using a few XML files it is ok"?  I'm 
sorry if I gave you that impression.  I've been trying to explain why it's 
NOT ok to use XML files for this purpose.

-Scott

Scott M. wrote:
> "shapper" <mdmoura@gmail.com> wrote in message 
> news:cca364f1-26ef-41fd-b907-884009e77be4@c3g2000yqd.googlegroups.com...
> On Nov 29, 4:19 pm, "Mark Rae [MVP]" <m...@markNOSPAMrae.net> wrote:
>> "shapper" <mdmo...@gmail.com> wrote in message
> 
> 
>> Scott's and Arne's first answers confirmed my ideas which I wasn't
>> sure.
> 
> What did I say that made you think "using a few XML files it is ok"?  I'm 
> sorry if I gave you that impression.  I've been trying to explain why it's 
> NOT ok to use XML files for this purpose.
> 

Just to be clear I didn't see that your reasons addressed the
possibility that in many simple instances the XML solution may be
cheaper and simpler with no significant downside.

Which I guess is why shapper has replied as he has. There is a suspicion
of developers discussing the merits of deep/pile foundations when all
the client has requested is a garden shed.

"Nick" <Nick@spam.com> wrote in message 
news:00e74721$0$11969$c3e8da3@news.astraweb.com...
> Scott M. wrote:
>> "shapper" <mdmoura@gmail.com> wrote in message
>> news:cca364f1-26ef-41fd-b907-884009e77be4@c3g2000yqd.googlegroups.com...
>> On Nov 29, 4:19 pm, "Mark Rae [MVP]" <m...@markNOSPAMrae.net> wrote:
>>> "shapper" <mdmo...@gmail.com> wrote in message
>>
>>
>>> Scott's and Arne's first answers confirmed my ideas which I wasn't
>>> sure.
>>
>> What did I say that made you think "using a few XML files it is ok"?  I'm
>> sorry if I gave you that impression.  I've been trying to explain why 
>> it's
>> NOT ok to use XML files for this purpose.
>>
>
> Just to be clear I didn't see that your reasons addressed the
> possibility that in many simple instances the XML solution may be
> cheaper and simpler with no significant downside.

You didn't see me mention:

1. Decreased application performance with the reading/writing of XML files.
2. No multi-user support.
3. No built in security.

> Which I guess is why shapper has replied as he has. There is a suspicion
> of developers discussing the merits of deep/pile foundations when all
> the client has requested is a garden shed.

4. Today the client may want a garden shed, but tomorrow they *will* want 
that shed to double as their apartment, vacation home, and factory floor. 
This solution will not scale when the time comes.  However, the cost, time, 
effort to set up the DBMS is not any greater than the XML solution and the 
DBMS solution will solve every one of these problems.
5. XML was not designed for this purpose in the first place.
6. No built-in way to enforce data types or data structures or data 
constraints.

Those are just off the top of my head.

Unfortunately, as I stated, too many people use XML because they *can*. 
Rather than believe that you should justify the DBMS scenario, the exact 
opposite should be the case.  This is clearly a design where data must be 
stored beyond just a user session, which is exactly what DBMS's are for. 
Really, the question shouldn't be why is a DBMS a good choice?  It should be 
why is XML a better choice?  For all the reasonse I've described, the answer 
is that it isn't.

-Scott

Scott M. wrote:
> "Nick" <Nick@spam.com> wrote in message 
> news:00e74721$0$11969$c3e8da3@news.astraweb.com...
>> Scott M. wrote:
>>> "shapper" <mdmoura@gmail.com> wrote in message
>>> news:cca364f1-26ef-41fd-b907-884009e77be4@c3g2000yqd.googlegroups.com...
>>> On Nov 29, 4:19 pm, "Mark Rae [MVP]" <m...@markNOSPAMrae.net> wrote:
>>>> "shapper" <mdmo...@gmail.com> wrote in message
>>>
>>>> Scott's and Arne's first answers confirmed my ideas which I wasn't
>>>> sure.
>>> What did I say that made you think "using a few XML files it is ok"?  I'm
>>> sorry if I gave you that impression.  I've been trying to explain why 
>>> it's
>>> NOT ok to use XML files for this purpose.
>>>
>> Just to be clear I didn't see that your reasons addressed the
>> possibility that in many simple instances the XML solution may be
>> cheaper and simpler with no significant downside.
> 
> You didn't see me mention:
> 
> 1. Decreased application performance with the reading/writing of XML files.
> 2. No multi-user support.
> 3. No built in security.
> 
Yes I saw you mention them. I do not believe that they are *always* true 
and/or significant to an app being fit for purpose.

>> Which I guess is why shapper has replied as he has. There is a suspicion
>> of developers discussing the merits of deep/pile foundations when all
>> the client has requested is a garden shed.
> 
> 4. Today the client may want a garden shed, but tomorrow they *will* want 
> that shed to double as their apartment, vacation home, and factory floor. 
> This solution will not scale when the time comes.  However, the cost, time, 
> effort to set up the DBMS is not any greater than the XML solution and the 
> DBMS solution will solve every one of these problems.

You didn't read the point where he explained DBMS support was more 
expensive? Or did you just decide this was not the case, just like you 
decided that the client's request for a shed was not what it appeared to be.

> 5. XML was not designed for this purpose in the first place.

A religious reason?

> 6. No built-in way to enforce data types or data structures or data 
> constraints.
> 

More religion?

> Those are just off the top of my head.
> 
> Unfortunately, as I stated, too many people use XML because they *can*. 
> Rather than believe that you should justify the DBMS scenario, the exact 
> opposite should be the case.  This is clearly a design where data must be 
> stored beyond just a user session, which is exactly what DBMS's are for. 
> Really, the question shouldn't be why is a DBMS a good choice?  It should be 
> why is XML a better choice?  For all the reasonse I've described, the answer 
> is that it isn't.
> 
> -Scott
> 
> 
> 
> 

"Nick" <Nick@spam.com> wrote in message 
news:01575953$0$7592$c3e8da3@news.astraweb.com...
> Scott M. wrote:
>> "Nick" <Nick@spam.com> wrote in message 
>> news:00e74721$0$11969$c3e8da3@news.astraweb.com...
>>> Scott M. wrote:
>>>> "shapper" <mdmoura@gmail.com> wrote in message
>>>> news:cca364f1-26ef-41fd-b907-884009e77be4@c3g2000yqd.googlegroups.com...
>>>> On Nov 29, 4:19 pm, "Mark Rae [MVP]" <m...@markNOSPAMrae.net> wrote:
>>>>> "shapper" <mdmo...@gmail.com> wrote in message
>>>>
>>>>> Scott's and Arne's first answers confirmed my ideas which I wasn't
>>>>> sure.
>>>> What did I say that made you think "using a few XML files it is ok"? 
>>>> I'm
>>>> sorry if I gave you that impression.  I've been trying to explain why 
>>>> it's
>>>> NOT ok to use XML files for this purpose.
>>>>
>>> Just to be clear I didn't see that your reasons addressed the
>>> possibility that in many simple instances the XML solution may be
>>> cheaper and simpler with no significant downside.
>>
>> You didn't see me mention:
>>
>> 1. Decreased application performance with the reading/writing of XML 
>> files.
>> 2. No multi-user support.
>> 3. No built in security.
>>
> Yes I saw you mention them. I do not believe that they are *always* true 
> and/or significant to an app being fit for purpose.

It's not a matter of what you belive. Items 1-3 (above) are true limitations 
of utilizing XML files for data storage.  There is no opinion here.

>
>>> Which I guess is why shapper has replied as he has. There is a suspicion
>>> of developers discussing the merits of deep/pile foundations when all
>>> the client has requested is a garden shed.
>>
>> 4. Today the client may want a garden shed, but tomorrow they *will* want 
>> that shed to double as their apartment, vacation home, and factory floor. 
>> This solution will not scale when the time comes.  However, the cost, 
>> time, effort to set up the DBMS is not any greater than the XML solution 
>> and the DBMS solution will solve every one of these problems.
>
> You didn't read the point where he explained DBMS support was more 
> expensive?

I'm responding to the original question:

"Do you think for this kind of project a XML structure to hold data
rather then SQL Server is ok?"

But, the OP could implement other DBMS solutions at no cost, if SQL Server 
were out of his cost range.

>Or did you just decide this was not the case, just like you decided that 
>the client's request for a shed was not what it appeared to be.

To use your "shed" metaphore again, even if I knew that a shed was only 
going to be needed forever, I still wouldn't build it with toothpicks.  I'd 
use lumber and just build something small.  (XML = toothpicks / DBMS = 
lumber).

>
>> 5. XML was not designed for this purpose in the first place.
>
> A religious reason?

Uh, a factual one?  I'm really at a loss to even respond to that question 
further as it implies a lack of understanding about what XML is.

>
>> 6. No built-in way to enforce data types or data structures or data 
>> constraints.
>>
>
> More religion?

Again, if you knew what XML is and how to use it, you'd know that all data 
in XML is Strings, that there is no built-in validation or way to constrain 
the data, nor is there any way to set up relationships between different 
sets of XML.  This is not an opinion, it is the way the language was 
designed.  If you want any of that, you need to use XSD (XML Schema 
Definitions), which will move the complexity of your "shed" up way past a 
simple DMBS design.

I really don't see any reasonable case to be made against any of the points 
I've brought up.  *Can* it be done?  Sure, the OP is already doing it.  But, 
that wasn't the question.  The fact that you seem to think I'm stating my 
*opinions* of how XML works, rather than the technical aspects of how XML is 
designed to work, even though you've been presented with these facts, just 
makes me scratch my head and wonder if you're not looking for an argument.

XML is, first and foremost, for the transport of data in a non-proprietary, 
platform-independent way.  This is not my opinion, it's a fact.  Since XML's 
inception, there have been many other uses devised for XML, but the vast 
majority involve the transport of data in a non-proprietary, 
platform-independent way (Web Services, RSS, XML as a storage format in 
DBMS).  And, yes, XML is sometimes used to store data on a file system. But, 
not for the purpose that the OP is doing, it is done as a replacement for 
what has long-been text-based data storage anyway, but now, XML offers 
greater structure - - configuration files.

>
>> Those are just off the top of my head.
>>
>> Unfortunately, as I stated, too many people use XML because they *can*. 
>> Rather than believe that you should justify the DBMS scenario, the exact 
>> opposite should be the case.  This is clearly a design where data must be 
>> stored beyond just a user session, which is exactly what DBMS's are for. 
>> Really, the question shouldn't be why is a DBMS a good choice?  It should 
>> be why is XML a better choice?  For all the reasonse I've described, the 
>> answer is that it isn't.

So, given all this, I would ask you to make the case FOR using XML, besides 
saying that a shed is what was asked for, since using a DBMS is not 
rocket-science and can certainly be quickly and easily set up for a "shed" 
today, but scalable when the shed has an "addition" put on it.


>> -Scott

Everyone else has made good points...especially in regards to why you pick a 
rdbms to begin with over txt, excel or other files.

My input will be this.  You can architect your code so you're now 
screwballed in the future if you need to swap out for a different dataStore.
(Aka, when you figure out that xml is too slow because your data grew too 
big).

See:
http://sholliday.spaces.live.com/Blog/cns!A68482B9628A842A!176.entry

This shows how to write an interface, and bring back 1 of several concretes 
depending on which rdbms you might pick.


....

(Note, I use the term "rdbms" very loosely with backends like Access(Jet 
database), Excel and Xml.  These are datastores, not really rdbms systems).

...........




"shapper" <mdmoura@gmail.com> wrote in message 
news:709fb04a-767e-4691-ae09-b42a14f2712e@m25g2000yqc.googlegroups.com...
> Hello,
>
> When a web application (site) is small in terms of data I usually use
> XML files.
>
> For example, at the moment I have a web site with the following XML
> files (Note: I use XML as they would be SQL Server tables):
>
> Users, UsersRoles, Roles, Texts, Albums and Photos.
>
> To give you an idea I will have at its maximum:
>
> 4 Users, 2 Roles, (8 UsersRoles), 4 Texts, 10 Albums and 200 Photos.
>
> Each photo will have a maximum of 40KB. Most of them only 10 or 20 KB.
> The functionality I need is really simple: Create, Delete, Get and
> Update. No really complex queries.
>
> Do you think for this kind of project a XML structure to hold data
> rather then SQL Server is ok?
> Will I have a huge problem in performance?
>
> I have this implemented and it is working quite fine.
>
> Where do you think it is the "point" of using a SQL database instead
> of XMl files?
>
> Thanks,
> Miguel
>
> 

"sloan" <sloan@ipass.net> wrote in message 
news:%23jBaAgdcKHA.4880@TK2MSFTNGP05.phx.gbl...
> See:
> http://sholliday.spaces.live.com/Blog/cns!A68482B9628A842A!176.entry
>
> This shows how to write an interface, and bring back 1 of several 
> concretes depending on which rdbms you might pick.
>
> (Note, I use the term "rdbms" very loosely with backends like Access(Jet 
> database), Excel and Xml.  These are datastores, not really rdbms 
> systems).

Why not just use the DBProvider Factory Pattern built right into ADO .NET?

-Scott 

sloan wrote:
> My input will be this.  You can architect your code so you're now 
> screwballed in the future if you need to swap out for a different dataStore.
> (Aka, when you figure out that xml is too slow because your data grew too 
> big).
> 
> See:
> http://sholliday.spaces.live.com/Blog/cns!A68482B9628A842A!176.entry
> 
> This shows how to write an interface, and bring back 1 of several concretes 
> depending on which rdbms you might pick.

It shows how not to write code.

It implements something that base .NET has had since version 2.0
in a way that only requires configuration to support a new database
with some custom code that requires code change to support a new
database.

Arne

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." <s-mar@nospam.nospam> wrote in message 
news:uCiNZFicKHA.2596@TK2MSFTNGP04.phx.gbl...
>
> "sloan" <sloan@ipass.net> wrote in message 
> news:%23jBaAgdcKHA.4880@TK2MSFTNGP05.phx.gbl...
>> See:
>> http://sholliday.spaces.live.com/Blog/cns!A68482B9628A842A!176.entry
>>
>> This shows how to write an interface, and bring back 1 of several 
>> concretes depending on which rdbms you might pick.
>>
>> (Note, I use the term "rdbms" very loosely with backends like Access(Jet 
>> database), Excel and Xml.  These are datastores, not really rdbms 
>> systems).
>
> Why not just use the DBProvider Factory Pattern built right into ADO .NET?
>
> -Scott
> 

One part of my copy/paste did not show up well in the previous post:


//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
}


This was the (other) author's method for getting around the times when you 
need to put in different syntaxes.




"sloan" <sloan@ipass.net> wrote in message 
news:u5$kQBrcKHA.2164@TK2MSFTNGP02.phx.gbl...
>
> 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." <s-mar@nospam.nospam> wrote in message 
> news:uCiNZFicKHA.2596@TK2MSFTNGP04.phx.gbl...
>>
>> "sloan" <sloan@ipass.net> wrote in message 
>> news:%23jBaAgdcKHA.4880@TK2MSFTNGP05.phx.gbl...
>>> See:
>>> http://sholliday.spaces.live.com/Blog/cns!A68482B9628A842A!176.entry
>>>
>>> This shows how to write an interface, and bring back 1 of several 
>>> concretes depending on which rdbms you might pick.
>>>
>>> (Note, I use the term "rdbms" very loosely with backends like Access(Jet 
>>> database), Excel and Xml.  These are datastores, not really rdbms 
>>> systems).
>>
>> Why not just use the DBProvider Factory Pattern built right into ADO 
>> .NET?
>>
>> -Scott
>>
>
> 

You're missing the entire point of the DBProvider Factory pattern.

You proceed from the false assumption that this pattern is designed so that 
no code will have to be changed when the DBMS does.  That is incorrect. 
It's quite acceptable for your CommandType or CommandText to change as your 
back-end data store changes.  This is just like your ConnectionString, which 
will need to change when the DBMS does.

What won't have to change though, is the instances of the objects and the 
way you use those instances.  That is the whole point of interface based 
programming.

-Scott

"sloan" <sloan@ipass.net> wrote in message 
news:u5$kQBrcKHA.2164@TK2MSFTNGP02.phx.gbl...
>
> 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 

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

"shapper" <mdmoura@gmail.com> wrote in message 
news:0d27818a-e79c-4baf-8ac3-cb53bd1d6cd3@k19g2000yqc.googlegroups.com...
> 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?

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