C# DBConnect.GetDataSetUsingCmdObj方法代码示例

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

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

 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 Page_Load(object sender, EventArgs e)
 {
     DBConnect db = new DBConnect();
         string merchEmail = Session["emailSession"].ToString();
         SqlCommand command = new SqlCommand();
         command.CommandType = CommandType.StoredProcedure;
         command.CommandText = "GetMerchAccount";
         command.Parameters.AddWithValue("@email", merchEmail);
         DataSet ds = db.GetDataSetUsingCmdObj(command);
         gvMerchAccount.DataSource = ds;
         gvMerchAccount.DataBind();
 }

 protected void btnAPI_Click(object sender, EventArgs e)
 {
     Label lblAPI = new Label();
     DBConnect db = new DBConnect();
     string merchEmail = Session["emailSession"].ToString();
     SqlCommand command = new SqlCommand();
     command.CommandType = CommandType.StoredProcedure;
     command.CommandText = "GetAPI";
     command.Parameters.AddWithValue("@email", merchEmail);
     DataSet ds = db.GetDataSetUsingCmdObj(command);
     string api = ds.Tables[0].Rows[0]["APIKey"].ToString();
     lblAPI.Text = api;
 }

        public DataSet GetDepartments()
        {
            SqlCommand dep = new SqlCommand();
            DBConnect objDB = new DBConnect();
            DataSet department = new DataSet();

            dep.CommandType = CommandType.StoredProcedure;
            dep.CommandText = "dbo.TP_getDepartments";

            department = objDB.GetDataSetUsingCmdObj(dep);

            return department;
        }

        public void loadProducts()
        {
            //display data from each item in Product DB into correct template feilds
            //display product name into label
            //display product url image into image

            DBConnect dbobj = new DBConnect();
            SqlCommand objCommand = new SqlCommand();
            objCommand.CommandType = CommandType.StoredProcedure;
            objCommand.CommandText = "GetAllProducts";
            DataSet ds = dbobj.GetDataSetUsingCmdObj(objCommand);
            rptProducts.DataSource = ds;
            rptProducts.DataBind();
        }

        //Register Merchant Button
        protected void btnContinue_Click(object sender, EventArgs e)
        {
            DBConnect DB = new DBConnect();
            Register register = new Register();
            Merchant newMerchant = new Merchant();

            newMerchant.groupName = txtName.Text;
            newMerchant.email = txtEmail.Text;
            newMerchant.address = txtAddress.Text;
            newMerchant.url = txtURL.Text;

            SqlCommand command = new SqlCommand();
            command.CommandType = CommandType.StoredProcedure;
            command.CommandText = "RegisterMerchant";
            command.Parameters.AddWithValue("@GroupName", newMerchant.groupName);
            command.Parameters.AddWithValue("@Email", newMerchant.email);
            command.Parameters.AddWithValue("@Address", newMerchant.address);
            command.Parameters.AddWithValue("@URL", newMerchant.url);
            DB.DoUpdateUsingCmdObj(command);

            DB = new DBConnect();
            SqlCommand key = new SqlCommand();
            key.CommandType = CommandType.StoredProcedure;
            key.CommandText = "InsertKey";
            key.Parameters.AddWithValue("@GroupName", newMerchant.groupName);
            DataSet ds = DB.GetDataSetUsingCmdObj(key);
            string apikey = ds.Tables[0].Rows[0]["APIKey"].ToString();
            lblGeneralError.Text = "Your APIKey is " + apikey;

            //Stored procedure to insert new merchant into a merchant table

            //if "Remember Me" is checked, store userName in cookie
            //if (chkbxRemeberMe.Checked)
            //{
            //    HttpCookie emailCookie = new HttpCookie("Login_Cookie");//cookie's name
            //    emailCookie.Values["email"] = txtEmail.Text;           //set cookies value

            //    emailCookie.Values["LastVisited"] = DateTime.Now.ToString();
            //    emailCookie.Expires = DateTime.Now.AddYears(1);
            //    Response.Cookies.Add(emailCookie);
            //}
            //else
            //{
            //    //remove user's email from username textbox
            //    Response.Cookies.Remove("mycookie");
            //}
            //Response.Redirect("Login.aspx");
        }

        protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack)
            {
                DBConnect DB = new DBConnect();
                SqlCommand command = new SqlCommand();
                command.CommandType = CommandType.StoredProcedure;
                command.CommandText = "GetDep";

                DataSet ds = DB.GetDataSetUsingCmdObj(command);
                ddDepartment.DataSource = ds;
                ddDepartment.DataTextField = "DepartmentName";
                ddDepartment.DataValueField = "DepartmentID";
                ddDepartment.DataBind();
            }
        }

        public void loadCreditCards(string email)
        {
            DBConnect objdb = new DBConnect();
            SqlCommand sqlCommand = new SqlCommand();
            sqlCommand.CommandType = CommandType.StoredProcedure;
            sqlCommand.CommandText = "TPgetCustomerCard";
            sqlCommand.Parameters.AddWithValue("@email", email);
            DataSet dataset = objdb.GetDataSetUsingCmdObj(sqlCommand);

            if (dataset.Tables[0].Rows.Count > 0)
            {
                ddlCreditCardList.DataSource = dataset;
                ddlCreditCardList.DataTextField = "CardNumber";
                ddlCreditCardList.DataValueField = "CVV";
                ddlCreditCardList.DataBind();
            }
        }

 protected void btnViewBill_Click(object sender, EventArgs e)
 {
     int studID = int.Parse(ddStudentSelect.SelectedValue.ToString());
         DBConnect db = new DBConnect();
         SqlCommand command = new SqlCommand();
         command.CommandType = CommandType.StoredProcedure;
         command.CommandText = "GetBill";
         command.Parameters.AddWithValue("@StudentID", studID);
         gvBill.DataSource = db.GetDataSetUsingCmdObj(command);
         gvBill.DataBind();
         int total = 0;
     for (int row = 0; row < gvBill.Rows.Count; row++)
     {
         total += int.Parse(gvBill.Rows[row].Cells[5].Text);
     }
     gvBill.FooterRow.Cells[1].Text = "Total Due: ";
     gvBill.FooterRow.Cells[5].Text = "$" + total;
 }

        protected void Page_Load(object sender, EventArgs e)
        {
            if (Request["email"] != null)
            {
                // check email validity
                string email = Request["email"].ToString();
                //check if email is unique w stored procedure
                DataSet myDS = new DataSet();
                SqlCommand objCommand = new SqlCommand();
                objCommand.CommandType = CommandType.StoredProcedure;
                objCommand.CommandText = "TP_ValidLogin";
                objCommand.Parameters.AddWithValue("@Email", email);
                objCommand.Parameters.AddWithValue("@password", "");
                DBConnect objDB = new DBConnect();
                myDS = objDB.GetDataSetUsingCmdObj(objCommand);

                if (myDS.Tables[0].Rows.Count > 0)
                {
                    Response.Write("Email already exists!");
                }
                else
                {
                    Response.Write("Ok");
                }
            }
            else if (Request["name"] != null)
            {
                // check everything else
                string name = Request["name"].ToString();
                if (name.Length > 0)
                {
                    Response.Write("Ok");
                }
                else
                {
                    Response.Write("Nope");
                }

            }
            else
            {
                Response.Redirect("Login.aspx");
            }
        }

        public float GetCreditLimit(string fName)
        {
            try
            {
                DBConnect DB = new DBConnect();
                SqlCommand command = new SqlCommand();
                command.CommandType = CommandType.StoredProcedure;
                command.CommandText = "GetCreditLimit";
                command.Parameters.AddWithValue("@FirstName", fName);
                DataSet ds = DB.GetDataSetUsingCmdObj(command);

                return float.Parse(ds.Tables[0].Rows[0]["CreditLimit"].ToString());
            }

            catch
            {
                return 0;
            }
        }

        public DataSet GetProductCatalog(string DepartmentNumber)
        {
            SqlCommand prod = new SqlCommand();
            DBConnect objDB = new DBConnect();
            DataSet product = new DataSet();

            prod.CommandType = CommandType.StoredProcedure;
            prod.CommandText = "dbo.TP_getProductCatalog";

            SqlParameter findProducts = new SqlParameter("@depNum", DepartmentNumber);
            findProducts.Direction = ParameterDirection.Input;
            findProducts.SqlDbType = SqlDbType.Int;
            findProducts.Size = 4;
            prod.Parameters.Add(findProducts);

            product = objDB.GetDataSetUsingCmdObj(prod);

            return product;
        }