[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

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

Posted on 2010-08-18
13
Medium Priority
?
437 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 2000 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 2000 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 2000 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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

Just a quick little trick I learned recently.  Now that I'm using jQuery with abandon in my asp.net applications, I have grown tired of the following syntax:      (CODE) I suppose it just offends my sense of decency to put inline VBScript on a…
Introduction Hi all and welcome to my first article on Experts Exchange. A while ago, someone asked me if i could do some tutorials on object oriented programming. I decided to do them on C#. Now you may ask me, why's that? Well, one of the re…
Want to learn how to record your desktop screen without having to use an outside camera. Click on this video and learn how to use the cool google extension called "Screencastify"! Step 1: Open a new google tab Step 2: Go to the left hand upper corn…
Is your data getting by on basic protection measures? In today’s climate of debilitating malware and ransomware—like WannaCry—that may not be enough. You need to establish more than basics, like a recovery plan that protects both data and endpoints.…
Suggested Courses

656 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