Exemple #1
0
        private void btnA_Delete_Click(object sender, RoutedEventArgs e)
        {
            try
            {
                SqlConnect con = new SqlConnect();
                con.conOpen();
                if (con != null)
                {
                    //check first if theres any book from tblBooks that uses the id of author
                    string     check    = "SELECT TOP 1 * FROM tblBooks WHERE bookauthor_id = @id";
                    SqlCommand cmdcheck = new SqlCommand(check, con.Con);
                    cmdcheck.Parameters.AddWithValue("@id", txtAuthoriD.Text.Trim());
                    SqlDataReader sdr = cmdcheck.ExecuteReader();

                    if (sdr.Read())                                     //if theres a record
                    {
                        string booktitle = sdr["booktitle"].ToString(); // if ever the booktitle is ask, we can use this.
                        MessageBox.Show("Cannot delete record, there's a reference from book list.");
                        sdr.Close();
                        return;
                    }
                    else  //we can proceed to deleting of that record
                    {
                        sdr.Close();
                        string     query = "DELETE FROM tblAuthor WHERE authorid = @id";
                        SqlCommand cmd   = new SqlCommand(query, con.Con);
                        cmd.Parameters.AddWithValue("@id", txtAuthoriD.Text.Trim());
                        cmd.ExecuteNonQuery();
                        AuthorLoad("");
                        enabledNewTransbutton();
                        clearControls();
                        MessageBox.Show("Record deleted successfully");
                    }
                }
                else
                {
                    return;
                }
                con.conclose();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }
Exemple #2
0
        private void btnLogin_Click(object sender, RoutedEventArgs e)
        {
            SqlConnect con = new SqlConnect();

            con.conOpen();
            if (con == null)
            {
                return;
            }
            else
            {
                Console.WriteLine("Successfully Connected");
                SqlCommand cmd = new SqlCommand("SELECT * FROM tblUsers WHERE username = @username and password = @password ", con.Con);
                cmd.Parameters.AddWithValue("@username", txtUsername.Text);
                cmd.Parameters.AddWithValue("@password", txtPassword.Password);

                SqlDataAdapter sda = new SqlDataAdapter(cmd);
                DataTable      dt  = new DataTable();
                sda.Fill(dt);
                if (sda.Fill(dt) > 0)
                {
                    Console.WriteLine("Valid Account");
                    con.conclose();
                    this.Close();
                    n.Show();
                }
                else
                {
                    Console.WriteLine("Invalid Account");
                }


                //SqlDataAdapter sda = new SqlDataAdapter("SELECT * FROM tblUsers WHERE username = '******' and password = '******' ", con.Con);
                //parametarized
                //string sql = "SELECT * FROM tblUsers WHERE username = @username and password = @password ";
                //using (SqlCommand cmd = new SqlCommand(sql, con.Con))
                //{
                //    var acctParam = new SqlParameter("username", SqlDbType.VarChar);
                //    acctParam.Value = txtUsername.Text;
                //    cmd.Parameters.Add(acctParam);
                //    var results = cmd.ExecuteReader();
                //}
            }
        }
        private void btnB_Delete_Click(object sender, RoutedEventArgs e)
        {
            try
            {
                SqlConnect con = new SqlConnect();
                con.conOpen();
                if (con != null)
                {
                    //let's check first if the data trying to delete has a record in our main transactions.
                    string     check    = "SELECT TOP 1 * FROM tblTransaction WHERE barrower_id = @id";
                    SqlCommand cmdcheck = new SqlCommand(check, con.Con);
                    cmdcheck.Parameters.AddWithValue("@id", txtBarrowerId.Text.Trim());
                    SqlDataReader sdr = cmdcheck.ExecuteReader();

                    if (sdr.Read()) //if theres a record, return, meaning we cannot allow it
                    {
                        MessageBox.Show("Cannot delete record, the data you are trying to delete has a history on main transaction.");
                        sdr.Close();
                        return;
                    }
                    else //we can change or update it
                    {
                        sdr.Close();
                        string     query = "DELETE FROM tblBarrower WHERE barrower_id = @id";
                        SqlCommand cmd   = new SqlCommand(query, con.Con);
                        cmd.Parameters.AddWithValue("@id", txtBarrowerId.Text.Trim());
                        cmd.ExecuteNonQuery();
                        gridBarrowerLoad("");
                        enabledNewTransbutton();
                        clearControls();
                        MessageBox.Show("Record deleted successfully");
                    }
                }
                else
                {
                    return;
                }
                con.conclose();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }
Exemple #4
0
        private void AuthorLoad(string value)
        {
            var _condition = "";

            if (value == "")
            {
                _condition = "";
            }
            else
            {
                //_condition = "WHERE author_fname LIKE '%" + value + "%' OR author_lname LIKE '%" + value + "%'";
                _condition = "WHERE author_fname LIKE '%" + @value + "%' OR author_lname LIKE '%" + @value + "%'";
            }

            SqlConnect con = new SqlConnect();

            con.conOpen();
            if (con == null)
            {
                return;
            }
            else
            {
                try
                {
                    string     q   = "SELECT * , (author_fname + ' ' + author_lname )as Author FROM tblAuthor " + _condition;
                    SqlCommand cmd = new SqlCommand(q, con.Con);
                    cmd.Parameters.AddWithValue("@value", value);
                    SqlDataAdapter sda = new SqlDataAdapter(cmd);
                    DataSet        ds  = new DataSet();
                    DataTable      dt  = new DataTable();
                    sda.Fill(dt);
                    dgAuthor.ItemsSource           = dt.DefaultView;
                    dgAuthor.Columns[0].Visibility = Visibility.Collapsed;
                    dgAuthor.Columns[1].Visibility = Visibility.Collapsed;
                    dgAuthor.Columns[2].Visibility = Visibility.Collapsed;
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message);
                }
            }
            con.conclose();
        }
Exemple #5
0
        private void CourseLoad(string value)
        {
            var _condition = "";

            if (value == "")
            {
                _condition = "";
            }
            else
            {
                _condition = "WHERE coursecode LIKE '%" + @value + "%' OR coursedesc LIKE '%" + @value + "%'";
            }

            SqlConnect con = new SqlConnect();

            con.conOpen();
            if (con == null)
            {
                return;
            }
            else
            {
                try
                {
                    string     q   = "SELECT * FROM tblCourse " + _condition;
                    SqlCommand cmd = new SqlCommand(q, con.Con);
                    cmd.Parameters.AddWithValue("@value", value);
                    SqlDataAdapter sda = new SqlDataAdapter(cmd);
                    DataSet        ds  = new DataSet();
                    DataTable      dt  = new DataTable();
                    sda.Fill(dt);
                    dgCourse.ItemsSource           = dt.DefaultView;
                    dgCourse.Columns[0].Visibility = Visibility.Collapsed;
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message);
                }
            }
            con.conclose();
        }
Exemple #6
0
        public bool saveTrans(string table, string[] cols, string[] vals)
        {
            try
            {
                SqlConnect con = new SqlConnect();
                con.conOpen();
                string[] param = new string[vals.Length];
                for (int y = 0; y < vals.Length; y++)
                {
                    param[y] = "@param" + y.ToString();
                }
                //param = param.TrimEnd(param[param.Length - 1]);

                if (con != null)
                {
                    string     query = "INSERT INTO " + table + " (" + getColumns(cols) + ") VALUES (" + separateParam(param) + ")   ";
                    SqlCommand cmd   = new SqlCommand(query, con.Con);
                    int        count = 0;
                    foreach (string paramX in vals)
                    {
                        var valParam = "@param" + count.ToString();
                        cmd.Parameters.AddWithValue(valParam, paramX);
                        Console.WriteLine("param:" + valParam + "   value:" + paramX);
                        count += 1;
                    }
                    Console.WriteLine(query);
                    cmd.ExecuteNonQuery();
                }
                else
                {
                    return(false);
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
                return(false);
            }
            return(true);
        }
        public void comboBoxLoad()
        {
            SqlConnect con = new SqlConnect();

            con.conOpen();
            if (con != null)
            {
                string         q   = "SELECT * , (author_fname + ' ' + author_lname) as Author FROM tblAuthor";
                SqlCommand     cmd = new SqlCommand(q, con.Con);
                SqlDataAdapter sda = new SqlDataAdapter(cmd);
                DataSet        dt  = new DataSet();
                sda.Fill(dt, "tblAuthor");
                cboBauthor.ItemsSource       = dt.Tables[0].DefaultView;
                cboBauthor.DisplayMemberPath = dt.Tables[0].Columns["Author"].ToString();
                cboBauthor.SelectedValuePath = dt.Tables[0].Columns["authorid"].ToString();
            }
            else
            {
                return;
            }
            con.conclose();
        }
        private void gridLoading(string value)
        {
            var _condition = "";

            if (value == "")
            {
                _condition = "";
            }
            else
            {
                _condition = "WHERE A.booktitle LIKE '%" + @value + "%' OR B.author_fname LIKE '%" + @value + "%' OR B.author_lname LIKE '%" + @value + "%'";
            }

            SqlConnect con = new SqlConnect();

            con.conOpen();
            if (con != null)
            {
                string     q   = "SELECT A.*, (B.author_fname + ' ' + B.author_lname) AS Author FROM tblBooks A INNER JOIN tblAuthor B ON A.bookauthor_id = B.authorid " + _condition;
                SqlCommand cmd = new SqlCommand(q, con.Con);
                cmd.Parameters.AddWithValue("@value", value);
                SqlDataAdapter sda = new SqlDataAdapter(cmd);
                DataTable      dt  = new DataTable();
                sda.Fill(dt);
                dgBooks.ItemsSource           = dt.DefaultView;
                dgBooks.Columns[0].Visibility = Visibility.Collapsed;
                dgBooks.Columns[2].Visibility = Visibility.Collapsed;
                dgBooks.Columns[1].Header     = "Book Title";
                dgBooks.Columns[6].Header     = "Author";
                dgBooks.Columns[3].Header     = "Published";
                dgBooks.Columns[4].Header     = "Quantity";
                dgBooks.Columns[5].Header     = "Stocks";
            }
            else
            {
                return;
            }
            con.conclose();
        }
Exemple #9
0
        private void UserControl_Loaded(object sender, RoutedEventArgs e)
        {
            SqlConnect con = new SqlConnect();

            con.conOpen();
            if (con == null)
            {
                return;
            }
            else
            {
                string        query = "SELECT COUNT(*) as 'count' FROM tblTransactions WHERE status = 0";
                SqlCommand    cmd   = new SqlCommand(query, con.Con);
                SqlDataReader sdr   = cmd.ExecuteReader();
                if (sdr.Read())
                {
                    string unreturned = sdr["count"].ToString();
                    txtUnreturnedbook.Text = "Unreturned books: " + unreturned;
                    Console.WriteLine("Unreturned books:" + sdr["count"]);
                }
            }
        }
        public void loadCourse()
        {
            SqlConnect con = new SqlConnect();

            con.conOpen();
            if (con != null)
            {
                string         q   = "SELECT course_id, coursecode FROM tblCourse";
                SqlCommand     cmd = new SqlCommand(q, con.Con);
                SqlDataAdapter sda = new SqlDataAdapter(cmd);
                DataSet        dt  = new DataSet();
                sda.Fill(dt, "tblCourse");
                cboCourse.ItemsSource       = dt.Tables[0].DefaultView;
                cboCourse.DisplayMemberPath = dt.Tables[0].Columns["coursecode"].ToString();
                cboCourse.SelectedValuePath = dt.Tables[0].Columns["course_id"].ToString();
            }
            else
            {
                return;
            }
            con.conclose();
        }
Exemple #11
0
 public bool updateTrans(string table, string[] cols, string[] vals, Dictionary <string, string> conds)
 {
     try
     {
         SqlConnect con = new SqlConnect();
         con.conOpen();
         string[] param = new string[vals.Length];
         for (int y = 0; y < vals.Length; y++)
         {
             param[y] = "@param" + y.ToString();
         }
         if (con != null)
         {
             string     query = "UPDATE " + table + " SET " + joinedColsVals(cols, param) + " WHERE " + iterateCond(conds);
             SqlCommand cmd   = new SqlCommand(query, con.Con);
             int        count = 0;
             foreach (string paramX in vals)
             {
                 var valParam = "@param" + count.ToString();
                 cmd.Parameters.AddWithValue(valParam, paramX);
                 Console.WriteLine("param:" + valParam + "   value:" + paramX);
                 count += 1;
             }
             Console.WriteLine(query);
             cmd.ExecuteNonQuery();
         }
         else
         {
             return(false);
         }
     }
     catch (Exception ex)
     {
         MessageBox.Show(ex.Message);
         return(false);
     }
     return(true);
 }
        private void gridBarrowerLoad(string value)
        {
            var _condition = "";

            if (value == "")
            {
                _condition = "";
            }
            else
            {
                _condition = "WHERE A.barrower_fname LIKE '%" + @value + "%' OR A.barrower_lastname LIKE '%" + @value + "%' OR B.coursecode LIKE '%" + @value + "%'";
            }

            SqlConnect con = new SqlConnect();

            con.conOpen();
            if (con != null)
            {
                string     q   = "SELECT A.*, B.course_id FROM tblBarrower A INNER JOIN tblCourse B ON A.course = B.course_id " + _condition;
                SqlCommand cmd = new SqlCommand(q, con.Con);
                cmd.Parameters.AddWithValue("@value", value);
                SqlDataAdapter sda = new SqlDataAdapter(cmd);
                DataTable      dt  = new DataTable();
                sda.Fill(dt);
                dgBarrower.ItemsSource       = dt.DefaultView;
                dgBarrower.Columns[0].Header = "Barrower Id";
                dgBarrower.Columns[1].Header = "First Name";
                dgBarrower.Columns[2].Header = "Last Name";
                dgBarrower.Columns[3].Header = "Course";
            }

            else
            {
                return;
            }
            con.conclose();
        }
Exemple #13
0
        private void btnB_Save_Click(object sender, RoutedEventArgs e)
        {
            try
            {
                //let's check first if the textboxes are empty since we cannot allow a blank data
                if (txtbTitle.Text.Trim() == null || txtbQty.Text.Trim() == null || cboBauthor.Text == null || dP_datePublished.Text == null)
                {
                    MessageBox.Show("Book info cannot be empty");
                    return;
                }
                if (Convert.ToInt32(txtbQty.Text) < 0)
                {
                    MessageBox.Show("Quantity cannot be less than zero");
                    return;
                }

                SqlConnect con = new SqlConnect();
                con.conOpen();
                if (con != null)
                {
                    //let's check first if the data trying to change is already exist.
                    string     check    = "SELECT TOP 1 * FROM tblBooks WHERE booktitle = @title";
                    SqlCommand cmdcheck = new SqlCommand(check, con.Con);
                    cmdcheck.Parameters.AddWithValue("@title", txtbTitle.Text.Trim());
                    SqlDataReader sdr = cmdcheck.ExecuteReader();

                    if (sdr.Read()) //if theres a record, return, meaning we cannot allow it
                    {
                        MessageBox.Show("Cannot add this record, the data you are trying to insert already exist.");
                        sdr.Close();
                        return;
                    }
                    else //we can add or insert it
                    {
                        sdr.Close();
                        string     query = "INSERT INTO tblBooks (booktitle, bookauthor_id, datepublished, qty, stocks) VALUES (@title, @author, @dtpub, @qty, @qty)";
                        SqlCommand cmd   = new SqlCommand(query, con.Con);
                        cmd.Parameters.AddWithValue("@title", txtbTitle.Text.Trim());
                        cmd.Parameters.AddWithValue("@author", cboBauthor.SelectedValue.ToString());
                        cmd.Parameters.AddWithValue("@dtpub", dP_datePublished.Text.Trim());
                        cmd.Parameters.AddWithValue("@qty", txtbQty.Text.Trim());
                        //cmd.Parameters.AddWithValue("@stocks", txtbStocks.Text.Trim());

                        cmd.ExecuteNonQuery();
                        gridLoading("");
                        enabledNewTransbutton();
                        clearControls();
                        MessageBox.Show("Record saved successfully");
                    }
                }
                else
                {
                    return;
                }
                con.conclose();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }