使用XmlReader迭代代替類型化DataSet和IDataReader解析關聯資料

來源:互聯網
上載者:User
xml|資料 正在修改的第一個電子購物程式中需要處理購物訂單和訂單明細的讀取/更新問題. 他們在資料庫中分成兩個表格儲存體,Order和OrderDetails.現在要把他們的資料讀取出來,並建立一個Order的資料實體供程式使用. 一開始的時候我考慮使用IDataReader讀取資料,即開啟兩個資料庫連接,一個用來讀取Order,一個用來讀取OrderDetails.後來感覺麻煩, 就開始考慮使用xml shema建立一個資料集,然後利用SqlServer的xml功能返回一個XmlReader,讀取到這個類型化的DataSet中,但是因為涉及到DataSet,擔心效能的影響,寫了一個小程式來測試其效能.調試過程中,缺發現,最快的方式應該是自己使用XmlReader迭代返回的Xml 字串.程式如下: 首先在SqlServer中使用for xml的Sql語句產生Xml: ALTER PROCEDURE Shop_GetOrders2 ( @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 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 下面的程式則解析內容: [STAThread] static void Main(string[] args) { for(int i=0;i<200;i++) { System.Threading.Thread thread = new System.Threading.Thread(new System.Threading.ThreadStart(Run1)); thread.Start(); } done.Set(); Console.Read(); } public static System.Threading.ManualResetEvent done = new System.Threading.ManualResetEvent(false);//線程並發控制 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(); } } 這種方法在開啟200個線程並發讀取的時候,整整比用兩個Connection讀取資料快了1-2秒.比DataSet的ReadXml快了4秒之多


相關文章

E-Commerce Solutions

Leverage the same tools powering the Alibaba Ecosystem

Learn more >

Apsara Conference 2019

The Rise of Data Intelligence, September 25th - 27th, Hangzhou, China

Learn more >

Alibaba Cloud Free Trial

Learn and experience the power of Alibaba Cloud with a free trial worth $300-1200 USD

Learn more >

聯繫我們

該頁面正文內容均來源於網絡整理,並不代表阿里雲官方的觀點,該頁面所提到的產品和服務也與阿里云無關,如果該頁面內容對您造成了困擾,歡迎寫郵件給我們,收到郵件我們將在5個工作日內處理。

如果您發現本社區中有涉嫌抄襲的內容,歡迎發送郵件至: info-contact@alibabacloud.com 進行舉報並提供相關證據,工作人員會在 5 個工作天內聯絡您,一經查實,本站將立刻刪除涉嫌侵權內容。