ioSamurai ioSamurai - 7 months ago 13
SQL Question

Best method to populate XML from SQL query in ASP.NET?

I need to create a service that will return XML containing data from the database. So I am thinking about using an ASHX that will accept things like date range and POST an XML file back. I have dealt with pages pulling data from SQL Server and populating into a datagrid for visual display but never into XML for delivery, what is the best way to do this? Also if an ASHX and POST isn't the best method for delivery let me know... thanks!

EDIT: These answers are great and pointing me in the right direction. I should have also mentioned that the XML format has already been decided so I can't use any automatically generated one.

Answer

Combining linq2sqlwith the XElement classes, something along the lines:

var xmlContacts =
      new XElement("contacts",
         (from c in context.Contacts
         select new XElement("contact",
            new XElement
            {
               new XElement("name", c.Name), 
               new XElement("phone", c.Phone),
               new XElement("postal", c.Postal)
            )
         )
         ).ToArray()
      )
   );

Linq2sql will retrieve the data in a single call to the db, and the processing of the XML will be done at the business server. This splits the load better, since you don't have the sql server doing all the job.