C# SqlCommand.ExecuteScalar方法代码示例

    protected void Button3_Click(object sender, EventArgs e)
    {
        String s = ConfigurationManager.ConnectionStrings["MediDB"].ConnectionString;
        SqlConnection con = new SqlConnection(s);
        SqlCommand cmd = new SqlCommand(" Select Count(*) from Admin where name='" + TextBox1.Text + "' and Password='" + TextBox2.Text + "'", con);
        con.Open();
        int m = (int)cmd.ExecuteScalar();
        if (m == 1)
        {
            Button1.Enabled = true ;
            Button2.Enabled = true ;
            Button4.Enabled = true ;
            Button5.Enabled = true ;
        }
        else
        {
            Response.Write("<Script>alert('Name or the password entered by you is incorrect. please try again!!')</Script>");
        }

        cmd.ExecuteScalar();

        con.Close();
        TextBox1.Text = "";
        TextBox2.Text = "";

    }

        protected void Tmbtn_Click(object sender, EventArgs e)
        {
            SqlCommand cmd = new SqlCommand("select MAX(wjh) from wj", cn);
            cn.Open();
            cmd.ExecuteScalar();
            //cn.Close();
            SqlCommand com = new SqlCommand("INSERT INTO Tm(Wjh,Tm,Tixing) VALUES (" +cmd.ExecuteScalar()+ ",'" + Tmtxt.Text.Trim() + "','" + TXDropDownList.Text.Trim() + "')", cn);
            //com.ExecuteNonQuery();
            //cn.Close();
            try
            {
                //cn.Open();
                int val = com.ExecuteNonQuery();
                cn.Close();
                this.NRListBox.Items.Add(this.Tmtxt.Text);
                if (val <= 0)
                    ClientScript.RegisterStartupScript(this.GetType(), "", "alert('插入数据失败!')");
                else
                    ClientScript.RegisterStartupScript(this.GetType(), "", "alert('插入数据成功!')");

            }
            //捕获异常
            catch (Exception exp)
            {
                //处理异常.......
                ClientScript.RegisterStartupScript(this.GetType(), "", "alert('插入数据失败! 详情:" + exp.Message + "')");

            }
        }

    public static bool Grabar(Entidades.Distrito pEntidad)
    {
      using (var cn = new SqlConnection(conexion.LeerCC))
      {
        // Contamos cuantos distritos existen segun el coddistrito o nomdistrito
        using (var cmd = new SqlCommand(@"select isnull(count(coddistrito),0) from distritos where [email protected] or [email protected]", cn))
        {
          cmd.Parameters.AddWithValue("cod", pEntidad.coddistrito);
          cmd.Parameters.AddWithValue("nom", pEntidad.nomdistrito);

          cn.Open();
          // Ejecutamos el comando y verificamos si el resultado es mayor a cero actualizar, caso contrario insertar
          if (Convert.ToInt32(cmd.ExecuteScalar()) > 0)
          {
            // Si es mayor a cero, quiere decir que existe al menos un registro con los datos ingresados
            // Entonces antes de actualizar, hacer las siguientes comprobaciones
            if (pEntidad.coddistrito == 0)
              throw new Exception("El distrito ya esta registrado en el sistema, verifique los datos por favor!...");

            // Verifica si ya existe un registro con el mismo nombre del distrito
            cmd.CommandText = @"select isnull(count(coddistrito),0) from distritos where coddistrito<>@cod and [email protected]";
            if (Convert.ToInt32(cmd.ExecuteScalar()) > 0)
              throw new Exception("No se puede grabar un valor duplicado, verifique los datos por favor!...");

            // Si las comprobaciones anteriores resultaron ser falsa, entonces actualizar
            cmd.CommandText = @"update distritos set [email protected] where [email protected]";
          }
          else
            cmd.CommandText = @"insert into distritos (nomdistrito) values (@nom)";

          // Ejecutamos el comando que puede ser para update o insert
          return Convert.ToBoolean(cmd.ExecuteNonQuery());
        }
      }
    }

    protected void Button1_Click(object sender, EventArgs e)
    {
        SqlCommand cmd=new SqlCommand("select password from consumer where email='"+TextBox1.Text+"'",con);
        con.Open();

        if (cmd.ExecuteScalar()!=null)
        {
            string s = cmd.ExecuteScalar().ToString();

            MailMessage mail = new MailMessage();
            mail.To.Add(TextBox1.Text);
            mail.From = new MailAddress("[email protected]");
            mail.Subject = "Remember Mail";
            string Body = "Password is " + s;
            mail.Body = Body;
            SmtpClient smtp = new SmtpClient();
            smtp.Host = "smtp.gmail.com";
            smtp.Port = 587;
            smtp.UseDefaultCredentials = false;
            smtp.Credentials = new System.Net.NetworkCredential("[email protected]", "9232663223");
            smtp.EnableSsl = true;
            smtp.Send(mail);
            Label1.Text = "PASSWORD SENT TO YOUR EMAIL ADDRESS";
            con.Close();
        }
        else
            Label1.Text = "No such email exists ";
    }

        /// <summary>
        /// Performs a scalar select and returns the value as a string
        /// </summary>
        /// <param name="qS"></param>
        /// <returns></returns>
        public static String ScalarString(String qS)
        {
            object returnValue = "";
            SqlConnection con = new SqlConnection(ConnectionString);
            SqlCommand cmd = new SqlCommand(qS, con);

            using (con)
            {
                if (con.State == ConnectionState.Open)
                {
                    returnValue = cmd.ExecuteScalar();
                    con.Close();
                }
                else
                {
                    con.Open();
                    returnValue = cmd.ExecuteScalar();
                    con.Close();
                }
            }

            if (returnValue == null)
            {
                return "";
            }
            else
                return returnValue.ToString();
        }

        /// <inheritdoc/>
        public bool CorrelativeConfigurationExists(string facilityId, string correlativeId)
        {
            if (string.IsNullOrWhiteSpace(facilityId)) return false;
            if (string.IsNullOrWhiteSpace(correlativeId)) return false;
            if (string.IsNullOrWhiteSpace(_connectionString)) return false;

            int count;

            using (var conexionSp = new SqlConnection(_connectionString))
            {
                const string query = "SELECT COUNT(PaqueteId) FROM Comunes.CorrelativosPaquetes " +
                                     "WHERE PaqueteId = @correlativeId AND PlantaId = @facilityId";

                using (var comandoSp = new SqlCommand(query, conexionSp))
                {
                    comandoSp.CommandType = CommandType.Text;
                    comandoSp.Parameters.AddWithValue("@correlativeId", correlativeId).Direction = ParameterDirection.Input;
                    comandoSp.Parameters.AddWithValue("@facilityId", facilityId).Direction = ParameterDirection.Input;
                    conexionSp.Open();
                    count = comandoSp.ExecuteScalar() is int ? (int)comandoSp.ExecuteScalar() : 0;
                    conexionSp.Close();
                }
            }
            return count > 0;
        }

        public static int? getMax()
        {
            int? id = null;
            string conexionCadena = ConfigurationManager.ConnectionStrings["ConexionComplejo"].ConnectionString;
            SqlConnection con = new SqlConnection();
            try
            {
                con.ConnectionString = conexionCadena;
                con.Open();
                string sql = "SELECT MAX(id_fact) from facturas";
                SqlCommand cmd = new SqlCommand();
                cmd.CommandText = sql;
                cmd.Connection = con;
                if(!cmd.ExecuteScalar().Equals(null))
                {
                    id =(int) cmd.ExecuteScalar();
                }
                else
                {
                    id = null;
                }

            }
            catch (SqlException ex)
            {
                throw new ApplicationException("Error al traer max id cliente" + ex.Message);

            }
            finally
            {
                con.Close();
            }
            return id;
        }

        public RequestsProcessedByEnum GetLeaveStatus(int leaveRequestId)
        {
            SqlConnection conn = null;
            SqlCommand cmd = null;

            try
            {
                conn = DALHelper.CreateSqlDbConnection();
                cmd = new SqlCommand("usp_GetLeaveStatus", conn);
                cmd.CommandType = System.Data.CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@LeaveRequestId", leaveRequestId);

                if (cmd.ExecuteScalar() != DBNull.Value)
                {
                    return (RequestsProcessedByEnum)Convert.ToInt32(cmd.ExecuteScalar());
                }
                else
                {
                    return RequestsProcessedByEnum.NotDefined;
                }
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                conn.Close();
                cmd.Dispose();
                conn.Dispose();
            }
        }

        /// <summary>
        /// Authenticates the user
        /// </summary>
        /// <param name="user">user from login form</param>
        /// <param name="password">password from login form</param>
        public static bool Authenticate(String user,String password)
        {
            var positiveIntRegex = new Regex(@"^\w+$");
            if (!positiveIntRegex.IsMatch(user))
            {
                return false;
            }
            if (!positiveIntRegex.IsMatch(password))
            {
                return false;
            }

            String encryptedPass = Encrypt(password);
            string constr = Settings.Default.UserDbConnectionString;
            SqlConnection con = new SqlConnection(constr);
            SqlCommand command = new SqlCommand();
            command.Connection = con;
            command.Parameters.AddWithValue("@Username", user);
            command.CommandText = "SELECT Password FROM Users WHERE Name = @Username";
            command.CommandType = CommandType.Text;

            con.Open();
            string _password = "";
            if (command.ExecuteScalar() != null)
                _password = command.ExecuteScalar().ToString();
            else
                return false;
            con.Close();
            if (encryptedPass.Equals(_password))
            {
                return true;
            }
            return false;
        }

        public static string ExecuteScalar(string strSql)
        {
            try
            {
                iniCon();

                SqlConnection sCon=new SqlConnection(m_strCon);
                sCon.Open();

                SqlCommand sCmd=new SqlCommand();
                sCmd.Connection =sCon ;
                sCmd.CommandText =strSql;
                if(sCmd.ExecuteScalar()!=null)
                {
                    string strRet=sCmd.ExecuteScalar().ToString();
                    sCon.Close();
                    return strRet;
                }
                else
                {
                    sCon.Close();
                    return "";
                }
            }catch(Exception ex)
            {
                MessageBox.Show(ex.ToString());
                Application.Exit();
                //MessageBox.Show(ex.ToString());
                return "";
            }
        }

        protected void login_Click(object sender, EventArgs e)
        {
            SqlConnection con = new SqlConnection("server=(localdb)\\v11.0;Initial Catalog=WebApplication2;Integrated Security=true");
            con.Open();
            string str = "select count(*) from Users where userName='" + username.Text + "'";
            SqlCommand command = new SqlCommand(str, con);
            int temp = Convert.ToInt32(command.ExecuteScalar().ToString());
            if (temp == 1)
            {
                string str2 = "select password from Users where userName='" + username.Text + "'";

                SqlCommand command2 = new SqlCommand(str2, con);
                string tempPass = command2.ExecuteScalar().ToString().ToLower().Trim();

                if (tempPass==pass.Text.ToLower().Trim())
                {
                    string str3 = "select user_id from Users where userName='" + username.Text + "'";
                    SqlCommand command3 = new SqlCommand(str3, con);
                    temp = Convert.ToInt32(command.ExecuteScalar().ToString());
                    con.Close();
                    Session["new"] = temp;
                    Response.Redirect("Home.aspx");
                }
                else
                {
                    Label1.Visible = true;
                    Label1.Text = "Wrong password!";
                }
            }
            else
            {
                Label1.Visible = true;
                Label1.Text = "Invalid user name!";
            }
        }

 public List<TableInfo> LoadTableList(string databaseConnectionString)
 {
     List<TableInfo> tables;
     using (var conn = new SqlConnection(databaseConnectionString))
     {
         conn.Open();
         var cmd = new SqlCommand(SqlQueries.QUERY_ALL_TABLES, conn);
         using (var reader = cmd.ExecuteReader())
         {
             tables = new List<TableInfo>();
             while (reader.Read())
             {
                 tables.Add(new TableInfo(reader.GetString(0), reader.GetString(1)));
             }
         }
         int tableCount = 0;
         foreach (var tableInfo in tables)
         {
             cmd = new SqlCommand(string.Format(SqlQueries.QUERY_DATA_SIZE, tableInfo.Name, tableInfo.Schema), conn);
             cmd.CommandTimeout = 60000;
             tableInfo.DataSizeBytes = (double) cmd.ExecuteScalar();
             cmd.CommandText = string.Format(SqlQueries.QUERY_INDEX_SIZE, tableInfo.Name, tableInfo.Schema);
             tableInfo.IndexSizeBytes = (double) cmd.ExecuteScalar();
             cmd.CommandText = string.Format(SqlQueries.QUERY_ROW_COUNT, tableInfo.Name, tableInfo.Schema);
             tableInfo.RowCount = (long) cmd.ExecuteScalar();
             OnTableLoadProgressChanged(new ProgressEventArgs(tableCount++*100 / tables.Count));
         }
     }
     return tables;
 }

        public string generatestudentcode()
        {
            string regcode = "";
            string query = "select RegistrationCode from StudentPersonalInformation where ID =(select Max(ID) from StudentPersonalInformation)";
            try
            {
                SqlCommand cmd = new SqlCommand(query, con);

                if (con.State == ConnectionState.Closed)
                    con.Open();

                if (cmd.ExecuteScalar() != null)
                {
                    //regcode = cmd.ExecuteScalar().ToString();
                    //regcode= regcode.Substring(0,regcode.LastIndexOf('/')-1) + Convert.ToInt32(regcode.Substring(regcode.LastIndexOf('/'), (regcode.Length - (regcode.LastIndexOf('/'))) + 1)) +1;
                    regcode = (Convert.ToInt32(cmd.ExecuteScalar()) + 1).ToString();
                }
                else
                    regcode = "1";
            //                    regcode = "ASTM/" + DropDownListCenter.SelectedValue + "/" + DateTime.Now.Year + "/1";

                //                regcode = "ASTM/" + DropDownListCenter.Items[0].Text + "/" + DropDownListCourse.SelectedValue + "/" + DateTime.Now.Year + "/1";

            }
            catch (Exception ex)
            { }
            finally
            {
                con.Close();
            }
            return regcode;
        }

    void GetCurrentMembership()
    {
        string userID = Session["UserID"].ToString();

        if (CheckMembership())
        {
            using (SqlConnection con = new SqlConnection(Helper.GetCon()))
            using (SqlCommand cmd = new SqlCommand())
            {
                con.Open();
                cmd.Connection = con;
                cmd.CommandText = "SELECT SUM(Length) FROM Memberships INNER JOIN Payments ON " +
                                  "Memberships.MembershipID=Payments.MembershipID WHERE [email protected] " +
                                  "AND MembershipStatus='Active' AND PaymentStatus='Paid'";
                cmd.Parameters.AddWithValue("@UserID", userID);
                int totalYears = (int)cmd.ExecuteScalar();

                cmd.CommandText = "SELECT TOP 1 StartDate FROM Memberships INNER JOIN Payments ON " +
                                  "Memberships.MembershipID=Payments.MembershipID WHERE [email protected] " +
                                  "AND MembershipStatus='Active' AND PaymentStatus='Paid'";
                cmd.Parameters.Clear();
                cmd.Parameters.AddWithValue("@UserID", userID);
                DateTime endDate = (DateTime)cmd.ExecuteScalar();
                DateTime totalEndDate = endDate.AddYears(totalYears);
                txtEndDate.Text = totalEndDate.ToString("D");
            }
        }
        else
        {
            txtEndDate.Text = "N/A";
            nomem.Visible = true;
            btnDisable.Visible = false;
            btnEnable.Visible = false;
        }
    }

        //-----------------------------------
        public void favoritecontrol()
        {
            try
            {
                if (Session["userid"] != null)
                {
                    myconnection.Open();
                    SqlCommand com = new SqlCommand("SPfavoritecontrol", myconnection);
                    com.CommandType = CommandType.StoredProcedure;

                    com.Parameters.Add("@movieid", SqlDbType.Int);
                    com.Parameters["@movieid"].Value = Convert.ToInt32(Request.QueryString["movieid"]);

                    com.Parameters.Add("@userid", SqlDbType.Int);
                    com.Parameters["@userid"].Value = Convert.ToInt32(Session["userid"]);

                    if (com.ExecuteScalar() != null)
                    {
                        if (Convert.ToInt32(com.ExecuteScalar().ToString()) != 0)
                        {
                            LinkButtonaddtofavorites.Text = "Added to favorites";
                        }
                    }
                }

            }
            catch (Exception ex)
            {
                Labelerror.Text = "error in controlling favorites" + ex.Message;
            }
            finally
            {
                myconnection.Close();
            }
        }