ASP.NET MVC 4 Web API + XDocument + 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.Linq; 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(); String Result = ""; if (dr.HasRows) { while (dr.Read()) { Result += dr[0].ToString(); } } con.Close(); con.Dispose(); return XDocument.Load(new StringReader(Result)); } |
RestController.cs ( Restful Controller ) |
using System; using System.Web.Http; using DAO; using System.Xml.Linq; public class RestController : ApiController { // GET api/rest public XDocument Get() { InventoryDAO dao = new InventoryDAO(); XDocument doc = dao.getRecord(); return doc; } … … } |
Remarks : I have tried the Similar Practice to Execute a SQL Statement on a 2500 Records ( 20 Fields ) Table with or without FOR XML PATH for 10 Times.
The Average Query Result ( From SQL Server Management Studio – Include Client Statement ) is as Follow.
with FOR XML PATH : 23.8 ms
without FOR XML PATH : 18.2 ms
It is Different From My Expectation that the Query with FOR XML PATH would be fast than the query without FOR XML PATH.
DBA’s Recommendation on FOR XML PATH