达梦数据库使用DmBulkCopy批导数据解决方案-开发框架文库

达梦数据库使用DmBulkCopy批导数据解决方案

解决方案1 - 使用 DmBulkCopy

达梦数据库使用DmBulkCopy批导数据会发生错误，达梦官网暂无解决方案。

C# 全选

bulkCopy.WriteToServer(table);

达梦数据库使用DmBulkCopy批导数据解决方案

暂无解决方案

参考：https://www.cscode.net/archive/newdoc/609070286024709.html

代码参考：

C# 全选

public override int BulkInsert<T>(List<T> entities, string tableName = null)
{
    if (String.IsNullOrEmpty(tableName))
    {
        var tableAttribute = (TableAttribute)typeof(T).GetCustomAttributes(typeof(TableAttribute), false).First();
        tableName = tableAttribute.Name;
    }

    DmTransaction tran = null;
    int count = 0;

    try
    {
        DmConnection connection = this.Connection as DmConnection;
        if (connection.State == ConnectionState.Closed) connection.Open();
        tran = connection.BeginTransaction(IsolationLevel.ReadCommitted, false);

        using (DmBulkCopy bulkCopy = new DmBulkCopy(connection, DmBulkCopyOptions.Default, tran))
        {
            bulkCopy.BulkCopyTimeout = 0;
            bulkCopy.BatchSize = entities.Count;
            bulkCopy.ColumnMappings.BuildMapping(typeof(T)); //添加字段映射                    
            bulkCopy.DestinationSchemaName = “CSFRAMEWORKV6_NORMAL”;
            bulkCopy.DestinationTableName ="_TEST_CUSTOMER";

            var table = DataTableHelper.ToDataTable<T>(entities);
            table.TableName = tableName;

            //重要：记录设置为新增状态
            foreach (DataRow r in table.Rows) r.SetAdded();

            bulkCopy.WriteToServer(table); //报错！！！达梦官网无解决方案
            bulkCopy.Close();
            count = table.Rows.Count;
        }

        tran.Commit();

        return count;
    }
    catch (Exception ex)
    {
        if (tran != null) tran.Rollback();
        throw;
    }
}

解决方案2 - 使用ADO.NET

因为方案1官方暂无解决方案，使用ADO.NET方式解决。

方案2测试了有两种方式：

DbContext.AddRange(实体列表)
DmDataAdapter适配器提交DataTable

C# 全选

        /// <summary>
        /// 达梦数据库批量导入第2解决方案
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="entities"></param>
        /// <param name="tableName"></param>
        /// <returns></returns>
        public override int BulkInsert<T>(List<T> entities, string tableName = null)
        {
            if (String.IsNullOrEmpty(tableName))
            {
                var tableAttribute = (TableAttribute)typeof(T).GetCustomAttributes(typeof(TableAttribute), false).First();
                tableName = tableAttribute.Name;
            }

            //方案1：使用EFCore的DbContext.AddRange(List<T>)，效率一般
            //int count1 = this.Add<T>(entities);

            //方案1：使用ADO.NET DataTable，效率较高
            //实体列表转DataTable
            var table = DataTableHelper.ToDataTable<T>(entities);
            table.TableName = tableName;

            try
            {
                using (DmConnection connection = new DmConnection(this.ConnectionString))
                {
                    if (connection.State == ConnectionState.Closed) connection.Open();

                    //重要：记录设置为新增状态
                    foreach (DataRow r in table.Rows) r.SetAdded();

                    var sql = "SELECT TOP 0 * FROM " + this.FormatSchema + this.FormatTableName(tableName);
                    DmDataAdapter adp = new DmDataAdapter(sql, connection);
                    adp.InsertCommand = new DmCommandBuilder(adp).GetInsertCommand(true) as DmCommand;
                    return adp.Update(table);
                }
            }
            catch (Exception ex)
            {
                throw;
            }
        }