Resolving associated data using XmlReader iterations instead of typed datasets and IDataReader

xml| data is being modified in the first electronic shopping program that needs to handle the read/update issues for purchase orders and order details. They are divided into two tables stored in the database, order and OrderDetails. Now we want to read their data and create a data entity for the program to use. At first I considered using IDataReader to read data, which opened two database connections, one to read the order, and one to read the OrderDetails. Later feel the trouble, began to consider using XML Shema to create a dataset, SQL Server's XML functionality is then used to return a XmlReader to the typed DataSet, but because of the dataset, fearing the impact of the performance, a small program is written to test its performance. During debugging, missing discovery, The quickest way to do this is to use the XML string returned by your own XmlReader iteration. The procedure is as follows: first generate Xml:alter PROCEDURE shop_getorders2 using the SQL statement for XML in SQL Server (@BlogID bigint) as SELECT 1 as Tag, NULL as Parent, O.orderid as [order!1! OrderID], O.userid as [Order!1!userid], o.useremail as [Order!1!useremail], o.usermobile as [order!1! Usermobile], O.userim as [Order!1!userim], o.receipt as [Order!1!receipt], o.city as [order!1!city], o.address as [Order!1 !address], o.postcode as [Order!1!postcode], O.usertel as [Order!1!usertel], o.deliverymethod as [order!1! Deliverymethod], o.deliverfee as [Order!1!deliverfee], o.paymethod as [Order!1!paymethod], o.realname as [Order!1! Realname], Case O.VIP when 1 Then ' true ' when 0 Then ' FalsE ' end as [Order!1!vip], o.blogid as [order!1! BlogID], o.usertitle as [order!1! Usertitle], o.insertdate as [order!1! Insertdate], NULL as [detail!2! ProductID], NULL as [detail!2! UnitPrice], NULL as [detail!2! Unitscore], NULL as [detail!2! Number] from the Orders O WHERE o.blogid = @BlogID UNION all SELECT 2 as Tag, 1 as Parent, O.orderid as [order!1! OrderID], NULL as [Order!1!userid], NULL as [Order!1!useremail], null as [order!1! Usermobile], NULL as [Order!1!userim], NULL as [Order!1!receipt], NULL as [order!1!city], NULL as [order!1!address], NULL As [Order!1!postcode], NULL as [Order!1!usertel], NULL as [Order!1!deliverymethod], NULL as [Order!1!deliverfee], NULL as [Order!1!paymethod], NULL as [Order!1!realname], NULL as [ORDER!1!VIP], null as [order!1! BlogID], NULL as [order!1! Usertitle], NULL as [order!1! Insertdate], OD. ProductID as [detail!2! ProductID], OD. UnitPrice as [detail!2! UnitPrice], OD. Unitscore as [detail!2! Unitscore], OD. Number as [detail!2! Number] from Orders O, OrderDetailS od WHERE o.orderid = od. OrderID and O.blogid = @BlogID for XML EXPLICIT the following program resolves the content: [STAThread] static void Main (string[] args) {for (int i=0;i&lt 200;i++) {System.Threading.Thread Thread = new System.Threading.Thread (new System.Threading.ThreadStart (RUN1)); Thread. Start (); Done. Set (); Console.read (); The public static System.Threading.ManualResetEvent is done = new System.Threading.ManualResetEvent (false);//thread concurrency control public static void Run1 () {done. WaitOne (); SqlConnection conn = new SqlConnection ("Uid=sa;pwd=sa;database=shop;server=chris"); try {SqlCommand COMM = conn. CreateCommand (); Comm.commandtext = "Shop_getorders2"; Comm.commandtype = CommandType.StoredProcedure; Comm. Parameters.Add (New SqlParameter ("@BlogID", 1)); Conn. Open (); System.Xml.XmlReader reader = Comm. ExecuteXmlReader (); Ordersdata ds = new Ordersdata (); Console.WriteLine (DateTime.Now.ToString () + "T"); while (reader. Read ()) {if (reader). Nodetype==system.xml.xmlnodetype.element&&reader. localname== "Order") {REader. GetAttribute ("OrderID"); Reader. GetAttribute ("userid"); Console.WriteLine (reader. GetAttribute ("useremail") + "------------------------------------------------------------"); Reader. GetAttribute ("Usermobile"); Reader. GetAttribute ("Userim"); Reader. GetAttribute ("receipt"); Reader. GetAttribute ("City"); Reader. GetAttribute ("Address"); Reader. GetAttribute ("postcode"); Reader. GetAttribute ("Usertel"); Reader. GetAttribute ("Deliverymethod"); Reader. GetAttribute ("Deliverfee"); Reader. GetAttribute ("Paymethod"); Reader. GetAttribute ("VIP"); Reader. GetAttribute ("BlogID"); Reader. GetAttribute ("Insertdate"); Reader. GetAttribute ("Usertitle"); while (reader. Read ()) {if (reader). localname== "Order" &&reader. Nodetype==system.xml.xmlnodetype.endelement) {break;} if (reader. Nodetype==system.xml.xmlnodetype.element && Reader. localname== "Detail") {reader. GetAttribute ("ProductID"); Console.WriteLine (reader. GetAttribute ("UnitPrice")); Reader. GetAttribute ("Unitscore"); Reader. GetAttribute ("NumbEr "); finally {conn}}}}. Close (); This method is 1-2 seconds faster than reading data with two connection when opening 200 threads concurrently. It's faster than the dataset's ReadXml 4 seconds.