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