批量导入数据到SQL

主要方法有:Bcp,Bulk insert,dts,openrowset,adapter.update,insert等

基中对bulk,adapter.update,insert测试如下:

private   static   void  TestAdapterUpdate()
        {
            DataRow newRow;
            SqlConnection con  =   new  SqlConnection(ConfigurationManager.ConnectionStrings[ " testCon " ].ConnectionString);
            SqlCommand com  =   new  SqlCommand();
            com.Connection  =  con;
            com.UpdatedRowSource  =  UpdateRowSource.None;
            com.CommandText  =   " select top 1 * from Auto_ValueAdmin " ;
            SqlDataAdapter adapter = new  SqlDataAdapter(com);
            SqlCommandBuilder builder  =   new  SqlCommandBuilder(adapter);
            DataSet ds  =   new  DataSet();
            adapter.Fill(ds);
            DateTime bdt  =  DateTime.Now;
             for  ( int  i  =   0 ; i  <   2000 ; i ++ )
            {
                newRow  =  ds.Tables[ 0 ].NewRow();
                 foreach  (DataRow row  in  ds.Tables[ 0 ].Rows)
                {
                     foreach  (DataColumn col  in  ds.Tables[ 0 ].Columns)
                    {
                         if  ( ! col.ColumnName.Equals( " ID " , StringComparison.CurrentCultureIgnoreCase))
                        {
                            newRow[col]  =  row[col];
                        }
                    }
                }
                ds.Tables[ 0 ].Rows.Add(newRow);
            }
            DateTime mdt  =  DateTime.Now;
             double  mrs  =  ((TimeSpan)(mdt  -  bdt)).TotalMilliseconds;
            Console.WriteLine(mrs);
            
            
            adapter.UpdateBatchSize  =   500 ;
            adapter.Update(ds);
            DateTime edt  =  DateTime.Now;
             double  rs  =  ((TimeSpan)(edt  -  bdt)).TotalMilliseconds;
            Console.WriteLine(rs);
             double  real  =  ((TimeSpan)(edt  -  mdt)).TotalMilliseconds;
            Console.WriteLine(real);
            Console.WriteLine( " 任意键退出 " );
            Console.Read();
        }

         private   static   void  TestInsertSql()
        {
            StringBuilder sb  =   new  StringBuilder();
             string  startSql  =   " INSERT INTO [Auto_ValueAdmin] ([UserSiteID], [IsChecked], [TaskID], [IsExpire], [FontFile1], [FontFile2], [FontFile3], [FontFile4], [FontFile5], [FontFile6], [FontFile7], [FontFile8], [FontFile9], [FontFile10], [FontFile11], [FontFile12], [FontFile13], [FontFile14], [FontFile15], [FontFile16], [FontFile17], [FontFile18], [FontFile19], [FontFile20], [FontFile21], [FontFile22], [FontFile23], [FontFile24], [FontFile25], [FontFile26], [FontFile27], [FontFile28], [FontFile29], [FontFile30], [FontFile31], [FontFile32], [FontFile33], [FontFile34], [FontFile35], [FontFile36], [FontFile37], [FontFile38], [FontFile39], [FontFile40], [FontFile41], [FontFile42], [FontFile43], [FontFile44], [FontFile45], [FontFile46], [FontFile47], [FontFile48], [FontFile49], [FontFile50]) values( " ;
            List < string >  sqlList  =   new  List < string > ();
            SqlConnection con  =   new  SqlConnection(ConfigurationManager.ConnectionStrings[ " testCon " ].ConnectionString);
            SqlCommand com  =   new  SqlCommand();
            com.Connection  =  con;
            com.CommandText  =   " select top 1 * from Auto_ValueAdmin " ;
            SqlDataAdapter adapter  =   new  SqlDataAdapter(com);
            SqlCommandBuilder builder  =   new  SqlCommandBuilder(adapter);
            DataTable dt  =   new  DataTable();
            adapter.Fill(dt);
            DateTime bdt  =  DateTime.Now;
             for  ( int  i  =   0 ; i  <   2000 ; i ++ )
            {
                sb  =   new  StringBuilder();
                sb.Append(startSql);
                 foreach  (DataRow row  in  dt.Rows)
                {
                     foreach  (DataColumn col  in  dt.Columns)
                    {
                         if  ( ! col.ColumnName.Equals( " ID " , StringComparison.CurrentCultureIgnoreCase))
                        {
                            sb.Append( " ' " );
                            sb.Append(row[col]);
                            sb.Append( " ', " );
                        }
                    }
                }
                sb.Remove(sb.Length  -   1 ,  1 );
                sb.Append( " ) " );
                sqlList.Add(sb.ToString());
            }

             // SqlTransaction st = null;
            DateTime mdt  =  DateTime.Now;
             if  (con.State  ==  ConnectionState.Closed)
            {
                con.Open();
            }
            com  =   new  SqlCommand();
            com.Connection  =  con;
             // st = con.BeginTransaction();
             // com.Transaction = st;
             foreach  ( string  item  in  sqlList)
            {
                
                com.CommandText  =  item;

                 try
                {
                     if  (con.State  ==  ConnectionState.Closed)
                    {
                        con.Open();
                    }
                    com.ExecuteNonQuery();
                }
                 catch  { }
            }
             try
            {
                 // st.Commit();
            }
             catch  { }
            DateTime edt  =  DateTime.Now;
             double  mrs  =  ((TimeSpan)(edt  -  mdt)).TotalMilliseconds;
            Console.WriteLine(mrs);
             double  rs  =  ((TimeSpan)(edt  -  bdt)).TotalMilliseconds;
             double  real  =  ((TimeSpan)(edt  -  mdt)).TotalMilliseconds;
            Console.WriteLine(real);
            Console.WriteLine(rs);

            

            
        }

         private   static   void  BulkCopy()
        {
            DataRow newRow;
            SqlConnection con  =   new  SqlConnection(ConfigurationManager.ConnectionStrings[ " testCon " ].ConnectionString);
            SqlCommand com  =   new  SqlCommand();
            com.Connection  =  con;
            com.UpdatedRowSource  =  UpdateRowSource.None;
            com.CommandText  =   " select top 1 * from Auto_ValueAdmin " ;
            SqlDataAdapter adapter  =   new  SqlDataAdapter(com);
            SqlCommandBuilder builder  =   new  SqlCommandBuilder(adapter);
            DataSet ds  =   new  DataSet();
            adapter.Fill(ds);

            con.Open();
            SqlBulkCopy bc  =   new  SqlBulkCopy(con);
            bc.BulkCopyTimeout  =   360 ;
            bc.DestinationTableName  =   " Auto_ValueAdmin " ;

             foreach  (DataColumn item  in  ds.Tables[ 0 ].Columns)
            {
                 if  ( ! item.ColumnName.Equals( " ID " , StringComparison.CurrentCultureIgnoreCase))
                {
                    bc.ColumnMappings.Add(item.ColumnName, item.ColumnName);
                }
            }

            DateTime bdt  =  DateTime.Now;
             for  ( int  i  =   0 ; i  <   2000 ; i ++ )
            {
                newRow  =  ds.Tables[ 0 ].NewRow();
                 foreach  (DataRow row  in  ds.Tables[ 0 ].Rows)
                {
                     foreach  (DataColumn col  in  ds.Tables[ 0 ].Columns)
                    {
                         if  ( ! col.ColumnName.Equals( " ID " , StringComparison.CurrentCultureIgnoreCase))
                        {
                            newRow[col]  =  row[col];
                        }
                    }
                }
                ds.Tables[ 0 ].Rows.Add(newRow);
            }
            DateTime mdt  =  DateTime.Now;
             double  mrs  =  ((TimeSpan)(mdt  -  bdt)).TotalMilliseconds;
            Console.WriteLine(mrs);

            bc.WriteToServer(ds.Tables[ 0 ]);
            DateTime edt  =  DateTime.Now;
             double  rs  =  ((TimeSpan)(edt  -  bdt)).TotalMilliseconds;
            Console.WriteLine(rs);
             double  real  =  ((TimeSpan)(edt  -  mdt)).TotalMilliseconds;
            Console.WriteLine(real);
        }

 

经测试,bulk的速度最快