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