C# OracleConnection.CreateCommand方法代碼示例

        public void TestConnectionWithString() 
        {
            int ret = 0;
            string connstring = "Data Source=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521)))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=pdborcl)));User Id=system;Password=admin1234;";

            string sql = @"select *from user_tables;";
            sql = getSqlString();
//            string sql = @"begin 
//create sequence TABLE22_SEQ;
//create table table22(id NUMBER DEFAULT 1);
//end;";
            using (DbConnection conn = new  OracleConnection(connstring))
            {
                conn.Open();
                DbCommand command = conn.CreateCommand();
                command.CommandType = CommandType.Text;
                command.CommandText = sql;

                ret = command.ExecuteNonQuery();

                conn.Close();
            }

            Assert.AreNotEqual(ret,0);
        }

        static void ADO_NET_Example()
        {
            var cs = "Data Source=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=www.machinejar.com)(PORT=1522)))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=devorc02)));User Id=hr;Password=hr;";

            OracleConnection con = new OracleConnection();
            con.ConnectionString = cs;
            con.Open();

            OracleCommand cmd = con.CreateCommand();
            cmd.CommandText = "HR_DEPTPERCOUNTRY";
            cmd.CommandType = System.Data.CommandType.StoredProcedure;

            OracleParameter orcParam = new OracleParameter("CNTR_CD", OracleDbType.Varchar2);
            orcParam.Size = 50;
            orcParam.Value = "UK";
            orcParam.Direction = System.Data.ParameterDirection.Input;
            cmd.Parameters.Add(orcParam);

            OracleParameter orcOutParam = new OracleParameter("RS_CURSOR", OracleDbType.RefCursor);
            orcOutParam.Size = 50;
            orcOutParam.Direction = System.Data.ParameterDirection.Output;
            cmd.Parameters.Add(orcOutParam);

            cmd.ExecuteNonQuery();

            OracleDataReader reader = ((OracleRefCursor)cmd.Parameters["RS_CURSOR"].Value).GetDataReader();

            while (reader.Read())
            {
                Console.WriteLine("Dept Name: {0} | Address: {1}, | Country: {2}", reader.GetString(0), reader.GetString(1), reader.GetString(2));
            }
        }

        /// <summary>
        /// 測試 調用 Oracle 返回結果集的函數.
        /// </summary>
        private void CallFuncWithTable(OracleConnection conn)
        {
            // 創建一個 Command.
            OracleCommand testCommand = conn.CreateCommand();

            // 定義需要執行的SQL語句.
            testCommand.CommandText = "pkg_HelloWorld.getHelloWorld";

            // 定義好，本次執行的類型，是存儲過程.
            testCommand.CommandType = CommandType.StoredProcedure;

            // 定義好，我這個參數，是 遊標 + 返回值.
            OracleParameter para = new OracleParameter("c", OracleDbType.RefCursor);
            para.Direction = ParameterDirection.ReturnValue;
            testCommand.Parameters.Add(para);


            // 執行SQL命令，結果存儲到Reader中.
            OracleDataReader testReader = testCommand.ExecuteReader();

            // 處理檢索出來的每一條數據.
            while (testReader.Read())
            {
                // 將檢索出來的數據，輸出到屏幕上.
                Console.WriteLine("調用函數:{0}; 返回:{1} - {2}",
                    testCommand.CommandText, testReader[0], testReader[1]
                    );
            }

            // 關閉Reader.
            testReader.Close();
        }

 public ChangeResultSettings IsExistsEmail(string email)
 {
     var result = new ChangeResultSettings();
     try
     {
         ConnectDB.CloseConnection(_connectGs);
         _connectGs = new OracleConnection();
         _connectGs = ConnectDB.GetOracleConnection(_connectGs);
         var cm = _connectGs.CreateCommand();
         cm.CommandText = "usp_check_exists_email";
         cm.CommandType = CommandType.StoredProcedure;
         cm.Parameters.Add(new OracleParameter("email_check", OracleDbType.Varchar2)).Value = email;
         var op = new OracleParameter("total_result", OracleDbType.Long, 15) { Direction = ParameterDirection.Output };
         cm.Parameters.Add(op);
         cm.ExecuteNonQuery();
         var total = Convert.ToInt32(cm.Parameters["total_result"].Value.ToString());
         switch (total)
         {
             case 0:
                 result.ChangeResult = ChangeResult.ThanhCong;
                 break;
             default:
                 result.ChangeResult = ChangeResult.ThatBai;
                 break;
         }
     }
     catch (Exception ex)
     {
         result.Message = "Có lỗi trong quá trình kiểm tra email. Vui lòng thử lại!";
         result.ChangeResult = ChangeResult.ThatBai;
     }
     return result;
 }

        public DataTable ChiTietDuAn(string mdv, string nsd, string pas, string maDonVi, long idDuAn)
        {
            try
            {
                ConnectDB.CloseConnection(_connectGs);
                _connectGs = new OracleConnection();
                _connectGs = ConnectDB.GetOracleConnection(_connectGs);
                var cm = _connectGs.CreateCommand();
                cm.CommandText = "usp_ChiTiet_DuAn";
                cm.CommandType = CommandType.StoredProcedure;
                cm.Parameters.Add(new OracleParameter("ma_donvi", OracleDbType.Varchar2)).Value = "";
                cm.Parameters.Add(new OracleParameter("nsd", OracleDbType.Varchar2)).Value = "";
                cm.Parameters.Add(new OracleParameter("pas", OracleDbType.Varchar2)).Value = "";
                cm.Parameters.Add(new OracleParameter("id_duan", OracleDbType.Long)).Value = idDuAn;
                cm.Parameters.Add(new OracleParameter("ma_donvi_thuchien", OracleDbType.Varchar2)).Value = maDonVi;
                cm.Parameters.Add(new OracleParameter("cs_lke", OracleDbType.RefCursor)).Direction =
                    ParameterDirection.Output;

                var tableGs = new DataTable();
                _oracleAdapter = new OracleDataAdapter(cm);
                _oracleAdapter.Fill(tableGs);
                return tableGs;
            }
            catch (Exception)
            {
                return null;
            }
            finally
            {
                ConnectDB.CloseConnection(_connectGs);
            }
        }

 public ChangeResultSettings CapNhatLoaiNguonVon(string mdv, string nsd, string pas, string maDonVi, long idDuAn, int loaiNguonVon)
 {
     var result = new ChangeResultSettings();
     try
     {
         ConnectDB.CloseConnection(_connectGs);
         _connectGs = new OracleConnection();
         _connectGs = ConnectDB.GetOracleConnection(_connectGs);
         var cm = _connectGs.CreateCommand();
         cm.CommandText = "usp_CapNhat_DuAn_LoaiNguonVon";
         cm.CommandType = CommandType.StoredProcedure;
         cm.Parameters.Add(new OracleParameter("ma_donvi", OracleDbType.Varchar2)).Value = "";
         cm.Parameters.Add(new OracleParameter("nsd", OracleDbType.Varchar2)).Value = "";
         cm.Parameters.Add(new OracleParameter("pas", OracleDbType.Varchar2)).Value = "";
         cm.Parameters.Add(new OracleParameter("id_duan", OracleDbType.Long)).Value = idDuAn;
         cm.Parameters.Add(new OracleParameter("ma_donvi_thuchien", OracleDbType.Varchar2)).Value = maDonVi;
         cm.Parameters.Add(new OracleParameter("loai_nguonvon", OracleDbType.Int32)).Value = loaiNguonVon;
         var op = new OracleParameter("row_updated", OracleDbType.Long, 15) { Direction = ParameterDirection.Output };
         cm.Parameters.Add(op);
         cm.ExecuteNonQuery();
         var rowUpdated = cm.Parameters["row_updated"].Value.ToString();
         if (rowUpdated == "0")
             result.ChangeResult = ChangeResult.ThatBai;
     }
     catch (Exception ex)
     {
         result.ChangeResult = ChangeResult.ThatBai;
         result.Message = ex.Message;
         throw;
     }
     return result;
 }

        /// <summary>
        /// Construtor, por default já conecta no banco de dados, caso não queira passe false no parâmetro.
        /// Construct open database connetion by default, if you don't want set startConnection = false
        /// </summary>
        /// <param name="startConnection">
        ///     Conectar no banco de dados, default true
        ///     Open database connection
        /// </param>
        public ODPNetConnect(bool startConnection = false)
        {
            ClearMessages();
            if (!startConnection)
            {
                return;
            }

            myConnection = GetConnection();
            if (!String.IsNullOrWhiteSpace(this.ERROR)) //Erro ao conectar. Connection error
            {
                return;
            }

            try
            {
                if (!myConnection.State.ToString().Equals("Open"))
                {
                    myConnection.Open();
                }

                myCommand = myConnection.CreateCommand();
            }
            catch (Exception e)
            {
                ERROR = e.Message;
                if (e.InnerException != null)
                {
                    ERROR += e.InnerException.Message;
                }
            }
        }

        /// <summary>
        /// выполнение запроса к базе
        /// </summary>
        /// <param name="cmdString">строка запроса</param>
        /// <returns>возвращает значение bool выполнено или нет</returns>
        public static bool ExecuteQuery(string cmdString)
        {
            using (var connection = new OracleConnection(GetConnectionString()))
            {
                var cmd = connection.CreateCommand();
                cmd.CommandText = cmdString;
                try
                {
                    connection.Open();
                    var transaction = connection.BeginTransaction();
                    Debug.WriteLine("Соединение открыто");
                    cmd.ExecuteNonQuery();
                    transaction.Commit();
                    Debug.WriteLine("Запрос выполнен");

                }
                catch (OracleException exception)
                {
                    Debug.WriteLine(exception.Message);
                    return false;
                }
                finally
                {
                    connection.Close();
                    Debug.WriteLine("Соединение закрыто");
                }
            }
            return true;
        }

 public DatabaseKoppeling()
 {
     string user = "TURVEN";
     string pw = "FRAGIE";
     conn = new OracleConnection();
     command = conn.CreateCommand();
     conn.ConnectionString = "User Id=" + user + ";Password=" + pw + ";Data Source=" + " //localhost:1521/xe" + ";";
 }

        public void Start()
        {
            string connectionString = "Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.3.92)(PORT=1521))(CONNECT_DATA=(SID=XE)));User Id=sa;Password=sa123456;";
            OracleConnection conn = new OracleConnection(connectionString);
            conn.Open();

            OracleCommand cmd = conn.CreateCommand();
            cmd.CommandText = "select * from test";
            IDataReader dr = cmd.ExecuteReader();
        }

 public DBReader(String ConnStr_, String PipeName_)
 {
     using (Logger l = new Logger("PipeCatcher"))
     {
         ConnStr = ConnStr_;
         PipeName = PipeName_;
         OraConn = new OracleConnection(ConnStr);
         OraCmd = OraConn.CreateCommand();
         l.msg("A new DBReader created ConnStr={0} PipeName={1}", ConnStr, PipeName);
     }
 }

 public static void CreateQueueIfNecessary(Address address, string connectionString)
 {
     using (OracleConnection connection = new OracleConnection(connectionString))
     {
         OracleCommand cmd = connection.CreateCommand();
         cmd.CommandText = InstallSql;
         cmd.Parameters.Add("queue", NormalizeQueueName(address));
         cmd.Parameters.Add("queueTable", (NormalizeQueueName(address) + "_tab").ToUpper());
         connection.Open();
         cmd.ExecuteNonQuery();
     }
 }

        public static List<MessageInfo> GetMessages(string businessId, DBConnection connection)
        {
            var connString = connection.getConnectionString();

               var result = new List<MessageInfo>();

               var output = "npu, correlationNpu, businessid, creationdate, processid, interfacetoinvoke, operation, payload";
               var filter = string.Format(" businessId = '{0}' ", businessId) ;
               var orderBy = " ORDER BY creationdate ASC";

               if (!string.IsNullOrEmpty(Configuration.IgnoreOperations))
               {
               var opers = Configuration.IgnoreOperations.Split(';');

               var formOper = " {0} AND NPU not like '%{1}%' ";

               foreach (var o in opers)
               {
                   filter = string.Format(formOper, filter, o);
               }
               }

               using(OracleConnection conn = new OracleConnection(connString)){

               var command = conn.CreateCommand();

               command.CommandText = connection.CreateQuery(output, "pt_msg_inbound", filter, orderBy);
               conn.Open();

               var reader = command.ExecuteReader();

               while (reader.Read())
               {
                   var msg = MessageInfo.LoadFromDB(reader);
                   result.Add(msg);
               }

               command.CommandText = connection.CreateQuery(output, "pt_msg_inbound_archive", filter, orderBy);
               reader = command.ExecuteReader();

               while (reader.Read())
               {
                   var msg = MessageInfo.LoadFromDB(reader);
                   result.Add(msg);

               }

               }

               result = result.OrderBy(a => a.ReceivedDatetime).ToList();

               return result;
        }

        /// <summary>
        /// Initializes the message receiver.
        /// </summary>
        /// <param name="address">The address of the message source.</param>
        /// <param name="transactional">Indicates if the receiver should be transactional.</param>
        public void Init(Address address, bool transactional)
        {
            this.inputQueueAddress = OracleAqsUtilities.NormalizeQueueName(address);

            using (OracleConnection conn = new OracleConnection(this.ConnectionString))
            {
                OracleCommand cmd = conn.CreateCommand();
                cmd.CommandText = "SELECT queue_table FROM dba_queues WHERE name = :queue";
                cmd.Parameters.Add("queue", this.inputQueueAddress);
                conn.Open();
                this.queueTable = cmd.ExecuteScalar() as string;
            }
        }

        public static DataSet getResultTable(OracleConnection conn, string sqlText)
        {
            OracleCommand cmd = conn.CreateCommand();

            cmd.CommandText = sqlText;

            OracleDataAdapter adapter = new OracleDataAdapter(cmd);
            DataSet ds = new DataSet();
            ds.Tables.Add(new DataTable("DbSource"));
            adapter.Fill(ds, "DbSource");

            return ds;
        }