csharp/bilalmehrban/Point-of-Sale-Desktop-Csharp-/POS/DAL/product_detailsdal.cs

product_detailsdal.cs
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using POS.BLL;

namespace POS.DAL
{
    clast product_detailsdal
    {
        #region Insert data into database
        public bool insert(product_detailsbll u)
        {
            //Create a boolean variable and set its value to false and return it
            bool issucess = false;
            //MEthod to connect Database
            connclast c = new connclast();
            SqlConnection conn = new SqlConnection(c.connection);
            try
            {
                //SQL Query to insert Data in DAtabase
                string query = "Insert into tbl_productdetails(product_id,product_name,price,quansaty,discount,total,purchase_price,inv_no,type,added_by,added_date,code)Values(@product_id,@product_name,@price,@quansaty,@discount,@total,@purchase_price,@inv_no,@type,@added_by,@added_date,@code)";
                //For Executing Command
                SqlCommand cmd = new SqlCommand(query, conn);
                //Pasting Values to the Variables
                cmd.Parameters.AddWithValue("@product_id", u.product_id);
                cmd.Parameters.AddWithValue("@product_name", u.product_name);
                cmd.Parameters.AddWithValue("@price", u.price);
                cmd.Parameters.AddWithValue("@quansaty", u.quansaty);
                cmd.Parameters.AddWithValue("@discount", u.discount);
                cmd.Parameters.AddWithValue("@total", u.total);
                cmd.Parameters.AddWithValue("@purchase_price", u.purchase_price);
                cmd.Parameters.AddWithValue("@inv_no", u.inv_no);
                cmd.Parameters.AddWithValue("@type", u.type);
                cmd.Parameters.AddWithValue("@added_by", u.added_by);
                cmd.Parameters.AddWithValue("@added_date", u.added_date);
                cmd.Parameters.AddWithValue("@code", u.code);
                //Database Connection Open
                conn.Open();
                //To execute non query
                int row = cmd.ExecuteNonQuery();
                //If the query is executed Successfully then the value to rows will be greater than 0 else it will be less than 0
                if (row > 0)
                {
                    //Query Sucessfull
                    issucess = true;
                }
                else
                {
                    //Query Failed
                    issucess = false;
                }
            }
            catch (Exception ex)
            {
                //Throw Message if any error occurs
                MessageBox.Show(ex.Message);
            }
            finally
            {
                //Closing Connection
                conn.Close();
            }

            return issucess;
        }
        #endregion

        #region Update data into database
        public bool update(product_detailsbll u)
        {
            //Create a boolean variable and set its value to false and return it
            bool issucess = false;
            //MEthod to connect Database
            connclast c = new connclast();
            SqlConnection conn = new SqlConnection(c.connection);
            try
            {
                //SQL Query to insert Data in DAtabase
                string query = "UPDATE tbl_productdetails set [email protected]_id,[email protected]_name,[email protected],[email protected],[email protected],[email protected],[email protected]_price,[email protected]_no,[email protected],[email protected]_by,[email protected]_date,[email protected] WHERE [email protected]";
                //For Executing Command
                SqlCommand cmd = new SqlCommand(query, conn);
                //Pasting Values to the Variables
                cmd.Parameters.AddWithValue("@id", u.id);
                cmd.Parameters.AddWithValue("@product_id", u.product_id);
                cmd.Parameters.AddWithValue("@product_name", u.product_name);
                cmd.Parameters.AddWithValue("@price", u.price);
                cmd.Parameters.AddWithValue("@quansaty", u.quansaty);
                cmd.Parameters.AddWithValue("@discount", u.discount);
                cmd.Parameters.AddWithValue("@total", u.total);
                cmd.Parameters.AddWithValue("@purchase_price", u.purchase_price);
                cmd.Parameters.AddWithValue("@inv_no", u.inv_no);
                cmd.Parameters.AddWithValue("@type", u.type);
                cmd.Parameters.AddWithValue("@added_by", u.added_by);
                cmd.Parameters.AddWithValue("@added_date", u.added_date);
                cmd.Parameters.AddWithValue("@code", u.code);
                //Database Connection Open
                conn.Open();
                //To execute non query
                int rows = cmd.ExecuteNonQuery();
                //If the query is executed Successfully then the value to rows will be greater than 0 else it will be less than 0
                if (rows > 0)
                {
                    //Query Sucessfull
                    issucess = true;
                }
                else
                {
                    //Query Failed
                    issucess = false;
                }
            }
            catch (Exception ex)
            {
                //Throw Message if any error occurs
                MessageBox.Show(ex.Message);
            }
            finally
            {
                //Closing Connection
                conn.Close();
            }

            return issucess;
        }
        #endregion

        #region Show Data between Two dates
        public DataTable BetweenTwoDates(string d1, string d2)
        {
            //MEthod to connect Database
            connclast c = new connclast();
            SqlConnection conn = new SqlConnection(c.connection);
            //TO hold the data from database 
            DataTable dt = new DataTable();
            try
            {
                //SQL Query to insert Data in DAtabase
                string query = "SELECT* FROM tbl_productdetails WHERE added_date BETWEEN '"+d1+"' AND '"+d2+"'";
                //For Executing Command
                SqlCommand cmd = new SqlCommand(query, conn);
                //Getting DAta from dAtabase
                SqlDataAdapter adapter = new SqlDataAdapter(cmd);
                //Database Connection Open
                conn.Open();
                //Fill Data in our DataTable
                adapter.Fill(dt);
            }
            catch (Exception ex)
            {
                //Throw Message if any error occurs
                MessageBox.Show(ex.Message);
            }
            finally
            {
                //Closing Connection
                conn.Close();
            }

            return dt;
        }
        #endregion

        #region Search Data From DataBase
        public DataTable Search(string inv_no)
        {
            //MEthod to connect Database
            connclast c = new connclast();
            SqlConnection conn = new SqlConnection(c.connection);
            //TO hold the data from database 
            DataTable dt = new DataTable();
            try
            {
                //SQL Query to search Data from DAtabase
                string query = "SELECT* FROM  tbl_productdetails WHERE inv_no = " + inv_no ;
                //For Executing Command
                SqlCommand cmd = new SqlCommand(query, conn);
                //Getting DAta from dAtabase
                SqlDataAdapter adapter = new SqlDataAdapter(cmd);
                //Database Connection Open
                conn.Open();
                //Fill Data in our DataTable
                adapter.Fill(dt);
            }
            catch (Exception ex)
            {
                //Throw Message if any error occurs
                MessageBox.Show(ex.Message);
            }
            finally
            {
                //Closing Connection
                conn.Close();
            }
            //Return the value in DataTable
            return dt;
        }
        #endregion

        #region Show Data Monthly And Daily
        public DataTable MonthlyAndDailyData(string d1, string d2)
        {
            //MEthod to connect Database
            connclast c = new connclast();
            SqlConnection conn = new SqlConnection(c.connection);
            //TO hold the data from database 
            DataTable dt = new DataTable();
            //string d1 = DateTime.Now.ToString();
            try
            {
                //SQL Query to insert Data in DAtabase
                string query = "SELECT* FROM tbl_productdetails WHERE added_date BETWEEN '" + d2 + "' AND '" + d1 + "' ";
                //For Executing Command
                SqlCommand cmd = new SqlCommand(query, conn);
                //Getting DAta from dAtabase
                SqlDataAdapter adapter = new SqlDataAdapter(cmd);
                //Database Connection Open
                conn.Open();
                //Fill Data in our DataTable
                adapter.Fill(dt);
            }
            catch (Exception ex)
            {
                //Throw Message if any error occurs
                MessageBox.Show(ex.Message);
            }
            finally
            {
                //Closing Connection
                conn.Close();
            }

            return dt;
        }
        #endregion

        #region METHOD TO GET CURRENT QUansaty from the Database based on Product ID
        public decimal GetProductQty(int id)
        {
            //MEthod to connect Database
            connclast c = new connclast();
            SqlConnection conn = new SqlConnection(c.connection);
            //Create a Decimal Variable and set its default value to 0
            decimal quansaty = 0;

            //Create Data Table to save the data from database temporarily
            DataTable dt = new DataTable();

            try
            {
                //Write WQL Query to Get Quansaty from Database
                string sql = "SELECT quansaty FROM tbl_productdetails WHERE id = " + id;

                //Cerate A SqlCommand
                SqlCommand cmd = new SqlCommand(sql, conn);

                //Create a SQL Data Adapter to Execute the query
                SqlDataAdapter adapter = new SqlDataAdapter(cmd);

                //open DAtabase Connection
                conn.Open();

                //Past the calue from Data Adapter to DataTable
                adapter.Fill(dt);

                //Lets check if the datatable has value or not
                if (dt.Rows.Count > 0)
                {
                    quansaty = decimal.Parse(dt.Rows[0]["quansaty"].ToString());
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            finally
            {
                //Close Database Connection
                conn.Close();
            }

            return quansaty;
        }
        #endregion

        #region METHOD TO UPDATE QUANsatY
        public bool UpdateQuansaty(int id, decimal quansaty)
        {

            //Create a Boolean Variable and Set its value to false
            bool success = false;

            //MEthod to connect Database
            connclast c = new connclast();
            SqlConnection conn = new SqlConnection(c.connection);

            try
            {
                //Write the SQL Query to Update Qty
                string sql = "UPDATE tbl_productdetails SET [email protected] WHERE [email protected]";

                //Create SQL Command to Past the calue into Queyr
                SqlCommand cmd = new SqlCommand(sql, conn);
                //Pasting the VAlue trhough parameters
                cmd.Parameters.AddWithValue("@quansaty", quansaty);
                cmd.Parameters.AddWithValue("@id", id);

                //Open Database Connection
                conn.Open();

                //Create Int Variable and Check whether the query is executed Successfully or not
                int rows = cmd.ExecuteNonQuery();
                //Lets check if the query is executed Successfully or not
                if (rows > 0)
                {
                    //Query Executed Successfully
                    success = true;
                }
                else
                {
                    //Failed to Execute Query
                    success = false;
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            finally
            {
                conn.Close();
            }

            return success;
        }
        #endregion

        #region METHOD TO DECREASE PRODUCT
        public bool DecreaseProduct(int id, decimal quansaty)
        {
            //Create Boolean Variable and SEt its Value to false
            bool success = false;

            //MEthod to connect Database
            connclast c = new connclast();
            SqlConnection conn = new SqlConnection(c.connection);

            try
            {
                //Get the Current product Quansaty
                decimal currentQty = GetProductQty(id);

                //Decrease the Product Quansaty based on product sales
                decimal NewQty = currentQty - quansaty;

                //Update Product in Database
                success = UpdateQuansaty(id, NewQty);
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            finally
            {
                conn.Close();
            }
            return success;
        }
        #endregion

        #region Delete record in Data Base
        public bool delete(product_detailsbll u)
        {
            //Create a boolean variable and set its value to false and return it
            bool issucess = false;
            //MEthod to connect Database
            connclast c = new connclast();
            SqlConnection conn = new SqlConnection(c.connection);
            try
            {
                //SQL Query to delete Data in DAtabase
                string query = "DELETE FROM tbl_productdetails WHERE [email protected]";
                //For Executing Command
                SqlCommand cmd = new SqlCommand(query, conn);
                //Pasting Values to the Variables
                cmd.Parameters.AddWithValue("@id", u.id);
                //Database Connection Open
                conn.Open();
                //To execute non query
                int row = cmd.ExecuteNonQuery();
                //If the query is executed Successfully then the value to rows will be greater than 0 else it will be less than 0
                if (row > 0)
                {
                    //Query Sucessfull
                    issucess = true;
                }
                else
                {
                    //Query Failed
                    issucess = false;
                }
            }

            catch (Exception ex)
            {
                //Throw Message if any error occurs
                MessageBox.Show(ex.Message);
            }
            finally
            {
                //Closing Connection
                conn.Close();
            }

            return issucess;
        }
        #endregion

    }
}