private void loadMovies()
        {
            CategoryID a;
            string     SQL = "SELECT category.`category_id`,category.`name` FROM category";

            try
            {
                cmd.CommandText = SQL;
                reader          = cmd.ExecuteReader();
                ListMovies.Clear();
                //lblEmployee.Text = "";
                while (reader.Read())
                {
                    a = new CategoryID(reader[0].ToString(),
                                       //boroume omoios na grapsoume reader[1] bla bla
                                       reader["name"].ToString());
                    ListMovies.Add(a);
                }
                cbxCategories.SelectedIndexChanged -= new EventHandler(cbxCategories_SelectedIndexChanged);
                bind(cbxCategories);
                cbxCategories.SelectedIndex = -1;
                cbxCategories.Text          = "Select Category ... ";
                // lblEmployee.Text = "";
                cbxCategories.SelectedIndexChanged += new EventHandler(cbxCategories_SelectedIndexChanged);
                if (reader != null)
                {
                    reader.Close();
                }
            }
            catch (Exception ex) { MessageBox.Show(ex.Message); }
            finally
            {
                //reader?.Close(); //if (reader != null) reader.Close();
            }
        }
Beispiel #2
0
        private void ShowFilms()
        {
            int    categoryID;
            string sWHERE = string.Empty;

            try
            {
                SQL = "SELECT" + Environment.NewLine +
                      "`film`.`film_id`     AS `FID`," + Environment.NewLine +
                      "`film`.`title`       AS `title`," + Environment.NewLine +
                      "`category`.`name`    AS `category`," + Environment.NewLine +
                      "`film`.`length`      AS `length`," + Environment.NewLine +
                      "`film`.`rating`      AS `rating`," + Environment.NewLine +
                      "`film`.`rental_rate` AS `price`," + Environment.NewLine +
                      "film.`special_features` AS special_features," + Environment.NewLine +
                      "film.`rental_duration` AS rental_duration," + Environment.NewLine +
                      "film.`replacement_cost` AS replacement_cost," + Environment.NewLine +
                      "`film`.`description` AS `description`" + Environment.NewLine +
                      "FROM category" + Environment.NewLine +
                      "LEFT JOIN film_category ON category.`category_id`= film_category.`category_id`" + Environment.NewLine +
                      "LEFT JOIN film ON film_category.`film_id`= film.`film_id`" + Environment.NewLine;

                sWHERE = " Where film.`title` LIKE " + My.Quote(txtMovieName.Text + "%");
                if (cbxCategories.SelectedIndex != -1)
                {
                    CategoryID a = (CategoryID)cbxCategories.SelectedItem;
                    categoryID = Convert.ToInt32(a.id);
                    sWHERE    += " AND category.`category_id`=" + categoryID;
                }

                if (txtMovieName.Text != string.Empty || cbxCategories.SelectedIndex != -1)
                {
                    SQL += Environment.NewLine + sWHERE;
                }
                cmd.CommandText = SQL;
                reader          = cmd.ExecuteReader();

                tblMovies.Rows.Clear();
                tblMovies.Load(reader);
                dvgMovies.DataSource = tblMovies;
                lblMovies.Text       = "Movies Found: " + tblMovies.Rows.Count.ToString();
                if (reader != null)
                {
                    reader.Close();
                }
            }
            catch (Exception ex) { MessageBox.Show(ex.ToString()); }
            finally
            {
                //reader?.Close();
            }
        }
        private void ShowPayments()
        {
            string from, to;
            string sWHERE = string.Empty;
            int    storeID;

            try
            {
                from = dtpFrom.Value.ToString("yyyy-MM-dd");
                to   = dtpTo.Value.ToString("yyyy-MM-dd");

                SQL = "SELECT payment.`payment_id`,CONCAT(customer.`first_name`,' ', customer.`last_name`) AS Customer,customer.`email`,payment.`amount`,payment.`payment_date`, address.`address` AS StoreAddress" + Environment.NewLine +
                      "FROM payment" + Environment.NewLine +
                      "JOIN customer ON payment.`customer_id`=customer.`customer_id`" + Environment.NewLine +
                      "JOIN store ON customer.`store_id`=store.`store_id`" + Environment.NewLine +
                      "JOIN address ON store.`address_id`=address.`address_id`" + Environment.NewLine;

                sWHERE  = "WHERE payment.`payment_date` BETWEEN '" + from + "' AND '" + to + "'";
                sWHERE += " AND customer.`last_name` LIKE '%" + txtCustomerLName.Text + "%'";
                if (cbxStore.SelectedIndex != -1)
                {
                    CategoryID a = (CategoryID)cbxStore.SelectedItem;
                    storeID = Convert.ToInt32(a.id);
                    sWHERE += "  AND store.`store_id`=" + storeID;
                }
                SQL            += sWHERE;
                cmd.CommandText = SQL;
                reader          = cmd.ExecuteReader();

                tblPayments.Rows.Clear();
                tblPayments.Load(reader);
                dvgPayments.DataSource = tblPayments;
                lblPayments.Text       = "Payments Found: " + tblPayments.Rows.Count.ToString();
                if (reader != null)
                {
                    reader.Close();
                }
            }
            catch (Exception ex) { MessageBox.Show(ex.ToString()); }
            finally
            {
                //reader?.Close();
            }
        }
Beispiel #4
0
        private void loadStores()
        {
            CategoryID a;
            string     SQL = "SELECT store.`store_id`,address.`address`" + Environment.NewLine +
                             "FROM store" + Environment.NewLine +
                             "LEFT JOIN address ON store.`address_id`= address.`address_id`;";

            try
            {
                cmd.CommandText = SQL;
                reader          = cmd.ExecuteReader();
                storeList.Clear();
                //lblEmployee.Text = "";
                while (reader.Read())
                {
                    a = new CategoryID(reader[0].ToString(),
                                       //boroume omoios na grapsoume reader[1] bla bla
                                       reader["address"].ToString());
                    storeList.Add(a);
                }
                cbxStore.SelectedIndexChanged -= new EventHandler(cbxStore_SelectedIndexChanged);
                bind(cbxStore);
                cbxStore.SelectedIndex = -1;
                cbxStore.Text          = "Select Store ... ";
                // lblEmployee.Text = "";
                cbxStore.SelectedIndexChanged += new EventHandler(cbxStore_SelectedIndexChanged);
                if (reader != null)
                {
                    reader.Close();
                }
            }
            catch (Exception ex) { MessageBox.Show(ex.Message); }
            finally
            {
                //reader?.Close(); //if (reader != null) reader.Close();
            }
        }