C# Profile.ReturnMDetails方法代码示例

    protected void btnReturn_Click(object sender, EventArgs e)
    {
        lblEMsg.Visible = false;
        string[] iBook;
        try
        {
            SqlDataReader sdr;
            Profile pro = new Profile();

            //Check if any book is issued for this member.
            string sql = "SELECT Count(1) FROM Book_Transaction WHERE Member_ID = '" + lstMember.SelectedItem.ToString() + "' And Book_ID = '" + LstBook.SelectedItem.Text + "' And Actual_Return_Date is Null";
            sdr = pro.ReturnMDetails(sql);
            sdr.Read();
            int iB = (int)sdr[0];
            if (iB == 0)
            {
                lblEMsg.Visible = true;
                lblEMsg.Text = "No Book is issued for this member, please select the correct member";
                return;
            }
            sdr.Close();

            //Insert the data.
            iBook = new string[4];

            iBook[0] = LstBook.SelectedItem.Text;
            iBook[1] = lstMember.SelectedItem.Text;
            iBook[2] = txtARDate.Text;
            iBook[3] = txtLFee.Text;

            bool status;
            status = pro.ReturnBook(iBook);

            if (status == false)
            {
                lblEMsg.Visible = true;
                lblEMsg.Text = "Book Returning Failed";
            }
            lblEMsg.Visible = true;
            lblEMsg.Text = "Book Returned.";
            lstMember.Text = "";
            LstBook.Text = "";
            txtBTitle.Text = "";
            txtAuthor.Text = "";
            txtPub.Text = "";
            txtCat.Text = "";
            txtSup.Text = "";
            txtRDate.Text = "";
            txtIDate.Text = "";
            lblName.Text = "";
            txtLFee.Text = "";
            return;
        }
        catch (Exception ex)
        {
            lblEMsg.Visible = true;
            lblEMsg.Text = ex.Message;
        }
    }

    protected void btnDelete_Click(object sender, EventArgs e)
    {
        lblEMsg.Visible = false;
        Profile pro = new Profile();

        try
        {
            if (lstid.SelectedItem.ToString() == "")
            {
                lblEMsg.Text = "Please select Publisher ID";
                lblEMsg.Visible = true;
            }
            else
            {
                bool status;
                status = pro.DeletePublisher(lstid.SelectedItem.ToString());

                if (status == false)
                {
                    lblEMsg.Visible = true;
                    lblEMsg.Text = "Publisher deletion Failed";
                }
                lblEMsg.Visible = true;
                lblEMsg.Text = "Publisher deleted.";

                lstid.Items.Clear();

                //Load all the Publisher First.
                string sql = "SELECT Publisher_ID FROM Publisher where Status = 'A'";
                SqlDataReader sdr;

                sdr = pro.ReturnMDetails(sql);

                while (sdr.Read())
                {
                    lstid.Items.Add(sdr["Publisher_ID"].ToString().Trim());
                }
                sdr.Close();
                txtPName.Text = "";
                txtPYear.Text = "";
                txtPHouse.Text = "";
                txtPEdition.Text = "";
                txtAdd.Text = "";
                txtPhone.Text = "";
                txtFax.Text = "";
                txtemail.Text = "";
                chkStatus.Checked = true;
                return;
                //txtCName.Text = "";
                //txtcDesc.Text = "";
            }
        }
        catch (Exception ex)
        {
            throw ex;
        }
    }

    protected void btnAdd_Click(object sender, EventArgs e)
    {
        lblEMsg.Visible = false;
        string[] Catg;
        try
        {
            string sql = "SELECT COUNT(*) FROM CATEGORY WHERE CATEGORY_NAME = '" + txtCName.Text + "' And Status = 'A'";
            SqlDataReader sdr;
            Profile pro = new Profile();
            sdr = pro.ReturnMDetails(sql);

            sdr.Read();
            int RegNo = (int)sdr[0];
            if (RegNo > 0)
            {
                lblEMsg.Text = "Category Name already exists.";
                lblEMsg.Visible = true;
                sdr.Close();
                return;
            }
            else
            {
                Catg = new string[15];

                Catg[0] = txtCategory.Text;
                Catg[1] = txtCName.Text;
                Catg[2] = txtcDesc.Text;
                if (chkStatus.Checked == true)
                {
                    Catg[3] = "A";
                }
                else
                {
                    Catg[3] = "I";
                }

                bool status;
                status = pro.AddCategory(Catg);

                if (status == false)
                {
                    lblEMsg.Visible = true;
                    lblEMsg.Text = "Category creation Failed";
                }
                lblEMsg.Visible = true;
                lblEMsg.Text = "Category added.";
                ReadCategory();
                txtCName.Text = "";
                txtcDesc.Text = "";
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
    }

    protected void btnDelete_Click(object sender, EventArgs e)
    {
        lblEMsg.Visible = false;
        Profile pro = new Profile();

        try
        {
            if (lstbook.SelectedItem.ToString() == "")
            {
                lblEMsg.Text = "Please select Book No";
                lblEMsg.Visible = true;
            }
            else
            {
                bool status;
                status = pro.DeleteBook(lstbook.SelectedItem.ToString());

                if (status == false)
                {
                    lblEMsg.Visible = true;
                    lblEMsg.Text = "Book deletion Failed";
                }
                lblEMsg.Visible = true;
                lblEMsg.Text = "Book deleted.";

                lstbook.Items.Clear();

                //Load all the Publisher First.
                string sql = "SELECT Book_ID FROM Book_Master where Status = 'A'";
                SqlDataReader sdr;
                sdr = pro.ReturnMDetails(sql);
                lstbook.Items.Add("");
                while (sdr.Read())
                {
                    lstbook.Items.Add(sdr["Book_ID"].ToString().Trim());
                }
                sdr.Close();
                txtBTitle.Text = "";
                LstAuthor.Text = "";
                LstCategory.Text = "";
                LstPublisher.Text = "";
                LstSupplier.Text = "";
                txtKey.Text = "";
                txtIsbn.Text = "";
                txtQuantity.Text = "";
                txtPrice.Text = "";
                chkStatus.Checked = false;
                lblEMsg.Visible = false;
            }
        }
        catch (Exception ex)
        {
            throw ex;
        }
    }

    protected void LoginButton_Click(object sender, EventArgs e)
    {
        try
        {
            string strRole;

            string sql = "SELECT MEMBER_ID, USER_NAME, PASSWORD, FIRST_NAME, MIDDLE_NAME, LAST_NAME, STATUS, USER_TYPE FROM MEMBER WHERE USER_NAME = '" + UserName.Text + "' AND PASSWORD = '" + Password.Text + "'";
            SqlDataReader sdr;

            Profile pro = new Profile();
            sdr = pro.ReturnMDetails(sql);
            if (sdr.Read() == true)
            {
                FormsAuthentication.Initialize();
                if(sdr["USER_TYPE"].ToString().Trim() == "A")
                {
                    strRole = "Admin";
                }
                else
                {
                    strRole = "Member";
                }

                //The AddMinutes determines how long the user will be logged in after leaving
               //the site if he doesn't log off.
               //FormsAuthenticationTicket fat = new FormsAuthenticationTicket(1, UserName.Text, DateTime.Now,
               //        DateTime.Now.AddMinutes(30), false, strRole, FormsAuthentication.FormsCookiePath);

                //Response.Cookies.Add(new HttpCookie(FormsAuthentication.FormsCookieName,
                //       FormsAuthentication.Encrypt(fat)));

                Session["MEMBER_ID"] = sdr["MEMBER_ID"].ToString().Trim();
                Session["USER_NAME"] = sdr["USER_NAME"].ToString().Trim();
                Session["PASSWORD"] = sdr["PASSWORD"].ToString().Trim();
                Session["NAME"] = sdr["FIRST_NAME"].ToString().Trim() + " " + sdr["MIDDLE_NAME"].ToString().Trim() + " " + sdr["LAST_NAME"].ToString().Trim();
                Session["STATUS"] = sdr["STATUS"].ToString().Trim();
                Session["USER_TYPE"] = sdr["USER_TYPE"].ToString().Trim();
                //Response.Redirect(FormsAuthentication.GetRedirectUrl("Home.aspx", false));
                Response.Redirect("Home.aspx");
            }
            else
            {
                lblErrorMsg.Visible = true;
                lblErrorMsg.Text = "Invalid User Name/Password.";
            }
            sdr.Close();
        }
        catch(Exception err)
        {
            lblErrorMsg.Visible = true;
            lblErrorMsg.Text = err.Message;
        }
    }

    protected void btnDelete_Click(object sender, EventArgs e)
    {
        lblEMsg.Visible = false;
        Profile pro = new Profile();

        try
        {
            if (lstid.SelectedItem.Text == "")
            {
                lblEMsg.Text = "Please select Author ID";
                lblEMsg.Visible = true;
            }
            else
            {
                bool status;
                status = pro.DeleteAuthor(lstid.SelectedItem.Text);

                if (status == false)
                {
                    lblEMsg.Visible = true;
                    lblEMsg.Text = "Author deletion Failed";
                }
                lblEMsg.Visible = true;
                lblEMsg.Text = "Author deleted.";
                lstid.Items.Clear();

                //Load all the Category First.
                string sql = "SELECT Author_ID FROM Author Where Status ='A'";
                SqlDataReader sdr;

                sdr = pro.ReturnMDetails(sql);

                while (sdr.Read())
                {
                    lstid.Items.Add(sdr["Author_ID"].ToString().Trim());
                }
                sdr.Close();
                txtAName.Text = "";
                txtAdd.Text = "";
                txtPhone.Text = "";
                txtFax.Text = "";
                txtEmail.Text = "";
                chkStatus.Checked = false;
                return;

            }
        }
        catch (Exception ex)
        {
            throw ex;
        }
    }

    protected void btnDelete_Click(object sender, EventArgs e)
    {
        lblEMsg.Visible = false;
        Profile pro = new Profile();

        try
        {
            if (lstid.Text == "")
            {
                lblEMsg.Text = "Please select Category ID";
                lblEMsg.Visible = true;
            }
            else
            {
                bool status;
                status = pro.DeleteCategory(lstid.SelectedItem.ToString());

                if (status == false)
                {
                    lblEMsg.Visible = true;
                    lblEMsg.Text = "Category deletion Failed";
                }
                lblEMsg.Visible = true;
                lblEMsg.Text = "Category deleted.";

                lstid.Items.Clear();

                //Load all the Category First.
                string sql = "SELECT Category_ID FROM CATEGORY Where Status = 'A'";
                SqlDataReader sdr;
                sdr = pro.ReturnMDetails(sql);

                while (sdr.Read())
                {
                    lstid.Items.Add(sdr["Category_ID"].ToString().Trim());
                }
                sdr.Close();
                txtCName.Text = "";
                txtcDesc.Text = "";
                return;
            }
        }
        catch (Exception ex)
        {
            throw ex;
        }
    }

    protected void Page_Load(object sender, EventArgs e)
    {
        if (Session["USER_TYPE"] == null)
        {
            Response.Redirect("Logout.aspx");
        }
        if (Page.IsPostBack == false)
        {
            if (Session["USER_TYPE"].ToString() == "A")
            {
                Panel2.Visible = false;
            }
            else
            {
                Panel3.Visible = false;
            }
            try
            {

                string sql = "SELECT Distinct Book_Title FROM Book_Master Where Status = 'A'";
                SqlDataReader sdr;
                Profile pro = new Profile();
                sdr = pro.ReturnMDetails(sql);
                lstBook.Items.Clear();
                lstBook.Items.Add("");
                while (sdr.Read())
                {
                    lstBook.Items.Add(sdr["Book_Title"].ToString().Trim());
                }
                sdr.Close();

                sql = "SELECT DISTINCT BM.BOOK_ID AS 'Book ID', BM.BOOK_TITLE AS 'Book Title', A.AUTHOR_NAME AS 'Author Name', P.PUBLISHER_NAME AS 'Pulisher Name', ";
                sql = sql + "P.PUBLISHING_YEAR AS 'Publishing Year', P.PUBLISHING_HOUSE AS 'Publishing House', P.PUBLISHER_EDITION AS 'Publisher Edition', C.CATEGORY_NAME AS 'Category Name', ";
                sql = sql + "S.SUPPLIER_NAME AS 'Supplier Name', BM.ISBN, BM.PRICE FROM BOOK_MASTER BM INNER JOIN AUTHOR A ON BM.AUTHOR_ID = A.AUTHOR_ID ";
                sql = sql + "INNER JOIN PUBLISHER P ON BM.PUBLISHER_ID = P.PUBLISHER_ID INNER JOIN CATEGORY C ON BM.CATEGORY_ID = C.CATEGORY_ID ";
                sql = sql + "INNER JOIN SUPPLIER S ON BM.SUPPLIER_ID = S.SUPPLIER_ID WHERE BM.BOOK_TITLE IS NULL ORDER BY BM.BOOK_ID";

                ReadBook(sql);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
    }

    protected void ChangePasswordPushButton_Click(object sender, EventArgs e)
    {
        try
        {
            string sql = "SELECT USER_NAME, PASSWORD FROM MEMBER WHERE USER_NAME = '" + txtUserName.Text + "' AND PASSWORD = '" + txtPassword.Text + "'";
            SqlDataReader sdr;

            Profile pro = new Profile();
            sdr = pro.ReturnMDetails(sql);
            if (sdr.Read() == true)
            {
                //Update New Password.
                Session["PASSWORD"] = txtNPassword.Text;

                bool status;
                status = pro.UpdatePassword(txtUserName.Text,txtNPassword.Text);

                if (status == false)
                {
                    lblMsg.Visible = true;
                    lblMsg.Text = "Password updation Failed";
                    return;
                }
                lblMsg.Visible = true;
                lblMsg.Text = "Password updated.";
                Response.Redirect("Home.aspx");
            }
            else
            {
                lblMsg.Visible = true;
                lblMsg.Text = "Invalid Password.";
                return;
            }
            sdr.Close();
        }
        catch (Exception ex)
        {
            throw ex;
        }
    }

    protected void lstid_SelectedIndexChanged(object sender, EventArgs e)
    {
        try
        {
            status = true;
            if (lstid.SelectedItem.ToString() == "")
            {
                lblEMsg.Visible = true;
                lblEMsg.Text = "Please select the Category";
                return;
            }
            string sql = "SELECT Category_Name, Category_Description, Status FROM CATEGORY WHERE Category_ID = '" + lstid.SelectedItem.ToString() + "'";
            SqlDataReader sdr;
            Profile pro = new Profile();
            sdr = pro.ReturnMDetails(sql);

            if (sdr.Read() == true)
            {
                txtCName.Text = sdr["Category_Name"].ToString().Trim();
                txtcDesc.Text = sdr["Category_Description"].ToString().Trim();
                chkStatus.Checked = true;
            }
            sdr.Close();
            return;
        }
        catch (Exception ex)
        {
            throw ex;
        }
    }

    protected void btnUpdate_Click(object sender, EventArgs e)
    {
        lblEMsg.Visible = false;
        string[] Auth;
        Profile pro = new Profile();
        try
        {
            if (lstid.SelectedItem.Text == "")
            {
                lblEMsg.Text = "Please select Author ID";
                lblEMsg.Visible = true;
            }
            else
            {
                Auth = new string[15];

                Auth[0] = lstid.Text;
                Auth[1] = txtAName.Text;
                Auth[2] = txtAdd.Text;
                Auth[3] = txtPhone.Text;
                Auth[4] = txtFax.Text;
                Auth[5] = txtEmail.Text;
                if (chkStatus.Checked == true)
                {
                    Auth[6] = "A";
                }
                else
                {
                    Auth[6] = "I";
                }

                bool status;
                status = pro.UpdateAuthor(Auth);

                if (status == false)
                {
                    lblEMsg.Visible = true;
                    lblEMsg.Text = "Author updation Failed";
                }
                lblEMsg.Visible = true;
                lblEMsg.Text = "Author updated.";

                //Load all the Category First.
                string sql = "SELECT Author_ID FROM Author";
                SqlDataReader sdr;
                sdr = pro.ReturnMDetails(sql);
                lstid.Items.Clear();
                while (sdr.Read())
                {
                    lstid.Items.Add(sdr["Author_ID"].ToString().Trim());
                }
                sdr.Close();
                txtAName.Text = "";
                txtAdd.Text = "";
                txtPhone.Text = "";
                txtFax.Text = "";
                txtEmail.Text = "";
                chkStatus.Checked = false;
                return;
            }
        }
        catch (Exception ex)
        {
            throw ex;
        }
    }

    private void ReadCategory()
    {
        try
        {
            string sql = "SELECT COUNT(*) FROM CATEGORY";
            SqlDataReader sdr;
            Profile pro = new Profile();
            sdr = pro.ReturnMDetails(sql);

            sdr.Read();
            int Cate = (int)sdr[0] + 1;
            if (Cate < 10)
            {
                txtCategory.Text = string.Format("C000{0}", Cate);
            }
            else if (Cate > 10 && Cate < 100)
            {
                txtCategory.Text = string.Format("C00{0}", Cate);
            }
            else
            {
                txtCategory.Text = string.Format("C0{0}", Cate);
            }
            sdr.Close();
            return;
        }
        catch (Exception ex)
        {
            throw ex;
        }
    }

    protected void btnAdd_Click(object sender, EventArgs e)
    {
        lblEMsg.Visible = false;
        string[] Supp;
        try
        {
            string sql = "SELECT COUNT(*) FROM SUPPLIER WHERE SUPPLIER_NAME = '" + txtSName.Text + "' And Status = 'A'";
            SqlDataReader sdr;
            Profile pro = new Profile();
            sdr = pro.ReturnMDetails(sql);

            sdr.Read();
            int RegNo = (int)sdr[0];
            if (RegNo > 0)
            {
                lblEMsg.Text = "Supplier Name already exists.";
                lblEMsg.Visible = true;
                sdr.Close();
                return;
            }
            else
            {
                Supp = new string[15];

                Supp[0] = txtSupplier.Text;
                Supp[1] = txtSName.Text;
                Supp[2] = txtAdd.Text;
                Supp[3] = txtPhone.Text;
                Supp[4] = txtFax.Text;
                Supp[5] = txtemail.Text;
                if (chkStatus.Checked == true)
                {
                    Supp[6] = "A";
                }
                else
                {
                    Supp[7] = "I";
                }

                bool status;
                status = pro.AddSupplier(Supp);

                if (status == false)
                {
                    lblEMsg.Visible = true;
                    lblEMsg.Text = "Supplier creation Failed";
                }
                lblEMsg.Visible = true;
                lblEMsg.Text = "Supplier added.";
                ReadSupplier();
                txtSName.Text = "";
                txtAdd.Text = "";
                txtPhone.Text = "";
                txtFax.Text = "";
                txtemail.Text = "";
            }
        }
        catch (Exception ex)
        {
            throw ex;
        }
    }

    private void ReadBook()
    {
        try
        {
            string sql = "SELECT Distinct Book_ID FROM Book_Master Where Status = 'A'";
            SqlDataReader sdr;
            Profile pro = new Profile();
            sdr = pro.ReturnMDetails(sql);
            lstbook.Items.Clear();
            lstbook.Items.Add("");
            while (sdr.Read())
            {
                lstbook.Items.Add(sdr["Book_ID"].ToString().Trim());
            }
            sdr.Close();

            //Retrieve Author Details
            sql = "SELECT Distinct Author_Name FROM Author Where Status = 'A'";
            sdr = pro.ReturnMDetails(sql);
            LstAuthor.Items.Clear();
            LstAuthor.Items.Add("");
            while (sdr.Read())
            {
                LstAuthor.Items.Add(sdr["Author_Name"].ToString().Trim());
            }
            sdr.Close();

            //Retrieve Publisher Details
            sql = "SELECT Distinct Publisher_Name FROM Publisher Where Status = 'A'";
            sdr = pro.ReturnMDetails(sql);
            LstPublisher.Items.Clear();
            LstPublisher.Items.Add("");
            while (sdr.Read())
            {
                LstPublisher.Items.Add(sdr["Publisher_Name"].ToString().Trim());
            }
            sdr.Close();

            //Retrieve Category Details
            sql = "SELECT Distinct Category_Name FROM Category Where Status = 'A'";
            sdr = pro.ReturnMDetails(sql);
            LstCategory.Items.Clear();
            LstCategory.Items.Add("");
            while (sdr.Read())
            {
                LstCategory.Items.Add(sdr["Category_Name"].ToString().Trim());
            }
            sdr.Close();

            //Retrieve Supplier Details
            sql = "SELECT Distinct Supplier_Name FROM Supplier Where Status = 'A'";
            sdr = pro.ReturnMDetails(sql);
            LstSupplier.Items.Clear();
            LstSupplier.Items.Add("");
            while (sdr.Read())
            {
                LstSupplier.Items.Add(sdr["Supplier_Name"].ToString().Trim());
            }
            sdr.Close();
        }
        catch (Exception ex)
        {
            throw ex;
        }
    }

    protected void Page_Load(object sender, EventArgs e)
    {
        if (Session["USER_TYPE"] == null)
        {
            Response.Redirect("Logout.aspx");
        }
        if (this.IsPostBack == false)
        {
            txtReg.Text = (string)Session["MEMBER_ID"];
            UserName.Text = (string)Session["USER_NAME"];
            Password.Text = (string)Session["PASSWORD"];
            ConfirmPassword.Text = (string)Session["PASSWORD"];

            try
            {
                string sql = "SELECT USER_NAME, PASSWORD, FIRST_NAME, MIDDLE_NAME, LAST_NAME, ADDRESS, E_MAIL_ID, LAND_LINE, MOBILE, SEX, SECURITY_Q, SECURITY_A FROM MEMBER WHERE MEMBER_ID = '" + txtReg.Text + "'";
                SqlDataReader sdr;

                Profile pro = new Profile();
                sdr = pro.ReturnMDetails(sql);
                if (sdr.Read() == true)
                {
                    UserName.Text = sdr["USER_NAME"].ToString().Trim();
                    Password.Text = sdr["PASSWORD"].ToString().Trim();
                    ConfirmPassword.Text = sdr["PASSWORD"].ToString().Trim();
                    txtFName.Text = sdr["FIRST_NAME"].ToString().Trim();
                    txtMName.Text = sdr["MIDDLE_NAME"].ToString().Trim();
                    txtLName.Text = sdr["LAST_NAME"].ToString().Trim();
                    txtAddress.Text = sdr["ADDRESS"].ToString().Trim();
                    Email.Text = sdr["E_MAIL_ID"].ToString().Trim();
                    txtLline.Text = sdr["LAND_LINE"].ToString().Trim();
                    txtMobile.Text = sdr["MOBILE"].ToString().Trim();
                    if (sdr["SEX"].ToString().Trim() == "M")
                    {
                        optMale.Checked = true;
                    }
                    else
                    {
                        optFemale.Checked = true;
                    }
                    Question.Text = sdr["SECURITY_Q"].ToString().Trim();
                    Answer.Text = sdr["SECURITY_A"].ToString().Trim();
                }
                sdr.Close();
            }
            catch (Exception err)
            {
                throw err;
            }
        }
        if (Session["USER_TYPE"].ToString() == "A")
        {
            Panel2.Visible = false;
        }
        else
        {
            Panel3.Visible = false;
        }
    }