C# DataBaseUtility.ExecuteUpdate方法代码示例

        public void DeleteFoodItem(int id)
        {
            DataBaseUtility db = new DataBaseUtility();

            string query = "delete from dbo.Orders where ItemId=" + SQLUtility.getInteger(id) + "";
            db.ExecuteUpdate(query);

            query = " delete from dbo.FoodItem where Id=" + SQLUtility.getInteger(id) + "";
            db.ExecuteUpdate(query);
        }

        //Method use  for General Manager
        public void AddAssignAreaManager(int gmId, int amId)
        {
            DataBaseUtility db = new DataBaseUtility();

            if (IsAreaManagerExist(amId))
            {
                string deleteQuery = "delete from dbo.AssignAreaManagerInfo where AreaManagerId=" + SQLUtility.getInteger(amId) + "";
                db.ExecuteUpdate(deleteQuery);
            }

            string query = "INSERT INTO [dbo].[AssignAreaManagerInfo] ([GeneralManagerId] ,[AreaManagerId] ,[CreatedDateTime] ,[LastUpdateDateTime]) "
                         + " VALUES (" + SQLUtility.getInteger(gmId) + "," + SQLUtility.getInteger(amId) + ",'" + SQLUtility.FormateDateYYYYMMDDWtithTime(DateTime.Now) + "','" + SQLUtility.FormateDateYYYYMMDDWtithTime(DateTime.Now) + "')";

            db.ExecuteUpdate(query);
        }

 // Add Food Order this order add by only manager
 public void AddFoodOrder(FoodOrderDTO dto)
 {
     DataBaseUtility db = new DataBaseUtility();
     string query = "INSERT INTO [dbo].[FoodOrder]([StoreId],[OrderDateTime],[CreatedDateTime],[LastUpdateDateTime])VALUES ( "
                     + "  " + SQLUtility.getInteger(dto.StoreId) + " , '" + SQLUtility.FormateDateYYYYMMDDWtithTime(dto.FoodOrderDate) + "' , '" + SQLUtility.FormateDateYYYYMMDDWtithTime(dto.CreatedDateTime) + "' ,'" + SQLUtility.FormateDateYYYYMMDDWtithTime(dto.LastUpdateDateTime) + "'   ) ";
     db.ExecuteUpdate(query);
 }

        public void Add(string name, string role, string emailId)
        {
            try
            {
                string password = "";

                if (name.Length <= 4)
                {
                    password = ValidationUtility.CreatePassword(name.Length + 2);
                }
                else
                {
                    password = ValidationUtility.CreatePassword(name.Length);
                }

                int roleId = ValidationUtility.ToInteger(role);

                DataBaseUtility db = new DataBaseUtility();
                string query = " insert into Users ([UserId],[Password],[RoleId],[EmailId],[CreatedDateTime],[LastUpdateDateTime]) "
                                + " values(" + SQLUtility.getString(name) + "," + SQLUtility.getString(password) + ",  " + SQLUtility.getInteger(roleId) + ",  " + SQLUtility.getString(emailId) + " ,  '" + DateTime.Now.ToString("yyyy/MM/dd hh:mm tt") + "' ,'" + DateTime.Now.ToString("yyyy/MM/dd hh:mm tt") + "' )";
                db.ExecuteUpdate(query);

            }
            catch (Exception ex)
            {
                log.Error(" Exception in Add Method  ", ex);
            }
        }

 //Add Employee Clocking Information
 public void AddEmpClockingInfo(int empTrackerId, EmployeeClockingDTO employeeClockingDTO)
 {
     DataBaseUtility db = new DataBaseUtility();
     string query = "INSERT INTO [dbo].[EmployeeClocking]([EmployeeTrackerId],[ClockFunctionTypeId],[ClockingTime],[MinutesWorked],[CreatedDateTime],[LastUpdateDateTime]) "
                 + " VALUES(" + SQLUtility.getInteger(empTrackerId) + "," + SQLUtility.getInteger(employeeClockingDTO.ClockFunctionTypeId) + ",'" + SQLUtility.FormateDateYYYYMMDDWtithTime(employeeClockingDTO.ClockingTime) + "', "
                 + " " + SQLUtility.getInteger(employeeClockingDTO.MinutesWorked) + ",'" + SQLUtility.FormateDateYYYYMMDDWtithTime(DateTime.Now) + "','" + SQLUtility.FormateDateYYYYMMDDWtithTime(DateTime.Now) + "' )";
     db.ExecuteUpdate(query);
 }

        public void AddActual(ManagerBonusDTO managerBonusDTO)
        {
            DataBaseUtility db = new DataBaseUtility();

            string query = "INSERT INTO [dbo].[ManagerBonus] ([StoreId] ,[LastDateOFMonth] ,[FoodCost] ,[LaborCost] ,[FoodLaborCost] ,[SalesIncrease] ,[CustomerIndex] ,[CustomerComplaint] ,[Catering] ,[SubInspection] ,[CreatedDateTime] ,[LastUpdateDateTime]) "
                           + " VALUES (" + SQLUtility.getInteger(managerBonusDTO.StoreId) + ",'" + SQLUtility.FormateDateYYYYMMDD(managerBonusDTO.LastDateOFMonth) + "',0,0,0,0,0,0,0,0,'" + SQLUtility.FormateDateYYYYMMDDWtithTime(DateTime.Now) + "','" + SQLUtility.FormateDateYYYYMMDDWtithTime(DateTime.Now) + "')";
            db.ExecuteUpdate(query);
        }

        public void DeleteComplaintsData(int cId)
        {
            DataBaseUtility db = new DataBaseUtility();

            string query = "delete from dbo.Complaints where Id=" + SQLUtility.getInteger(cId) + "";

            db.ExecuteUpdate(query);
        }

 public void AddEmpInfo(int storeId, EmployeeInfoDTO employeeInfoDTO)
 {
     DataBaseUtility db = new DataBaseUtility();
     string query = "INSERT INTO [dbo].[EmployeeInfo] ([EmpId],[StoreId],[EmpFirstName],[EmpMiddleName],[EmpLastName],[CreatedDateTime],[LastUpdateDateTime]) "
                    + "  VALUES(" + SQLUtility.getInteger(employeeInfoDTO.EmpId) + "," + SQLUtility.getInteger(storeId) + "," + SQLUtility.getString(employeeInfoDTO.EmpFirstName) + "," + SQLUtility.getString(employeeInfoDTO.EmpMiddleName) + ", "
                    + " " + SQLUtility.getString(employeeInfoDTO.EmpLastName) + ",'" + SQLUtility.FormateDateYYYYMMDDWtithTime(DateTime.Now) + "','" + SQLUtility.FormateDateYYYYMMDDWtithTime(DateTime.Now) + "')";
     db.ExecuteUpdate(query);
 }

        //Add Food order
        public void AddFoodOrderItem(string item)
        {
            DataBaseUtility db = new DataBaseUtility();

            string query = "INSERT INTO [dbo].[FoodItem]([FoodName],[CreatedDateTime],[LastUpdateDateTime]) "
                    + " VALUES(" + SQLUtility.getString(item) + ",'" + SQLUtility.FormateDateYYYYMMDDWtithTime(DateTime.Now) + "','" + SQLUtility.FormateDateYYYYMMDDWtithTime(DateTime.Now) + "')";

            db.ExecuteUpdate(query);
        }

        //Add Inactive Store
        public void AddInactiveStore(StoreDTO storeDTO)
        {
            DataBaseUtility db = new DataBaseUtility();

            string query = "INSERT INTO [dbo].[Store] ([StoreNumber] ,[StoreName] ,[ConnectionString] ,[IsStoreActive] ,[CreatedDateTime] ,[LastUpdateDateTime])  VALUES(" + SQLUtility.getInteger(storeDTO.StoreNumber) + " , "
                + "" + SQLUtility.getString(storeDTO.StoreName) + "," + SQLUtility.getString(storeDTO.ConnectionString) + ",'" + storeDTO.IsActive + "','" + SQLUtility.FormateDateYYYYMMDDWtithTime(DateTime.Now) + "','" + SQLUtility.FormateDateYYYYMMDDWtithTime(DateTime.Now) + "')";

            db.ExecuteUpdate(query);
        }

        // Add Order by manager
        // one food order have multiple order
        public void AddOrder(OrderDTO dto)
        {
            DataBaseUtility db = new DataBaseUtility();

            string query = " INSERT INTO [dbo].[Orders]([FoodOrderId],[ItemId],[NumberRemaining],[NumberOver],[CreatedDateTime],[LastUpdateDateTime])VALUES ( "
                          + "  " + SQLUtility.getInteger(dto.FoodOrderId) + " , " + SQLUtility.getInteger(dto.ItemId) + " ,  " + SQLUtility.getInteger(dto.NumberRemaining) + " , " + SQLUtility.getInteger(dto.NumberOver) + "  , "
                          + "  '" + SQLUtility.FormateDateYYYYMMDDWtithTime(dto.CreatedDateTime) + "' , '" + SQLUtility.FormateDateYYYYMMDDWtithTime(dto.LastUpdateDateTime) + "' ) ";

            db.ExecuteUpdate(query);
        }

        public void AddBounus(ManagerBonusDTO managerBonusDTO, bool isZeroBasis)
        {
            DataBaseUtility db = new DataBaseUtility();

            String query = "INSERT INTO [dbo].[ManagerBonus] ([StoreId] ,[FirstDateOfMonth],[LastDateOfMonth],[FoodCost] ,[LaborCost] ,[FoodLaborCost] ,[SalesIncrease] ,[CustomerIndex] ,[CustomerComplaint]  "
                           + " ,[Catering] ,[SubInspection],[IsZeroBasis] ,[CreatedDateTime] ,[LastUpdateDateTime]) "
                       + " VALUES (" + SQLUtility.getInteger(managerBonusDTO.StoreId) + ", '" + SQLUtility.FormateDateYYYYMMDD(managerBonusDTO.FirstDateOFMonth) + "','" + SQLUtility.FormateDateYYYYMMDD(managerBonusDTO.LastDateOFMonth) + "'," + SQLUtility.getDouble(managerBonusDTO.FoodCost) + "," + SQLUtility.getDouble(managerBonusDTO.LaborCost) + "," + SQLUtility.getDouble(managerBonusDTO.FoodLaborCost) + "," + SQLUtility.getDouble(managerBonusDTO.SalesIncrease) + ", "
                        + " " + SQLUtility.getDouble(managerBonusDTO.CustomerIndex) + "," + SQLUtility.getDouble(managerBonusDTO.CustomerComplaint) + "," + SQLUtility.getDouble(managerBonusDTO.Catering) + ", "
                          + " " + SQLUtility.getDouble(managerBonusDTO.SubInspection) + ",'" + isZeroBasis + "' , '" + SQLUtility.FormateDateYYYYMMDDWtithTime(DateTime.Now) + "','" + SQLUtility.FormateDateYYYYMMDDWtithTime(DateTime.Now) + "') ";
            db.ExecuteUpdate(query);
        }

        public void AddComplaints(ComplaintsDTO complaintsDTO)
        {
            DataBaseUtility db = new DataBaseUtility();

            //string query = "INSERT INTO [dbo].[Complaints] ([StoreId],[ComplaintDate],[CustomerContacted],[FeedbackReceived],[ProblemFixed],[Comment],[Response] "
            //               + " ,[CreatedDateTime],[LastUpdateDateTime]) VALUES(" + SQLUtility.getInteger(complaintsDTO.StoreId) + ",'" + SQLUtility.FormateDateYYYYMMDDWtithTime(complaintsDTO.ComplaintDate) + "' ,"
            //              + " '" + complaintsDTO.CustomerContacted + "','" + complaintsDTO.FeedbackRecieved + "','" + complaintsDTO.ProblemFixed + "',"
            //                + " " + SQLUtility.getString(complaintsDTO.Comment) + ", " + SQLUtility.getString(complaintsDTO.Response) + ",'" + SQLUtility.FormateDateYYYYMMDDWtithTime(complaintsDTO.CreatedDateTime) + "','" + SQLUtility.FormateDateYYYYMMDDWtithTime(complaintsDTO.LastUpdateDateTime) + "' )";

            string query = "INSERT INTO [dbo].[Complaints] ([StoreId] ,[ComplaintDate],[CustomerContacted] ,[FeedbackReceived],[ProblemFixed] ,[EmailedSubway] "
                           +" ,[Comment],[Response],[Name] ,[Telephone] ,[Email],[CreatedDateTime] ,[LastUpdateDateTime]) "
                            + " VALUES(" + SQLUtility.getInteger(complaintsDTO.StoreId) + ",'" + SQLUtility.FormateDateYYYYMMDD(complaintsDTO.ComplaintDate) + "', '" + complaintsDTO.CustomerContacted + "','"+complaintsDTO.FeedbackRecieved+"', "
                             + " '" + complaintsDTO.ProblemFixed + "','" + complaintsDTO.EmailedSubway + "'," + SQLUtility.getString(ValidationUtility.TruncateString(complaintsDTO.Comment, 500)) + "," + SQLUtility.getString(ValidationUtility.TruncateString(complaintsDTO.Response, 500)) + "," + SQLUtility.getString(complaintsDTO.Name) + "," + SQLUtility.getString(complaintsDTO.TelePhone) + "," + SQLUtility.getString(complaintsDTO.Email) + ",'" + SQLUtility.FormateDateYYYYMMDDWtithTime(complaintsDTO.CreatedDateTime) + "','" + SQLUtility.FormateDateYYYYMMDDWtithTime(complaintsDTO.LastUpdateDateTime) + "')";
            db.ExecuteUpdate(query);
        }

        public void DeleteMaintenanceRequest(int id)
        {
            DataBaseUtility db = new DataBaseUtility();

            try
            {
                string query = " delete from dbo.StoreMaintainance where Id = " + SQLUtility.getInteger(id) + "  ";

                db.ExecuteUpdate(query);
            }
            catch (Exception ex)
            {
                log.Error(" Exception in  DeleteMaintenanceRequest Method ", ex);
            }
        }

        public void AddPaperWork(ArrayList list)
        {
            foreach (WeeklyPaperworkDTO dto in list)
            {
                DataBaseUtility db = new DataBaseUtility();
                string query = "INSERT INTO [dbo].[WeeklyPaperWork]([StoreId],[NetSales],[GiftCardSales],[AR],[PaidOuts],[PettyExpense],[Total],[StoreTransfer],[PFG1],[PFG2],[TotalPFG],[Coke],[TotalCost],[CostPercent],[LaborCost],[LaborCostPercent],[Royalty],[FAF],[CostAndLaborCostPercent],[AdjTax],[GCRedeem],[GCDifference],[TaxPercent], "
                            + "  [ActualSalesTaxper],[DifferenceOfSalesTax],[NonTaxableSale],[WeekStartDate],[CreatedDateTime],[LastUpdateDateTime]) "
                    + " VALUES (" + SQLUtility.getInteger(dto.StoreId) + " , " + SQLUtility.getDouble(dto.NetSales) + " ," + SQLUtility.getDouble(dto.GiftCardSales) + "," + SQLUtility.getDouble(dto.Ar) + "," + SQLUtility.getDouble(dto.PaidOuts) + "," + SQLUtility.getDouble(dto.PettyExpense) + ", "
                   + " " + SQLUtility.getDouble(dto.Total) + "," + SQLUtility.getDouble(dto.StoreTransfer) + "," + SQLUtility.getDouble(dto.Pfg1) + "," + SQLUtility.getDouble(dto.Pfg2) + "," + SQLUtility.getDouble(dto.TotalPFG) + "," + SQLUtility.getDouble(dto.Coke) + "," + SQLUtility.getDouble(dto.TotalCost) + ", "
                    + " " + SQLUtility.getDouble(dto.CostPercent) + "," + SQLUtility.getDouble(dto.LaborCost) + "," + SQLUtility.getDouble(dto.LaborCostPercent) + "," + SQLUtility.getDouble(dto.Royalty) + "," + SQLUtility.getDouble(dto.Faf) + "," + SQLUtility.getDouble(dto.CostAndLaborCostPercent) + "," + SQLUtility.getDouble(dto.AdjTax) + "," + SQLUtility.getDouble(dto.GcRedeem) + "," + SQLUtility.getDouble(dto.GcDifference) + ", "
                    + " " + SQLUtility.getDouble(dto.TaxPercent) + ", " + SQLUtility.getDouble(dto.ActualSalesTaxper) + "," + SQLUtility.getDouble(dto.DifferenceOfSalesTax) + "," + SQLUtility.getDouble(dto.NonTaxableSale) + ",'" + SQLUtility.FormateDateYYYYMMDD(dto.WeekStartDate) + "','" + SQLUtility.FormateDateYYYYMMDDWtithTime(DateTime.Now) + "','" + SQLUtility.FormateDateYYYYMMDDWtithTime(DateTime.Now) + "')";

                db.ExecuteUpdate(query);

            }
        }