Solved

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

Posted on 2010-08-18
13
427 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
 
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
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 
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

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

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…
Problem Hi all,    While many today have fast Internet connection, there are many still who do not, or are connecting through devices with a slower connect, so light web pages and fast load times are still popular.    If your ASP.NET page …
This video discusses moving either the default database or any database to a new volume.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

757 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now