private void button1_Click(object sender, EventArgs e)
{
if (_dataAdapter == null)
{
MessageBox.Show("无法更新数据库!");
return;
}
if (dgv.Rows.Count < 1)
{
return;
}
//cmdBuilder = new DB2CommandBuilder(_dataAdapter);
_dataAdapter.Update(_dataTable);
SearchNodes(_selectNodeName, treeRootView.Nodes[0]);
}
public override void Update(System.Data.DataTable data, string selectsql)
{
DB2Command cmd;
using (cmd = new DB2Command(selectsql, conn))
{
if (this._s == DBStatus.Begin_Trans)
{
cmd.Transaction = this.tran;
}
DB2DataAdapter adt = new DB2DataAdapter(cmd);
DB2CommandBuilder builder = new DB2CommandBuilder(adt);
try
{
adt.UpdateCommand = builder.GetUpdateCommand();
adt.Update(data);
}
catch (Exception ex)
{
throw ex;
}
finally
{
cmd.Cancel();
cmd = null;
}
}
}
private void button3_Click(object sender, EventArgs e)
{
try {
DB2CommandBuilder cmb = new DB2CommandBuilder(adapter);
adapter.Update(ds);
MessageBox.Show("Tabla Actualizada Correctamente");
} catch {
}
}
public override void SaveChanges()
{
try
{
adapter.Update(dtRecords);
}
catch (DB2Exception db2Exp)
{
throw db2Exp;
}
}
//若传入的是excel文件目录则插入数据库，否则按照传入的语句返回查询结果
internal DataSet UpToDb2(string path)
{
DataSet dtst = new DataSet();
DB2Connection conn = new DB2Connection()
{
ConnectionString = "Database=cig;Password=Shaorong01;User ID=db2admin"
};
DB2Command comm = new DB2Command
{
Connection = conn
};
DB2DataAdapter codata = new DB2DataAdapter
{
SelectCommand = comm
};
DB2CommandBuilder cb = new DB2CommandBuilder(codata);//构建数据库插入语句
try
{
if (path.IndexOf("xls") > 0)
{
//从excel读取策略日期参数;
pam = Excel_cyc(path);
//将投放策略日期存入数据库
if (pam.Length == 7)
{
string selectSql = string.Format("select * from DB2ADMIN.SALES_CYCLE where cyclcode='{0}'", pam[0]);
DataTable temp1 = new DataTable();
comm.CommandText = selectSql;
codata.Fill(temp1);
if (temp1.Rows.Count < 1)
{
string insertSql = string.Format("INSERT INTO DB2ADMIN.SALES_CYCLE(YEAR, MONTH, WEEK, BEGINDATE, ENDDATE, " +
"CYCLCODE, CYCLNAME) VALUES ('{0}','{1}','{2}','{3}','{4}','{5}','{6}')", pam[1], pam[2], pam[3], pam[4], pam[5], pam[0], pam[6]);
comm.CommandText = insertSql;
conn.Open();
comm.ExecuteNonQuery();
conn.Close();
}
temp1.Dispose();
}
string cycle = pam[0];
comm.CommandText = "SELECT CYCLE, PRODUCT_CODE, PRODUCT_NAME, SALE_DEPT, TYPE_NAME, CUST_TYPE, SGP_NUM ,QR_ID FROM DB2ADMIN.QUANTITIES_RECORD_NEW WHERE CYCLE='" + cycle + "'";
codata.Fill(dtst, "excel");
DataSet stt1 = new DataSet();
//从excel读取策略内容
stt1 = ReadExcel(path);
if (stt1 != null)
{
//将excel内容转换为数据库结构
handleDataTable(stt1, cycle, ref dtst);
}
//丢弃读取的excel内容
stt1.Dispose();
//向数据库写入
codata.Update(dtst.Tables["excel"].Select(null, null, DataViewRowState.Added));
dtst.Clear();
}
else
{
comm.CommandText = path;
codata.Fill(dtst, "pam");
}
return(dtst);
// DataSetValue(dtst);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
return(null);
}
finally
{
codata.Dispose();
comm.Dispose();
conn.Dispose();
dtst.Dispose();
}
}
public DataSet Update(DataSet ds, int index)
{
int i = 0;
string table = "Table";
string fieldsString = " SET ";
string whereString = " WHERE ";
string[] sqlWords = sqlSelect.Split(' ');
for (i = 0; i < sqlWords.Length; i++)
{
if (sqlWords[i] == "FROM")
{
table = sqlWords[i + 1];
break;
}
}
for (i = 0; i < ds.Tables[index].Columns.Count; i++)
{
fieldsString += ds.Tables[index].Columns[i].ColumnName + " = ?";
if (i != ds.Tables[index].Columns.Count - 1)
{
fieldsString += ", ";
}
}
for (i = 0; i < ds.Tables[index].PrimaryKey.Length; i++)
{
whereString += ds.Tables[index].PrimaryKey[i] + " = ?";
if (i != ds.Tables[index].PrimaryKey.Length - 1)
{
whereString += " AND ";
}
}
DB2Connection dc = OpenConnection();
DB2DataAdapter adapter = new DB2DataAdapter(sqlSelect, dc);
DB2Command updateCommand = new DB2Command("UPDATE " + table + fieldsString + whereString, adapter.SelectCommand.Connection);
exceptions += "UPDATE " + table + fieldsString + whereString + cambioLinea;
for (i = 0; i < ds.Tables[index].Columns.Count; i++)
{
if (ds.Tables[index].Columns[i].DataType.ToString() == "System.String")
{
updateCommand.Parameters.Add("@" + ds.Tables[index].Columns[i] + "", (DB2Type)typesRelation[ds.Tables[index].Columns[i].DataType], ds.Tables[index].Columns[i].MaxLength, ds.Tables[index].Columns[i].ColumnName);
}
else
{
updateCommand.Parameters.Add("@" + ds.Tables[index].Columns[i] + "", (DB2Type)typesRelation[ds.Tables[index].Columns[i].DataType], (int)lengthsRelation[(DB2Type)typesRelation[ds.Tables[index].Columns[i].DataType]], ds.Tables[index].Columns[i].ColumnName);
}
}
for (i = 0; i < ds.Tables[index].PrimaryKey.Length; i++)
{
if (ds.Tables[index].PrimaryKey[i].DataType.ToString() == "System.String")
{
parm = updateCommand.Parameters.Add("@old" + ds.Tables[index].PrimaryKey[i] + "", (DB2Type)typesRelation[ds.Tables[index].PrimaryKey[i].DataType], ds.Tables[index].PrimaryKey[i].MaxLength, ds.Tables[index].PrimaryKey[i].ColumnName);
}
else
{
parm = updateCommand.Parameters.Add("@old" + ds.Tables[index].PrimaryKey[i] + "", (DB2Type)typesRelation[ds.Tables[index].PrimaryKey[i].DataType], (int)lengthsRelation[(DB2Type)typesRelation[ds.Tables[index].PrimaryKey[i].DataType]], ds.Tables[index].PrimaryKey[i].ColumnName);
}
parm.SourceVersion = DataRowVersion.Original;
}
exceptions += updateCommand.Parameters.Count.ToString() + " Parameters" + cambioLinea;
adapter.UpdateCommand = updateCommand;
updateStatus = false;
try
{
affectedRows = adapter.Update(ds.Tables[index]);
updateStatus = true;
HistorialSeguimientoTabla(TABLA_SEGUIR, sqlSelect); //Almacenamiento historial de seguimiento a tabla.
}
catch (Exception e)
{
exceptions += e.ToString() + cambioLinea;
AdministradorCarpetasRegistro.GrabarLogs(TipoRegistro.Error, Sql, e, exceptions);
}
CloseConnection(dc);
return(ds);
}
public DataSet Delete(DataSet ds, int index)
{
int i = 0;
string table = "Table";
string whereString = " WHERE ";
string[] sqlWords = sqlSelect.Split(' ');
for (i = 0; i < sqlWords.Length; i++)
{
if (sqlWords[i] == "FROM")
{
table = sqlWords[i + 1];
break;
}
}
for (i = 0; i < ds.Tables[0].PrimaryKey.Length; i++)
{
whereString += ds.Tables[0].PrimaryKey[i] + " = ?";
if (i != ds.Tables[0].PrimaryKey.Length - 1)
{
whereString += " AND ";
}
}
DB2Connection dc = OpenConnection();
DB2DataAdapter adapter = new DB2DataAdapter(sqlSelect, dc);
Sql = "DELETE FROM " + table + " " + whereString + "";
AdministradorCarpetasRegistro.GrabarLogs(TipoRegistro.Actividad, Sql, null, String.Empty);
DB2Command deleteCommand = new DB2Command(Sql, adapter.SelectCommand.Connection);
for (i = 0; i < ds.Tables[0].PrimaryKey.Length; i++)
{
if (ds.Tables[0].PrimaryKey[i].DataType.ToString() == "System.String")
{
deleteCommand.Parameters.Add("@" + ds.Tables[0].PrimaryKey[i] + "", (DB2Type)typesRelation[ds.Tables[0].PrimaryKey[i].DataType], ds.Tables[0].PrimaryKey[i].MaxLength, ds.Tables[0].PrimaryKey[i].ColumnName);
}
else
{
deleteCommand.Parameters.Add("@" + ds.Tables[0].PrimaryKey[i] + "", (DB2Type)typesRelation[ds.Tables[0].PrimaryKey[i].DataType], (int)lengthsRelation[(DB2Type)typesRelation[ds.Tables[0].PrimaryKey[i].DataType]], ds.Tables[0].PrimaryKey[i].ColumnName);
}
}
adapter.DeleteCommand = deleteCommand;
deleteStatus = false;
try
{
affectedRows = adapter.Update(ds.Tables[index]);
deleteStatus = true;
HistorialSeguimientoTabla(TABLA_SEGUIR, sqlSelect); //Almacenamiento historial de seguimiento a tabla.
}
catch (Exception e)
{
exceptions += e.ToString() + cambioLinea;
AdministradorCarpetasRegistro.GrabarLogs(TipoRegistro.Error, Sql, e, exceptions);
}
CloseConnection(dc);
return(ds);
}
public DataSet Insert(DataSet ds, int index)
{
int i = 0;
string table = "Table";
string fieldsString = "(";
string valuesString = "(";
string[] sqlWords = sqlSelect.Split(' ');
for (i = 0; i < sqlWords.Length; i++)
{
if (sqlWords[i] == "FROM")
{
table = sqlWords[i + 1];
break;
}
}
for (i = 0; i < ds.Tables[index].Columns.Count; i++)
{
fieldsString += ds.Tables[index].Columns[i].ColumnName;
valuesString += "?";
if (i != ds.Tables[index].Columns.Count - 1)
{
fieldsString += ", ";
valuesString += ", ";
}
}
fieldsString += ")";
valuesString += ")";
DB2Connection dc = OpenConnection();
DB2DataAdapter adapter = new DB2DataAdapter(sqlSelect, dc);
Sql = "INSERT INTO " + table + " " + fieldsString + " VALUES " + valuesString + "";
AdministradorCarpetasRegistro.GrabarLogs(TipoRegistro.Actividad, Sql, null, string.Empty);
DB2Command insertCommand = new DB2Command(Sql, adapter.SelectCommand.Connection);
//exceptions += ds.Tables[0].Columns[0].DataType.ToString() + ":::" + cambioLinea;
for (i = 0; i < ds.Tables[index].Columns.Count; i++)
{
if (ds.Tables[index].Columns[i].DataType.ToString() == "System.String")
{
insertCommand.Parameters.Add("@" + ds.Tables[index].Columns[i] + "", (DB2Type)typesRelation[ds.Tables[index].Columns[i].DataType], ds.Tables[index].Columns[i].MaxLength, ds.Tables[index].Columns[i].ColumnName);
}
else
{
insertCommand.Parameters.Add("@" + ds.Tables[index].Columns[i] + "", (DB2Type)typesRelation[ds.Tables[index].Columns[i].DataType], (int)lengthsRelation[(DB2Type)typesRelation[ds.Tables[index].Columns[i].DataType]], ds.Tables[index].Columns[i].ColumnName);
}
}
adapter.InsertCommand = insertCommand;
insertStatus = false;
try
{
affectedRows = adapter.Update(ds.Tables[index]);
insertStatus = true;
HistorialSeguimientoTabla(TABLA_SEGUIR, sqlSelect); //Almacenamiento historial de seguimiento a tabla.
}
catch (Exception e)
{
exceptions += e.ToString() + "INSERT INTO " + table + " " + fieldsString + " VALUES " + valuesString + "" + cambioLinea;
exceptions += "parameters: " + insertCommand.Parameters.Count.ToString() + cambioLinea;
AdministradorCarpetasRegistro.GrabarLogs(TipoRegistro.Error, Sql, e, exceptions);
}
CloseConnection(dc);
return(ds);
}