WAWILORD DATABASE 2/12/2016 2

WITH SEARCH FUNCTION



using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;

namespace Macarse_Grocery
{

    public partial class Form1 : Form
    {
        SqlConnection con = new SqlConnection
                            (
                                @"server = localhost\SQLEXPRESS; Initial Catalog = Macarse_Grocery; Integrated Security = true;"
                            );

        SqlCommand cmd;
        SqlDataReader rdr;

        public Form1()
        {
            InitializeComponent();
        }

        private void loadCustomer()
        {
            dataCustomer.Rows.Clear();
            con.Open();
            cmd = new SqlCommand("SELECT custID, custLname, custFname, custMI FROM Customer;", con);

            rdr = cmd.ExecuteReader();

            while (rdr.Read())
            {
                dataCustomer.Rows.Add(rdr[0].ToString(), rdr[1].ToString(), rdr[2].ToString(), rdr[3].ToString());
            }

            con.Close();
        }



        private void loadProducts()
        {
            dataProducts.Rows.Clear();
            con.Open();
            cmd = new SqlCommand("SELECT prodID, prodName, prodPrice FROM Products;", con);

            rdr = cmd.ExecuteReader();

            while (rdr.Read())
            {
                dataProducts.Rows.Add(rdr[0].ToString(), rdr[1].ToString(), rdr[2].ToString());
            }

            con.Close();
        }


        private void tabPage2_Click(object sender, EventArgs e)
        {

        }

        private void Form1_Load(object sender, EventArgs e)
        {
            loadCustomer();
            loadProducts();
            loadPurchase();
        }

        private void btnPurchase_Click(object sender, EventArgs e)
        {
            if (dataCustomer.SelectedRows.Count != 0 && dataProducts.SelectedRows.Count != 0)
            {

                con.Open();
                cmd = new SqlCommand(@"INSERT INTO Purchase
                                        (custID, prodID, DatePurchased)
                                            VALUES('" + txtcustID.Text + "','" + txtprodID.Text + "','" + DateTime.Now.ToString() + "');", con);

                cmd.ExecuteNonQuery();
                con.Close();
                MessageBox.Show("Purchase Succesfully!");
                txtcustID.Clear();
                txtprodID.Clear();
                loadPurchase();
            }
        }

        private void loadPurchase()
        {
            dataPurchase.Rows.Clear();
            con.Open();
                 cmd = new SqlCommand(@"SELECT Purchase.PurchaseID AS BID,
                    Customer.custFname + ' ' + Customer.custMi + ' ' + Customer.custLname AS
                    'Customer', Products.prodName + ' priced at ' + cast(Products.prodPrice AS varchar) AS 'Product' FROM Products
                    INNER JOIN Purchase
                     ON Products.prodID = Purchase.prodID
                    INNER JOIN Customer
                      ON Purchase.custID = Customer.custID", con);
            rdr = cmd.ExecuteReader();

                while(rdr.Read())
                {
                    dataPurchase.Rows.Add(rdr[0].ToString(), rdr[1].ToString(), rdr[2].ToString());
                }
            con.Close();
        }

        private void txtcustID_TextChanged(object sender, EventArgs e)
        {

        }

        private void dataCustomer_SelectionChanged(object sender, EventArgs e)
        {
            if (dataCustomer.SelectedRows.Count != 0)
            {
                txtcustID.Text = dataCustomer.SelectedRows[0].Cells[0].Value.ToString();
                txtcustLname.Text = dataCustomer.SelectedRows[0].Cells[1].Value.ToString();
                txtcustFname.Text = dataCustomer.SelectedRows[0].Cells[2].Value.ToString();
                txtcustMI.Text = dataCustomer.SelectedRows[0].Cells[3].Value.ToString();

            }
        }

        private void txtprodID_TextChanged(object sender, EventArgs e)
        {
            if (dataProducts.SelectedRows.Count != 0)
            {



            }
        }

        private void dataProducts_SelectionChanged(object sender, EventArgs e)
        {
            if (dataProducts.SelectedRows.Count != 0)
            {
                txtprodID.Text = dataProducts.SelectedRows[0].Cells[0].Value.ToString();
                txtprodName.Text = dataProducts.SelectedRows[0].Cells[1].Value.ToString();
                txtprodPrice.Text = dataProducts.SelectedRows[0].Cells[2].Value.ToString();


            }

        }



        private void dataPurchase_CellContentClick(object sender, DataGridViewCellEventArgs e)
        {

        }

        private void label5_Click(object sender, EventArgs e)
        {

        }

        private void button1_Click(object sender, EventArgs e)
        {
            con.Open();
            cmd = new SqlCommand(@"INSERT INTO Customer
                                    (custLname, custFname, custMI)
                                   VALUES('" + txtcustLname.Text + "','" + txtcustFname.Text + "','"+txtcustMI.Text+ "')", con);

            cmd.ExecuteNonQuery();
            con.Close();

            MessageBox.Show("Customer Added!");
            loadCustomer();
            txtcustLname.Clear();
            txtcustFname.Clear();
            txtcustMI.Clear();

        }

        private void button2_Click(object sender, EventArgs e)
        {
            con.Open();
            cmd = new SqlCommand("UPDATE Customer SET custLname = '" + txtcustLname.Text + "', custFname = '" + txtcustFname.Text + "', custMI = '" +txtcustMI.Text+ "' WHERE custID = '" + txtcustID.Text
                    + "';", con); // same shit above, but its update now
            cmd.ExecuteNonQuery();
            con.Close();
            MessageBox.Show("Update Succesful");
            loadCustomer(); //received the new input and just refresh again for it to show.
            txtcustLname.Clear();
            txtcustFname.Clear();
            txtcustMI.Clear();
            txtcustID.Clear(); //clear again
        }

        private void button3_Click(object sender, EventArgs e)
        {
            con.Open();
            cmd = new SqlCommand(@"INSERT INTO Products
                                    (prodName, prodPrice)
                                   VALUES('" + txtprodName.Text + "','" + txtprodPrice.Text + "')", con);

            cmd.ExecuteNonQuery();
            con.Close();

            MessageBox.Show("Products Added!");
            loadProducts();
            txtprodName.Clear();
            txtprodPrice.Clear();
            txtprodID.Clear();

        }

        private void button4_Click(object sender, EventArgs e)
        {
            con.Open();
            cmd = new SqlCommand("UPDATE Products SET prodName = '" + txtprodName.Text + "', prodPrice = '" + txtprodPrice.Text + "' WHERE prodID = '" + txtprodID.Text
                    + "';", con); // same shit above, but its update now
            cmd.ExecuteNonQuery();
            con.Close();
            MessageBox.Show("Update Succesful");
            loadProducts(); //received the new input and just refresh again for it to show.
            txtprodName.Clear();
            txtprodPrice.Clear();
            txtprodID.Clear(); //clear again

        }


        // SEARCH


        private void SearchCustomer()
        {
            dataCustomer.Rows.Clear();
            con.Open();
            cmd = new SqlCommand(
                @"SELECT * FROM Customer WHERE custLname LIKE '%" + txtCSearch.Text
                + "%' OR custFname LIKE '%" + txtCSearch.Text
                + "%' OR custMI Like '%" + txtCSearch.Text
                + "%' OR custID LIKE '%" + txtCSearch.Text
                + "%';", con);
            rdr = cmd.ExecuteReader();
            while (rdr.Read())
            {
                dataCustomer.Rows.Add(rdr[0].ToString(), rdr[1].ToString(), rdr[2].ToString(), rdr[3].ToString());
            }

            con.Close();
        }


        private void SearchProducts()
        {
            dataProducts.Rows.Clear();
            con.Open();
            cmd = new SqlCommand(
                @"SELECT * FROM Products WHERE prodName LIKE '%" + txtPSearch.Text
                + "%' OR prodPrice LIKE '%" + txtPSearch.Text
                + "%' OR prodID LIKE '%" + txtPSearch.Text
                + "%';", con);
            rdr = cmd.ExecuteReader();
            while (rdr.Read())
            {
                dataProducts.Rows.Add(rdr[0].ToString(), rdr[1].ToString(), rdr[2].ToString());
            }

            con.Close();
        }

        private void SearchPurchase()
        {
            dataPurchase.Rows.Clear();
            con.Open();
            cmd = new SqlCommand(
                @"SELECT * FROM Purchase WHERE custID LIKE '%" + txtTSearch.Text //TSearch instead of PSearch coz PSearch is taken by Product
                + "%' OR prodID LIKE '%" + txtTSearch.Text
                + "%' OR DatePurchased Like '%" + txtTSearch.Text
                + "%';", con);
            rdr = cmd.ExecuteReader();
            while (rdr.Read())
            {
                dataPurchase.Rows.Add(rdr[0].ToString(), rdr[1].ToString(), rdr[2].ToString(), rdr[3].ToString());
            }

            con.Close();
        }

        private void btnCSearch_Click(object sender, EventArgs e)
        {
            SearchCustomer();
        }

        private void btnPSearch_Click(object sender, EventArgs e)
        {
            SearchProducts();
        }

        private void btnTSearch_Click(object sender, EventArgs e)
        {
            SearchPurchase();
        }

        private void dataCustomer_CellContentClick(object sender, DataGridViewCellEventArgs e)
        {

        }
    }
}