1. The Application I am working on is being developed to work with SQL Server, However in the future we may want to port it to use on another DB say Sybase ASA, or Oracle. The application is built using 3-Tier Arch so hopefully only the DataAccess layer should
need to be altered if we go for this change.
I am wondering the most generic yet still robust class library to use , i.e. System.Data.sql or System.Data.QleDb or whatever. By using one or the other do I limit myself as to what DB I can access in the future?
2. At the moment I am going to use the System.Data.OleDb class for my data access (unless you have a good answer to the above question) I would like to return XML from a table using a stored procedure and the FOR XML AUTO function. The only samples I can find
doing this are using the SQL provider or the microsoft managed classes, which seem great but that brings me back to question 1, if I use these classes do I limit myself as to which database I can use?
OK only two questions, but if you could offer any answers/advice that would be great.
Thanks for reading
Jeremy
Oracle, Sybase and SQL Server 2000 XML implementation are not the same so you cannot use FOR XML AUTO but you can use the code from the link below which uses OPENXML because current SQL Server will not decompose your XML while .NET XML classes will decompose
it. This is just to get you started you shoud look up XML implementation in all three databases. Hope this helps.
Hi All,
after half day I finally found an easy but not strongly tested solution.
If u have a statement like this:
SELECT A,B,C
FROM MYTABLE
FOR XML RAW('row_node_name'), ROOT('root_node_name'), ELEMENTS
try to use to following approach:
Declare @Data as xml
set @Data = (
SELECT A,B,C
FROM MYTABLE
FOR XML RAW('row_node_name'), ROOT('root_node_name'), ELEMENTS
)
SELECT @Data
You can run it by a standard OleDbCommand.
When I sad "not strongly tested" I mean that I've not idea about possible limitations regarding retrieved flows.
If someone has suggestions, considerations or something else please contact me.
I hope you'll find it usefull.
salsipius
Member
158 Points
37 Posts
Using FOR XML AUTO with an OleDB Command in C#
Aug 10, 2005 04:39 PM|LINK
1. The Application I am working on is being developed to work with SQL Server, However in the future we may want to port it to use on another DB say Sybase ASA, or Oracle. The application is built using 3-Tier Arch so hopefully only the DataAccess layer should need to be altered if we go for this change.
I am wondering the most generic yet still robust class library to use , i.e. System.Data.sql or System.Data.QleDb or whatever. By using one or the other do I limit myself as to what DB I can access in the future?
2. At the moment I am going to use the System.Data.OleDb class for my data access (unless you have a good answer to the above question) I would like to return XML from a table using a stored procedure and the FOR XML AUTO function. The only samples I can find doing this are using the SQL provider or the microsoft managed classes, which seem great but that brings me back to question 1, if I use these classes do I limit myself as to which database I can use?
OK only two questions, but if you could offer any answers/advice that would be great.
Thanks for reading
Jeremy
Caddre
All-Star
26581 Points
5308 Posts
Re: Using FOR XML AUTO with an OleDB Command in C#
Aug 10, 2005 09:01 PM|LINK
http://msdn.microsoft.com/msdnmag/issues/05/06/DataPoints/default.aspx
Gift Peddie
Roby75
Member
2 Points
1 Post
Re: Using FOR XML AUTO with an OleDB Command in C#
Nov 22, 2012 01:38 PM|LINK
Hi All,
after half day I finally found an easy but not strongly tested solution.
If u have a statement like this:
SELECT A,B,C
FROM MYTABLE
FOR XML RAW('row_node_name'), ROOT('root_node_name'), ELEMENTS
try to use to following approach:
Declare @Data as xml
set @Data = (
SELECT A,B,C
FROM MYTABLE
FOR XML RAW('row_node_name'), ROOT('root_node_name'), ELEMENTS
)
SELECT @Data
You can run it by a standard OleDbCommand.
When I sad "not strongly tested" I mean that I've not idea about possible limitations regarding retrieved flows.
If someone has suggestions, considerations or something else please contact me.
I hope you'll find it usefull.
Good luck to everybody and see you soon.