private void gridView1_Click(object sender, EventArgs e)
{
try
{
needfix = false;
if (dttabledetail.Rows.Count != 0)
{
DataSet dsconnstr = DALUse.Query(string.Format("select * from PT_Setting where pt_id ='{0}'", cmb_ptname.SelectedValue.ToString()));
if (dsconnstr.Tables.Count > 0 && dsconnstr.Tables[0].Rows.Count > 0)
{
connstr = dsconnstr.Tables[0].Rows[0]["connstr"].ToString();
}
string sqltemp = string.Format("select * from pt_target_field where table_name ='{0}' and pt_id = '{1}'", gridView1.GetDataRow(gridView1.FocusedRowHandle)["TABLE_NAME"].ToString(), ((DataRowView)cmb_ptname.SelectedItem).Row["pt_id"].ToString());
string sqltarget = string.Format("select * from {0} where 1=0 ", gridView1.GetDataRow(gridView1.FocusedRowHandle)["TABLE_NAME"].ToString());
DataSet dstarget = DALUseSpecial.Query(sqltarget, connstr);
if (dstarget.Tables.Count > 0)
{
dttarget = dstarget.Tables[0];
}
DataSet dstemp = DALUse.Query(sqltemp);
if (dstemp.Tables.Count > 0)
{
dttemp = dstemp.Tables[0];
}
CheckTargetDict(dttemp, dttarget);
gc_pt_target.DataSource = dttemp;
}
}
catch (Exception ex)
{
//MessageBox.Show(ex.ToString());
}
}
//将目标表的字段同步到本地
public void SynTargetField()
{
try
{
string sqltarget = string.Format("select * from {0} where 1=0 ", gridView1.GetDataRow(gridView1.FocusedRowHandle)["TABLE_NAME"].ToString());
DataSet dstarget = DALUseSpecial.Query(sqltarget, connstr);
if (dstarget.Tables.Count > 0)
{
foreach (DataColumn dc in dstarget.Tables[0].Columns)
{
string columntype = "";
if (dc.DataType.Name.ToString().Contains("String"))
{
columntype = "STRING";
}
else if (dc.DataType.Name.ToString().Contains("Decimal") || dc.DataType.Name.ToString().Contains("Int"))
{
columntype = "NUMBER";
}
else if (dc.DataType.Name.ToString().Contains("DateTime"))
{
columntype = "DATE";
}
else
{
MessageBox.Show(dc.DataType.Name.ToString());
}
string sql = string.Format("insert into PT_TARGET_FIELD(compare_id,pt_name,table_name,field_name,field,field_type,compare_name,pt_id) values('{7}','{0}','{1}','{2}','{3}','{4}','{5}','{6}')", cmb_ptname.Text, gridView1.GetDataRow(gridView1.FocusedRowHandle)["TABLE_NAME"].ToString(), dc.ColumnName, dc.ColumnName, columntype, "", cmb_ptname.SelectedValue.ToString(), Guid.NewGuid());
string sql1 = string.Format("select * from PT_TARGET_FIELD where pt_id = '{0}' and table_name = '{1}' and field = '{2}'", cmb_ptname.SelectedValue.ToString(), gridView1.GetDataRow(gridView1.FocusedRowHandle)["TABLE_NAME"].ToString(), dc.ColumnName);
DataSet ds = DALUse.Query(sql1);
if (ds.Tables.Count > 0 && ds.Tables[0].Rows.Count == 0)
{
DALUse.ExecuteSql(sql);
}
}
}
else
{
MessageBox.Show("目标库不存在表：" + gridView1.GetDataRow(gridView1.FocusedRowHandle)["TABLE_NAME"].ToString());
}
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
}
public static bool InsertDataIntoTarget(DataSet source)
{
bool result = true;
string errormess = "";
string connstr = "";
List <string> sqllist = new List <string>();
try
{
//修改人:吴海龙;修改时间2014-07-19;修改原因:从pt_tables_dict 设置那些表导出
//DataTable targettable = DALUse.Query(string.Format("select * from PT_Up_DataBase_Table where pt_id ='{0}'", pt_id)).Tables[0];
DataTable targettable = DALUse.Query(string.Format("select * from pt_tables_dict where pt_id ='{0}' and exportflag = 'TRUE'", pt_id)).Tables[0];
connstr = DALUse.Query(string.Format("select * from PT_Setting where pt_id ='{0}'", pt_id)).Tables[0].Rows[0]["connstr"].ToString();
string dbType = StrConvertToDateTime.getClientConnectType(connstr);
foreach (DataRow drtarget in targettable.Rows)
{//修改PT_TABLE->TABLE_NAME
//string sql1 = string.Format("select * from PT_TARGET_FIELD where table_name = '{0}' ", drtarget["TABLE_NAME"].ToString());
string sql1 = string.Format("select * from PT_comparison where pt_id ='{1}' and table_name = '{0}' ", drtarget["TABLE_NAME"].ToString(), pt_id);
DataTable targetcolumns = DALUse.Query(sql1).Tables[0];
//构造插入的列
string insertcolumns = "";
foreach (DataRow drcolumnname in targetcolumns.Rows)//拼接插入的列
{
insertcolumns += drcolumnname["FIELD"].ToString() + ",";
}
insertcolumns = insertcolumns.Trim(',');
DataTable dtsource = null;
foreach (DataTable dt1 in source.Tables)
{
if (dt1.TableName.Contains(drtarget["ID"].ToString()))
{
dtsource = dt1;
}
}
if (dtsource == null)
{
clsWriteErrorLogToDataBase.WriteErrorLogTodataBase(drtarget["table_name"].ToString() + "未导出！");
continue;
}
//构造插入的行
//------------
foreach (DataRow drinsertdata in dtsource.Rows)
{
string sql = "";
string insertvalue = "";
foreach (string dcitem in insertcolumns.Split(','))
{
string sqldatatype = string.Format("select * from PT_TARGET_FIELD where pt_id = '{0}' and table_name = '{1}' and field = '{2}'", pt_id, drtarget["TABLE_NAME"].ToString(), dcitem.ToString());
string dataType = DALUse.Query(sqldatatype).Tables[0].Rows[0]["FIELD_TYPE"].ToString();
insertvalue += StrConvertToDateTime.makeInsertvalue(drinsertdata[dcitem.ToUpper()].ToString(), false, StrConvertToDateTime.getClientConnectType("TargetConnection"), dataType);
//bool valueflag = false;//指定列是否赋值。
//foreach (DataColumn dcdata in dtsource.Columns)
//{
// //2014-12-30 吴海龙 优化赋值过程
// if (dcitem.ToUpper().Equals(getFieldName(pt_id, drtarget["TABLE_NAME"].ToString(), dcdata.ToString()).ToUpper()))
// {
// //insertvalue += "'" + drinsertdata[dcitem].ToString() + "',";
// //insertvalue += StrConvertToDateTime.makeInsertvalue(drinsertdata[dcdata.ToString()].ToString(), false, StrConvertToDateTime.getClientConnectType("TargetConnection"), dataType);
// valueflag = true;
// //StrConvertToDateTime.ToDate("2014-06-19 11:11:11", false, dbType);
// }
//}
//if (!valueflag)
//{
// insertvalue += "'',";
//}
//----------------------
//string sqldatatype = string.Format("select * from PT_TARGET_FIELD where pt_id = '{0}' and table_name = '{1}' and field = '{2}'", pt_id, drtarget["TABLE_NAME"].ToString(), dcitem.ToString());
//string dataType = DALUse.Query(sqldatatype).Tables[0].Rows[0]["FIELD_TYPE"].ToString();
//bool valueflag = false;//指定列是否赋值。
//foreach (DataColumn dcdata in dtsource.Columns)
//{
// //2014-12-30 吴海龙 优化赋值过程
// if (dcitem.ToUpper().Equals(getFieldName(pt_id, drtarget["TABLE_NAME"].ToString(), dcdata.ToString()).ToUpper()))
// {
// //insertvalue += "'" + drinsertdata[dcitem].ToString() + "',";
// insertvalue += StrConvertToDateTime.makeInsertvalue(drinsertdata[dcdata.ToString()].ToString(), false, StrConvertToDateTime.getClientConnectType("TargetConnection"), dataType);
// valueflag = true;
// //StrConvertToDateTime.ToDate("2014-06-19 11:11:11", false, dbType);
// }
//}
//if (!valueflag)
//{
// insertvalue += "'',";
//}
}
insertvalue = insertvalue.Trim(',');
sql = string.Format("insert into {0} ({1}) values ({2})",
drtarget["TABLE_NAME"].ToString(),
insertcolumns,
insertvalue);
sqllist.Add(sql);
//CommonFunction.WriteErrorLog("插入目标表sql："+sql);
errormess = sql;
ToolFunction.clsProperty.insertcount = ToolFunction.clsProperty.insertcount++;
try//针对插入操作上传日志。
{
if (DALUseSpecial.ExecuteSql(sql, connstr) == 1)
{
okcount++;
result = true;
}
else
{
falsecount++;
result = false;
}
}
catch (Exception ex)
{
falsecount++;
result = false;
CommonFunction.WriteErrorLog("插入目标表sql：" + sql + "\n" + ex.ToString());
continue;
}
}
//------------------
}
}
catch (Exception ex)
{
result = false;
//throw;
CommonFunction.WriteErrorLog(ex.Message);
}
return(result);
}
