SqlBulkCopy—将数据表一次性复制到指定的数据表中

需求点

将一个List对象，批量(一次性)插入数据库中。

思路：

1         将List<T>转换为对应的DataTable

1.1       生成表结构

1.2       插入数据到表中

2         将DataTable批量插入库中。

 

操作：

1.1　生成表结构及插入数据

 

　   　　/// <summary>

        /// 将对象List转换为DataTable

        /// </summary>

        /// <param name="operatorLogList">操作日志列表</param>

        /// <returns>DataTable</returns>

        private static DataTable ListToDataTable(List<OperatorLogInfo> operatorLogList)

        {

            DataTable table = new DataTable();

 

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

            {

                return null;

            }

 

            // 生成表结构

            table.Columns.Add("code", typeof(Int32));

            table.Columns.Add("create_time", typeof(DateTime));

            table.Columns.Add("plan_code", typeof(Int32));

            table.Columns.Add("operation_description", typeof(String));

            table.Columns.Add("operation_type", typeof(Int32));

            table.Columns.Add("task_code", typeof(Int32));

            table.Columns.Add("task_assembly", typeof(String));

            table.Columns.Add("task_param", typeof(String));

 

            //　插入数据

            foreach (OperatorLogInfo operatorLogInfo in operatorLogList)

            {

                DataRow row = table.NewRow();

 

                row["code"] = operatorLogInfo.Code;

                row["create_time"] = operatorLogInfo.CreateTime;

                row["plan_code"] = operatorLogInfo.PlanId;

                row["operation_description"] = operatorLogInfo.OperationDescription;

                row["operation_type"] = (int)operatorLogInfo.OperatorType;

                row["task_code"] = operatorLogInfo.TaskId;

                row["task_assembly"] = operatorLogInfo.TaskAssembly;

                row["task_param"] = operatorLogInfo.TaskParamAssembly;

 

                table.Rows.Add(row);

            }

 

            return table;

        }

 

2将DataTable批量插入库中。

 　　 /// <summary>

        /// 批量写入操作日志

        /// </summary>

        /// <param name="operatorLogList">操作日志列表</param>

        /// <returns>是否成功</returns>

        public static bool InsertOperatorLogList(List<OperatorLogInfo> operatorLogList)

        {

            IDataHelper dbo = DataHelperFactory.Create(ConnectKey);

            bool result = false;

 

            // 生成表数据

            DataTable table = ListToDataTable(operatorLogList);

            if (null == table)

            {

                return result;

            }

 

            using (SqlConnection conn = (SqlConnection)dbo.CreateConnection)

            {

                conn.Open();

 

                SqlTransaction tran = conn.BeginTransaction();

 

                using (SqlBulkCopy bulkCopy = new SqlBulkCopy(conn, SqlBulkCopyOptions.CheckConstraints | SqlBulkCopyOptions.KeepIdentity, tran))

                {

                    try

                    {

                        // 涉及表名

                        bulkCopy.DestinationTableName = "pds_operation_log";

                        bulkCopy.WriteToServer(table);

 

                        tran.Commit();

                        result = true;

                    }

                    catch

                    {

                        tran.Rollback();

                        result = false;

                    }

                }

            }