How to query and output information from database as XML in C# in ASP.NET

Hi, I'm new to ASP.NET and working in databases. I'm wishing to query a database and output the results of the query in XML, where each row returned by the database is another element in the XML doc.

So, let's say the database has 30 rows and each has a unique ID, a person's name, their address, and a latitude and longitude for each.

In ASP with VBScript you might execute a SQL query where the results are stored in a recordset object, and then you'd execute a loop that cycles through each record in the recordset spitting out the text and moving onto the next record. The loop would terminate when the recordset would reach end of file (EOF).

How would one achieve the same results in ASP.NET, written in C#?

Side question: How hard is it to process and properly encode the values from the DB so they are valid XML, as well, in the event odd characters are in it?

I'm attaching sample database contents and a sample output.
--- Sample database ---

DBID,CUSTFNAME,CUSTLNAME,CUSTADDRESS,CUSTLAT,CUSTLON
1,"John","Smith","222 W Main St., Springfield, USA",41.25454,-88.548798
2,"Mary","Jones","123 N Western Av., Springfield, USA",41.27876,-88.5874312
3,"Donatella","Kochanowski","321 S East Av., Springfield, USA",41.2678487,-87.978974


--- Sample Output ---
<?xml version="1.0" encoding="utf-8" ?>
<markers>
 <customer firstname="John" lastname="Smith" address="222 W. Main St., Springfield, USA" latitude="41.25454" longitude="-88.548798" />
</markers>

Open in new window

elevatedconsultingAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

DhaestCommented:
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
DhaestCommented:
Retrieving Data as XML from SQL Server
http://articles.sitepoint.com/article/data-as-xml-sql-server
0
elevatedconsultingAuthor Commented:
Dhaest:

All of the top links save as an XML file. The link in your second post I'm not sure applies to my situation. I can't control the SQL database, although it is MS-SQL. I just have to develop a script that outputs data from the database in XML format.

But rather than having the .aspx save a separate XML file, I want it to respond with the XML document directly to the requestor. (Thus, if you opened the script in IE, you'd just see an XML page in your browser.)

Would it be a simple modification to the code in your first link to do this?
0
JavaScript Best Practices

Save hours in development time and avoid common mistakes by learning the best practices to use for JavaScript.

DhaestCommented:
DataTable.WriteXml has a whole bunch of overloads, including ones which write to a Stream, or a TextWriter.

You can use a MemoryStream or a StringWriter with these overloads to keep it all in memory.

Example
string result;
using (StringWriter sw = new StringWriter()) {
dataTable.WriteXml(sw);
result = sw.ToString();
}
0
DhaestCommented:
If you don't actually need a string but read-only, processable XML, it's a better idea to use MemoryStream and XPathDocument:

If you don't actually need a string but read-only, processable XML, it's a 
better idea to use MemoryStream and XPathDocument:

XPathDocument result;
using (MemoryStream ms = new MemoryStream()) {
dataTable.WriteXml(ms);
ms.Position = 0;
result = new XPathDocument(ms);
}

Note that you can also directly create an XmlDataDocument from a DataSet 
(rather than a DataTable).

Open in new window

0
elevatedconsultingAuthor Commented:
I'm sorry--I'm having trouble understanding this decontextualized.

Can you give me a sample script that opens a system DSN named "mydsn" and outputs an XML document like I demonstrated from a table that contains the sample I typed out?

Being able to see something that is relatively complete will help me understand it in such a way that I can write something sufficient for my actual project (it's a lot more complicated, but all I need is something simple that would work as described to put all the pieces together in my head).

Thanks.
0
DhaestCommented:
Through a DSN ? What database are you using ?

Example below
private static void ReadOrderData(string connectionString)
{
    string queryString = 
        "SELECT DBID,CUSTFNAME,CUSTLNAME,CUSTADDRESS,CUSTLAT,CUSTLON FROM YourTable;";
    string connectionString= "Server=(local);DataBase=Northwind;Integrated Security=SSPI";
    using (SqlConnection connection = new SqlConnection(connectionString))
    {
        try
        {
           SqlDataAdapter daCustomers = new SqlDataAdapter(queryString , conn);
           daCustomers.Fill(dsCustomers, "Customers"); 

           string result;
           using (StringWriter sw = new StringWriter()) 
           {
              dsCustomers.Tables[0].WriteXml(sw);
              result = sw.ToString();
           }
        }
        finally
        {
        }
    }
}

Open in new window

0
elevatedconsultingAuthor Commented:
Right now I am testing with Access set up with a dsn. Later I'll be working with an mssql server by dsn.
0
DhaestCommented:
I agree with an access-database, because you can connect with a OdbcConnection through a dsn, but for sql-server, I wouldn't recommand it
0
elevatedconsultingAuthor Commented:
Thanks. Any reason why you don't recommend it? Even with caching the page? Is there some other method I should look into for establishing a RESTful XML API into a DB, in general?

Also: I assume in that connection string "Northwind" is the DSN name? I don't have to change anything if I pick any other kind of established and working DSN?
0
DhaestCommented:
therefore I can refer the following articles,thoughts

1.   The .NET Framework Data Provider for SQL Server uses its own protocol to communicate with SQL Server. Therefore, it does not support the use of an ODBC data source name (DSN) when connecting to SQL Server because it does not add an ODBC layer.
Please refer to this article: http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlconnection.connectionstring.aspx
 
If DSN is a critical part of your application consider using System.Data.Odbc.OdbcConnection (http://msdn.microsoft.com/en-us/library/system.data.odbc.odbcconnection.aspx)


2. The advantage of using OleDbConnection is flexibility.  You can change your database (for instance, move to Oracle)and not have to change your code.  The advantage of SqlConnection is performance.  The SqlConnection is tuned specifically for accessing Sql Server.  If you don't expect you'll be changing databases, definitely use the SqlConnection.

(Each of those classes does the same thing but is optimised for different databases. The SqlConnection deals directly with SQL Server, the OleDbConnection requires an OLEDB provider for the database to which you want to connect, while the OdbcConnection requires an ODBC driver for the database. SqlConnection is more efficient when using SQL Server because there is no OLEDB provider in between and you can make use of all possible SQL Server-specific optimisations. An OLEDB provider generally provides more efficient access than an ODBC driver. You really don't need to know more than that as a developer, and I don't. :) Others may know more of the low-level details but I don't really see that it is of anything other than academic value to the vast majority as it won't help you code any better.)
0
elevatedconsultingAuthor Commented:
Thanks for all your help here. You've given me a lot to consider and absorb. It's very much appreciated.
0
DhaestCommented:
Glad I could help you further ...
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ASP.NET

From novice to tech pro — start learning today.