使用SqlBulkCopy批量导入数据

SqlBulkCopy批量导入数据库的方法如下

 

  #region 将抓取的Mysql数据导入Sqlserver
         ///   <summary>
         ///  将抓取的Mysql数据导入Sqlserver
         ///   </summary>
         ///   <param name="sqldb"> 数据集 DataTable </param>
         ///   <param name="sqlConn"> 数据库连接字符 </param>
         ///   <param name="tableName"> 表名 </param>
         ///   <param name="dicSource"> 数据库字段对应字典，如：dic.Add("UzaiTravelClassID", "UzaiTravelClassID");字典中key为DataTable中的字段名，Value为导入数据的表中对应的字段名 </param>
         ///   <param name="intBatchSize"> 每次导入数据数 </param>
         ///   <returns></returns>
         public  bool InsertSqlServer(DataTable sqldb,  string sqlConn,  string tableName, Dictionary< string,  string> dicSource,  int intBatchSize)
        {
             try
            {
                 // 数据批量导入sqlserver,创建实例
                System.Data.SqlClient.SqlBulkCopy sqlbulk =  new System.Data.SqlClient.SqlBulkCopy(sqlConn);
                sqlbulk.BatchSize = intBatchSize;
                 // 目标数据库表名
                sqlbulk.DestinationTableName = tableName;
                sqlbulk.SqlRowsCopied +=  new SqlRowsCopiedEventHandler(bulkCopy_SqlRowsCopied);
                 // 数据集字段索引与数据库字段索引映射
                 foreach (KeyValuePair< string,  string> item  in dicSource)
                {
                    sqlbulk.ColumnMappings.Add(item.Key, item.Value);
                }
                sqlbulk.NotifyAfter = intBatchSize;
                 // 导入
                sqlbulk.WriteToServer(sqldb);
                sqlbulk.Close();
                 return  true;
            }
             catch
            {
                 return  false;
            }

        }
         ///   <summary>
         ///  导入数据后触发的事件
         ///   </summary>
         ///   <param name="sender"></param>
         ///   <param name="e"></param>
         private  void bulkCopy_SqlRowsCopied( object sender, SqlRowsCopiedEventArgs e)
        {
            Console.WriteLine( " {0} Rows have been copied ", e.RowsCopied.ToString());
        }
         #endregion

 

使用范例如下:

数据集 DataTable的定义:

 

            DataTable dtResult =  new DataTable(); // 数据库查询的结果集
            DataTable dt =  new DataTable();
            dt.Columns.Add( " DicName ", typeof( string));
            dt.Columns.Add( " DicValue ",  typeof( string));
            dt.Columns.Add( " DES ",  typeof( string));
            dt.Columns.Add( " Type ",  typeof( string));

             foreach (DataRow dr  in dtResult.Rows)
            {
                DataRow drNew = dt.NewRow();
                drNew[ " DicName "] = dr[ " DicName1 "];
                drNew[ " DicValue "] = dr[ " DicValue1 "];
                drNew[ " DES "] = dr[ " DES1 "];
                drNew[ " Type "] = dr[ " Type1 "];
                dt.Rows.Add(drNew);
            }

Dictionary的定义：

            Dictionary< string,  string> dicColumns =  new Dictionary< string,  string>();
             string strCurrentTable =  " UzaiSearchDictionary ";//表名
            dicColumns.Add( " DicName ",  " DicName2 ");//key:dt 中的列名，value:表中对应的列名
            dicColumns.Add( " DicValue ",  " DicValue2 ");
            dicColumns.Add( " DES ",  " DES2 ");
            dicColumns.Add( " Type ",  " Type2 ");
            InsertSqlServer(dt, SqlHelper.WebSearchUpdateConStr1, strCurrentTable, dicColumns,  1000);

 

转载于:https://www.cnblogs.com/ruolinzhanyuan/archive/2012/03/23/2413405.html