Solved

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

Posted on 2010-08-18
13
433 Views
Last Modified: 2012-05-10
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

0
Comment
Question by:elevatedconsulting
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 5
13 Comments
 
LVL 53

Accepted Solution

by:
Dhaest earned 500 total points
ID: 33464562
0
 
LVL 53

Expert Comment

by:Dhaest
ID: 33464567
Retrieving Data as XML from SQL Server
http://articles.sitepoint.com/article/data-as-xml-sql-server
0
 

Author Comment

by:elevatedconsulting
ID: 33464784
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 53

Assisted Solution

by:Dhaest
Dhaest earned 500 total points
ID: 33464851
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
 
LVL 53

Expert Comment

by:Dhaest
ID: 33464862
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
 

Author Comment

by:elevatedconsulting
ID: 33466106
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
 
LVL 53

Expert Comment

by:Dhaest
ID: 33472276
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
 

Author Comment

by:elevatedconsulting
ID: 33474046
Right now I am testing with Access set up with a dsn. Later I'll be working with an mssql server by dsn.
0
 
LVL 53

Expert Comment

by:Dhaest
ID: 33474069
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
 

Author Comment

by:elevatedconsulting
ID: 33474475
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
 
LVL 53

Assisted Solution

by:Dhaest
Dhaest earned 500 total points
ID: 33474519
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
 

Author Comment

by:elevatedconsulting
ID: 33477858
Thanks for all your help here. You've given me a lot to consider and absorb. It's very much appreciated.
0
 
LVL 53

Expert Comment

by:Dhaest
ID: 33482538
Glad I could help you further ...
0

Featured Post

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

User art_snob (http://www.experts-exchange.com/M_6114203.html) encountered strange behavior of Android Web browser on his Mobile Web site. It took a while to find the true cause. It happens so, that the Android Web browser (at least up to OS ver. 2.…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Michael from AdRem Software outlines event notifications and Automatic Corrective Actions in network monitoring. Automatic Corrective Actions are scripts, which can automatically run upon discovery of a certain undesirable condition in your network.…
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…

728 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question