C# OracleClient.OracleConnection类代码示例

        /// <summary>
        /// 释放当前连接
        /// </summary>
        /// <param name="conn">oracle连接通道</param>
        /// <param name="connStr">oracle连接字符串</param>
        public static void freeConnectionToPool(OracleConnection conn,string connStr)
        {
            //如果当前连接字符串不在连接管理器内，首先在管理器中创建
            if (queueManager.ContainsKey(connStr) == false)
                queueManager.Add(connStr, new Queue<OracleConnection>());

            lock (queueManager[connStr])
            {
                if (queueManager[connStr].Count < maxOpen)
                {
                    queueManager[connStr].Enqueue(conn);
                }
                else
                {
                    try
                    {
                        conn.Close();
                        conn = null;
                    }
                    catch (Exception e)
                    {
                        Console.WriteLine(e);
                    }
                }

            }
        }

        /// <summary>
        /// This method opens (if necessary) and assigns a connection, transaction, command type and parameters 
        /// to the provided command.
        /// </summary>
        /// <param name="command">the OracleCommand to be prepared</param>
        /// <param name="connection">a valid OracleConnection, on which to execute this command</param>
        /// <param name="transaction">a valid OracleTransaction, or 'null'</param>
        /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
        /// <param name="commandText">the stored procedure name or PL/SQL command</param> 
        /// <param name="commandParameters">an array of OracleParameters to be associated with the command or 'null' if no parameters are required</param>
        private static void PrepareCommand(OracleCommand command, OracleConnection connection, OracleTransaction transaction, CommandType commandType, string commandText, OracleParameter[] commandParameters)
        {
            //if the provided connection is not open, we will open it
            if (connection.State != ConnectionState.Open)
            {
                connection.Open();
            }

            //associate the connection with the command
            command.Connection = connection;

            //set the command text (stored procedure name or Oracle statement)
            command.CommandText = commandText;
            command.CommandTimeout = 200000;
            //if we were provided a transaction, assign it.
            if (transaction != null)
            {
                command.Transaction = transaction;
            }

            //set the command type
            command.CommandType = commandType;

            //attach the command parameters if they are provided
            if (commandParameters != null)
            {
                AttachParameters(command, commandParameters);
            }

            return;
        }

 public static void FillTreeView(TreeNode node, string sql)
 {
     try
     {
         OracleConnection conn = new OracleConnection(DataAccess.OIDSConnStr);
         conn.Open();
         OracleCommand cmd = conn.CreateCommand();
         cmd.CommandText = sql;
         OracleDataReader dr = cmd.ExecuteReader();
         if (dr.HasRows)
         {
             while (dr.Read())
             {
                 node.Nodes.Add(dr[0].ToString());
             }
             conn.Close();
             dr.Close();
         }
     }
     catch (OracleException ox)
     {
         MessageBox.Show(ox.Message.ToString());
         return;
     }
 }

		// returns a Open connection 
		public override void GetConnection () 
		{
			string connectionString = null;
			try {
				connectionString = ConfigClass.GetElement (configDoc, "database", "connectionString");
			} catch (XPathException e) {
				Console.WriteLine ("Error reading the config file !!");
				Console.WriteLine (e.Message);
				return;
			}
			
			con = new OracleConnection (connectionString);
			
			try {
				con.Open ();
			} catch (OracleException e) {
				Console.WriteLine ("Cannot establish connection with the database");
				Console.WriteLine ("Probably the database is down");
				con = null;
			} catch (InvalidOperationException e) {
				Console.WriteLine ("Cannot open connection");
				Console.WriteLine ("Probably the connection is already open");
				con = null;
			} catch (Exception e) {
				Console.WriteLine ("Cannot open connection");
				con = null;
			}
		}

        /// <summary>
        /// Simplify the creation of a Oracle command object by allowing
        /// a stored procedure and optional parameters to be provided
        /// </summary>
        /// <remarks>
        /// e.g.:  
        ///  OracleCommand command = CreateCommand(conn, "AddCustomer", "CustomerID", "CustomerName");
        /// </remarks>
        /// <param name="connection">A valid OracleConnection object</param>
        /// <param name="spName">The name of the stored procedure</param>
        /// <param name="sourceColumns">An array of string to be assigned as the source columns of the stored procedure parameters</param>
        /// <returns>A valid OracleCommand object</returns>
        public static OracleCommand CreateCommand(OracleConnection connection, string spName, params string[] sourceColumns)
        {
            if( connection == null ) throw new ArgumentNullException( "connection" );
            if( spName == null || spName.Length == 0 ) throw new ArgumentNullException( "spName" );

            // Create a OracleCommand
            OracleCommand cmd = new OracleCommand( spName, connection );
            cmd.CommandType = CommandType.StoredProcedure;

            // If we receive parameter values, we need to figure out where they go
            if ((sourceColumns != null) && (sourceColumns.Length > 0))
            {
                // Pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)
                OracleParameter[] commandParameters = OracleHelperParameterCache.GetSpParameterSet(connection, spName);

                // Assign the provided source columns to these parameters based on parameter order
                for (int index=0; index < sourceColumns.Length; index++)
                    commandParameters[index].SourceColumn = sourceColumns[index];

                // Attach the discovered parameters to the OracleCommand object
                AttachParameters (cmd, commandParameters);
            }

            return cmd;
        }

        private void EditStudentFrm_Load(object sender, EventArgs e)
        {
            //Author: Niall Stack - t00174406
            string CloudDB = "Data Source=cp3dbinstance.c4pxnpz4ojk8.us-east-1.rds.amazonaws.com:1521/cp3db;User Id=sw4;Password=sw4;";
            try
            {
                OracleConnection conn = new OracleConnection(CloudDB);

                OracleCommand cmd = new OracleCommand("SELECT * FROM Students", conn);

                cmd.CommandType = CommandType.Text;

                OracleDataAdapter da = new OracleDataAdapter(cmd);

                DataSet ds = new DataSet();

                da.Fill(ds, "ss");

                studGrd.DataSource = ds.Tables["ss"];

                conn.Close();
            }
            catch (OracleException ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        public A()
        {
            oracon = new OracleConnection("server = 127.0.0.1/orcx; user id = qzdata; password = xie51");
            oracon2 = new OracleConnection("server = 10.5.67.11/pdbqz; user id = qzdata; password = qz9401tw");
            wordapp = new word.Application();
            worddoc = new word.Document();
            worddoc = wordapp.Documents.Add();

            worddoc.SpellingChecked = false;
            worddoc.ShowSpellingErrors = false;

     //       wordapp.Visible = true;
            ta.wordapp = wordapp;
            ta.worddoc = worddoc;
            if (IS_YEAR)
            {
                datestr = dsf.GetDateStr(the_year_begin_int, the_month_begin_int, the_year_end_int, the_month_end_int);
            }
            else
            {
                datestr = dsf.GetDateStr(the_date);
            }
            
       //     datestr_abid = "(" + datestr + "and a.ab_id >=1 and a.ab_id <= 7)";
            datestr_abid = "(" + datestr + "and" + abidstr + ")";
            oracon2.Open();
            orahlper = new OraHelper(oracon2);
            orahlper.feedback = true;

            the_month_begin = new DateTime(the_date.Year, the_date.Month, 1, 0, 0, 0);
            the_month_end = the_month_begin.AddMonths(1).AddSeconds(-1);

        }

        protected void GV_RowUpdating(object sender, GridViewUpdateEventArgs e)
        {
            using (OracleConnection conn = new OracleConnection(DBHelper.ConnectionString))
            {
                string reason_id = GV.DataKeys[e.RowIndex].Values[0].ToString();
                string reason_desc = ((TextBox)GV.Rows[e.RowIndex].FindControl("TxtDesc")).Text;
                string active = ((CheckBox)(GV.Rows[e.RowIndex].FindControl("ChkActive"))).Checked == true ? "1" : "0";
                string sqlupdate = "update jp_lack_reason set reason_desc = '" + reason_desc + "',is_valid='" + active + "' where reason_id = '" + reason_id + "' ";
                OracleCommand updatecomm = new OracleCommand(sqlupdate, conn);
                try
                {
                    conn.Open();
                    updatecomm.ExecuteNonQuery();

                    GV.EditIndex = -1;
                    GVDataBind();

                }
                catch (Exception ex)
                {
                    conn.Close();
                    Response.Write("<script language=javascript>alert('" + ex.Message + "')</script>");
                }
                finally
                {
                    updatecomm.Dispose();
                    conn.Dispose();
                    conn.Close();
                }
            }
        }

 /// <summary>
 /// execute a query£¬return DataSet
 /// </summary>
 /// <param name="SQLString"></param>
 /// <returns>DataSet</returns>
 public static DataSet Query(string connectionString, CommandType cmdType, string SQLString, params OracleParameter[] cmdParms)
 {
     using (OracleConnection connection = new OracleConnection(connectionString))
     {
         OracleCommand cmd = new OracleCommand();
         PrepareCommand(cmd, connection, null, cmdType, SQLString, cmdParms);
         using (OracleDataAdapter da = new OracleDataAdapter(cmd))
         {
             DataSet ds = new DataSet();
             //try
             //{
                 da.Fill(ds, "ds");
                 cmd.Parameters.Clear();
             //}
             //catch (System.Data.OracleClient.OracleException ex)
             //{
             //    throw new Exception(ex.Message);
             //}
             //finally
             //{
             //    if (connection.State != ConnectionState.Closed)
             //    {
             //        connection.Close();
             //    }
             //}
             return ds;
         }
     }
 }

 /// <summary>
 /// 填充combox
 /// </summary>
 /// <param name="cb"></param>
 /// <param name="sql"></param>
 private void FillComb(ComboBox cb,string sql)
 {
     try
     {
         OracleConnection conn = new OracleConnection(DataAccess.OIDSConnStr);
         conn.Open();
         OracleCommand cmd = conn.CreateCommand();
         cmd.CommandText = sql;
         OracleDataReader dr = cmd.ExecuteReader();
         if (dr.HasRows)
         {
             cb.Items.Clear();
             cb.Items.Add("");
             while (dr.Read())
             {
                 cb.Items.Add(dr[0].ToString());
             }
             conn.Close();
             dr.Close();
         }
     }
     catch (OracleException ox)
     {
         MessageBox.Show(ox.Message.ToString());
         return;
     }
 }

        /// <summary>
        /// 执行存储过程
        /// </summary>
        /// <param name="name"></param>
        /// <param name="paramList"></param>
        /// <returns></returns>
        public static bool ExecuteProduce(string name, IList<DbParameter> paramList)
        {
            OracleConnection conn = new OracleConnection();
            conn.ConnectionString = ConnectionString;
            conn.Open();

            OracleCommand dbCommand = new OracleCommand();
            dbCommand.CommandType = CommandType.StoredProcedure;
            dbCommand.CommandText = name;

            try
            {
                foreach (DbParameter param in paramList)
                {
                    dbCommand.Parameters.Add(param);
                }
                dbCommand.ExecuteNonQuery();
                return true;
            }
            catch (Exception ex)
            {
                conn.Close();
                return false;
            }
            finally
            {
                conn.Close();
            }
        }

        public static LoanList GetList(int applicantId)
        {
            LoanList loans = new LoanList();
            using(OracleConnection oraDbConn =
                    new OracleConnection(
                        ConnStringFactory.getConnString(
                            ConnStringFactory.ConnStringType.Oracle))){
                oraDbConn.Open();
                using(OracleCommand getLoansByAppIdCommand = new OracleCommand()){
                    getLoansByAppIdCommand.CommandType = CommandType.StoredProcedure;
                    getLoansByAppIdCommand.CommandText = "LoansPKG.getLoansByAppId";
                    getLoansByAppIdCommand.Connection = oraDbConn;
                    getLoansByAppIdCommand.Parameters.AddWithValue("AppId", applicantId);

                    OracleParameter outputCursor = new OracleParameter("IO_CURSOR", OracleType.Cursor);
                    outputCursor.Direction = ParameterDirection.Output;
                    getLoansByAppIdCommand.Parameters.Add(outputCursor);

                    using (OracleDataReader loanListReader =
                        getLoansByAppIdCommand.ExecuteReader()) {
                        while(loanListReader.Read()){
                            loans.Add(FillDataRecord(loanListReader));
                        }
                    }
                }
            }
            return loans;
        }

		public void run()
		{
			OracleConnection con = null;
			OracleTransaction txn;
			Exception exp = null;
			try
			{
				BeginCase("OracleTransaction Rollback");

				//
				//prepare data
				base.PrepareDataForTesting(MonoTests.System.Data.Utils.ConnectedDataProvider.ConnectionString);

				string Result = "";
				con = new OracleConnection(MonoTests.System.Data.Utils.ConnectedDataProvider.ConnectionString);
				con.Open();
				txn = con.BeginTransaction();
				OracleCommand cmd = new OracleCommand("Update Employees Set LastName = 'StamLastName' Where EmployeeID = 100", con, txn);
				cmd.ExecuteNonQuery();
				txn.Rollback();

				//
				//
				cmd = new OracleCommand("Select LastName From Employees Where EmployeeID = 100", con);
				Result = cmd.ExecuteScalar().ToString();
				Compare(Result,"Last100" );
				this.Log(Result);

			} 
			catch(Exception ex){exp = ex;}
			finally{EndCase(exp); exp = null;}

			if (con.State == ConnectionState.Open) con.Close();
		}

 /// <summary>
 /// 执行SQL语句，返回影响的记录数
 /// </summary>
 /// <param name="SQLString">SQL语句</param>
 /// <returns>影响的记录数</returns>
 public static int ExecuteSql(string SQLString)
 {
     using (OracleConnection connection = new OracleConnection(connectionString))
     {
         using (OracleCommand cmd = new OracleCommand(SQLString, connection))
         {
             try
             {
                 connection.Open();
                 int rows = cmd.ExecuteNonQuery();
                 return rows;
             }
             catch (System.Data.OracleClient.OracleException E)
             {
                 connection.Close();
                 throw new Exception(E.Message);
             }
             finally
             {
                 cmd.Dispose();
                 connection.Close();
             }
         }
     }
 }

		internal OracleTransaction (OracleConnection connection, IsolationLevel isolevel, OciTransactionHandle transaction)
		{
			this.connection = connection;
			this.isolationLevel = isolevel;
			this.transaction = transaction;
			isOpen = true;
		}