Esempio n. 1
0
        /// <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);
            }
        }
Esempio n. 2
0
    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();
    }
Esempio n. 3
0
        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);
            }
        }
Esempio n. 4
0
        ////////////////////////////////////////////////////
        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);
        }
Esempio n. 5
0
 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);
             }
         }
     }
 }
Esempio n. 6
0
        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);
                }
            }
        }
Esempio n. 7
0
        /// <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);
        }
Esempio n. 8
0
        //연속실행형

        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;
            }
        }
Esempio n. 9
0
        /// <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;
            }
        }
Esempio n. 10
0
 /// <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;
 }
Esempio n. 11
0
        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);
        }