正在修改的第一個電子購物程序中需要處理購物訂單和訂單明細的讀取/更新問題. 他們在數據庫中分成兩個表存儲,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秒之多
