C# OracleCommand.ExecuteScalar方法代码示例

        public bool CheckUseAcademicPositionName()
        {
            bool result = true;
            OracleConnection conn = ConnectionDB.GetOracleConnection();

            // Create the command
            OracleCommand command = new OracleCommand("SELECT count(ACAD_NAME) FROM TB_ACADEMIC_POSITION WHERE ACAD_NAME = :ACAD_NAME ", conn);

            // Add the parameters.
            command.Parameters.Add(new OracleParameter("ACAD_NAME", ACAD_NAME));
            try
            {
                if (conn.State != ConnectionState.Open)
                {
                    conn.Open();
                }
                int count = (int)(decimal)command.ExecuteScalar();
                if (count >= 1)
                {
                    result = false;
                }

            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                command.Dispose();
                conn.Close();
            }
            return result;
        }

        private void button1_Click(object sender, EventArgs e)
        {
            #region connection
            try
            {
                con = new OracleConnection();
                con.ConnectionString = "Data Source=orcl.iacsd.org;User ID=user101;Password=user101;Unicode=True";
                cmd = new OracleCommand();

                cmd.CommandType = CommandType.Text;
                //Provide the name of stored procedu  re
                cmd.CommandText = "select * from Emp";

                cmd.Connection = con;
                con.Open();
                MessageBox.Show(" Number of Rows =" + cmd.ExecuteScalar().ToString());

            }
            catch (OracleException ex)
            {
                MessageBox.Show(ex.Message);
            }
            finally
            {
                if (con.State == ConnectionState.Open)
                {
                    con.Close();
                }
            }
            #endregion
        }

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

        public string ExecuteScalar(string sql)
        {
            try
            {
                using (OracleConnection connection = new OracleConnection(_ConnectionString))
                {
                    WriteLog("[Oracle]" + sql);
                    connection.Open();
                    var cmd = new OracleCommand(sql, connection);
                    object o = cmd.ExecuteScalar();

                    string s = null;
                    if (o != null && Object.Equals(o, System.DBNull.Value) == false)
                    {
                        s = o.ToString();
                    }

                    WriteLog("[Oracle]查询结果：" + s);

                    return s;
                }
            }
            catch (Exception ex)
            {
                if (AfterExceptionThrow != null) AfterExceptionThrow(ex);
                else throw ex;
                return null;
            }
        }

        public void DoNormalConnectionTest()
        {
            using (OracleConnection conn = new OracleConnection(_ConnectionString))
            {
                OracleCommand comm = new OracleCommand("select * from web_sql");
                comm.Connection = conn;

                conn.Open();

                comm.ExecuteScalar();

                Thread.Sleep(_SleepTime);

                conn.Close();
            }
        }

		public void SetUp()
		{
			Exception exp = null;
			BeginCase("Setup");
			try
			{
				//prepare Data
				OracleCommand cmdPrepare = new OracleCommand("", new OracleConnection(MonoTests.System.Data.Utils.ConnectedDataProvider.ConnectionString));
				cmdPrepare.Connection.Open();
				cmdPrepare.CommandText = "DELETE FROM Employees WHERE EmployeeID = 99999";
				cmdPrepare.ExecuteScalar();
				cmdPrepare.Connection.Close();
				cmdPrepare.Dispose();

				con = new OracleConnection(MonoTests.System.Data.Utils.ConnectedDataProvider.ConnectionString);
				cmd = new OracleCommand("", con);
				con.Open();
			}
			catch(Exception ex){exp = ex;}
			finally{EndCase(exp); exp = null;}
		}

        protected override void Execute(CodeActivityContext context)
        {
            List<Dictionary<string, string>> retorno = new List<Dictionary<string, string>>();
            using (OracleConnection connection =
                        new OracleConnection(connectionStringDestino.Get(context)))
            {
                connection.Open();

                using (OracleCommand command = new OracleCommand(query.Get(context), connection))
                {

                    LogScheduler.Save("Oracle", "Incio Query : \n " + query.Get(context));
                    AddParameters(context, command);
                    if (persist.Get(context))
                    {
                        try
                        {
                            command.ExecuteScalar();
                        }
                        catch (Exception ex)
                        {
                            LogScheduler.Save("OracleUpdate", ex.Message);
                        }

                    }
                    else
                        GetResultadoOracle(retorno, command);

                    LogScheduler.Save("Oracle", "Fim Query : \n " + query.Get(context));

                }

                connection.Close();

            }
            resultQuery.Set(context, retorno);
        }

	public void run()
	{
		Exception exp = null;

		OracleConnection con = new OracleConnection(MonoTests.System.Data.Utils.ConnectedDataProvider.ConnectionString);
		con.Open();

		//Currently not running on Oracle/DB2: .Net-Failed, GH:Pass
		//not runnig on postgres because "The 'current catalog' property is not supported by the 'Mainsoft.JDBC.OLEDB.1' provider."
		if (ConnectedDataProvider.GetDbType(con) != DataBaseServer.Oracle  &&
			ConnectedDataProvider.GetDbType(con) != DataBaseServer.DB2 && 
			ConnectedDataProvider.GetDbType(con) != DataBaseServer.Sybase &&
			ConnectedDataProvider.GetDbType(con) != DataBaseServer.PostgreSQL)
		{
			try
			{
				BeginCase("Change DataBase");
				((IDbConnection)con).ChangeDatabase("GHTDB_EX");
				Compare(((IDbConnection)con).Database , "GHTDB_EX");
			} 
			catch(Exception ex){exp = ex;}
			finally{EndCase(exp); exp = null;}

			try
			{
				BeginCase("Check DataBase Changed ");
				OracleCommand cmd = new OracleCommand("select count(*) from Customers", con);
				object obj = cmd.ExecuteScalar();
				Compare(obj != null, true);
			} 
			catch(Exception ex){exp = ex;}
			finally{EndCase(exp); exp = null;}

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

	}

        /// <summary>
        /// ����/ɾ��/�޸� sys_Roles
        /// </summary>
        /// <param name="fam">sys_RolesTableʵ����</param>
        /// <returns>����0������</returns>
        public override int sys_RolesInsertUpdate(sys_RolesTable fam)
        {
            int rInt = 0;

            using (OracleConnection Conn = GetSqlConnection())
            {
                string CommTxt;
                OracleCommand cmd = new OracleCommand();
                cmd.Connection = Conn;
                if (fam.DB_Option_Action_ == "Insert")
                {
                    CommTxt = "Insert into 	sys_Roles(RoleID,R_UserID,R_RoleName,R_Description)VALUES(SEQ_sys_Roles_ID.NEXTVAL,:R_UserID,:R_RoleName,:R_Description)";
                    cmd.CommandText = CommTxt;

                    cmd.Parameters.Add("R_UserID", OracleType.Int32).Value = fam.R_UserID; //�ǽ������û�ID
                    cmd.Parameters.Add("R_RoleName", OracleType.NVarChar).Value = fam.R_RoleName + ""; //��ɫ����
                    cmd.Parameters.Add("R_Description", OracleType.NVarChar).Value = fam.R_Description + ""; //��ɫ����
                }
                else if (fam.DB_Option_Action_ == "Update")
                {

                    CommTxt = "UPDATE sys_Roles SET	R_UserID = :R_UserID,R_RoleName = :R_RoleName,R_Description = :R_Description WHERE (RoleID = :RoleID)";
                    cmd.CommandText = CommTxt;

                    cmd.Parameters.Add("R_UserID", OracleType.Int32).Value = fam.R_UserID; //�ǽ������û�ID
                    cmd.Parameters.Add("R_RoleName", OracleType.NVarChar).Value = fam.R_RoleName + ""; //��ɫ����
                    cmd.Parameters.Add("R_Description", OracleType.NVarChar).Value = fam.R_Description + ""; //��ɫ����
                    cmd.Parameters.Add("RoleID", OracleType.Int32).Value = fam.RoleID;
                }
                else if (fam.DB_Option_Action_ == "Delete")
                {
                    CommTxt = "Delete from  sys_Roles  WHERE (RoleID = :RoleID)";
                    cmd.CommandText = CommTxt;
                    cmd.Parameters.Add("RoleID", OracleType.Int32).Value = fam.RoleID;
                }
                else
                    throw new ApplicationException("�޷�ʶ��IJ�������!");
                Conn.Open();
                OracleTransaction Tran = Conn.BeginTransaction();
                cmd.Transaction = Tran;
                try
                {
                    rInt = cmd.ExecuteNonQuery();
                    cmd.Parameters.Clear();
                    if (fam.DB_Option_Action_ == "Insert")
                    {
                        cmd.CommandText = "select SEQ_sys_Roles_ID.CURRVAL from  dual";
                        rInt = Convert.ToInt32(cmd.ExecuteScalar());
                    }
                    Tran.Commit();
                }
                catch (Exception ex)
                {
                    Tran.Rollback();
                    throw new ApplicationException(ex.ToString());
                }
                finally
                {
                    Tran.Dispose();
                    cmd.Dispose();
                    Conn.Dispose();
                    Conn.Close();
                }
            }
            return rInt;
        }

 public override object ExecuteScalar(CommandType commandType, string commandText, ParameterCollection commandParameters)
 {
     object obj2 = null;
     using (OracleCommand command = new OracleCommand())
     {
         this.PrepareCommand(command, commandType, commandText, commandParameters);
         obj2 = command.ExecuteScalar();
         command.Parameters.Clear();
     }
     return obj2;
 }

		/// <summary>
		/// This method will prepare table for test
		/// </summary>
		private int prepareTableForTest(OracleConnection con,int recordsNumber,string baseTableName,string keyField
			,params string[] otherNonNullableFieldsName)
		{
			string tableName = getDbObjectName(baseTableName,con);
			OracleCommand cmd = new OracleCommand("select max(" + keyField + ") from " + tableName,con);
			string str_ret = cmd.ExecuteScalar().ToString();
//			Console.WriteLine("ExecuteScalar:" + str_ret);
			// on some databases the max is on a field which is decimal
			decimal maxRecord = decimal.Parse(str_ret);
			int resultCount = Convert.ToInt32(maxRecord)+recordsNumber;
			string sqlStmt = string.Empty;
			string valueStmt = string.Empty;

			//Constrcut the statemnet once : --> TODO://Move this logic to seperate method
			for(int i=0;i<otherNonNullableFieldsName.Length;i++)
			{
				sqlStmt+= otherNonNullableFieldsName[i] + ",";
				valueStmt+="'a',";
			}

			//Trim the last ","
			if (otherNonNullableFieldsName.Length > 0)
			{
				sqlStmt =  sqlStmt.Remove(sqlStmt.Length-1,1);
				sqlStmt = "," + sqlStmt;

				valueStmt =  valueStmt.Remove(valueStmt.Length-1,1);
				valueStmt = "," + valueStmt;
			}
				

			for (int index=Convert.ToInt32(maxRecord)+1;index<=resultCount;index++)
			{
				cmd.CommandText="Insert into " + tableName + " (" + keyField + sqlStmt + ") values ("
					+ index + valueStmt + ")";
				cmd.ExecuteNonQuery();
			}
			return Convert.ToInt32(maxRecord);

		}

		//[Test]
		public void DoTestTypes1(OracleConnection conn)
		{
			DataSet ds = new DataSet();
			OracleCommand comm = new OracleCommand("",conn);
			OracleDataAdapter da = new OracleDataAdapter();
			da.SelectCommand = comm;

			string tableName = getDbObjectName("Employees",conn);
			int expectedRowsCount = 8;

			#region Select by full table name in the same catalog

			string[] arr = new string[2];

			arr[0] = "LastName";
			arr[1] = "FirstName";

			prepareTableForTest(conn,expectedRowsCount,"Employees","EmployeeID",arr);
			comm.CommandText="select max(EmployeeID) from " + tableName;
			// on some databases the max is on a field which is decimal
			decimal maxEmployee = decimal.Parse(comm.ExecuteScalar().ToString()) - expectedRowsCount;

			comm.CommandText = "SELECT EmployeeID FROM " + tableName + " where EmployeeID > " +  maxEmployee.ToString() ;
			da.Fill(ds);

			Exception exp = null;
			try
			{
				BeginCase("Select by full table name in the same catalog");
				Compare(ds.Tables[0].Rows.Count ,expectedRowsCount );
			}
			catch(Exception ex)	{exp = ex;}
			finally	{cleanTableAfterTest(conn,"Employees","EmployeeID",Convert.ToInt32(maxEmployee));
				EndCase(exp); exp = null;}

			#endregion //Select by full table name in the same catalog

		}

 /// <summary>
 /// Executes the SQL statement using <see cref="OracleConnection"/>, and returns the value in the first column 
 /// of the first row in the resultset.
 /// </summary>
 /// <param name="sql">The SQL statement to be executed.</param>
 /// <param name="connection">The <see cref="OracleConnection"/> to use for executing the SQL statement.</param>
 /// <param name="parameters">The parameters to be passed to the SQL stored procedure being executed.</param>
 /// <returns>Value in the first column of the first row in the resultset.</returns>
 public static object ExecuteScalar(this OracleConnection connection, string sql, params object[] parameters)
 {
     OracleCommand command = new OracleCommand(sql, connection);
     command.PopulateParameters(parameters);
     return command.ExecuteScalar();
 }

		public void run()
		{
			Exception exp = null;
			int intRecordsAffected = 0;

			string sql = "Update Shippers Set CompanyName=:CompName Where ShipperID = 2";
			OracleConnection con = new OracleConnection(MonoTests.System.Data.Utils.ConnectedDataProvider.ConnectionString);
			OracleCommand cmd = new OracleCommand("", con);
			con.Open();


			//get expected result
			cmd.CommandText = "select count(*) from Shippers where ShipperID = 2";
			int ExpectedRows = int.Parse(cmd.ExecuteScalar().ToString());

			cmd.CommandText = sql;

			//Currently not running on DB2: .Net-Failed, GH:Pass
			//if (con.Provider.IndexOf("IBMDADB2") >= 0) return ;

			cmd.Parameters.Add(new OracleParameter()); 
			cmd.Parameters[0].ParameterName = "CompName";
			cmd.Parameters[0].OracleType = OracleType.VarChar; //System.InvalidOperationException:
			cmd.Parameters[0].Size = 20; //System.InvalidOperationException
			cmd.Parameters[0].SourceColumn = "CompanyName";
			cmd.Parameters[0].Value = "Comp1";

			try
			{
				BeginCase("Prepare Exception - missing OracleType");
				cmd.Prepare();
			} 
			catch(Exception ex){exp = ex;}
			finally{EndCase(exp); exp = null;}
			cmd.Parameters[0].OracleType = OracleType.VarChar; 

//			try
//			{
//				BeginCase("Prepare Exception - missing Size");
//				try
//				{
//					cmd.Parameters[0].Size = 0;
//					cmd.Prepare();
//				}
//				catch (Exception ex) {exp = ex;}
//				Compare(exp.GetType().FullName, typeof(InvalidOperationException).FullName );
//				exp=null;
//			} 
//			catch(Exception ex){exp = ex;}
//			finally{EndCase(exp); exp = null;}
//			cmd.Parameters[0].Size = 20;

			try
			{
				BeginCase("Prepare Exception - missing Size");
				try
				{
					con.Close();
					cmd.Prepare();
				}
				catch (Exception ex) {exp = ex;}
				Compare(exp.GetType().FullName, typeof(InvalidOperationException).FullName );
				exp=null;
			} 
			catch(Exception ex){exp = ex;}
			finally{EndCase(exp); exp = null;}
			con.Open();

			try
			{
				BeginCase("ExecuteNonQuery first time");
				intRecordsAffected = cmd.ExecuteNonQuery();
				Compare(intRecordsAffected , ExpectedRows);
			} 
			catch(Exception ex){exp = ex;}
			finally{EndCase(exp); exp = null;}


			try
			{
				BeginCase("ExecuteNonQuery second time, chage value");
				cmd.Parameters[0].Value = "Comp2";
				intRecordsAffected  = cmd.ExecuteNonQuery();
				Compare(intRecordsAffected , ExpectedRows);
			} 
			catch(Exception ex){exp = ex;}
			finally{EndCase(exp); exp = null;}
	
			if (con.State == ConnectionState.Open) con.Close();

		}

        /// <summary>
        /// ִ�ж���SQL��䣬ʵ�����ݿ�����
        /// </summary>
        /// <param name="SQLStringList">����SQL���</param>		
        public static bool ExecuteSqlTran(string conStr, List<CommandInfo> cmdList)
        {
            using (OracleConnection conn = new OracleConnection(conStr))
            {
                conn.Open();
                OracleCommand cmd = new OracleCommand();
                cmd.Connection = conn;
                OracleTransaction tx = conn.BeginTransaction();
                cmd.Transaction = tx;
                try
                {
                    foreach (CommandInfo c in cmdList)
                    {
                        if (!String.IsNullOrEmpty(c.CommandText))
                        {
                            PrepareCommand(cmd, conn, tx, CommandType.Text, c.CommandText, (OracleParameter[])c.Parameters);
                            if (c.EffentNextType == EffentNextType.WhenHaveContine || c.EffentNextType == EffentNextType.WhenNoHaveContine)
                            {
                                if (c.CommandText.ToLower().IndexOf("count(") == -1)
                                {
                                    tx.Rollback();
                                    throw new Exception("Oracle:Υ��Ҫ��" + c.CommandText + "�������select count(..�ĸ�ʽ");
                                    //return false;
                                }

                                object obj = cmd.ExecuteScalar();
                                bool isHave = false;
                                if (obj == null && obj == DBNull.Value)
                                {
                                    isHave = false;
                                }
                                isHave = Convert.ToInt32(obj) > 0;

                                if (c.EffentNextType == EffentNextType.WhenHaveContine && !isHave)
                                {
                                    tx.Rollback();
                                    throw new Exception("Oracle:Υ��Ҫ��" + c.CommandText + "����ֵ�������0");
                                    //return false;
                                }
                                if (c.EffentNextType == EffentNextType.WhenNoHaveContine && isHave)
                                {
                                    tx.Rollback();
                                    throw new Exception("Oracle:Υ��Ҫ��" + c.CommandText + "����ֵ�������0");
                                    //eturn false;
                                }
                                continue;
                            }
                            int res = cmd.ExecuteNonQuery();
                            if (c.EffentNextType == EffentNextType.ExcuteEffectRows && res == 0)
                            {
                                tx.Rollback();
                                throw new Exception("Oracle:Υ��Ҫ��" + c.CommandText + "������Ӱ����");
                                // return false;
                            }
                        }
                    }
                    tx.Commit();
                    return true;
                }
                catch (System.Data.OracleClient.OracleException E)
                {
                    tx.Rollback();
                    throw E;
                }
                finally
                {
                    if (conn.State != ConnectionState.Closed)
                    {
                        conn.Close();
                    }
                }
            }
        }