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); }