/// <summary>mssql导入到mysql
///
/// </summary>
/// <param name="mssqltabname"></param>
/// <param name="mysqltabname"></param>
/// <param name="id"></param>
private void DoMssqlAddToMysql(string mssqltabname, string mysqltabname, string id)
{
try
{
DataTable dt = m_orddal.GetList("State=1 and id='" + id + "'").Tables[0];
string setordkey = dt.Rows[0]["id"].ToString();
DataTable dtColumns = m_setdal.GetList("State=1 and SetKey='" + setordkey + "'and (ChangLiang='' OR ChangLiang IS nULL )").Tables[0];
string mysqlColumns = string.Empty;
string mssqlColumns = string.Empty;
string mysqlColumnsParas = string.Empty;
for (int i = 0; i < dtColumns.Rows.Count; i++)
{
mssqlColumns = mssqlColumns + dtColumns.Rows[i]["SetValue"] + ",";
}
for (int i = 0; i < dtColumns.Rows.Count; i++)
{
mysqlColumns = mysqlColumns + dtColumns.Rows[i]["SetText"] + ",";
mysqlColumnsParas = mysqlColumnsParas + "@" + dtColumns.Rows[i]["SetText"] + ",";
}
string getmssql = "Select " + mssqlColumns.TrimEnd(',') + " from " + mssqltabname;
m_sqlserverhelper.CreateCommand(getmssql);
DataTable dtmssql = m_sqlserverhelper.ExecuteQuery();
//标记ID
DataRow[] drs = dtColumns.Select("Remark ='True'");
//if (drs.Length == 0)
//{
// MessageBox.Show(@"未找到设置的对应标记ID");
// return;
//}
string mysqlColumnsId = drs[0]["SetText"].ToString();
string mssqlColumnsId = drs[0]["SetValue"].ToString();
string getmysql = "Select " + mysqlColumns.TrimEnd(',') + " from " + mysqltabname;
m_myhelper.CreateCommand(getmysql);
DataTable dtmysql = m_myhelper.ExecuteQuery();
string ids = string.Empty;
for (int i = 0; i < dtmysql.Rows.Count; i++)
{
ids = ids + dtmysql.Rows[i][mysqlColumnsId].ToString().Trim() + ",";
}
ids = ids.TrimEnd(',');
var drsRows = ids.Length > 0 ? dtmssql.Select(" " + mssqlColumnsId + " not in (" + ids + " )") : dtmssql.Select(" 1=1");
foreach (DataRow dr in drsRows)
{
m_myhelper.CreateCommand("set names 'gbk' ;insert into " + mysqltabname + "(" + mysqlColumns.TrimEnd(',') + ") values (" + mysqlColumnsParas.TrimEnd(',') + ")");
for (int j = 0; j < dr.Table.Columns.Count; j++)
{
m_myhelper.AddParameter(mysqlColumnsParas.Split(',')[j], dr[dr.Table.Columns[j].ColumnName]);
}
m_myhelper.ExecuteNonQuery();
MsSqlImprortToMysqlAmount = MsSqlImprortToMysqlAmount + 1;
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
return;
}
}
private void dataGridView1_CellClick(object sender, DataGridViewCellEventArgs e)
{
if (grdTable.CurrentRow != null)
{
_setOrdKey = grdTable.CurrentRow.Cells[0].Value.ToString();
_setOrdText = grdTable.CurrentRow.Cells[1].Value.ToString();
}
btnNew.Text = @"新增" + _setOrdText;
btnEdit.Text = @"修改" + _setOrdText;
btnDelete.Text = @"删除" + _setOrdText;
grdTableColumn.DataSource = _setdal.GetList("State=1 AND SetKey='" + _setOrdKey + "'", "id,SetValue,SetText,Remark,ChangLiang").Tables[0];
}