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