ASP.NET MVC 4 Web API + XmlDocument + SQL FOR XML PATH

ASP.NET MVC 4 Web API + XmlDocument + SQL FOR XML PATH

   ASP.NET – WebAPI Restful Return Data Structure
 
   {"Inventory":
      {"Item":[
         {"ID":"1","Model":"Cisco XXXXX","System":"Server"},
         {"ID":"2","Model":"Cisco XXX","System":"Switch"},
         {"ID":"3","Model":"Cisco XXXX","System":"Switch"},
         {"ID":"4","Model":"Cisco XXXXXXXX","System":"Switch"},
         {"ID":"5","Model":"APC XXXX","System":"Others"},
         {"ID":"6","Model":"HP XXXXXXX","System":"Networked Storage"},
         {"ID":"7","Model":"HP XXXXXXXX","System":"Server"},
         {"ID":"8","Model":"Cisco XXXXX","System":"Switch"},
         {"ID":"9","Model":"Cisco XXXXXXXX","System":"Switch"}
      ]}
   }
 
   SQL Return Data Structure
 
   SELECT [ID], [Model], [System] FROM [dbo].[Inventory] WHERE [ID] < 10 FOR XML PATH ('Item'), root ('Inventory')
 
 
   <Inventory>
      <Item>
         <ID>1</ID>
         <Model>Cisco XXXXX</Model>
         <System>Server</System>
      </Item>
      <Item>
         <ID>2</ID>
         <Model>Cisco XXX</Model>
         <System>Switch</System>
      </Item>
      <Item>
         <ID>3</ID>
         <Model>Cisco XXXX</Model>
         <System>Switch</System>
      </Item>
      <Item>
         <ID>4</ID>
         <Model>Cisco XXXXXXXX</Model>
         <System>Switch</System>
      </Item>
      <Item>
         <ID>5</ID>
         <Model>APC XXXX</Model>
         <System>Others</System>
      </Item>
      <Item>
         <ID>6</ID>
         <Model>HP XXXXXXX</Model>
         <System>Networked Storage</System>
      </Item>
      <Item>
         <ID>7</ID>
         <Model>HP XXXXXXXX</Model>
         <System>Server</System>
      </Item>
      <Item>
         <ID>8</ID>
         <Model>Cisco XXXXX</Model>
         <System>Switch</System>
      </Item>
      <Item>
         <ID>9</ID>
         <Model>Cisco XXXXXXXX</Model>
         <System>Switch</System>
      </Item>
   </Inventory>
 
   DAO.cs ( DAO )
 
   using System;
   using System.IO;
   using System.Text;
   using System.Data.SqlClient;
   using System.Xml;
 
   public XDocument getRecord()
   {
 
      String connectionString = "XXX XXXX";
      SqlConnection con = new SqlConnection(connectionString);
      con.Open();
 
      StringBuilder sb = new StringBuilder();
      sb.Append("SELECT [ID], [Model], [System] FROM [dbo].[Inventory] WHERE [ID] < 10 FOR XML PATH (‘Item’), root (‘Inventory’)");
 
      SqlCommand cmd = new SqlCommand(sb.ToString(), con);
      SqlDataReader dr = cmd.ExecuteReader();
 
      XmlDocument xdoc = new XmlDocument();
 
      String Result = "";
 
      if (dr.HasRows)
      {
         while (dr.Read())
         {
            Result += dr[0].ToString();
         }
      }
 
      con.Close();
      con.Dispose();
 
      xdoc.LoadXml(Result);
 
      return xdoc;
 
   }
 
   RestController.cs ( Restful Controller )
 
   using System;
   using System.Web.Http;
   using DAO;
   using System.Xml;
 
   public class RestController : ApiController
   {
      // GET api/rest
      public XmlDocument Get()
      {
         InventoryDAO dao = new InventoryDAO();
 
         XDocument doc = dao.getRecord();
 
         return doc;
      }
 
      … …
 
   }