Solved

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

Posted on 2010-08-18
13
429 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
  • 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
ScreenConnect 6.0 Free Trial

Discover new time-saving features in one game-changing release, ScreenConnect 6.0, based on partner feedback. New features include a redesigned UI, app configurations and chat acknowledgement to improve customer engagement!

 
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

Problems using Powershell and Active Directory?

Managing Active Directory does not always have to be complicated.  If you are spending more time trying instead of doing, then it's time to look at something else. For nearly 20 years, AD admins around the world have used one tool for day-to-day AD management: Hyena. Discover why

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Deploying to Azure 3 29
Cant save 3D 4 19
Getting error in connectionstring with Excel. 30 32
c# - Best approach for objects in functions 1 10
We all know that functional code is the leg that any good program stands on when it comes right down to it, however, if your program lacks a good user interface your product may not have the appeal needed to keep your customers happy. This issue can…
Exception Handling is in the core of any application that is able to dignify its name. In this article, I'll guide you through the process of writing a DRY (Don't Repeat Yourself) Exception Handling mechanism, using Aspect Oriented Programming.
Along with being a a promotional video for my three-day Annielytics Dashboard Seminor, this Micro Tutorial is an intro to Google Analytics API data.
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

773 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