private void btnMigration_Click1(object sender, EventArgs e)
{
var tablenames = new List <string>();
foreach (ListViewItem item in lvTable.CheckedItems)
{
tablenames.Add(item.Text);
}
var frmMessage = new frmMessage();
var thread = new Thread(delegate()
{
foreach (var item in tablenames)
{
this.BeginInvoke(new ThreadStart(delegate()
{
frmMessage.labMessage.Text = $"正在查询{item}表……";
}));
List <dynamic> list = null;
string pgsql = null;
using (var con = new SqlConnection(_sqlconnectionstring))
{
list = con.Query <dynamic>($@"select * from {item}").ToList();
this.BeginInvoke(new ThreadStart(delegate()
{
frmMessage.labMessage.Text = $"正在搬运{item}表，总共{list.Count()}条记录……";
}));
var fields = con.Query <string>($@"select sc.name from syscolumns sc,systypes st where sc.xtype=st.xtype and st.status=0 and sc.id in(select id from sysobjects where xtype='U' and name='{item}')");
var pgsqlField = new StringBuilder();
var pgsqlPar = new StringBuilder();
foreach (var field in fields)
{
pgsqlField.Append($"{field},");
pgsqlPar.Append($"@{field},");
}
pgsql = $"insert into {item}({pgsqlField.ToString().TrimEnd(',')}) values({pgsqlPar.ToString().TrimEnd(',')})";
}
var index = 0;
var count = 1000;
using (var pgcon = new Npgsql.NpgsqlConnection(_pgconnectionstring))
{
while (index * count < list.Count)
{
this.BeginInvoke(new ThreadStart(delegate()
{
frmMessage.labMessage.Text = $"正在搬运{item}表，{index * count}/{list.Count()}……";
}));
var newlist = new List <dynamic>();
newlist.AddRange(list.Skip(index * count).Take(count));
pgcon.Execute(pgsql, newlist);
index++;
}
}
}
this.BeginInvoke(new ThreadStart(delegate()
{
frmMessage.picLoad.Image = Resources.migration1;
frmMessage.labMessage.Text = $"全部搬运完成！";
frmMessage.tmrCount.Stop();
}));
});
frmMessage.MigrationThread = thread;
frmMessage.tmrCount.Start();
thread.Start();
frmMessage.ShowDialog();
}
private void btnMigration_Click(object sender, EventArgs e)
{
var tablenames = new List <string>();
foreach (ListViewItem item in lvTable.CheckedItems)
{
tablenames.Add(item.Text);
}
var frmMessage = new frmMessage();
var thread = new Thread(delegate()
{
foreach (var item in tablenames)
{
this.BeginInvoke(new ThreadStart(delegate()
{
frmMessage.labMessage.Text = $"正在查询{item}表……";
}));
var pagesize = 5000;
var pageindex = 1;
while (true)
{
List <dynamic> list = null;
string pgsql = null;
List <string> keyfields = null;
int recordCount = 0;
using (var con = new SqlConnection(_sqlconnectionstring))
{
keyfields = con.Query <string>($@"SELECT c.name Cname FROM sys.objects T INNER JOIN sys.objects P ON t.object_id=p.parent_object_id AND t.type='U' AND p.type='PK' INNER JOIN sys.SysColumns C ON c.id=t.object_id INNER JOIN sysindexes i ON i.name=p.name INNER JOIN sysindexkeys k ON k.id=c.id AND k.colid=c.colid AND k.indid=i.indid where t.name='{item}'").ToList();
if (keyfields.Count() > 0)
{
list = con.Query <dynamic>($@"SELECT TOP {pagesize} * FROM (SELECT ROW_NUMBER() OVER (ORDER BY {keyfields[0]}) AS RowNo,* FROM {item})querytable WHERE RowNo > {(pageindex - 1) * pagesize} ").ToList();
if (list.Count == 0)
{
break;
}
}
else
{
list = con.Query <dynamic>($@"SELECT * from {item}").ToList();
}
recordCount = con.ExecuteScalar <int>($@"SELECT count(1) as sl from {item}");
//生成pg的insert into 语句
var fields = con.Query <string>($@"select sc.name from syscolumns sc,systypes st where sc.xtype=st.xtype and st.status=0 and sc.id in(select id from sysobjects where xtype='U' and name='{item}')");
var pgsqlField = new StringBuilder();
var pgsqlPar = new StringBuilder();
foreach (var field in fields)
{
pgsqlField.Append($"{field},");
pgsqlPar.Append($"@{field},");
}
pgsql = $"insert into {item}({pgsqlField.ToString().TrimEnd(',')}) values({pgsqlPar.ToString().TrimEnd(',')})";
}
using (var pgcon = new Npgsql.NpgsqlConnection(_pgconnectionstring))
{
this.BeginInvoke(new ThreadStart(delegate()
{
frmMessage.labMessage.Text = $"正在搬运{item}表，{pageindex * pagesize}/{recordCount}条记录……";
}));
pgcon.Execute(pgsql, list);
}
//没有关键了，就退出
if (keyfields.Count() == 0)
{
break;
}
pageindex++;
}
}
this.BeginInvoke(new ThreadStart(delegate()
{
frmMessage.picLoad.Image = Resources.migration1;
frmMessage.labMessage.Text = $"全部搬运完成！";
frmMessage.tmrCount.Stop();
}));
});
frmMessage.MigrationThread = thread;
frmMessage.tmrCount.Start();
thread.Start();
frmMessage.ShowDialog();
}
