批量更新数据库

 

 

   前言：最近做了个项目：把TXT文件中的资源信息全量更新到数据库中，拿到这个项目后，我首先考虑到性能问题应该是个大问题，于是想到了用批处理解决，但是批处理不能支持事物回滚，且只能一条一条SQL执行，效率虽然比直接更新数据库要快，且不占用CPU。经咨询有一种更好的方法，就是先用C#自带提供的方法SqlBulkCopy批量更新到临时表中，然后联表执行SQL进行更新，插入表的效率很高，50万数据只用5秒左右，更新一百万数据用了4分钟，比起批处理用12分钟效率高了很高，且如果插入失败还可以实物回滚，很好，嘿嘿

下面给出SqlBulkCopy的具体方法：

public class BulkCopyHelper

   {

       /// <summary>

       /// 入库的数据库表名称

       /// </summary>

       private string tableName;

       public string TableName

       {

           get { return tableName; }

           set { tableName = value; }

       }

 

 

       /// <summary>

       /// 待插入的数据源

       /// </summary>

       private DataTable insertSource;

       public System.Data.DataTable InsertSource

       {

           get { return insertSource; }

           set { insertSource = value; }

       }

 

 

       /// <summary>

       /// 数据库的连接串

       /// </summary>

       private string sqlConnection;

       public string SqlConnection

       {

           get { return sqlConnection; }

           set { sqlConnection = value; }

       }

 

 

       /// <summary>

       /// 数据属性与数据库字段对应关系

       /// Key:属性

       /// Value:数据库字段

       /// </summary>

       private Dictionary<string, string> columnNames;

       public Dictionary<string, string> ColumnNames

       {

           get { return columnNames; }

           set { columnNames = value; }

       }

 

       /// <summary>

       /// 每次入库的大小

       /// </summary>

       private int batchSize;

       public int BatchSize

       {

           get { return batchSize; }

           set { batchSize = value; }

       }

 

 

       public BulkCopyHelper()

       {

           

       }

       public  BulkCopyHelper(string tableName, string sqlConnection, int batchSize,DataTable insertSource)

       {

           this.tableName = tableName;

           this.sqlConnection = sqlConnection;

           this.insertSource = insertSource;

           this.batchSize = batchSize;

 

       }

 

 

        /// <summary>

        /// 自动映射字段名

        /// </summary>

        private void AutoSetColumns()

        {

            if (ColumnNames == null || ColumnNames.Count == 0)

            {

                ColumnNames = new Dictionary<string, string>();

                foreach (DataColumn column in InsertSource.Columns)

                {

                    ColumnNames.Add(column.ColumnName, column.ColumnName);

                }

            }

        }

 

        /// <summary>

        /// 批量的插入

        /// </summary>

        /// <returns></returns>

        public bool ExcuteBulkCopy()

        {

            //判断是否可以插入

            if (String.IsNullOrEmpty(TableName))

            {

                throw new SqlNullValueException("表名不能为空");

            }

            if (InsertSource == null || InsertSource.Rows.Count == 0)

            {

                return true;

            }

            //声明连接字符串

            SqlConnection conn = new SqlConnection(SqlConnection);

 

            conn.Open();

            SqlTransaction sqlbulkTransaction = conn.BeginTransaction();

 

            //声明 SqlBulkCopy

            SqlBulkCopy sqlBC = new SqlBulkCopy(conn, SqlBulkCopyOptions.CheckConstraints, sqlbulkTransaction);

 

            //设置一个批，写入多少条记录

            sqlBC.BatchSize = BatchSize != 0 ? BatchSize : 5000;

 

            //设置超时的秒数

            sqlBC.BulkCopyTimeout = 1200;

 

            //设置要写入的数据库

            sqlBC.DestinationTableName = TableName;

 

            //对应数据行

            AutoSetColumns();

            foreach (string columnName in ColumnNames.Keys)

            {

                sqlBC.ColumnMappings.Add(columnName, columnNames[columnName]);

            }

 

            try

            {

                //开始写入

                sqlBC.WriteToServer(InsertSource);

 

                //提交事务

                sqlbulkTransaction.Commit();

                return true;

            }

            catch (Exception exception)

            {

                sqlbulkTransaction.Rollback();

                throw;

            }

            finally

            {

                sqlBC.Close();

                conn.Close();

            }

        }

 

    }

 

 

调用方法：

 

 public bool InsertResourceInfoToDb(Hashtable table, ref string errorInfo)

        {

            try

            {

                //1、清空表中的数据

                string sql = "truncate table CPResourceNoAndHotNo";

                if (!db.ExecuteUpdate(sql, ref errorInfo))

                {

                    return false;

                }

                //转换成DataTable

                DataTable inserttTable = new DataTable();

                inserttTable.Columns.Add("CPResourceNo");

                inserttTable.Columns.Add("HotNum");

                //2、批量插入数据

                foreach (string cpReousrceNo in table.Keys)

                {

                    int hotNo;

                    string hotNoStr = table[cpReousrceNo].ToString();

                    if (!int.TryParse(hotNoStr.Split('.')[0], out hotNo))

                    {

                        continue;

                    }

                    DataRow row = inserttTable.NewRow();

                    row.BeginEdit();

                    row["CPResourceNo"] = cpReousrceNo;

                    row["HotNum"] = hotNo;

                    row.EndEdit();

                    inserttTable.Rows.Add(row);

                }

 

                bool res = db.BatchBulkCopyInsert(inserttTable, "CPResourceNoAndHotNo");

                inserttTable.Rows.Clear();

                return res;

            }

            catch (Exception ex)

            {

                return false;

            }

 

        }

注：CPResourceNoAndHotNo表有两个字段：CPResourceNo，HotNum