XML vs SQL Server
shapper
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
Scott M.
"shapper" <mdm...@gmail.com> wrote in message
news:709fb04a-767e-4691...@m25g2000yqc.googlegroups.com...
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
Arne Vajhøj
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
Mark Rae [MVP]
news:709fb04a-767e-4691...@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
Patrice
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
Mark Rae [MVP]
news:%23OJ0VIG...@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...?
Patrice
> 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
Mark Rae [MVP]
> 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...
Arne Vajhøj
> "Patrice" <http://scribe-en.blogspot.com/> wrote in message
> news:%23OJ0VIG...@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
Patrice
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
shapper
> 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 ...
Scott M.
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
Mark Rae [MVP]
news:3007cfc1-1644-4f6b...@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...
shapper
> "shapper" <mdmo...@gmail.com> wrote in message
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
Scott M.
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
Nick
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.
Scott M.
"Nick" <Ni...@spam.com> wrote in message
news:00e74721$0$11969$c3e...@news.astraweb.com...
> Scott M. wrote:
>> "shapper" <mdm...@gmail.com> wrote in message
>> news:cca364f1-26ef-41fd...@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
Nick
> "Nick" <Ni...@spam.com> wrote in message
> news:00e74721$0$11969$c3e...@news.astraweb.com...
>> Scott M. wrote:
>>> "shapper" <mdm...@gmail.com> wrote in message
>>> news:cca364f1-26ef-41fd...@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.
>
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?
Scott M.
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
sloan
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" <mdm...@gmail.com> wrote in message
news:709fb04a-767e-4691...@m25g2000yqc.googlegroups.com...
Scott M.
"sloan" <sl...@ipass.net> wrote in message
news:%23jBaAgd...@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.
>
> 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
Arne Vajhøj
> 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
sloan
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-...@nospam.nospam> wrote in message
news:uCiNZFic...@TK2MSFTNGP04.phx.gbl...
sloan
//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" <sl...@ipass.net> wrote in message
news:u5$kQBrcK...@TK2MSFTNGP02.phx.gbl...
Scott M.
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
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
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?