注册

DmBulkCopy是否支持临时表的批量插入

🐇+-+🐇 2023/06/12 606 0

为提高效率,提问时请提供以下信息,问题描述清晰可优先响应。
【DM版本】:DM Database Server 64 V8
【操作系统】:win11
【CPU】:(英特尔)Intel(R) Core(TM) i5-10400 CPU @ 2.90GHz(2901 MHz)
【问题描述】*:新增临时表并对临时表批量写入,执行不报错临时表空数据
#region 批量写入临时表

            //临时表名称
            var tmpTabName = "PBXC.##" + tableName + "_" + Guid.NewGuid().ToString("N");

            //字段集合
            var tmpTabFeds = feds.Select(item =>
            {
                var f = item.ParameterName.Replace(":", "");
                if (item.DmSqlType == DmDbType.Int64) { f += " BIGINT"; }
                else if (item.DmSqlType == DmDbType.Int32) { f += " INT"; }
                else if (item.DmSqlType == DmDbType.Decimal) { f += " DECIMAL(18,4)"; }
                else if (item.DmSqlType == DmDbType.Date) { f += " DATE"; }
                else if (item.DmSqlType == DmDbType.Time) { f += " TIME"; }
                else if (item.DmSqlType == DmDbType.DateTime) { f += " DATETIME"; }
                else if (item.DmSqlType == DmDbType.Bit) { f += " BIT"; }
                else if (item.DmSqlType == DmDbType.VarChar)
                {
                    //若为不限大小则启用TEXT类型
                    if (item.Size == -1)
                    {
                        f += " TEXT";
                    }
                    else
                    {
                        f += " NVARCHAR(" + item.Size + " CHAR)";
                    }
                }
                return f;
            });

            DmConnection conn =  DbHelperDM.Connection();

            //创建临时表
            DbHelperDM.ExecuteSql("CREATE GLOBAL TEMPORARY TABLE " + tmpTabName + "(" + string.Join(",", tmpTabFeds) + ") ON COMMIT PRESERVE ROWS;", conn);

            //待更新字段集合
            var upTabFeds = new List<string>();

            //获取数据库链接字符串
            //var conn = PubConstant.GetConnectionString("ConnectionString");

            //指定更新主键范围
            var upPkParams = new List<DmParameter>();
            var upPkNames = new List<string>();

            //链接数据库
            using (var bulkCopy = new DmBulkCopy(conn))
            {
                bulkCopy.BatchSize = list.Count;
                bulkCopy.DestinationTableName = tmpTabName;
                bulkCopy.BulkCopyTimeout = 30000;

                var table = new DataTable(tmpTabName);
                var props = list[0].GetType().GetProperties();

                int clrprops = 0;
                foreach (var propertyInfo in props)
                {
                    var fedName = propertyInfo.Name;
                    bulkCopy.ColumnMappings.Add(fedName, fedName);
                    table.Columns.Add(fedName, Nullable.GetUnderlyingType(propertyInfo.PropertyType) ?? propertyInfo.PropertyType);
                    if (fedName != pkFed)
                    {
                        upTabFeds.Add(fedName + "=(SELECT " + fedName + " FROM " + tmpTabName + " WHERE " + pkFed + "=" + tableName + "." + pkFed + ")");
                        //Photo=(SELECT Photo FROM T_DVPM_DvPm_20210607210838789 WHERE DvPmID=T_DVPM_DvPm.DvPmID)
                    }
                }

                var values = new object[props.Length - clrprops];
                foreach (var item in list)
                {
                    for (var i = 0; i < values.Length; i++)
                    {
                        values[i] = props[i].GetValue(item)!;
                        if (props[i].Name == pkFed)
                        {
                            //目前支持三种类型主键
                            var sqlDbType = DmDbType.Int64;
                            if (values[i].GetType() == typeof(long)) { sqlDbType = DmDbType.Int64; }
                            else if (values[i].GetType() == typeof(int)) { sqlDbType = DmDbType.Int32; }
                            else if (values[i].GetType() == typeof(string)) { sqlDbType = DmDbType.VarChar; }
                            var pName = ":" + pkFed + "_" + values[i].ToString()!.Replace(".", "_");
                            upPkNames.Add(pName);
                            upPkParams.Add(new DmParameter() { ParameterName = pName, DmSqlType = sqlDbType, Value = values[i] });
                        }
                    }

                    table.Rows.Add(values);
                }

                bulkCopy.WriteToServer(table);
            }
回答 0
暂无回答
扫一扫
联系客服