When you need load POCO objects from a SQL Server database in a faster way than LinqToSQL, you may consider using SQL Server’s XML features. The FOR XML PATH command can be a really powerful tool to convert your relational data into XML that C# can de-serialize into C# plain objects. The key is to find how C# serializes objects to XML. In order to do that, I did a small test:
public class Store
{
public string Name { get; set; }
public int Sales { get; set; }
public List<StoreContact> StoreContact { get; set; }
}
public class StoreContact
{
public int ContactId { get; set; }
public int ContactTypeID { get; set; }
}
public static void TestSaveToXML(string path)
{
XmlSerializer serializer = new XmlSerializer(typeof(List<Store>));
TextWriter writer = new StreamWriter(path);
List<Store> stores = new List<Store>();
List<StoreContact> stores_contcts = new List<StoreContact>();
stores_contcts.Add(new StoreContact() { ContactId = 2, ContactTypeID = 4 }); ;
stores_contcts.Add(new StoreContact() { ContactId = 1, ContactTypeID = 6 });
stores.Add(new Store() { Name = "Fleas", Sales = 9989, StoreContact = stores_contcts });
stores.Add(new Store() { Name = "Lowe", Sales = 9989, StoreContact = stores_contcts });
stores.Add(new Store() { Name = "Juns", Sales = 9989, StoreContact = stores_contcts});
serializer.Serialize(writer, stores);
writer.Close();
//open it
serializer = new XmlSerializer(typeof(List<Store>));
TextReader reader = new StreamReader(path);
List<Store> po_list = (List<Store>)serializer.Deserialize(reader);
reader.Close();
}I then opened the file created and looked at the element names. With that information, I wrote a View in the AdventureWorks database containing the following query (note the use of XML PATH):
select (SELECT s.[Name] AS "Name", (select
--Get the store contacts belonging to the Store
c.[ContactID] AS "ContactId",
c.[ContactTypeID] AS "ContactTypeID"
from [Sales].[StoreContact] c where s.[CustomerID] =
c.[CustomerID] for xml path('StoreContact'), type)
as "ArrayOfStore"
FROM [Sales].[Store] s
ORDER BY s.[Name]
FOR XML PATH('Store')) as "xml";When executed, the view returns a column (“xml”) containing the XML ready to be de-serialized by C#. The following code contains the class SQLServerXMLOrm to help you convert the results of sql queries like the above into a List of C# objects. The code also contains a class used to test SQLServerXMLOrm:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlClient;
using Mvc2Test.Models.physical;
using System.Xml.Serialization;
using System.IO;
namespace Mvc2Test.Models.logical
{
/// <summary>
///
/// </summary>
/// <typeparam name="PocoType">Must be a class that contains the fields and object returned by the SQL query. Its fields must have
/// the same exact name as in SQL. See sample class TestXML for a valid query and poco classes </typeparam>
public class SQLServerXMLOrm<PocoType>
{
/// <summary>
/// Runs a query in SQL Server and converts its XML result into a list of POCO objects.
/// The query MUST use "FOR XML PATH" to be able to return an XML result.
/// This method can help you automate the process of having to map stright relational records into object
/// that contain children objects.
/// </summary>
/// <param name="connection_string">Connection string to a SQL server</param>
/// <param name="sql_query">SQL query that returns an XML result</param>
/// <returns></returns>
public List<PocoType> GetObjectsFromSQLXML(string connection_string, string sql_query)
{
string xml = "";
using (SqlConnection cn = new SqlConnection(connection_string))
{
cn.Open();
using (SqlCommand cmd = new SqlCommand(sql_query, cn))
{
using (SqlDataReader dr = cmd.ExecuteReader())
{
while (dr.Read())
{
xml = dr[0].ToString();
}
}
}
cn.Close();
}
xml = "<ArrayOfStore>" + xml + "</ArrayOfStore>";
return TransformXML(xml);
}
public List<PocoType> TransformXML(string xml)
{
XmlSerializer serializer = new XmlSerializer(typeof(List<PocoType>));
TextReader reader = new StringReader(xml);
List<PocoType> po_list = (List<PocoType>)serializer.Deserialize(reader);
reader.Close();
return po_list;
}
}
public class TestXML
{
//The following classes were created manually and their properies' names must match the name of the fields is SQL
public class Store
{
public string Name { get; set; }
public int Sales { get; set; }
public List<StoreContact> StoreContact { get; set; }
}
public class StoreContact
{
public int ContactId { get; set; }
public int ContactTypeID { get; set; }
}
public static void TestSaveToXML(string path)
{
XmlSerializer serializer = new XmlSerializer(typeof(List<Store>));
TextWriter writer = new StreamWriter(path);
List<Store> stores = new List<Store>();
List<StoreContact> stores_contcts = new List<StoreContact>();
stores_contcts.Add(new StoreContact() { ContactId = 2, ContactTypeID = 4 }); ;
stores_contcts.Add(new StoreContact() { ContactId = 1, ContactTypeID = 6 });
stores.Add(new Store() { Name = "Fleas", Sales = 9989, StoreContact = stores_contcts });
stores.Add(new Store() { Name = "Lowe", Sales = 9989, StoreContact = stores_contcts });
stores.Add(new Store() { Name = "Juns", Sales = 9989, StoreContact = stores_contcts});
serializer.Serialize(writer, stores);
writer.Close();
//open it
serializer = new XmlSerializer(typeof(List<Store>));
TextReader reader = new StreamReader(path);
List<Store> po_list = (List<Store>)serializer.Deserialize(reader);
reader.Close();
}
/// <summary>
/// </summary>
/// <returns></returns>
public static void LoadFromXML()
{
DateTime beg = DateTime.Now;
SQLServerXMLOrm<Store> orm = new SQLServerXMLOrm<Store>();
var list = orm.GetObjectsFromSQLXML((new AwDataContext()).Connection.ConnectionString, "select * from vXMLTest");
DateTime end = DateTime.Now;
double timetaken = (end - beg).TotalMilliseconds;
int size = list.Count;
}
public static void UseLinq()
{
DateTime beg = DateTime.Now;
List<Store> stores = new List<Store>();
Store curr_store;
var db = new AwDataContext();
var store_grps = (from rows in db.vTests orderby rows.Name select rows).GroupBy(r => r.Name).ToList();
DateTime end = DateTime.Now;
double timetaken = (end - beg).TotalMilliseconds;
foreach (var g in store_grps)
{
curr_store = new Store();
}
}
}
}All the code above is under the MIT license.
Comments