批量插入或更新数据

/// <summary>
        /// 大数据单表批量插入,带事务
        /// </summary>
        /// <param name="keepID"></param>
        /// <param name="tableName"></param>
        /// <param name="dt"></param>
        /// <param name="connectionString"></param>
        /// <returns></returns>
        public static bool MsSqlBulkCopyInsert(bool keepID, string tableName, DataTable dt, ref string msg, string connectionString = null)
        {
            if (dt == null || dt.Rows.Count <= 0)
            {
                msg = "找不到数据";
                return true;
            }
            using (SqlConnection conn = string.IsNullOrWhiteSpace(connectionString) ? new SqlConnection(MSSQLHelper.connectionString) : new SqlConnection(connectionString))
            {
                conn.Open();
                using (SqlTransaction trans = conn.BeginTransaction())
                {
                    try
                    {
                        using (SqlBulkCopy sbc = new SqlBulkCopy(conn, (keepID ? SqlBulkCopyOptions.KeepIdentity : SqlBulkCopyOptions.Default) | SqlBulkCopyOptions.FireTriggers, trans))
                        {
                            sbc.BatchSize = dt.Rows.Count;   //每一批次中的行数
                            //Bulkcopy 无法判断数据是否已经存在,如果在bulkcopy时发生数据库返回错误,此次的bulkcopy操作会rollback。建议在数据库中添加临时表,bulkcopy的destination设置为临时表,bulkcopy完成后运行从临时表到真正表的插入操作,筛选duplicate数据并删除
                            sbc.DestinationTableName = string.Concat("[", tableName + "]");
                            sbc.BulkCopyTimeout = 300;//超时之前操作完成所允许的秒数,大批量数量需要的时长5分钟。 报错:“超时时间已到。在操作完成之前超时时间已过或服务器未响应
                            foreach (DataColumn column in dt.Columns)
                            {
                                sbc.ColumnMappings.Add(column.ColumnName, column.ColumnName);
                            }
                            sbc.WriteToServer(dt);
                            trans.Commit();
                            return true;
                        }
                    }
                    catch (Exception err)
                    {
                        trans.Rollback();//回滚事务
                        msg = err.Message;
                    }
                }
            }
            return false;
        }

        /// <summary>
        /// 大数据多表批量插入,带事务
        /// </summary>
        /// <param name="keepID"></param>
        /// <param name="tableName"></param>
        /// <param name="dt"></param>
        /// <param name="connectionString"></param>
        /// <returns></returns>
        public static bool MsSqlBulkCopyManyInsert(bool keepID, Dictionary<string, DataTable> dic, ref string msg, string connectionString = null)
        {
            if (dic == null || dic.Count <= 0)
            {
                msg = "找不到数据";
                return true;
            }
            using (SqlConnection conn = string.IsNullOrWhiteSpace(connectionString) ? new SqlConnection(MSSQLHelper.connectionString) : new SqlConnection(connectionString))
            {
                conn.Open();
                using (SqlTransaction trans = conn.BeginTransaction())
                {
                    try
                    {
                        using (SqlBulkCopy sbc = new SqlBulkCopy(conn, (keepID ? SqlBulkCopyOptions.KeepIdentity : SqlBulkCopyOptions.Default) | SqlBulkCopyOptions.FireTriggers, trans))
                        {
                            foreach (var item in dic)
                            {
                                string tableName = item.Key;
                                DataTable dt = item.Value;
                                if (dt == null || dt.Rows.Count <= 0)
                                {
                                    continue;
                                }
                                sbc.BatchSize = dt.Rows.Count;   //每一批次中的行数
                                                                 //Bulkcopy 无法判断数据是否已经存在,如果在bulkcopy时发生数据库返回错误,此次的bulkcopy操作会rollback。建议在数据库中添加临时表,bulkcopy的destination设置为临时表,bulkcopy完成后运行从临时表到真正表的插入操作,筛选duplicate数据并删除
                                sbc.DestinationTableName = string.Concat("[", tableName + "]");
                                sbc.BulkCopyTimeout = 300;//超时之前操作完成所允许的秒数,大批量数量需要的时长5分钟。 报错:“超时时间已到。在操作完成之前超时时间已过或服务器未响应
                                sbc.ColumnMappings.Clear();
                                foreach (DataColumn column in dt.Columns)
                                {
                                    sbc.ColumnMappings.Add(column.ColumnName, column.ColumnName);
                                }
                                sbc.WriteToServer(dt);
                            }
                            trans.Commit();
                            return true;
                        }
                    }
                    catch (Exception err)
                    {
                        trans.Rollback();//回滚事务
                        msg = err.Message;
                        return false;
                    }
                }
            }
        }

        /// <summary>
        /// 大数据批量更新
        /// </summary>
        /// <param name="table"></param>
        /// <param name="tableName"></param>
        /// <param name="limitWhere"></param>
        /// <param name="onceUpdateNumber"></param>
        /// <param name="connectionString"></param>
        /// <returns></returns>
        public static int MsSqlUpdate(DataTable table, string tableName, string limitWhere, int onceUpdateNumber = 5000, string connectionString = null)
        { 

            int result = -1;
            SqlConnection conn = new SqlConnection(string.IsNullOrWhiteSpace(connectionString) ? MSSQLHelper.connectionString :connectionString);
            SqlCommand comm = conn.CreateCommand();
            comm.CommandType = CommandType.Text;
            SqlDataAdapter adapter = new SqlDataAdapter(comm);
            SqlCommandBuilder commandBulider = new SqlCommandBuilder(adapter);
            commandBulider.ConflictOption = ConflictOption.OverwriteChanges;
            try
            {
                conn.Open();
                //设置批量更新的每次处理条数
                adapter.UpdateBatchSize = table.Rows.Count;
                adapter.SelectCommand.Transaction = conn.BeginTransaction();
                adapter.SelectCommand.CommandText = string.Format("select * from {0} where {1}", string.IsNullOrWhiteSpace(tableName) ? table.TableName : tableName, limitWhere);
                result = adapter.Update(table);
                adapter.SelectCommand.Transaction.Commit();/////提交事务
            }
            catch (Exception ex)
            {
                if (adapter.SelectCommand != null && adapter.SelectCommand.Transaction != null)
                {
                    adapter.SelectCommand.Transaction.Rollback();
                }
                throw ex;
            }
            finally
            {
                conn.Close();
                conn.Dispose();
            }
            return result;
        }

posted @   卡萨丁·周  阅读(352)  评论(0编辑  收藏  举报
编辑推荐:
· AI与.NET技术实操系列:基于图像分类模型对图像进行分类
· go语言实现终端里的倒计时
· 如何编写易于单元测试的代码
· 10年+ .NET Coder 心语,封装的思维:从隐藏、稳定开始理解其本质意义
· .NET Core 中如何实现缓存的预热?
阅读排行:
· 25岁的心里话
· 闲置电脑爆改个人服务器(超详细) #公网映射 #Vmware虚拟网络编辑器
· 零经验选手,Compose 一天开发一款小游戏!
· 通过 API 将Deepseek响应流式内容输出到前端
· AI Agent开发,如何调用三方的API Function,是通过提示词来发起调用的吗
点击右上角即可分享
微信分享提示