数据迁移

HiSql支持如从SqlServer 将物理表结构和数据同步传输到MySql 或其它的HiSql支持的数据库中

以下演示一下把表H_TB_Comment中的数据从A数据库迁移到B数据库的样例代码

  //建立两个库的连接

  //数据源
  HiSql.HiSqlClient lclClient = DbInst.GetSqlClient("LOCALDB");

  //目标库
  HiSql.HiSqlClient sqlClient = DbInst.GetSqlClient("BISERVER");

  
  //在目标库创建一个与本地库一样的结构信息的表
  sqlClient.Context.DMInitalize.BuildTabCreate(lclClient.Context.DMInitalize.GetTabStruct("H_TB_Comment"));


  //执行数据传输..
  TransData(lclClient,sqlClient,"H_TB_Comment");

如果需要把本地库所有的表和数据传输到目标库,可以通过DbFirst 获取表和视图，获取到所有表再循环一张表一张表的传输。

传输数据的方法

    static void TransData( HiSqlClient lclClient, HiSqlClient sqlClient, string taname)
    {

        int total = 0;
        TabInfo tabInfo = lclClient.Context.DMInitalize.GetTabStruct(taname);
        string _sort = "";
        var colkeys = tabInfo.GetColumns.Where(c => c.IsPrimary == true).ToList();
        int _idx = 0;
        int _psize = 10000;
        int _currpage = 1;

        int _pagecount = 0;
        foreach (HiColumn hicol in colkeys)
        {
            if (_idx == colkeys.Count - 1)
            {
                _sort += $" {hicol.FieldName} asc";
            }
            else {
                _sort += $" {hicol.FieldName} asc,";
            }
            _idx++;
        }
        try
        {
            DataTable dt = lclClient.HiSql($"select * from {taname} order by {_sort}").Take(_psize).Skip(_currpage).ToTable(ref total);

            if (total % _psize == 0)
                _pagecount = total / _psize;
            else
                _pagecount = (total / _psize) + 1;


            int _effect = sqlClient.BulkCopyExecCommand(taname, dt);

            while (_currpage < _pagecount)
            {
                _currpage++;
                dt = lclClient.HiSql($"select * from {taname} order by {_sort}").Take(_psize).Skip(_currpage).ToTable(ref total);
                if (dt.Rows.Count > 0)
                {

                    _effect = sqlClient.BulkCopyExecCommand(taname, dt);
                }
                Console.WriteLine($"传输第[{_currpage}]页记录数[{dt.Rows.Count}]");
            }

            //Console.WriteLine(quot;受影响的行{_effect}");

            Console.WriteLine($"表[{taname}]传输完成...");
        }
        catch (Exception E)
        {
            Console.WriteLine(E.Message.ToString());
        }


        



    }