SqlBulkCopy 批量insert

1.获取要插入的数据datatable

/// <summary>
        /// 从FMS取银行信息
        /// </summary>
        /// <returns></returns>
        public void GetBankInfoFromFMS()
        {
            try
            {
                string connectionStr = Tool.GetConnectionStrings("BPMConnection");
                BudgetServerClient client = new BudgetServerClient();
                BankInfoRequest request = new BankInfoRequest();
                request.PageIndex = 0;
                request.PageSize = int.MaxValue;
                var response = client.QueryBankInfo(request);
                if (response != null && response.total > 0 && response.data != null && response.data.Count > 0)
                {
                    //先删除
                    string sql = "delete from BPM_ExternalSupplier_BankInfo";
                    SqlHelper.ExecuteNonQuery(connectionStr, sql);


                    //准备要批量插入的数据
                    DataTable table = new DataTable();
                    table.Columns.Add("ID");
                    table.Columns["ID"].DataType = typeof(Guid);
                    table.Columns.Add("Pkid");
                    table.Columns.Add("BankName");
                    table.Columns.Add("BankNum");
                    table.Columns.Add("Province");
                    table.Columns.Add("Citye");
                    table.Columns.Add("CreateUser");
                    table.Columns.Add("CreateTime");
                    table.Columns.Add("ModifiedUser");
                    table.Columns.Add("ModifiedTime");
                    table.Columns.Add("IsDeleted");
                    table.Columns["IsDeleted"].DataType = typeof(bool);
                    foreach (var item in response.data)
                    {
                        DataRow row = table.NewRow();
                        row["ID"] = Guid.NewGuid();
                        row["Pkid"] = item.Pkid;
                        row["BankName"] = item.BankName;
                        row["BankNum"] = item.BankNum;
                        row["Province"] = item.Province;
                        row["Citye"] = item.Citye;
                        row["CreateUser"] = "xuguanghui";
                        row["CreateTime"] = DateTime.Now;
                        row["ModifiedUser"] = "xuguanghui";
                        row["ModifiedTime"] = DateTime.Now;
                        row["IsDeleted"] = 0;
                        table.Rows.Add(row);
                    }
                    SqlHelper.BulkCopyData(table, connectionStr, "BPM_ExternalSupplier_BankInfo");
                }
            }
            catch (Exception ex)
            {
                Tool.SendErrorNotication("系统错误通知", "从FMS取银行信息GetBankInfoFromFMS方法错误，错误消息：" + ex.Message);
            }
        }

 

2.调用SqlBulkCopy

 /// <summary>
        /// 批量写入
        /// </summary>
        /// <param name="sourceTable">数据源</param>
        /// <param name="connStr">数据库连接串</param>
        /// <param name="destinationTableName">目标表</param>
        public static void BulkCopyData(DataTable sourceTable, string connStr, string destinationTableName)
        {
            SqlBulkCopy sbc = new SqlBulkCopy(connStr, SqlBulkCopyOptions.UseInternalTransaction);
            sbc.BulkCopyTimeout = 5000;
            try
            {
                sbc.DestinationTableName = destinationTableName;
                foreach (DataColumn dc in sourceTable.Columns)
                {
                    sbc.ColumnMappings.Add(dc.ColumnName, dc.ColumnName);
                }
                sbc.WriteToServer(sourceTable);
            }
            catch
            {
                //The SqlBulkCopy object is automatically closed at the end of the using block.
                //可以不写，会自动关闭
                sbc.Close();
            }
            finally
            {
                //The SqlBulkCopy object is automatically closed at the end of the using block.
                //可以不写，会自动关闭
                sbc.Close();
            }
        }

 

转载于:https://www.cnblogs.com/xuguanghui/p/6991317.html