/// <summary> /// 단일 실행형(일부 컬럼만 업데이트 할때) /// </summary> /// <param name="dt">업데이트할 테이블</param> /// <param name="strColNm">수정할 컬럼이름</param> /// <returns></returns> public int updateTable(DataTable dt, string strTableName, string strColNm) { int affectRow = 0; OracleCommandBuilder cb; this.oDataAdapter = new OracleDataAdapter("SELECT " + strColNm + " FROM " + strTableName, this.oConn); cb = new OracleCommandBuilder(this.oDataAdapter); this.oDataAdapter.DeleteCommand = cb.GetDeleteCommand(); this.oDataAdapter.InsertCommand = cb.GetInsertCommand(); this.oDataAdapter.UpdateCommand = cb.GetUpdateCommand(); try { this.oConn.Open(); this.oTr = this.oConn.BeginTransaction(); this.oDataAdapter.DeleteCommand.Transaction = this.oTr; this.oDataAdapter.InsertCommand.Transaction = this.oTr; this.oDataAdapter.UpdateCommand.Transaction = this.oTr; affectRow = this.oDataAdapter.Update(dt); this.oTr.Commit(); return(affectRow); } catch (Exception ex) { this.oTr.Rollback(); this.oConn.Close(); throw new Exception("DB 업로드 오류", ex); } }
public static void Main() { OracleConnection thisConnection = new OracleConnection( @"data source=topcredu;User ID=scott;Password=tiger"); thisConnection.Open(); OracleDataAdapter thisAdapter = new OracleDataAdapter("SELECT * from emp", thisConnection); OracleCommandBuilder thisBuilder = new OracleCommandBuilder(thisAdapter); Console.WriteLine("SQL SELECT Command is:\n{0}\n", thisAdapter.SelectCommand.CommandText); OracleCommand updateCommand = thisBuilder.GetUpdateCommand(); Console.WriteLine("SQL UPDATE Command is:\n{0}\n", updateCommand.CommandText); OracleCommand insertCommand = thisBuilder.GetInsertCommand(); Console.WriteLine("SQL INSERT Command is:\n{0}\n", insertCommand.CommandText); OracleCommand deleteCommand = thisBuilder.GetDeleteCommand(); Console.WriteLine("SQL DELETE Command is:\n{0}", deleteCommand.CommandText); thisConnection.Close(); }
void comboBox_CurrentChanged(string curItem) { //string curItem = (string)sender; string sql = @"select * from tracker_tb_vl where sgroup = '%group%' and prj = 'BPGOM' order by 1,2".Replace("%group%", curItem); try { //ds = New DataSet("sGroup") da_sGroup = new OracleDataAdapter(sql, cnn); da_sGroup.AcceptChangesDuringUpdate = true; OracleCommandBuilder cb = new OracleCommandBuilder(da_sGroup); //set_upd_cmd() da_sGroup.UpdateCommand = cb.GetUpdateCommand(); da_sGroup.InsertCommand = cb.GetInsertCommand(); da_sGroup.DeleteCommand = cb.GetDeleteCommand(); DataTable dt = MyDb.Oracle.sql2DT(sql, (System.Data.Common.DbConnection)cnn); dt.TableName = "sGroup"; ds = new DataSet("sGroup"); ds.Tables.Add(dt); //Class_Db_Oracle.get_crud(ref canInsert, ref canSelect, ref canUpdate, ref canDelete, wbs, // Class_Common.CurrentUserDisc(xMainWindow), cnn);//.ParentForm), cnn); xDataGrid.DataSource = ds.Tables["sGroup"].DefaultView; //ultraGrid1.DataMember = "sGroup"; } catch (Exception ex) { Console.WriteLine(ex.Message); } }
//////////////////////////////////////////////////// public override IDbCommand GetDeleteCommand(DataTable table, IDbDataAdapter adapter) { OracleCommandBuilder builder = new OracleCommandBuilder((OracleDataAdapter)adapter); OracleCommand command = builder.GetDeleteCommand(); if (m_connexion.IsInTrans()) { command.Transaction = (OracleTransaction)m_connexion.Transaction; } return(command); }
public void UpdateTableContent(string tableName, DataTable table) { using (OracleCommand command = _connection.CreateCommand( )) { command.CommandText = string.Format("select * from {0}", tableName); using (OracleDataAdapter adapter = new OracleDataAdapter(command)) { using (OracleCommandBuilder builder = new OracleCommandBuilder(adapter)) { adapter.UpdateCommand = builder.GetUpdateCommand( ); adapter.InsertCommand = builder.GetInsertCommand( ); adapter.DeleteCommand = builder.GetDeleteCommand( ); adapter.Update(table); } } } }
public void DeleteRow(string tableName, object[] itemArray) { if (tableName == "" || tableName == null) { return; } using (var cmd = _databaseConnection.CreateCommand()) { cmd.CommandText = "select * from " + tableName; DataTable table = new DataTable(); using (var adapter = new OracleDataAdapter(cmd)) { adapter.Fill(table); for (int i = 0; i < table.Rows.Count; i++) { bool equal = true; for (int j = 0; j < itemArray.Length; j++) { if (!itemArray[j].Equals(table.Rows[i].ItemArray[j])) { equal = false; break; } } if (equal) { table.Rows[i].Delete(); break; } } OracleCommandBuilder commandBuilder = new OracleCommandBuilder(adapter); //adapter.UpdateCommand = commandBuilder.GetUpdateCommand(); adapter.DeleteCommand = commandBuilder.GetDeleteCommand(); adapter.Update(table); } } }
/// <summary> /// Update Multi Table /// </summary> /// <param name="ds">Multi Update Data</param> /// <param name="sqlList">Multi Select sql</param> /// <returns></returns> public ResultData UpdateMultiTable(List <DataTable> ds, List <string> sqlList) { var connection = CreateConnection(); OracleConnection oracleConn = (OracleConnection)connection; OracleTransaction tran = oracleConn.BeginTransaction(IsolationLevel.ReadCommitted); ResultData resultData = new ResultData(); resultData.Status = ResultStatus.Fail; try { foreach (DataTable inputDt in ds) { OracleCommand command = new OracleCommand(sqlList[ds.IndexOf(inputDt)], oracleConn); OracleDataAdapter dataAdapter = new OracleDataAdapter(); dataAdapter.SelectCommand = command; OracleCommandBuilder commandBuilder = new OracleCommandBuilder(dataAdapter); dataAdapter.InsertCommand = commandBuilder.GetInsertCommand(); dataAdapter.UpdateCommand = commandBuilder.GetUpdateCommand(); dataAdapter.DeleteCommand = commandBuilder.GetDeleteCommand(); command.Transaction = tran; int rows = dataAdapter.Update(inputDt); if (rows >= 1) { resultData.Status = ResultStatus.Success; } else { tran.Rollback(); return(resultData); } } tran.Commit(); } catch (Exception ex) { tran.Rollback(); throw ex; } return(resultData); }
//연속실행형 public void updateDataSet(DataSet ds) { //DataTable dt; int i = 0; OracleCommandBuilder cb; OracleDataAdapter[] oDA = new OracleDataAdapter[ds.Tables.Count]; foreach (DataTable dt in ds.Tables) { oDA[i] = new OracleDataAdapter("SELECT * FROM " + dt.TableName, this.oConn); cb = new OracleCommandBuilder(oDA[i]); oDA[i].DeleteCommand = cb.GetDeleteCommand(); oDA[i].InsertCommand = cb.GetInsertCommand(); oDA[i].UpdateCommand = cb.GetUpdateCommand(); i++; } try { i = 0; this.oConn.Open(); this.oTr = this.oConn.BeginTransaction(); foreach (DataTable updateTable in ds.Tables) { oDA[i].DeleteCommand.Transaction = this.oTr; oDA[i].InsertCommand.Transaction = this.oTr; oDA[i].UpdateCommand.Transaction = this.oTr; oDA[i].Update(updateTable); i++; } this.oTr.Commit(); } catch (Exception oErr) { this.oTr.Rollback(); this.oConn.Close(); throw oErr; } }
/// <summary> /// use oraclecommand update DB by sql string and update column /// </summary> /// <param name="inputDT"></param> /// <param name="sql"></param> /// <returns></returns> public ResultData UpdateOracleDB(DataTable inputDT, string sql) { var connection = CreateConnection(); OracleConnection oracleConn = (OracleConnection)connection; OracleCommand command = new OracleCommand(sql, oracleConn); OracleTransaction tran = oracleConn.BeginTransaction(IsolationLevel.ReadCommitted); ResultData resultData = new ResultData(); resultData.Status = ResultStatus.Fail; command.Transaction = tran; try { OracleDataAdapter dataAdapter = new OracleDataAdapter(); dataAdapter.SelectCommand = command; OracleCommandBuilder commandBuilder = new OracleCommandBuilder(dataAdapter); dataAdapter.InsertCommand = commandBuilder.GetInsertCommand(); dataAdapter.UpdateCommand = commandBuilder.GetUpdateCommand(); dataAdapter.DeleteCommand = commandBuilder.GetDeleteCommand(); int rows = dataAdapter.Update(inputDT); if (rows >= 1) { tran.Commit(); resultData.Status = ResultStatus.Success; return(resultData); } else { tran.Rollback(); return(resultData); } } catch (Exception ex) { tran.Rollback(); throw ex; } }
/// <summary> /// ����selectedCommandText�����OracleDataAdapter�����Զ����� /// InsertCommand��DeleteCommand��UpdateCommand /// </summary> /// <param name="selectedCommandText">selectedCommandText</param> /// <returns>The SqlDataAdapter</returns> public override IDbDataAdapter GetDataAdapter(string selectCommandText, IDbConnection connection) { OracleDataAdapter adapter = new OracleDataAdapter(selectCommandText,(OracleConnection)connection); adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey; using(OracleCommandBuilder builder = new OracleCommandBuilder(adapter)) { adapter.InsertCommand = (OracleCommand)((ICloneable)builder.GetInsertCommand()).Clone(); adapter.DeleteCommand = (OracleCommand)((ICloneable)builder.GetDeleteCommand()).Clone(); adapter.UpdateCommand = (OracleCommand)((ICloneable)builder.GetUpdateCommand()).Clone(); } return adapter; }
public OracleDataAdapter GetDataAdapterForOracle(string Sql, List <ReportFilter> Filter = null) { OracleConnection conn = null; OracleDataAdapter adp = null; OracleCommandBuilder cmdb = null; try { ConnectionDriversConfig driversConfig = DMEEditor.Utilfunction.LinkConnection2Drivers(Dataconnection.ConnectionProp); //string adtype = Dataconnection.DataSourceDriver.AdapterType; //string cmdtype = Dataconnection.DataSourceDriver.CommandBuilderType; //string cmdbuildername = driversConfig.CommandBuilderType; //Type adcbuilderType = Type.GetType("OracleCommandBuilder"); //List<ConstructorInfo> lsc = DMEEditor.assemblyHandler.GetInstance(adtype).GetType().GetConstructors().ToList(); ; //List<ConstructorInfo> lsc2 = DMEEditor.assemblyHandler.GetInstance(cmdbuildername).GetType().GetConstructors().ToList(); ; //ConstructorInfo ctor = lsc[GetCtorForAdapter(lsc)]; //ConstructorInfo BuilderConstructer = lsc2[GetCtorForCommandBuilder(adcbuilderType.GetConstructors().ToList())]; //ObjectActivator<Oracle.ManagedDataAccess.Client.OracleDataAdapter> adpActivator = GetActivator<Oracle.ManagedDataAccess.Client.OracleDataAdapter>(ctor); //ObjectActivator<Oracle.ManagedDataAccess.Client.OracleCommandBuilder> cmdbuilderActivator = GetActivator<Oracle.ManagedDataAccess.Client.OracleCommandBuilder>(BuilderConstructer); //create an instance: // adp = OracleDataAdapter( RDBMSConnection.DbConn); conn = (OracleConnection)RDBMSConnection.DbConn; adp = new OracleDataAdapter(Sql, conn); cmdb = new OracleCommandBuilder(adp); try { //Oracle.ManagedDataAccess.Client.OracleCommand cmdBuilder = cmdbuilderActivator(adp); if (Filter != null) { if (Filter.Count > 0) { if (Filter.Where(p => !string.IsNullOrEmpty(p.FilterValue) && !string.IsNullOrWhiteSpace(p.FilterValue) && !string.IsNullOrEmpty(p.Operator) && !string.IsNullOrWhiteSpace(p.Operator)).Any()) { foreach (ReportFilter item in Filter.Where(p => !string.IsNullOrEmpty(p.FilterValue) && !string.IsNullOrWhiteSpace(p.FilterValue))) { OracleParameter parameter = adp.SelectCommand.CreateParameter(); string dr = Filter.Where(i => i.FieldName == item.FieldName).FirstOrDefault().FilterValue; parameter.ParameterName = "p_" + item.FieldName; if (item.valueType == "System.DateTime") { parameter.DbType = DbType.DateTime; parameter.Value = DateTime.Parse(dr).ToShortDateString(); } else { parameter.Value = dr; } if (item.Operator.ToLower() == "between") { OracleParameter parameter1 = adp.SelectCommand.CreateParameter(); parameter1.ParameterName = "p_" + item.FieldName + "1"; parameter1.DbType = DbType.DateTime; string dr1 = Filter.Where(i => i.FieldName == item.FieldName).FirstOrDefault().FilterValue1; parameter1.Value = DateTime.Parse(dr1).ToShortDateString(); adp.SelectCommand.Parameters.Add(parameter1); } // parameter.DbType = TypeToDbType(tb.Columns[item.fieldname].DataType); adp.SelectCommand.Parameters.Add(parameter); } } } } // adp.ReturnProviderSpecificTypes = true; adp.SuppressGetDecimalInvalidCastException = true; adp.InsertCommand = cmdb.GetInsertCommand(true); adp.UpdateCommand = cmdb.GetUpdateCommand(true); adp.DeleteCommand = cmdb.GetDeleteCommand(true); } catch (Exception ex) { // DMEEditor.AddLogMessage("Fail", $"Error in Creating builder commands {ex.Message}", DateTime.Now, -1, ex.Message, Errors.Failed); } adp.MissingSchemaAction = MissingSchemaAction.AddWithKey; adp.MissingMappingAction = MissingMappingAction.Passthrough; ErrorObject.Flag = Errors.Ok; } catch (Exception ex) { DMEEditor.AddLogMessage("Fail", $"Error in Creating Adapter {ex.Message}", DateTime.Now, -1, ex.Message, Errors.Failed); adp = null; } return(adp); }