学习参考专栏地址

文章目录

• 一、环境描述
• 二、准备工作
• 三、编写代码调用bulkCopy
• 四、测试结果

一、环境描述

【DM版本】:dm8
【操作系统】：win11-64位
【开发工具】：vs2022

二、准备工作

--数据库创建分区表并插入数据:
DROP TABLE TEST_DEST;
DROP TABLE TEST_SOURCE;

CREATE TABLE TEST_DEST(
	C1 VARCHAR,
	PRICE INT
)
PARTITION BY RANGE(PRICE)(
	PARTITION P1 VALUES LESS THAN (10),
	PARTITION P2 VALUES LESS THAN (100),
	PARTITION P3 VALUES LESS THAN (1000),
	PARTITION P4 VALUES LESS THAN (MAXVALUE)
);

CREATE TABLE TEST_SOURCE(
	C1 VARCHAR,
	PRICE INT
)
PARTITION BY RANGE(PRICE)(
	PARTITION P1 VALUES LESS THAN (10),
	PARTITION P2 VALUES LESS THAN (100),
	PARTITION P3 VALUES LESS THAN (1000),
	PARTITION P4 VALUES LESS THAN (MAXVALUE)
);

create or replace procedure p1 (c1 int)
as
begin
for i in 1..c1 loop
insert into test_source values(i,i*12);
end loop;
commit;
end;
/
call p1(100);

三、编写代码调用bulkCopy

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using Dm;
using System.Data.Common;

/* 数据库创建分区表并插入数据:
DROP TABLE TEST_DEST;
DROP TABLE TEST_SOURCE;

CREATE TABLE TEST_DEST(
	C1 VARCHAR,
	PRICE INT
)
PARTITION BY RANGE(PRICE)(
	PARTITION P1 VALUES LESS THAN (10),
	PARTITION P2 VALUES LESS THAN (100),
	PARTITION P3 VALUES LESS THAN (1000),
	PARTITION P4 VALUES LESS THAN (MAXVALUE)
);

CREATE TABLE TEST_SOURCE(
	C1 VARCHAR,
	PRICE INT
)
PARTITION BY RANGE(PRICE)(
	PARTITION P1 VALUES LESS THAN (10),
	PARTITION P2 VALUES LESS THAN (100),
	PARTITION P3 VALUES LESS THAN (1000),
	PARTITION P4 VALUES LESS THAN (MAXVALUE)
);

create or replace procedure p1 (c1 int)
as
begin
for i in 1..c1 loop
insert into test_source values(i,i*12);
end loop;
commit;
end;
/
call p1(100);
 */
namespace testCopyPartitionTable
{
    class conn
    {
        static void Main(string[] args)
        {
            conn.PerformBulkCopy();
        }
        private static void PerformBulkCopy()
        {
            //建立数据库连接
            string connectionString = ("Server=localhost:5236; UserId=SYSDBA; PWD=123456789;");
            DmConnection sourceConnection = new DmConnection(connectionString);
            sourceConnection.Open();
            //创建DataTable且列名须保持跟目的表列名一致
            DataTable table = new DataTable();
            table.Columns.Add("C1");
            table.Columns.Add("PRICE");
            //读取源端表数据并插入到DataTable
            DmCommand myCommand = new DmCommand("SELECT * FROM TEST_SOURCE", sourceConnection);
            DmDataReader reader = (DmDataReader)myCommand.ExecuteReader();
            while (reader.Read())
            {
                DataRow row = table.NewRow();
                row["C1"] = reader.GetString(0);
                row["PRICE"] = reader.GetInt64(1);
                //如果想单独操作某一分区可以在此处加if语句
                table.Rows.Add(row);
                //Console.WriteLine((string)row["C1"]+(string)row["PRICE"]);    //测试打印写入的数据
            }
            //插入前查询表行数
            DmCommand DestCommand0 = new DmCommand("SELECT count(*) FROM TEST_DEST", sourceConnection);
            DmDataReader Destreader0 = (DmDataReader)DestCommand0.ExecuteReader();
            while (Destreader0.Read())
            {
                int i = Destreader0.GetInt32(0);
                Console.WriteLine("TEST_DEST行数：" + i);
            }
            Destreader0.Close();

            var bulkCopy = new DmBulkCopy(sourceConnection);
            //在插入的目标表
            bulkCopy.DestinationTableName = "TEST_DEST";
            //DataTable列名与数据库列名的映射
            for (int i = 0; i < table.Columns.Count; i++)
            {
                bulkCopy.ColumnMappings.Add(table.Columns[i].ColumnName, table.Columns[i].ColumnName);
            }
            bulkCopy.WriteToServer(table);//写入到数据库中
            bulkCopy.Close();
            reader.Close();
            //验证数据插入行数
            DmCommand DestCommand = new DmCommand("SELECT count(*) FROM TEST_DEST", sourceConnection);
            DmDataReader Destreader = (DmDataReader)DestCommand.ExecuteReader();
            while (Destreader.Read())
            {
                int i = Destreader.GetInt32(0);
                Console.WriteLine("TEST_DEST行数：" + i);
            }
            Destreader.Close();
            sourceConnection.Close();
        }
    }
}

四、测试结果

VS运行结果，可以看到已经copy成功

数据库侧查询，结果与预期一致