C# Utilities.DBConnect类代码示例

        protected void btnSaveChanges_Click(object sender, EventArgs e)
        {
            if (String.IsNullOrEmpty(txtCurrentEmail.Text) || String.IsNullOrEmpty(txtCurrentPassword.Text) || String.IsNullOrEmpty(txtNewPassword.Text))
            Page.ClientScript.RegisterStartupScript(this.GetType(), "Script", "<script> alert('All fields are required.Thank you!'); </script>");

            SqlCommand pass = new SqlCommand();
            DBConnect objDB = new DBConnect();

            //string currentPassword = (string)pass.ExecuteScalar();
            //if (currentPassword == txtCurrentPassword.Text)
            //     { }

            //else
            //    lblMsg.Text = "Please enter your current password again!";
            Salt salt = new Salt();
            string currentEmail = txtCurrentEmail.Text;
            string currentPassword = txtCurrentPassword.Text;
            string newPassword1 = txtNewPassword.Text;
            customer customerObj=new customer();
            int result = customerObj.ResetPassword(currentEmail, currentPassword, newPassword1, salt.ToString(),role);
            if (result == 0)
                lblMsg.Text = "The password you entered is incorrect!";

            else if (result == 1)
                lblMsg.Text = "The password was changed successfully.";
            //  Response.Redirect("LoginPage.aspx");
        }

        public Boolean AssestmentsUpdate(String fiscalYear,double financialAidRateGrad,double plandFundFeeGradRate,double financialAidRateUndergrad,double plandFundFeeRateUndergrad )
        {
            SqlCommand objcomm = new SqlCommand();

            objcomm.CommandType = CommandType.StoredProcedure;

            objcomm.CommandText = "AssesmentsUpdate";

            objcomm.Parameters.Add("@fiscalYearId", fiscalYear);
            objcomm.Parameters.Add("@financialAidRateGrad", financialAidRateGrad);
            objcomm.Parameters.Add("@plandFundFeeGradRate", plandFundFeeGradRate);
            objcomm.Parameters.Add("@financialAidRateUndergrad", financialAidRateUndergrad);
            objcomm.Parameters.Add("@plandFundFeeRateUndergrad", plandFundFeeRateUndergrad);

            SqlParameter outputParameter = new SqlParameter("@result", DbType.Int32);
            outputParameter.Direction = ParameterDirection.ReturnValue;

            objcomm.Parameters.Add(outputParameter);

            DBConnect objDB = new DBConnect();
            objDB.GetDataSetUsingCmdObj(objcomm);

            int result = int.Parse(objcomm.Parameters["@result"].Value.ToString());

            if (result == 1)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        protected void btnSaveChanges_Click(object sender, EventArgs e)
        {
            string email = txtCurrentEmail.Text;
            string newPhone = txtPhonenumber.Text;

            SqlCommand phoneCmd = new SqlCommand();
            DBConnect objDB = new DBConnect();

            phoneCmd.CommandType = CommandType.StoredProcedure;
            phoneCmd.CommandText = "dbo.TP_ResetPhonenumber";

            SqlParameter inputEmail = new SqlParameter("@email", email);
            inputEmail.Direction = ParameterDirection.Input;
            inputEmail.SqlDbType = SqlDbType.NVarChar;
            inputEmail.Size = 50;
            phoneCmd.Parameters.Add(inputEmail);

            SqlParameter inputPhone = new SqlParameter("@newPhone", newPhone);
            inputPhone.Direction = ParameterDirection.Input;
            inputPhone.SqlDbType = SqlDbType.NVarChar;
            inputPhone.Size = 50;
            phoneCmd.Parameters.Add(inputPhone);

            phoneCmd.Parameters.Add("@results", SqlDbType.Int).Direction = ParameterDirection.Output;
            objDB.DoUpdateUsingCmdObj(phoneCmd);
            int result = Convert.ToInt32(phoneCmd.Parameters["@results"].Value);

            if (result == 1)
                lblMsg.Text = "Your phone number was changed succesfully!";
            else if (result == 0)
                lblMsg.Text = "Your phone number was not changed!Please try again";
        }

        public string[] UpdateCustomer(CustomerClass fred, CreditCardClass cc, object[] stupid)
        {
            DBConnect DB = new DBConnect();
            SqlCommand command = new SqlCommand();
            string[] meh = new string[3];
            ErrorCodes ec = new ErrorCodes();
            command.CommandType = CommandType.StoredProcedure;
            command.CommandText = "CustomerUpdate";
            command.Parameters.AddWithValue("@FirstName", fred.Firstname);
            command.Parameters.AddWithValue("@Address", fred.Address);
            command.Parameters.AddWithValue("@City", fred.City);
            command.Parameters.AddWithValue("@State", fred.State);
            command.Parameters.AddWithValue("@ZipCode", fred.ZipCode);
            command.Parameters.AddWithValue("@AccountBalance", cc.AccountBalance);
            meh[0] = (DB.DoUpdateUsingCmdObj(command) > 0) ? "true" : "false";
            SqlCommand sandy = new SqlCommand();
            sandy.CommandType = CommandType.StoredProcedure;
            sandy.CommandText = "VerifyInfo";
            sandy.Parameters.AddWithValue("@CreditCardNum", cc.CardNumber);
            sandy.Parameters.AddWithValue("@CVV", cc.CVV);
            sandy.Parameters.AddWithValue("@TransAmnt", float.Parse(stupid[2].ToString()));
            sandy.Parameters.AddWithValue("@FirstName", fred.Firstname);
            SqlParameter returnParam = new SqlParameter("@RVAL", DbType.Int32);
            returnParam.Direction = ParameterDirection.ReturnValue;
            sandy.Parameters.Add(returnParam);

            //            DataSet ds = DB.GetDataSetUsingCmdObj(sandy);
            DB.GetDataSetUsingCmdObj(sandy);
            meh[1] = sandy.Parameters["@RVAL"].Value.ToString();
            meh[2] = ec.GetErrorCodeMessage(int.Parse(meh[1]));

            return meh;
        }

        public Boolean cartExists(int loginID)
        {
            Boolean cartExists = false;

            DataSet cart = new DataSet();
            DBConnect objDB = new DBConnect();
            SqlCommand logID = new SqlCommand();

            logID.CommandType = CommandType.StoredProcedure;
            logID.CommandText = ("TP_findCartByLogin");

            SqlParameter theID = new SqlParameter("@loginID", loginID);
            theID.Direction = ParameterDirection.Input;
            theID.SqlDbType = SqlDbType.Int;
            theID.Size = 4;
            logID.Parameters.Add(theID);

            cart = objDB.GetDataSetUsingCmdObj(logID);
            int size = Convert.ToInt32(cart.Tables[0].Rows[0][0].ToString());

            if (size > 0)
            {
                cartExists = true;
                return cartExists;
            }

            else
            {
                return cartExists;
            }
        }

        protected void btnSaveChanges_Click(object sender, EventArgs e)
        {
            if (String.IsNullOrEmpty(txtPhonenumber.Text) || String.IsNullOrEmpty(txtCurrentEmail.Text))
            Page.ClientScript.RegisterStartupScript(this.GetType(), "Script", "<script> alert('All fields are required.Thank you!'); </script>");

            string email = txtCurrentEmail.Text;
            string newPhone = txtPhonenumber.Text;
            SqlCommand phoneCmd = new SqlCommand();
            DBConnect objDB = new DBConnect();

            phoneCmd.CommandType = CommandType.StoredProcedure;
            phoneCmd.CommandText = "dbo.TP_ResetPhonenumber";

            SqlParameter inputEmail = new SqlParameter("@email", email);
            inputEmail.Direction = ParameterDirection.Input;
            inputEmail.SqlDbType = SqlDbType.NVarChar;
            inputEmail.Size = 50;
            phoneCmd.Parameters.Add(inputEmail);

            SqlParameter inputPhone = new SqlParameter("@newPhone", newPhone);
            inputPhone.Direction = ParameterDirection.Input;
            inputPhone.SqlDbType = SqlDbType.NVarChar;
            inputPhone.Size = 50;
            phoneCmd.Parameters.Add(inputPhone);

            phoneCmd.Parameters.Add("@results", SqlDbType.Int).Direction = ParameterDirection.Output;
            objDB.DoUpdateUsingCmdObj(phoneCmd);
            int result = Convert.ToInt32(phoneCmd.Parameters["@results"].Value);

            if (result == 1)
                lblMsg.Text = "Your phone number was changed succesfully!";
            else if (result == 0)
                lblMsg.Text = "Your phone number was not changed!Please try again";
        }

        protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack)
            {
                string query = "SELECT * FROM Student";
                DBConnect db = new DBConnect();
                ddStudentSelect.DataSource = db.GetDataSet(query);
                ddStudentSelect.DataValueField = "StudentID";
                ddStudentSelect.DataTextField = "Name";
                ddStudentSelect.DataBind();

                SqlCommand command = new SqlCommand();
                command.CommandType = CommandType.StoredProcedure;
                command.CommandText = "GetAllCourses";
                gvCourses.DataSource = db.GetDataSetUsingCmdObj(command);
                gvCourses.DataBind();

                for(int r = 0; r<gvCourses.Rows.Count; r++)
                {
                    CheckBox cb;
                    cb = (CheckBox)gvCourses.Rows[r].FindControl("cbSelectCourse");
                    if (int.Parse(gvCourses.Rows[r].Cells[10].Text) == 0)
                    {
                        cb.Enabled = false;
                    }
                }
            }
        }

        public Boolean AssessmentsUpdate(String fiscalYear)
        {
            SqlCommand objcomm = new SqlCommand();

            objcomm.CommandType = CommandType.StoredProcedure;

            objcomm.CommandText = "UpdateAssessments";

            objcomm.Parameters.Add("@fiscalYear", fiscalYear);

            SqlParameter outputParameter = new SqlParameter("@result", DbType.Int32);
            outputParameter.Direction = ParameterDirection.ReturnValue;

            objcomm.Parameters.Add(outputParameter);

            DBConnect objDB = new DBConnect();
            objDB.DoUpdateUsingCmdObj(objcomm);

            int result = int.Parse(objcomm.Parameters["@result"].Value.ToString());

            if (result == 1)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        protected void btnConfirm_Click(object sender, EventArgs e)
        {
            string oldpass = txtOldPass.Text;
            string newpass1 = txtNewPass1.Text;
            string newpass2 = txtNewPass2.Text;
            if (newpass1 == newpass2)
            {
                DBConnect db = new DBConnect();

                string email = Session["emailSession"].ToString();
                SqlCommand command = new SqlCommand();
                command.CommandType = CommandType.StoredProcedure;
                command.CommandText = "UpdateMerch";
                command.Parameters.AddWithValue("@email", email);
                command.Parameters.AddWithValue("@oldPass", oldpass);
                command.Parameters.AddWithValue("@newPass1", newpass1);
                command.Parameters.AddWithValue("@newPass2", newpass2);
                db.DoUpdateUsingCmdObj(command);
            }

            else
            {
                Label lblerror = new Label();
                lblerror.Text = "New Passwords do not match please try again";
            }
        }

        public bool ValidMerchantLogin(string email, string password)
        {
            DataSet myDS = new DataSet();
            SqlCommand objCommand = new SqlCommand();
            objCommand.CommandType = CommandType.StoredProcedure;
            objCommand.CommandText = "TP_ValidMerchantLogin";
            objCommand.Parameters.AddWithValue("@email", email);
            objCommand.Parameters.AddWithValue("@password", password);
            DBConnect objDB = new DBConnect();
            myDS = objDB.GetDataSetUsingCmdObj(objCommand);

            bool returnvalue = true;

            //if the values are found in the DB return true
            if ((myDS.Tables[0].Rows.Count <= 1) && (myDS.Tables[0].Rows[0]["password"].ToString() == password))
            {
                returnvalue = true;
            }

            //the user does not exist in the DB
            else
            {
                returnvalue = false;
            }

            return returnvalue;
        }

        public bool AddNewCustomer(Customer newCustomer)
        {
            DBConnect objDB = new DBConnect();
            SqlCommand objCommand = new SqlCommand();
            objCommand.CommandType = CommandType.StoredProcedure;
            objCommand.CommandText = "AddTPCustomer";
            objCommand.Parameters.AddWithValue("@Name", newCustomer.name);
            objCommand.Parameters.AddWithValue("@Email", newCustomer.email );
            objCommand.Parameters.AddWithValue("@password", newCustomer.password);
            objCommand.Parameters.AddWithValue("@address", newCustomer.shippingAddress);
            objCommand.Parameters.AddWithValue("@city", newCustomer.shippingAddress);
            objCommand.Parameters.AddWithValue("@state", newCustomer.shippingState);
            objCommand.Parameters.AddWithValue("@zipcode", newCustomer.shippingZipcode);

            //integer to determine if value was added into DB or not
            int result= objDB.DoUpdateUsingCmdObj(objCommand);

            if (!(result <= 0)) //if a new row was successsfully added
            {
                return false;
            }
            else               //else the row was not added successfully
            {
                return true;

            }
        }

        protected void btnSaveChanges_Click(object sender, EventArgs e)
        {
            string email = txtCurrentEmail.Text;
            string newName = txtNewName.Text;

            SqlCommand name = new SqlCommand();
            DBConnect objDB = new DBConnect();

            name.CommandType = CommandType.StoredProcedure;
            name.CommandText = "dbo.TP_ResetName";

            SqlParameter inputEmail = new SqlParameter("@email", email);
            inputEmail.Direction = ParameterDirection.Input;
            inputEmail.SqlDbType = SqlDbType.NVarChar;
            inputEmail.Size = 50;
            name.Parameters.Add(inputEmail);

            SqlParameter inputName = new SqlParameter("@newName", newName);
            inputName.Direction = ParameterDirection.Input;
            inputName.SqlDbType = SqlDbType.NVarChar;
            inputName.Size = 50;
            name.Parameters.Add(inputName);

               name.Parameters.Add("@result", SqlDbType.Int).Direction = ParameterDirection.Output;
               objDB.DoUpdateUsingCmdObj(name);
                int result = Convert.ToInt32(name.Parameters["@result"].Value);

            if (result == 1)
                  lblMsg.Text = "Your name was changed succesfully!";
            else if (result==0)
                  lblMsg.Text = "Your name was not changed!";
        }

        protected void btnPlaceOrder_Click(object sender, EventArgs e)
        {
            List<cartItem> cartItems = new List<cartItem>();
            cartItems = (List<cartItem>)Session["cart"];

            DBConnect objDB = new DBConnect();
            SqlCommand addPurchase = new SqlCommand();

            addPurchase.CommandType = CommandType.StoredProcedure;
            addPurchase.CommandText = "TP_CustomerTransactions";

            SqlCommand updateProdQuan = new SqlCommand();

            updateProdQuan.CommandType = CommandType.StoredProcedure;
            updateProdQuan.CommandText = "TP_subtractProducts";

            foreach(cartItem obj in cartItems)
            {
                SqlParameter prodID = new SqlParameter("@prodNum", obj.ProdNum);
                prodID.Direction = ParameterDirection.Input;
                prodID.SqlDbType = SqlDbType.Int;
                prodID.Size = 4;
                addPurchase.Parameters.Add(prodID);

                SqlParameter quantity = new SqlParameter("@quantity", obj.ProdQuantity);
                quantity.Direction = ParameterDirection.Input;
                prodID.SqlDbType = SqlDbType.Int;
                prodID.Size = 4;
                addPurchase.Parameters.Add(quantity);

                SqlParameter loginiD = new SqlParameter("@loginID", Convert.ToInt32(Session["loginId"]));
                loginiD.Direction = ParameterDirection.Input;
                loginiD.SqlDbType = SqlDbType.Int;
                loginiD.Size = 4;
                addPurchase.Parameters.Add(loginiD);

                SqlParameter quan = new SqlParameter("@prodNum", obj.ProdNum);
                quan.Direction = ParameterDirection.Input;
                quan.SqlDbType = SqlDbType.Int;
                quan.Size = 4;
                updateProdQuan.Parameters.Add(quan);
                objDB.DoUpdateUsingCmdObj(updateProdQuan);

            }
            //SqlCommand removeCart = new SqlCommand();

            //updateProdQuan.CommandType = CommandType.StoredProcedure;
            //updateProdQuan.CommandText = "TP_removeCart";

            //SqlParameter id = new SqlParameter("@custID", Convert.ToInt32(Session["loginId"]));
            //id.Direction = ParameterDirection.Input;
            //id.SqlDbType = SqlDbType.Int;
            //id.Size = 4;
            //objDB.DoUpdateUsingCmdObj(removeCart);

            //List<cartItem> emptyCart = new List<cartItem>();
            //Session["cart"] = emptyCart;
        }

 public void rebind()
 {
     DBConnect db = new DBConnect();
     SqlCommand command = new SqlCommand();
     command.CommandType = CommandType.StoredProcedure;
     command.CommandText = "GetAllCourses";
     gvCourses.DataSource = db.GetDataSetUsingCmdObj(command);
     gvCourses.DataBind();
 }

        protected void btnSaveChanges_Click(object sender, EventArgs e)
        {
            if (String.IsNullOrEmpty(txtCurrentEmail.Text) || String.IsNullOrEmpty(txtPassword.Text) || String.IsNullOrEmpty(txtNewEmail2.Text))
                Page.ClientScript.RegisterStartupScript(this.GetType(), "Script", "<script> alert('All fields are required.Thank you!'); </script>");

            {
                string currentEmail = txtCurrentEmail.Text;
                string passord = txtPassword.Text;
                string newEmail2 = txtNewEmail2.Text;

                SqlCommand emailCmd = new SqlCommand();
                DBConnect objDB = new DBConnect();

                emailCmd.CommandType = CommandType.StoredProcedure;
                emailCmd.CommandText = "dbo.TP_ResetEmail";

                SqlParameter inputEmail = new SqlParameter("@currentEmail", currentEmail);

                inputEmail.Direction = ParameterDirection.Input;
                inputEmail.SqlDbType = SqlDbType.NVarChar;
                inputEmail.Size = 50;
                emailCmd.Parameters.Add(inputEmail);

                SqlParameter inputPassword = new SqlParameter("@password", passord);

                inputPassword.Direction = ParameterDirection.Input;
                inputPassword.SqlDbType = SqlDbType.NVarChar;
                inputPassword.Size = 50;
                emailCmd.Parameters.Add(inputPassword);

                SqlParameter inputEmail2 = new SqlParameter("@newEmail", newEmail2);

                inputEmail2.Direction = ParameterDirection.Input;
                inputEmail2.SqlDbType = SqlDbType.NVarChar;
                inputEmail2.Size = 50;
                emailCmd.Parameters.Add(inputEmail2);

                emailCmd.Parameters.Add("@result", SqlDbType.Int).Direction = ParameterDirection.Output;

                objDB.DoUpdateUsingCmdObj(emailCmd);
                int result = Convert.ToInt32(emailCmd.Parameters["@result"].Value);

                if (result == -1)
                    lblMsg.Text = "The password you entered is incorrect!";

                else if (result == 0)
                {
                    lblMsg.Text = "The new email you entered already exists.";
                }
                else
                {
                    lblMsg.Text = "The email was changed succesfully.";
                }
            }
        }