批量导入数据方法分析。（转帖）

上篇提要：

       接到一个需求，实现一个小功能—导入Excel，想想诸位活跃于.NET平台上的兄弟们，其中应该有相当一部分是从事如信息系统类开发的，所以小弟在这里姑且臭屁一下导入Excel的几种实现方法，如有错误之处，烦请大虾指正。

       1、 Excel导入 — 循环执行插入操作，批量导入数据

       2、 Excel导入 — 使用Linq提供的InsertAllOnSubmit方法，批量导入数据

       3、 Excel导入 — 使用SqlBulkCopy的WriteToServer方法，批量导入数据

      本篇重点： 

      在上一篇中谈到了.NET的三种数据拷贝方法，但是并没有做性能测试，今天闲来无事，索性做下性能测试，测试数据量10W条。

      机器配置及开发工具：CPU/P E5200 2.5GHZ

                    内存/2G

                    操作系统/Microsoft WINDOWS XP SP3

                    数据库/Microsoft SQL SERVER 2005

                    Framework/.Net Framework 3.5 SP1

                    开发工具/Microsoft  VS 2008 SP1

      原始数据表：IPInfo[IPid（Identity）,IPFrom,IPTo,IPLocation,IPCity,IPToNumber,IPFromNumber]

      数据条数：345000条（这里用10W条来测试）

             

      导入数据表：IPInfo1[IPid（Identity）,IPFrom,IPTo,IPLocation,IPCity,IPToNumber,IPFromNumber]

      数据条数：0条，每次导入前，均清空IPInfo1表中数据

      首先是测试第一种方法，也就是直接用ADO.NET执行循环插入操作，批量导入数据，这里先贴上CODE：

代码

public   static   void  InsertToIPInfo1_ADO(DataTable dt)
        {
             using  (SqlConnection conn  =   new  SqlConnection( " Data Source=WSH-S0904003;Initial Catalog=GroupSell;User ID=sa;Password=123 " ))
            {
                conn.Open();
                 string  strSql  =   " Insert Into IPInfo1(IPFrom,IPTo,IPLocation,IPCity,IPToNumber,IPFromNumber) values (@IPFrom,@IPTo,@IPLocation,@IPCity,@IPToNumber,@IPFromNumber) " ;
                 for  ( int  i  =   0 ; i  <  dt.Rows.Count; i ++ )
                {
                    SqlCommand Comm  =  conn.CreateCommand();
                    Comm.CommandText  =  strSql;
                    Comm.CommandType  =  CommandType.Text;
                    Comm.Parameters.Add( " @IPFrom " , SqlDbType.VarChar).Value  =  dt.Rows[i][ " IPFrom " ];
                    Comm.Parameters.Add( " @IPTo " , SqlDbType.VarChar).Value  =  dt.Rows[i][ " IPTo " ];
                    Comm.Parameters.Add( " @IPLocation " , SqlDbType.VarChar).Value  =  dt.Rows[i][ " IPLocation " ];
                    Comm.Parameters.Add( " @IPCity " , SqlDbType.VarChar).Value  =  dt.Rows[i][ " IPCity " ];
                    Comm.Parameters.Add( " @IPToNumber " , SqlDbType.VarChar).Value  =  dt.Rows[i][ " IPToNumber " ];
                    Comm.Parameters.Add( " @IPFromNumber " , SqlDbType.VarChar).Value  =  dt.Rows[i][ " IPFromNumber " ];
                    Comm.ExecuteNonQuery();
                }
            }
        }

 

         下面是调用方法：

代码

class  Program
    {
         static   void  Main( string [] args)
        {
             // 取10W数据
            DataTable dt  =  IpInfo_BLL.get10WData();
             // 开始时间
            Console.WriteLine(DateTime.Now.ToString());
            DateTime db  =  DateTime.Now;
            
             // Linq InsertAllOnSubmit()
            IpInfo_BLL.InsertToIPInfo1_Linq(dt);
            
             // ADO Insert
             // IpInfo_BLL.InsertToIPInfo1_ADO(dt);

             // SqlBulkCopy 
             // IpInfo_BLL.InsertToIPInfo1_SqlCopy(dt);

             // 时间差值
            Console.WriteLine(DateTime.Now  -  db);
        }
        
    }

 

          下面是取DataTable数据的方法，索性也贴上来：

代码

public   static  DataTable get10WData() 
        {
             using  (SqlConnection conn  =   new  SqlConnection( " Data Source=WSH-S0904003;Initial Catalog=GroupSell;User ID=sa;Password=123 " ))
            {
                 string  strSql  =   " select top(100000) * from IPInfo " ;
                DataSet ds  =   new  DataSet();
                SqlDataAdapter adaper  =   new  SqlDataAdapter(strSql, conn);
                adaper.Fill(ds);
                 return  ds.Tables[ 0 ];
            }

        }

 

              下面上结果：

           耗时2分34秒8281250（小数部分）

           下面我们测试第二种方法也就是使用Linq的InsertAllOnSubmit的方法实现数据拷贝：

代码

public   static   void  InsertToIPInfo1_Linq(DataTable dt)
        {
             using  (IPInfo1DataContext db  =   new  IPInfo1DataContext()) 
            {
                var query  =  from q  in  dt.AsEnumerable()
                            select  new
                            {
                                IPFrom  =  q[ " IPFrom " ].ToString().Trim(),
                                IPTo  =  q[ " IPTo " ].ToString().Trim(),
                                IPLocation  =  q[ " IPLocation " ].ToString().Trim(),
                                IPCity  =  q[ " IPCity " ].ToString().Trim(),
                                IPToNumber  =  q[ " IPToNumber " ].ToString().Trim(),
                                IPFromNumber  =  q[ " IPFromNumber " ].ToString().Trim()
                            };
                List < IPInfo1 >  list  =   new  List < IPInfo1 > ();
                 foreach  (var q  in  query)
                {
                    IPInfo1 Entity  =   new  IPInfo1();
                    Entity.IPCity  =  q.IPCity;
                    Entity.IPFrom  =  q.IPFrom;
                    Entity.IPFromNumber  =  q.IPFromNumber;
                    Entity.IPLocation  =  q.IPLocation;
                    Entity.IPTo  =  q.IPTo;
                    Entity.IPToNumber  =  q.IPToNumber;
                    list.Add(Entity);
                }
                db.IPInfo1.InsertAllOnSubmit(list);
                db.SubmitChanges();
            }
        }

 

            下面上结果：

          耗时1分50秒4843750（小数部分）

          下面我们来看第三种方法，使用SqlBulkCopy的WriteToServer方法，批量导入数据，大家要做好心理准备哦！

代码

public   static   void  InsertToIPInfo1_SqlCopy(DataTable dt) 
        {
             using  (SqlBulkCopy bulkCopy  =   new  SqlBulkCopy( " Data Source=WSH-S0904003;Initial Catalog=GroupSell;User ID=sa;Password=123 " ))
            {
                bulkCopy.DestinationTableName  =   " IPInfo1 " ;
                bulkCopy.WriteToServer(dt);
            }
        }

 

          下面上测试结果：

            耗时：0分5秒9532774（小数部分）

        测试总结：

        测试结果显示选择使用ADO.NET的基本方法来复制大批数据是错误的，也是耗时最长的，这里的测试是在本机进行的，如果数据库的Server跟网站的Server不在同一台服务器上，将会花费更多的时间。当然Linq的效率要比前者高些，略高2/5，但Linq操作起来便捷，对效率要求不是太苛刻的朋友，用Linq导入数据也是一种不错的选择，当然了，最棒的方法还是SqlBulkCopy，但是这种做法的缺点是要求EXCEL的格式必须与要导入的Table完全一致而且还要删除多余的Sheet。所以测试结果如上，朋友们可以视情况而定。