使用XmlReader迭代代替类型化DataSet和IDataReader解析关联数据

         正在修改的第一个电子购物程序中需要处理购物订单和订单明细的读取/更新问题. 他们在数据库中分成两个表存储,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秒之多

另外,csdn的这个编辑器,真是难用.为什么不欢一个呢?freetextbox或者fckeditor都可以,关键在于,他们都支持多个浏览器,而csdn这个,在firefox下面根本无法使用.