Ejemplo n.º 1
0
        public void Insert(REF_Course oREF_Course)
        {
            DBConnecton db;

            db = new DBConnecton();
            string     sqlQuery;
            SqlCommand oSqlCommand;

            try

            {
                sqlQuery = "INSERT INTO `maincourse` (`idmaincourse`, `course_id`, `course_name`, `course_duration`, `course_price`, `description`) VALUES" +
                           " (NULL, '" + oREF_Course.COURSECODE + "', '" + oREF_Course.COURSENAME + "', '" + oREF_Course.COURSEDURATION + "', '" + oREF_Course.COURSEPRICE + "', '" + oREF_Course.DESCRIPTION + "');";

                //open connection
                if (db.OpenConnection() == true)
                {
                    //create command and assign the query and connection from the constructor
                    MySqlCommand cmd = new MySqlCommand(sqlQuery, db.getConnetion());

                    //Execute command
                    cmd.ExecuteNonQuery();

                    //close connection
                    db.CloseConnection();
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        public void Insert(REF_Item oREF_Item)
        {
            DBConnecton db;

            db = new DBConnecton();
            string     sqlQuery;
            SqlCommand oSqlCommand;

            try

            {
                sqlQuery = "INSERT INTO `tblitem` (`ID`, `Name`, `Price`, `Status`, `ExpiredDate`,`DelivaryPrice`) VALUES (NULL, '" + oREF_Item.Name + "', '" + oREF_Item.Price + "', '0', '" + oREF_Item.ExpiredDate + "','" + oREF_Item.Delivary + "');";

                //open connection
                if (db.OpenConnection() == true)
                {
                    //create command and assign the query and connection from the constructor
                    MySqlCommand cmd = new MySqlCommand(sqlQuery, db.getConnetion());

                    //Execute command
                    cmd.ExecuteNonQuery();

                    //close connection
                    db.CloseConnection();
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
Ejemplo n.º 3
0
        public void Insert(REF_Service oREF_Service)
        {
            DBConnecton db;

            db = new DBConnecton();
            string     sqlQuery;
            SqlCommand oSqlCommand;

            try

            {
                sqlQuery = "INSERT INTO `mainservice` (`idmainservice`, `service_id`, `service_name`, `service_duration`, `price_per_hour`, `description`)" +
                           " VALUES(NULL, '" + oREF_Service.ServiceiD + "', '" + oREF_Service.Service_Name + "', '" + oREF_Service.Service_Duration + "', '" + oREF_Service.Price_Per_Hour + "', '" + oREF_Service.Description + "');";
                //open connection
                if (db.OpenConnection() == true)
                {
                    //create command and assign the query and connection from the constructor
                    MySqlCommand cmd = new MySqlCommand(sqlQuery, db.getConnetion());

                    //Execute command
                    cmd.ExecuteNonQuery();

                    //close connection
                    db.CloseConnection();
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
Ejemplo n.º 4
0
        public void Update(REF_Course oREF_Course)
        {
            DBConnecton db;

            db = new DBConnecton();
            string     sqlQuery;
            SqlCommand oSqlCommand;

            try

            {
                sqlQuery = "UPDATE `maincourse` SET `course_id` = '" + oREF_Course.COURSECODE + "', `course_name` = '" + oREF_Course.COURSENAME + "'," +
                           " `course_duration` = '" + oREF_Course.COURSEDURATION + "', `course_price` = '" + oREF_Course.COURSEPRICE + "', `description` = '" + oREF_Course.DESCRIPTION + "' WHERE `maincourse`.`idmaincourse` = " + oREF_Course.ID + ";";

                //open connection
                if (db.OpenConnection() == true)
                {
                    //create command and assign the query and connection from the constructor
                    MySqlCommand cmd = new MySqlCommand(sqlQuery, db.getConnetion());

                    //Execute command
                    cmd.ExecuteNonQuery();

                    //close connection
                    db.CloseConnection();
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        public void Insert(REF_Offers oREF_Offers)
        {
            DBConnecton db;

            db = new DBConnecton();
            string     sqlQuery;
            SqlCommand oSqlCommand;

            try

            {
                sqlQuery = "INSERT INTO `mainoffer` (`idmainoffer`, `offer_type`, `offer_id`, `offer_name`, `offer_start_date`, `offer_end_date`, `offer_price_min`, `offer_price_max`, `offer_description`)" +
                           " VALUES (NULL, '" + oREF_Offers.OFFER_TYPE + "', '" + oREF_Offers.Offer_ID + "', '" + oREF_Offers.OFFER_NAME + "', '" + oREF_Offers.START_DATE + "', '" + oREF_Offers.END_DATE + "', '" + oREF_Offers.MIN_PRICE + "', '" + oREF_Offers.MAX_PRICE + "', '" + oREF_Offers.DESCCRIPTION + "');";

                //open connection
                if (db.OpenConnection() == true)
                {
                    //create command and assign the query and connection from the constructor
                    MySqlCommand cmd = new MySqlCommand(sqlQuery, db.getConnetion());

                    //Execute command
                    cmd.ExecuteNonQuery();

                    //close connection
                    db.CloseConnection();
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
Ejemplo n.º 6
0
        public void Update(REF_Service oREF_Service)
        {
            DBConnecton db;

            db = new DBConnecton();
            string     sqlQuery;
            SqlCommand oSqlCommand;

            try

            {
                sqlQuery = "UPDATE `mainservice` SET `service_id` = '" + oREF_Service.ServiceiD + "', `service_name` = '" + oREF_Service.Service_Name + "', " +
                           "`service_duration` = '" + oREF_Service.Service_Duration + "', `price_per_hour` = '" + oREF_Service.Price_Per_Hour + "', `description` = '" + oREF_Service.Description + "' WHERE `mainservice`.`idmainservice` = " + oREF_Service.ID + "";

                //open connection
                if (db.OpenConnection() == true)
                {
                    //create command and assign the query and connection from the constructor
                    MySqlCommand cmd = new MySqlCommand(sqlQuery, db.getConnetion());

                    //Execute command
                    cmd.ExecuteNonQuery();

                    //close connection
                    db.CloseConnection();
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        public void Update(REF_Offers oREF_Offers)
        {
            DBConnecton db;

            db = new DBConnecton();
            string     sqlQuery;
            SqlCommand oSqlCommand;

            try

            {
                sqlQuery = "UPDATE `mainoffer` SET `offer_type` = '" + oREF_Offers.OFFER_TYPE + "', `offer_id` = '" + oREF_Offers.Offer_ID + "', `offer_name` = '" + oREF_Offers.OFFER_NAME + "', `offer_start_date` = '" + oREF_Offers.START_DATE + "', " +
                           "`offer_end_date` = '" + oREF_Offers.END_DATE + "', `offer_price_min` = '" + oREF_Offers.MIN_PRICE + "', `offer_price_max` = '" + oREF_Offers.MAX_PRICE + "', `offer_description` = '" + oREF_Offers.DESCCRIPTION + "' WHERE `mainoffer`.`idmainoffer` = " + oREF_Offers.ID + "";

                //open connection
                if (db.OpenConnection() == true)
                {
                    //create command and assign the query and connection from the constructor
                    MySqlCommand cmd = new MySqlCommand(sqlQuery, db.getConnetion());

                    //Execute command
                    cmd.ExecuteNonQuery();

                    //close connection
                    db.CloseConnection();
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        public void UpdateRepair(REF_Damages oREF_Damages)
        {
            DBConnecton db;

            db = new DBConnecton();
            string     sqlQuery;
            SqlCommand oSqlCommand;

            try

            {
                sqlQuery = "UPDATE `tbldamage` SET `repaired` = 'Repaired' WHERE `tbldamage`.`id` = " + oREF_Damages.id + "; ";

                //open connection
                if (db.OpenConnection() == true)
                {
                    //create command and assign the query and connection from the constructor
                    MySqlCommand cmd = new MySqlCommand(sqlQuery, db.getConnetion());

                    //Execute command
                    cmd.ExecuteNonQuery();

                    //close connection
                    db.CloseConnection();
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        public void Insert(REF_Damages oREF_Damages)
        {
            DBConnecton db;

            db = new DBConnecton();
            string     sqlQuery;
            SqlCommand oSqlCommand;

            try

            {
                sqlQuery = "INSERT INTO `tbldamage` (`id`, `itemname`,`discription`) VALUES (NULL, '" + oREF_Damages.itemname + "', '" + oREF_Damages.discription + "');";

                //open connection
                if (db.OpenConnection() == true)
                {
                    //create command and assign the query and connection from the constructor
                    MySqlCommand cmd = new MySqlCommand(sqlQuery, db.getConnetion());

                    //Execute command
                    cmd.ExecuteNonQuery();

                    //close connection
                    db.CloseConnection();
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
Ejemplo n.º 10
0
        public void Insert(REF_EnrollCourse oREF_EnrollCourse)
        {
            DBConnecton db;

            db = new DBConnecton();
            string     sqlQuery;
            SqlCommand oSqlCommand;

            try

            {
                sqlQuery = "INSERT INTO `tblenrollcourse` (`ID`, `CusID`, `CourseID`, `Status`, `OfferID`, `offer_prices`, `Course_price`, `Total`)" +
                           "VALUES (NULL, '" + oREF_EnrollCourse.CusID + "', '" + oREF_EnrollCourse.CourseID + "', '0', '" + oREF_EnrollCourse.OfferID + "', '" + oREF_EnrollCourse.offer_prices + "', '" + oREF_EnrollCourse.Course_price + "', '" + oREF_EnrollCourse.Total + "');";

                //open connection
                if (db.OpenConnection() == true)
                {
                    //create command and assign the query and connection from the constructor
                    MySqlCommand cmd = new MySqlCommand(sqlQuery, db.getConnetion());

                    //Execute command
                    cmd.ExecuteNonQuery();

                    //close connection
                    db.CloseConnection();
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
Ejemplo n.º 11
0
        public void Insert(REF_Employee oREF_Employee)
        {
            DBConnecton db;

            db = new DBConnecton();
            string     sqlQuery;
            SqlCommand oSqlCommand;

            try

            {
                sqlQuery = "INSERT INTO `tblemployee` (`id`, `name`,`address`, `jobtype`, `basicsal`) VALUES (NULL, '" + oREF_Employee.name + "', '" + oREF_Employee.address + "','" + oREF_Employee.jobtype + "','" + oREF_Employee.basicsal + "');";

                //open connection
                if (db.OpenConnection() == true)
                {
                    //create command and assign the query and connection from the constructor
                    MySqlCommand cmd = new MySqlCommand(sqlQuery, db.getConnetion());

                    //Execute command
                    cmd.ExecuteNonQuery();

                    //close connection
                    db.CloseConnection();
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
Ejemplo n.º 12
0
        public DataTable SelectItemByID(REF_Item oREF_Item)
        {
            DBConnecton db;

            db = new DBConnecton();
            DataTable dataTable = new DataTable();
            string    query     = "SELECT * FROM `tblitem` WHERE `ID`='" + oREF_Item.ID + "'";

            //Open connection
            if (db.OpenConnection() == true)
            {
                //Create Command
                MySqlCommand cmd = new MySqlCommand(query, db.getConnetion());
                //Create a data reader and Execute the command
                MySqlDataReader dataReader = cmd.ExecuteReader();

                DataSet ds = new DataSet();

                ds.Tables.Add(dataTable);
                ds.EnforceConstraints = false;
                dataTable.Load(dataReader);
                dataReader.Close();

                //close Connection
                db.CloseConnection();

                //return list to be displayed
                return(dataTable);
            }
            else
            {
                return(dataTable);
            }
        }
Ejemplo n.º 13
0
        public void Insert(REF_BookServices oREF_BookServices)
        {
            DBConnecton db;

            db = new DBConnecton();
            string     sqlQuery;
            SqlCommand oSqlCommand;

            try

            {
                sqlQuery = "INSERT INTO `tblbookservice` (`ID`, `CusID`, `ServiceID`, `Status`, `OfferID`, `ServicePrice`, `OfferPrice`, `TotalPrice`)" +
                           "VALUES (NULL, '" + oREF_BookServices.CusID + "', '" + oREF_BookServices.ServiceID + "', '0', '" + oREF_BookServices.OfferID + "', '" + oREF_BookServices.ServicePrice + "', '" + oREF_BookServices.OfferPrice + "', '" + oREF_BookServices.TotalPrice + "');";

                //open connection
                if (db.OpenConnection() == true)
                {
                    //create command and assign the query and connection from the constructor
                    MySqlCommand cmd = new MySqlCommand(sqlQuery, db.getConnetion());

                    //Execute command
                    cmd.ExecuteNonQuery();

                    //close connection
                    db.CloseConnection();
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
Ejemplo n.º 14
0
        public void Delete(REF_Employee oREF_Employee)
        {
            DBConnecton db;

            db = new DBConnecton();
            string     sqlQuery;
            SqlCommand oSqlCommand;

            try

            {
                sqlQuery = "DELETE FROM `tblemployee` WHERE `tblemployee`.`id` = " + oREF_Employee.id + "";

                //open connection
                if (db.OpenConnection() == true)
                {
                    //create command and assign the query and connection from the constructor
                    MySqlCommand cmd = new MySqlCommand(sqlQuery, db.getConnetion());

                    //Execute command
                    cmd.ExecuteNonQuery();

                    //close connection
                    db.CloseConnection();
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
Ejemplo n.º 15
0
        public void Insert(REF_Stock oREF_Stock)
        {
            DBConnecton db;

            db = new DBConnecton();
            string     sqlQuery;
            SqlCommand oSqlCommand;

            try

            {
                sqlQuery = "INSERT INTO `tblstock` (`ID`, `ItemID`, `Qty`, `Status`) VALUES (NULL, '" + oREF_Stock.ItemID + "', '" + oREF_Stock.Qty + "', '0');";

                //open connection
                if (db.OpenConnection() == true)
                {
                    //create command and assign the query and connection from the constructor
                    MySqlCommand cmd = new MySqlCommand(sqlQuery, db.getConnetion());

                    //Execute command
                    cmd.ExecuteNonQuery();

                    //close connection
                    db.CloseConnection();
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
Ejemplo n.º 16
0
        public void Insert(REF_Employee oREF_Employee)
        {
            DBConnecton db;

            db = new DBConnecton();
            string     sqlQuery;
            SqlCommand oSqlCommand;

            try

            {
                sqlQuery = "INSERT INTO `salary` (`id`, `empid`,`empname`,`othours`, `bonous`, `salary`) VALUES (NULL, '" + oREF_Employee.id + "','" + oREF_Employee.name + "', '" + oREF_Employee.othours + "','" + oREF_Employee.bonous + "','" + oREF_Employee.salary + "');";

                //open connection
                if (db.OpenConnection() == true)
                {
                    //create command and assign the query and connection from the constructor
                    MySqlCommand cmd = new MySqlCommand(sqlQuery, db.getConnetion());

                    //Execute command
                    cmd.ExecuteNonQuery();

                    //close connection
                    db.CloseConnection();
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        public DataTable SelectByUserNameAndPasswordHash(REF_User oREF_User)
        {
            DBConnecton db;

            db = new DBConnecton();
            DataTable dataTable = new DataTable();
            string    sqlQuery  = "SELECT * FROM `tbluser` WHERE `UserName` LIKE '" + oREF_User.USER_NAME + "' AND `password` LIKE '" + oREF_User.PASSWORD + "'";

            //Open connection
            if (db.OpenConnection() == true)
            {
                //Create Command
                MySqlCommand cmd = new MySqlCommand(sqlQuery, db.getConnetion());
                //Create a data reader and Execute the command
                MySqlDataReader dataReader = cmd.ExecuteReader();

                DataSet ds = new DataSet();

                ds.Tables.Add(dataTable);
                ds.EnforceConstraints = false;
                dataTable.Load(dataReader);
                dataReader.Close();

                //close Connection
                db.CloseConnection();

                //return list to be displayed
                return(dataTable);
            }
            else
            {
                return(dataTable);
            }
        }
Ejemplo n.º 18
0
        public DataTable SelectReturnReport()
        {
            DBConnecton db;

            db = new DBConnecton();
            DataTable dataTable = new DataTable();
            string    query     = "SELECT R.`ID`,R.`CusID`,U.name,R.`ItemID`,I.Name,R.`Qty`,R.`Deposit`,R.`Addition`,R.`Refund` FROM `tblreturn` R,users U,tblitem I WHERE R.`CusID`=U.user_id AND R.`ItemID`=I.ID";

            //Open connection
            if (db.OpenConnection() == true)
            {
                //Create Command
                MySqlCommand cmd = new MySqlCommand(query, db.getConnetion());
                //Create a data reader and Execute the command
                MySqlDataReader dataReader = cmd.ExecuteReader();

                DataSet ds = new DataSet();

                ds.Tables.Add(dataTable);
                ds.EnforceConstraints = false;
                dataTable.Load(dataReader);
                dataReader.Close();

                //close Connection
                db.CloseConnection();

                //return list to be displayed
                return(dataTable);
            }
            else
            {
                return(dataTable);
            }
        }
Ejemplo n.º 19
0
        public DataTable Selectstock()
        {
            DBConnecton db;

            db = new DBConnecton();
            DataTable dataTable = new DataTable();
            string    query     = "SELECT I.ID,I.Name FROM `tblstock` S,tblitem I WHERE S.ItemID = I.ID AND I.Status = 0";

            //Open connection
            if (db.OpenConnection() == true)
            {
                //Create Command
                MySqlCommand cmd = new MySqlCommand(query, db.getConnetion());
                //Create a data reader and Execute the command
                MySqlDataReader dataReader = cmd.ExecuteReader();

                DataSet ds = new DataSet();

                ds.Tables.Add(dataTable);
                ds.EnforceConstraints = false;
                dataTable.Load(dataReader);
                dataReader.Close();

                //close Connection
                db.CloseConnection();

                //return list to be displayed
                return(dataTable);
            }
            else
            {
                return(dataTable);
            }
        }
Ejemplo n.º 20
0
        public DataTable SelectRentDetailsByID(REF_Rent oREF_Rent)
        {
            DBConnecton db;

            db = new DBConnecton();
            DataTable dataTable = new DataTable();
            string    query     = "SELECT * FROM `tblrent` R, users C,tblitem I WHERE R.`CusID`=C.user_id AND R.`ItemID`= I.ID AND R.`ID` = " + oREF_Rent.ID + "";

            //Open connection
            if (db.OpenConnection() == true)
            {
                //Create Command
                MySqlCommand cmd = new MySqlCommand(query, db.getConnetion());
                //Create a data reader and Execute the command
                MySqlDataReader dataReader = cmd.ExecuteReader();

                DataSet ds = new DataSet();

                ds.Tables.Add(dataTable);
                ds.EnforceConstraints = false;
                dataTable.Load(dataReader);
                dataReader.Close();

                //close Connection
                db.CloseConnection();

                //return list to be displayed
                return(dataTable);
            }
            else
            {
                return(dataTable);
            }
        }
Ejemplo n.º 21
0
        public int Insert(REF_Rent oREF_Rent)
        {
            int         RID = 0;
            DBConnecton db;

            db = new DBConnecton();
            string     sqlQuery;
            SqlCommand oSqlCommand;

            try

            {
                sqlQuery = "INSERT INTO `tblrent` (`ID`, `CusID`, `ItemID`, `Qty`, `Deposit`, `Daliy Rate`, `Total Cost`, `NumberOfDay`, `Status`) "
                           + "VALUES (NULL, '" + oREF_Rent.CusID + "', '" + oREF_Rent.ItemID + "', '" + oREF_Rent.Qty + "', '" + oREF_Rent.Deposit + "', '" + oREF_Rent.DaliyRate + "', '" + oREF_Rent.TotalCost + "', '" + oREF_Rent.NumberOfDay + "', '0');";

                //open connection
                if (db.OpenConnection() == true)
                {
                    //create command and assign the query and connection from the constructor
                    MySqlCommand cmd = new MySqlCommand(sqlQuery, db.getConnetion());

                    //Execute command
                    RID = cmd.ExecuteNonQuery();
                    long id = cmd.LastInsertedId;
                    RID = Convert.ToInt32(id);
                    //close connection
                    db.CloseConnection();
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return(RID);
        }
Ejemplo n.º 22
0
        public void Delete(REF_Item oREF_Item)
        {
            DBConnecton db;

            db = new DBConnecton();
            string     sqlQuery;
            SqlCommand oSqlCommand;

            try

            {
                sqlQuery = "UPDATE `tblitem` SET `Status` = '1' WHERE `tblitem`.`ID` = " + oREF_Item.ID + "";

                //open connection
                if (db.OpenConnection() == true)
                {
                    //create command and assign the query and connection from the constructor
                    MySqlCommand cmd = new MySqlCommand(sqlQuery, db.getConnetion());

                    //Execute command
                    cmd.ExecuteNonQuery();

                    //close connection
                    db.CloseConnection();
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
Ejemplo n.º 23
0
        public void Update(REF_Employee oREF_Employee)
        {
            DBConnecton db;

            db = new DBConnecton();
            string     sqlQuery;
            SqlCommand oSqlCommand;

            try

            {
                sqlQuery = "UPDATE `salary` SET `empid` = '" + oREF_Employee.id + "', `empname` = '" + oREF_Employee.name + "', " +
                           "`othours` = '" + oREF_Employee.othours + "', `bonous` = '" + oREF_Employee.bonous + "', `salary` = '" + oREF_Employee.salary + "' WHERE `salary`.`id` = " + oREF_Employee.salaryID + "";

                //open connection
                if (db.OpenConnection() == true)
                {
                    //create command and assign the query and connection from the constructor
                    MySqlCommand cmd = new MySqlCommand(sqlQuery, db.getConnetion());

                    //Execute command
                    cmd.ExecuteNonQuery();

                    //close connection
                    db.CloseConnection();
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
Ejemplo n.º 24
0
        public DataTable Selectbasicsalary(REF_Employee oREF_Employee)
        {
            DBConnecton db;

            db = new DBConnecton();
            DataTable dataTable = new DataTable();
            string    query     = "SELECT * FROM tblemployee WHERE id= " + oREF_Employee.id + "";

            //Open connection
            if (db.OpenConnection() == true)
            {
                //Create Command
                MySqlCommand cmd = new MySqlCommand(query, db.getConnetion());
                //Create a data reader and Execute the command
                MySqlDataReader dataReader = cmd.ExecuteReader();

                DataSet ds = new DataSet();

                ds.Tables.Add(dataTable);
                ds.EnforceConstraints = false;
                dataTable.Load(dataReader);
                dataReader.Close();

                //close Connection
                db.CloseConnection();

                //return list to be displayed
                return(dataTable);
            }
            else
            {
                return(dataTable);
            }
        }
Ejemplo n.º 25
0
        public void Update(REF_Stock oREF_Stock)
        {
            DBConnecton db;

            db = new DBConnecton();
            string     sqlQuery;
            SqlCommand oSqlCommand;

            try

            {
                sqlQuery = "UPDATE `tblstock` SET `Qty` = '" + oREF_Stock.Qty + "' WHERE `tblstock`.`ID` = " + oREF_Stock.ID + "";

                //open connection
                if (db.OpenConnection() == true)
                {
                    //create command and assign the query and connection from the constructor
                    MySqlCommand cmd = new MySqlCommand(sqlQuery, db.getConnetion());

                    //Execute command
                    cmd.ExecuteNonQuery();

                    //close connection
                    db.CloseConnection();
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
Ejemplo n.º 26
0
        public DataTable SelectCourseFinance2()
        {
            DBConnecton db;

            db = new DBConnecton();
            DataTable dataTable = new DataTable();
            string    query     = "SELECT C.service_name as Name , Sum(`TotalPrice`) as Total FROM `tblbookservice` E,mainservice C WHERE E.`ServiceID`=C.idmainservice Group BY E.`ServiceID`";

            //Open connection
            if (db.OpenConnection() == true)
            {
                //Create Command
                MySqlCommand cmd = new MySqlCommand(query, db.getConnetion());
                //Create a data reader and Execute the command
                MySqlDataReader dataReader = cmd.ExecuteReader();

                DataSet ds = new DataSet();

                ds.Tables.Add(dataTable);
                ds.EnforceConstraints = false;
                dataTable.Load(dataReader);
                dataReader.Close();

                //close Connection
                db.CloseConnection();

                //return list to be displayed
                return(dataTable);
            }
            else
            {
                return(dataTable);
            }
        }
Ejemplo n.º 27
0
        public void Insert(REF_Return oREF_Return)
        {
            DBConnecton db;

            db = new DBConnecton();
            string     sqlQuery;
            SqlCommand oSqlCommand;

            try

            {
                sqlQuery = "INSERT INTO `tblreturn` (`ID`, `CusID`, `ItemID`, `Qty`, `Deposit`, `Addition`, `Refund`, `Status`)"
                           + "VALUES (NULL, '" + oREF_Return.CusID + "', '" + oREF_Return.ItemID + "', '" + oREF_Return.Qty + "', '" + oREF_Return.Deposit + "', '" + oREF_Return.Addition + "', '" + oREF_Return.Refund + "', '0');";

                //open connection
                if (db.OpenConnection() == true)
                {
                    //create command and assign the query and connection from the constructor
                    MySqlCommand cmd = new MySqlCommand(sqlQuery, db.getConnetion());

                    //Execute command
                    cmd.ExecuteNonQuery();

                    //close connection
                    db.CloseConnection();
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
Ejemplo n.º 28
0
        public void Update(REF_Employee oREF_employee)
        {
            DBConnecton db;

            db = new DBConnecton();
            string     sqlQuery;
            SqlCommand oSqlCommand;

            try

            {
                //sqlQuery = "UPDATE `tblemployee` SET `name` = '" + oREF_employee.name + "', `address` = '" + oREF_employee.address + "', `jobtype` = '" + oREF_employee.jobtype + "', `basicsal` = '" + oREF_employee.basicsal + "', " + " WHERE `id` = " + oREF_employee.id + "";
                sqlQuery = "UPDATE `tblemployee` SET `name` = '" + oREF_employee.name + "', `address` = '" + oREF_employee.address + "', `jobtype` = '" + oREF_employee.jobtype + "', `basicsal` = '" + oREF_employee.basicsal + "' WHERE `tblemployee`.`id` = " + oREF_employee.id + "";
                //open connection
                if (db.OpenConnection() == true)
                {
                    //create command and assign the query and connection from the constructor
                    MySqlCommand cmd = new MySqlCommand(sqlQuery, db.getConnetion());

                    //Execute command
                    cmd.ExecuteNonQuery();

                    //close connection
                    db.CloseConnection();
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
Ejemplo n.º 29
0
        public DataTable SelectEmployee()
        {
            DBConnecton db;

            db = new DBConnecton();
            DataTable dataTable = new DataTable();
            string    query     = "SELECT * FROM tblemployee";

            //Create a list to store the result
            List <string>[] list = new List <string> [9];
            list[0] = new List <string>();
            list[1] = new List <string>();
            list[2] = new List <string>();
            list[3] = new List <string>();
            list[4] = new List <string>();
            list[5] = new List <string>();
            list[6] = new List <string>();
            list[7] = new List <string>();

            //Open connection
            if (db.OpenConnection() == true)
            {
                //Create Command
                MySqlCommand cmd = new MySqlCommand(query, db.getConnetion());
                //Create a data reader and Execute the command
                MySqlDataReader dataReader = cmd.ExecuteReader();

                DataSet ds = new DataSet();

                ds.Tables.Add(dataTable);
                ds.EnforceConstraints = false;
                dataTable.Load(dataReader);
                dataReader.Close();

                //close Connection
                db.CloseConnection();

                //return list to be displayed
                return(dataTable);
            }
            else
            {
                return(dataTable);
            }
        }
Ejemplo n.º 30
0
        public DataTable SelectBookCourse()
        {
            DBConnecton db;

            db = new DBConnecton();
            DataTable dataTable = new DataTable();
            string    query     = "SELECT B.ID, C.name, S.course_name,B.`Course_price`, O.offer_name ,B.`offer_prices`,`Total` FROM `tblenrollcourse` B , users C, maincourse S,mainoffer O WHERE B.CusID = C.user_id AND B.CourseID = S.idmaincourse AND B.OfferID = O.idmainoffer";



            //Open connection
            if (db.OpenConnection() == true)
            {
                //Create Command
                MySqlCommand cmd = new MySqlCommand(query, db.getConnetion());
                //Create a data reader and Execute the command
                MySqlDataReader dataReader = cmd.ExecuteReader();

                DataSet ds = new DataSet();

                ds.Tables.Add(dataTable);
                ds.EnforceConstraints = false;
                dataTable.Load(dataReader);
                dataReader.Close();

                //close Connection
                db.CloseConnection();

                //return list to be displayed
                return(dataTable);
            }
            else
            {
                return(dataTable);
            }
        }