C# OracleCommand.ExecuteScalar方法代码示例

 protected void btnadd_Click(object sender, EventArgs e)
 {
     string ctc, ctd, dcc, poc;
     string ad = "";
     int  nowh;
     ctc = txtctc.Text;
     ctd = txtctd.Text;
     dcc = ddldcc.SelectedValue;
     poc = txtpoc.Text;
     if (txtnowh.Text == "")
     {
         nowh = 0;
     }
     else
     {
         nowh = int.Parse(txtnowh.Text);
     }
     int m;
     string str = "select count(*) from ct where ctc='" + txtctc.Text + "' and dcc='"+ddldcc.Text+"'";
     con.Open();
     OracleCommand cmdcount = new OracleCommand(str, con);
     m = int.Parse(cmdcount.ExecuteScalar().ToString());
     if (m == 1)
     {
         lblctmsg.Text = "code already exists";
     }
     else
     {
         string s = "insert into ct values('" + ctc + "','" + ctd + "','" + poc + "','" + nowh + "','" + dcc + "')";
         OracleCommand cmd = new OracleCommand(s, con);
         cmd.ExecuteNonQuery();
         lblctmsg.Text = " values inserted successfully ";
     }
     con.Close();
 }

        public object ExecuteScalar(string sql)
        {
            object value = null;
            try
            {
                var con = CONNECTION.OpenCon();

                var cmd = new OracleCommand(sql, con);
                value = cmd.ExecuteScalar();

                cmd.Dispose();
                CONNECTION.CloseCon(con);
            }
            catch (Exception ex)
            {
                SLLog.WriteError(new LogData
                {
                    Source = ToString(),
                    FunctionName = "ExecuteScalar Error!",
                    Ex = ex,
                });
                return null;
            }

            return value;
        }

 protected void btnadd_Click(object sender, EventArgs e)
 {
     string stcd, stdes;
     stcd = txtstcd.Text;
     stdes = txtstdes.Text;
     int z;
     string s = "select count(*) from Sm where stcd='" + stcd + "'";
     con.Open();
     OracleCommand cmdcount = new OracleCommand(s, con);
     z = int.Parse(cmdcount.ExecuteScalar().ToString());
     if (z == 1)
     {
         lblsmmsg.Text = "Code already exists";
     }
     else
     {
         string str = "insert into Sm values('" + stcd + "','" + stdes + "')";
         OracleCommand cmd = new OracleCommand(str, con);
         cmd.ExecuteNonQuery();
         lblsmmsg.Text = "Values inserted Successfully";
         txtstcd.Text = "";
         txtstdes.Text = "";
     }
     con.Close();
 }

        public static bool IsAvailable(string columnName, string value)
        {
            bool available = false;
            if (!String.IsNullOrWhiteSpace(columnName) && value != null)
            {
                try
                {
                    using (OracleConnection connection = new OracleConnection(CONNECTION_STRING))
                    {
                        OracleCommand command = new OracleCommand();
                        command.CommandText = String.Format("SELECT COUNT(*) FROM Users WHERE {0} LIKE :value",columnName);
                        command.Parameters.Add(":value", OracleDbType.NVarchar2).Value = value;
                        command.Connection = connection;
                        connection.Open();
                        int count = Convert.ToInt32(command.ExecuteScalar());
                        if (count==0)
                            available = true;
                        else
                            available = false;
                    }
                }

                catch (Exception e)
                {
                    available = false;
                    Logger.LogException(e);
                }
            }
            return available;
        }

        internal List<LXJHGLInstance> ImportTasks(List<COMMON.LXJHGLInstance> tasks)
        {
            List<LXJHGLInstance> duplicated = new List<LXJHGLInstance>();
            this.dbParam.Open();

            OracleCommand queryCmd = new OracleCommand();
            queryCmd.Connection = (OracleConnection)this.dbParam.Connection;
            queryCmd.CommandText = "SELECT count(*) FROM dq_route_taskmng WHERE ID=:ID and VERSION=:VERSION";
            queryCmd.Parameters.Add(":ID", OracleDbType.NVarchar2);
            queryCmd.Parameters.Add(":VERSION", OracleDbType.Int32);

            OracleCommand insertCmd = new OracleCommand();
            insertCmd.Connection = (OracleConnection)this.dbParam.Connection;
            insertCmd.CommandText = @"INSERT INTO dq_route_taskmng(ID,NAME,VERSION,RELEASER,RELEASETIME,TYPE,CREATOR,PLANEDTIME,TASKCREATIME,STATUS,DIFFICULTY)
                                      values(:ID,:NAME,:VERSION,:RELEASER,:RELEASETIME,:TYPE,:CREATOR,:PLANEDTIME,:TASKCREATIME,:STATUS,:DIFFICULTY)    ";
            insertCmd.Parameters.Add(":ID", OracleDbType.NVarchar2);
            insertCmd.Parameters.Add(":NAME", OracleDbType.NVarchar2);
            insertCmd.Parameters.Add(":VERSION", OracleDbType.Int32);
            insertCmd.Parameters.Add(":RELEASER", OracleDbType.NVarchar2);
            insertCmd.Parameters.Add(":RELEASETIME", OracleDbType.Date);
            insertCmd.Parameters.Add(":TYPE", OracleDbType.NVarchar2);
            insertCmd.Parameters.Add(":CREATOR", OracleDbType.NVarchar2);
            insertCmd.Parameters.Add(":PLANEDTIME", OracleDbType.Date);
            insertCmd.Parameters.Add(":TASKCREATIME", OracleDbType.Date);
            insertCmd.Parameters.Add(":STATUS", OracleDbType.Int32);
            insertCmd.Parameters.Add(":DIFFICULTY", OracleDbType.Int32);


            foreach (var task in tasks)
            {
                queryCmd.Parameters[":ID"].Value = task.Id;
                queryCmd.Parameters[":VERSION"].Value = task.Version;
                object cnt = queryCmd.ExecuteScalar();
                if (cnt != null && Convert.ToInt32(cnt) > 0) duplicated.Add(task);
                else
                {
                    insertCmd.Parameters[":ID"].Value = task.Id;
                    insertCmd.Parameters[":NAME"].Value = task.Name;
                    insertCmd.Parameters[":VERSION"].Value = task.Version;
                    insertCmd.Parameters[":RELEASER"].Value = task.Releaser;
                    insertCmd.Parameters[":RELEASETIME"].Value = task.Releasetime;
                    insertCmd.Parameters[":TYPE"].Value = task.Type;
                    insertCmd.Parameters[":CREATOR"].Value = task.Creator;
                    insertCmd.Parameters[":PLANEDTIME"].Value = task.Planedtime;
                    insertCmd.Parameters[":TASKCREATIME"].Value = task.Taskcreatime;
                    insertCmd.Parameters[":STATUS"].Value = LXJHGLStatus.未分配;
                    insertCmd.Parameters[":DIFFICULTY"].Value = task.Difficulty;
                    insertCmd.ExecuteNonQuery();
                }
            }
            this.dbParam.Commit();
            this.dbParam.Close();
            return duplicated;
        }

        protected void Button1_Click(object sender, EventArgs e)
        {
            string CS = ConfigurationManager.ConnectionStrings["oracle"].ConnectionString;
            OracleConnection conn = new OracleConnection(CS);

            OracleCommand camd = new OracleCommand("select count(*) from USER_TABLE where U_NAME = '" + TextBox1.Text + "'", conn);
            conn.Open();
            temp = Convert.ToInt32(camd.ExecuteScalar().ToString());
            if (temp == 1)
            {
                using (OracleCommand cmd = new OracleCommand("insert into ADDRESS (APP_ADD_ID,APP_NAME,FLOOR_NUM,ROAD,APP_ADD,AREA_NAME,CITY) values (U_ID_SEQ.NEXTVAL,'" + TextBoxAppName.Text + "','" + TextBoxFloor.Text + "','" + TextBoxRoad.Text + "','" + TextBoxAppArea.Text + "','" + TextBoxCity.Text + "','" + TextBoxCountry.Text + "')", conn))
                {
                    cmd.ExecuteNonQuery();
                    using (OracleCommand comd = new OracleCommand("select APP_ADD_ID from ADDRESS where APP_NAME = '" + TextBoxAppName.Text + "'", conn))
                    {
                        using (OracleCommand oracle = new OracleCommand("select USER_ID from USER_TABLE where U_NAME='" + TextBox1.Text + "'", conn))
                        {
                            string u_id = oracle.ExecuteScalar().ToString();
                            Session["U_ID"] = u_id;

                            string id = comd.ExecuteScalar().ToString();
                            Session["ADDId"] = id;
                            Response.Redirect("~/Allapartments.aspx");

                        }

                        //string id = comd.ExecuteScalar().ToString();
                        //Session["ADDId"] = id;
                        //Response.Redirect("~/Allapartments.aspx");
                    }
                }
            }
            else
            {
                Label1.ForeColor = System.Drawing.Color.Red;
                Label1.Text = "Name is not correct";
            }
            //conn.Open();

            //if (Convert.ToBoolean(camd.ExecuteNonQuery()))
            //{
            //    //using(OracleCommand comd = new OracleCommand("select APP_ADD_ID from ADDRESS where APP_NAME = '" +TextBoxAppName.Text + "'"))))

            //    Label1.ForeColor = System.Drawing.Color.Green;
            //    Label1.Text = "Data Saved In The Database";

            //}
            //else
            //{
            //    Label1.ForeColor = System.Drawing.Color.Red;
            //    Label1.Text = "Data Cannot Saved In The Database";
            //}
        }

 protected object getValue(string query)
 {
     object result = new object();
     OracleCommand cmd = new OracleCommand();
     cmd.Connection = Connection.ConnectionData();
     cmd.CommandType = CommandType.Text;
     cmd.CommandText = query;
     Connection.OpenConnection();
     result = cmd.ExecuteScalar();
     Connection.CloseConnection();
     return result;
 }

        protected void Page_Load(object sender, EventArgs e) {
            int companyCount = 0;

            using(var conn = new OracleConnection(ConfigTool.GetConnectionString("LOCAL_XE")))
            using(var cmd = new OracleCommand("SELECT COUNT(*) FROM NH_COMPANY", conn)) {
                conn.Open();
                companyCount = cmd.ExecuteScalar().AsInt();
            }

            lblMessage.Text = "CompanyCount=" + companyCount;


            lblMessage2.Text = "CompanyCount=" + Repository<Company>.Count();
        }

        private void FormTipster_Load(object sender, EventArgs e)
        {
            lblUser.Text = FormLogin.translator["Username " + FormLogin.currLanguage];
            lblFirst.Text = FormLogin.translator["FirstName " + FormLogin.currLanguage];
            lblSur.Text = FormLogin.translator["LastName " + FormLogin.currLanguage];
            lblE.Text = FormLogin.translator["Email " + FormLogin.currLanguage];
            lblM.Text = FormLogin.translator["Money " + FormLogin.currLanguage];
            btnTransaction.Text = FormLogin.translator["SendMoney " + FormLogin.currLanguage];
            using (OracleConnection conn = new OracleConnection(FormLogin.connString))
            {
                conn.Open();

                string query = "select * from Tipster where idTipster = " + idTipster;
                OracleCommand command = new OracleCommand(query, conn);
                command.CommandType = CommandType.Text;

                OracleDataReader reader = command.ExecuteReader();
                reader.Read();

                lblUsername.Text = reader.GetString(1);
                lblFirstName.Text = reader.GetString(3);
                lblSurname.Text = reader.GetString(4);
                lblMoney.Text = reader.GetInt32(5).ToString();
                lblEmail.Text = reader.GetString(6);
                
                query = "select COUNT(*) + 1 FROM Tipster WHERE Money > " + Int32.Parse(lblMoney.Text);

                command = new OracleCommand(query, conn);
                command.CommandType = CommandType.Text;

                lblRank.Text = command.ExecuteScalar().ToString();

                reader.Close();

            }
            
            if (idTipster != FormLogin.IdLoggedTipster)
            {
                btnTransaction.Visible = true;
            }
            else
            {
                changeView();
            }

            setDataGrid();

        }

        private bool isRegulated()
        {
            if (username.Trim().Length == 0)
            {
                MessageBox.Show("Username is empty!");
                return false;
            }

            if (password.Trim().Length == 0 || repPassword.Trim().Length == 0) 
            {
                MessageBox.Show("One of the password fields is empty!");
                return false;
            }

            if (!password.Equals(repPassword))
            {
                MessageBox.Show("Passwords do not match.");
                return false;
            }
            username = username.Trim();
             
            using (OracleConnection conn = new OracleConnection(FormLogin.connString)) // using avtomatski ja zatvora otvorenata konekcija
            {
                conn.Open();

                // mozhebi bi bilo podobro da se realizira so funkcija na nivo na baza
                string query = "SELECT COUNT(*) FROM Tipster WHERE UPPER(username) LIKE '" + username.ToUpper() + "'";
                OracleCommand commCheck = new OracleCommand(query, conn);
                commCheck.CommandType = CommandType.Text;
                Object o = commCheck.ExecuteScalar();

                
                int numRows = Int32.Parse(o.ToString());
                
                if (numRows != 0)
                {
                    MessageBox.Show("Already exists tipster with that username.");
                    conn.Clone(); // za sekoj slucaj
                    return false;
                }

                
                
             }
           
            return true;
        }

 protected void btnsbmt_Click(object sender, EventArgs e)
 {
     string uid, pwd;
     uid = txtaname.Text;
     pwd = txtpwd.Text;
     con.Open();
     OracleCommand cmd = new OracleCommand("select count(*) from cou where userid='" + uid + "' and pwd='" + pwd + "'", con);
     int c=int.Parse(cmd.ExecuteScalar().ToString());
     if (c == 1)
     {
         Response.Redirect("http://localhost/P_CRMS/website/Home.aspx");
     }
     else
     {
         lblloginmsg.Text = "Enter correct userid and password";
     }
 }

        public bool AddNewCategory(Categorie newCat)
        {
            bool success = false;
            int newCatId = 0;

            connection.Open();
            OracleCommand command = new OracleCommand();
            command.Connection = connection;

            command.CommandText = "SELECT MAX(CATEGORIEID) FROM CATEGORIE";
            newCatId = (int)((decimal)command.ExecuteScalar() + 1);

            newCat.CategorieID = newCatId;

            command.CommandText = "INSERT INTO CATEGORIE VALUES (:id, :naam, :omschrijving, :parent)";
            OracleParameter parID = new OracleParameter("id", newCat.CategorieID);
            OracleParameter parNaam = new OracleParameter("naam", newCat.Categorienaam);
            OracleParameter parOmschrijving = new OracleParameter("omschrijving", newCat.Omschrijving);
            OracleParameter parParent = new OracleParameter("parent", newCat.SubcategorieVan);

            command.Parameters.Add(parID);
            command.Parameters.Add(parNaam);
            command.Parameters.Add(parOmschrijving);
            command.Parameters.Add(parParent);

            try
            {
                if (command.ExecuteNonQuery() == 1)
                {
                    success = true;
                }
            }
            catch (OracleException ex)
            {
                Console.WriteLine("Record is not inserted into the database table.");
                Console.WriteLine("Exception Message: " + ex.Message);
                Console.WriteLine("Exception Source: " + ex.Source);
            }
            finally
            {
                connection.Close();
            }

            return success;
        }

 public static int ReadRecordCountFromEFTestDB(string tableName)
 {
     int ret = 0;
     try
     {
         using(OracleConnection conn = new OracleConnection(EF_SQL_ConnectionString))
         {
             conn.Open();
             using(OracleCommand cmd = new OracleCommand(string.Format("SELECT COUNT(*) FROM {0}", tableName), conn))
             {
                 ret = Convert.ToInt32(cmd.ExecuteScalar());
             }
             conn.Close();
         }
     }
     catch { }
     return ret;
 }

 protected void btndlt_Click(object sender, EventArgs e)
 {
     con.Open();
     string s = "select count(*) from Sm where stcd= '" + txtstcd.Text + "'";
     OracleCommand cmd = new OracleCommand(s, con);
     int c;
     c = int.Parse(cmd.ExecuteScalar().ToString());
     if (c == 1)
     {
         string str = "delete from Sm where  stcd= '" + txtstcd.Text + "'";
         OracleCommand cmddel = new OracleCommand(str, con);
         cmddel.ExecuteNonQuery();
         lblsmmsg.Text = "Values Deleted";
         con.Close();
     }
     txtstcd.Text = "";
     txtstdes.Text = "";
 }

 public static string ReadRecordValueFromEFTestDB(string tableName, string fieldName, string whereClause)
 {
     string ret = string.Empty;
     try
     {
         using (OracleConnection conn = new OracleConnection(EF_SQL_ConnectionString))
         {
             conn.Open();
             using (OracleCommand cmd = new OracleCommand(string.Format("SELECT t.{0} FROM {1} t where {2}", fieldName, tableName, whereClause), conn))
             {
                 ret = cmd.ExecuteScalar().ToString();
             }
             conn.Close();
         }
     }
     catch { }
     return ret;
 }