C# DBDataHelper.ExecSQL方法代码示例

    public void CreateEmployee(Employees objEmployee)
    {
        DBDataHelper.ConnectionString = ConfigurationManager.ConnectionStrings["CSBiometricAttendance"].ConnectionString;
        List<SqlParameter> lstMasterEmployeeDetail = new List<SqlParameter>();
        lstMasterEmployeeDetail.Add(new SqlParameter("@facultyId", objEmployee.Id));
        lstMasterEmployeeDetail.Add(new SqlParameter("@name", objEmployee.Name));
        lstMasterEmployeeDetail.Add(new SqlParameter("@joiningDate", objEmployee.JoiningDate));
        lstMasterEmployeeDetail.Add(new SqlParameter("@gender", objEmployee.Gender));
        lstMasterEmployeeDetail.Add(new SqlParameter("@createdOn", objEmployee.CreatedOn));
        lstMasterEmployeeDetail.Add(new SqlParameter("@isDeleted", false));
        DataTable dt = new DataTable();
        DataSet ds;
        int EmployeeId;
        using (DBDataHelper objDDBDataHelper = new DBDataHelper())
        {
            ds = objDDBDataHelper.GetDataSet("spCreateEmployee", SQLTextType.Stored_Proc, lstMasterEmployeeDetail);
            EmployeeId = Convert.ToInt32(ds.Tables[0].Rows[0][0]);
        }
        List<SqlParameter> lstEmployeeDetail = new List<SqlParameter>();
        lstEmployeeDetail.Add(new SqlParameter("@employeeId", EmployeeId));
        lstEmployeeDetail.Add(new SqlParameter("@roleId", objEmployee.RoleId));
        lstEmployeeDetail.Add(new SqlParameter("@departmentId", objEmployee.DepartmentId));
        lstEmployeeDetail.Add(new SqlParameter("@contactNumber", objEmployee.ContactNumber));
        lstEmployeeDetail.Add(new SqlParameter("@weeklyOffDay", objEmployee.WeeklyOffDay));
        lstEmployeeDetail.Add(new SqlParameter("@shiftId", objEmployee.ShiftId));
        lstEmployeeDetail.Add(new SqlParameter("@createdAt", objEmployee.CreatedOn));
        lstEmployeeDetail.Add(new SqlParameter("@updatedAt", objEmployee.UpdatedOn));
        lstEmployeeDetail.Add(new SqlParameter("@isDeleted", false));
        using (DBDataHelper objDDBDataHelper = new DBDataHelper())
        {
            objDDBDataHelper.ExecSQL("spCreateEmployeeTransc", SQLTextType.Stored_Proc, lstEmployeeDetail);
        }

    }

    public bool UpdateEmployee(Employees objEmployee)
    {
        List<SqlParameter> lstEmployeeDetail = new List<SqlParameter>();
        lstEmployeeDetail.Add(new SqlParameter("@employeeId", objEmployee.Id));
        lstEmployeeDetail.Add(new SqlParameter("@gender", objEmployee.Gender));
        lstEmployeeDetail.Add(new SqlParameter("@joiningDate", objEmployee.JoiningDate));
        lstEmployeeDetail.Add(new SqlParameter("@isDeleted", false));
        lstEmployeeDetail.Add(new SqlParameter("@updatedOn", DateTime.Now));
        lstEmployeeDetail.Add(new SqlParameter("@password", objEmployee.Password));
        lstEmployeeDetail.Add(new SqlParameter("@roleId", objEmployee.RoleId));
        lstEmployeeDetail.Add(new SqlParameter("@departmentId", objEmployee.DepartmentId));
        lstEmployeeDetail.Add(new SqlParameter("@contactNumber", objEmployee.ContactNumber));
        lstEmployeeDetail.Add(new SqlParameter("@weeklyOffDay", objEmployee.WeeklyOffDay));
        lstEmployeeDetail.Add(new SqlParameter("@shiftId", objEmployee.ShiftId));
        DataTable dt = new DataTable();
        try
        {
            using (DBDataHelper objDDBDataHelper = new DBDataHelper())
            {
                objDDBDataHelper.ExecSQL("spUpdateEmployeeByEmployeeId", SQLTextType.Stored_Proc, lstEmployeeDetail);

            }
            return true;
        }
        catch(Exception ex)
        {
            return false;
        }
    }

    protected void btnAddSession_Click(object sender, EventArgs e)
    {
        #region Add_Session
        DBDataHelper.ConnectionString = ConfigurationManager.ConnectionStrings["CSBiometricAttendance"].ConnectionString;

        DateTime sessionStartDate = new DateTime(Int32.Parse(DateTime.Now.Year.ToString()), 8, 1);
        DateTime sessionEndDate = new DateTime(sessionStartDate.Year + 1, 7, 31);

        string query = "Select Count(*) from tblSession Where SessionStartDate=[email protected]";
        List<SqlParameter> lstParams = new List<SqlParameter>();
        lstParams.Add(new SqlParameter("@sessionStartDate", sessionStartDate));
        DataTable ds = new DataTable();
        using (DBDataHelper objDDBDataHelper = new DBDataHelper())
        {
            ds = objDDBDataHelper.GetDataTable(query, SQLTextType.Query, lstParams);
        }

        if (ds.Rows.Count != 0)
            if (Int32.Parse(ds.Rows[0][0].ToString()) == 0)
            {

                string addSession = @"INSERT INTO [dbo].[tblSession]
                                      ([SessionStartDate]
                                       ,[SessionEndDate])
                                       VALUES (@sessionStartDate,@sessionEndDate)";

                List<SqlParameter> lstParams2 = new List<SqlParameter>();
                lstParams2.Add(new SqlParameter("@sessionStartDate", sessionStartDate));
                lstParams2.Add(new SqlParameter("@sessionEndDate", sessionEndDate));

                using (DBDataHelper objDDBDataHelper = new DBDataHelper())
                {
                    objDDBDataHelper.ExecSQL(addSession, SQLTextType.Query, lstParams2);
                }
                ManageReports objManageReprts = new ManageReports();
                objManageReprts.AssignSessionWiseLeave(sessionStartDate);
                objManageReprts.UpdateLeaveBalanceTable(sessionStartDate);
                ScriptManager.RegisterClientScriptBlock(this, this.GetType(), "alertMessage", "alert('Session Added')", true);
            }
            else
            {
                ScriptManager.RegisterClientScriptBlock(this, this.GetType(), "alertMessage", "alert('Session Exists')", true);
            }
        #endregion
    }

    public void Add(Day day)
    {

        DBDataHelper.ConnectionString = ConfigurationManager.ConnectionStrings["CSBiometricAttendance"].ConnectionString;
        List<SqlParameter> list_params = new List<SqlParameter>() { new SqlParameter("@date", day.Date), new SqlParameter("@status", day.Status) };
        try
        {
            using (DBDataHelper helper = new DBDataHelper())
            {
                helper.ExecSQL("Insert into [tblSpecialDays] values (@date,@status)", SQLTextType.Query, list_params);

            }

        }
        catch(Exception)
        {

        }
    }

 protected void btnAddHoliday_Click(object sender, EventArgs e)
 {
     string query = "INSERT INTO [tblHolidays] VALUES (@date,@status,@nameOfHoliday)";
     DBDataHelper.ConnectionString = ConfigurationManager.ConnectionStrings["CSBiometricAttendance"].ConnectionString;
     DataTable dt = new DataTable();
     List<SqlParameter> lstData = new List<SqlParameter>();
     lstData.Add(new SqlParameter("@date", Calendar1.SelectedDate.Date.ToString("d")));
     lstData.Add(new SqlParameter("@nameOfHoliday", txtHoliday.Text == "" ? ((Convert.ToInt32(ddlStatus.SelectedValue)) == (int)DayStatus.Holiday ? DayStatus.Holiday.ToString() : "Weekly Off") : (txtHoliday.Text)));
     lstData.Add(new SqlParameter("@status", ddlStatus.SelectedValue));
     using (DBDataHelper objDDBDataHelper = new DBDataHelper())
     {
         objDDBDataHelper.ExecSQL(query, SQLTextType.Query, lstData);
     }
     ScriptManager.RegisterStartupScript(this, this.GetType(), "script", "alert('Holiday Added Successfully...');", true);
     btnAddHoliday.Enabled = false;
     btnAddHoliday.Text = "Holiday Exists";
     txtHoliday.Text = dt.Rows[0][0] == DBNull.Value ? "" : dt.Rows[0][0].ToString();
     BindData();
 }

 /// <summary>
 /// Adds a new Department
 /// </summary>
 /// <param name="Department">String value for Department Name</param>
 /// <returns>True if Department is added successfully</returns>
 public bool AddDepartment(string Department)
 {
     DBDataHelper.ConnectionString = ConfigurationManager.ConnectionStrings["CSBiometricAttendance"].ConnectionString;
     List<SqlParameter> lstDepartment = new List<SqlParameter>();
     lstDepartment.Add(new SqlParameter("@name", Department));
     lstDepartment.Add(new SqlParameter("@createdOn", DateTime.Now));
     lstDepartment.Add(new SqlParameter("@updatedOn", DateTime.Now));
     DataTable dt = new DataTable();
     try
     {
         using (DBDataHelper objDDBDataHelper = new DBDataHelper())
         {
             objDDBDataHelper.ExecSQL("spCreateDepartment", SQLTextType.Stored_Proc, lstDepartment);
         }
         return true;
     }
     catch (Exception ex)
     {
         return false;
     }
 }

    protected void btnAddSession_Click(object sender, EventArgs e)
    {
        DBDataHelper.ConnectionString = ConfigurationManager.ConnectionStrings["CSBiometricAttendance"].ConnectionString;
        DBDataHelper helper = new DBDataHelper();

        #region Get Last Session
        string query = @"SELECT TOP 1 [SessionStartDate],[SessionEndDate]
                         FROM [BiometricsAttendanceSystem].[dbo].[tblSession]
                         ORDER BY [SessionStartDate] DESC";
        DataTable dt = new DataTable();
        DateTime startDate = new DateTime();
        DateTime endDate = new DateTime();
        using (DBDataHelper objDDBDataHelper = new DBDataHelper())
        {
            dt = objDDBDataHelper.GetDataTable(query, SQLTextType.Query);
            startDate = DateTime.Parse(dt.Rows[0][0].ToString());
            endDate = DateTime.Parse(dt.Rows[0][1].ToString());
        } 
        #endregion

        #region Adding New Session
        startDate = startDate.AddYears(1);
        endDate = endDate.AddYears(1);
        string query1 = @"INSERT INTO [BiometricsAttendanceSystem].[dbo].[tblSession] 
                          VALUES (@startDate,@endDate)";
        List<SqlParameter> paramsLst = new List<SqlParameter>();
        paramsLst.Add(new SqlParameter("@startDate", startDate));
        paramsLst.Add(new SqlParameter("@endDate", endDate));
        using (DBDataHelper objDDBDataHelper = new DBDataHelper())
        {
            objDDBDataHelper.ExecSQL(query1, SQLTextType.Query, paramsLst);
        } 
        #endregion

        #region Update Leave Balance Table
        

        #endregion
    }

 /// <summary>
 /// Adds a new task to the database and assigns the task to different persons.
 /// </summary>
 /// <param name="task">An object of Task class containing the details about the task.</param>
 /// <returns>It returns true if the task is added and assigned successfully.</returns>
 public bool AddNewTask(Task task)
 {
     SqlParameter Name = new SqlParameter("name", task.Name);
     SqlParameter Description = new SqlParameter("description", task.Description);
     SqlParameter StartDate = new SqlParameter("startDate", task.StartDate);
     SqlParameter EndDate = new SqlParameter("endDate", task.EndDate);
     SqlParameter HODID = new SqlParameter("hodID", task.AssignedBy.ID);
     SqlParameter PriorityID = new SqlParameter("priorityID", (int)task.Priority);
     SqlParameter TaskTypeID = new SqlParameter("TaskTypeID", (int)task.Type);
     SqlParameter TaskID = new SqlParameter("id", SqlDbType.Int);
     TaskID.Direction = ParameterDirection.Output;
     TaskID.Size = 50;
     SqlParameter ReminderTime = new SqlParameter("reminderTime", task.ReminderTime);
     List<SqlParameter> parameterCollection = new List<SqlParameter>()
     {
         Name,Description,StartDate,EndDate,HODID,PriorityID,TaskTypeID,TaskID,ReminderTime
     };
     using (DBDataHelper helper = new DBDataHelper())
     {
         helper.ExecSQL("dbo.AddNewTask", SQLTextType.Stored_Proc, parameterCollection);
     }
     task.ID = int.Parse(TaskID.SqlValue.ToString());
     return AssignTaskToFaculties(task);
 }

 protected void btnUpdate_Click(object sender, EventArgs e)
 {
     DBDataHelper.ConnectionString = ConfigurationManager.ConnectionStrings["CSBiometricAttendance"].ConnectionString;
     List<SqlParameter> lstData = new List<SqlParameter>();
     lstData.Add(new SqlParameter("@employeeId", Convert.ToInt32(Session["id"])));
     lstData.Add(new SqlParameter("@sickleave", Convert.ToInt32(txtEditSL.Text)));
     lstData.Add(new SqlParameter("@emergencyLeave", Convert.ToInt32(txtEditEL.Text)));
     if (ddlDate.SelectedValue == "0")
     {
         lstData.Add(new SqlParameter("@sessionstart", new DateTime(2015, 08, 01)));
         lstData.Add(new SqlParameter("@sessionend", new DateTime(2016, 07, 31)));
     }
     else
     {
         lstData.Add(new SqlParameter("@sessionstart", new DateTime(2016, 08, 01)));
         lstData.Add(new SqlParameter("@sessionend", new DateTime(2017, 07, 31)));
     }
     string query = "Insert into tblLeavesOldStock values(@employeeId,@sickleave,@emergencyLeave,@sessionstart,@sessionend)";
     using (DBDataHelper objDDBDataHelper = new DBDataHelper())
     {
         objDDBDataHelper.ExecSQL(query, SQLTextType.Query, lstData);
     }
     BindData();
 }

    public bool AssignHalfDayLeaveRemovingShortDayLeave(int employeeId, DateTime date)
    {
        DBDataHelper.ConnectionString = ConfigurationManager.ConnectionStrings["CSBiometricAttendance"].ConnectionString;
        DBDataHelper helper = new DBDataHelper();
        List<SqlParameter> lst_params = new List<SqlParameter>();
        lst_params.Add(new SqlParameter("@employeeId", employeeId));
        lst_params.Add(new SqlParameter("@date", date));
        string query = "SELECT Count([EmployeeId]) FROM  [tblLeave] Where [Date] = @date AND [EmployeeId] [email protected] AND isDeleted=0";
        DataTable dt = new DataTable();

        using (DBDataHelper objDDBDataHelper = new DBDataHelper())
        {
            dt = objDDBDataHelper.GetDataTable(query, SQLTextType.Query, lst_params);
        }
        if (Int32.Parse(dt.Rows[0][0].ToString()) == 0)
        {
            AssignLeave(employeeId, date, (int)LeaveTypes.HDL);
            return true;
        }
        else
        {
            List<SqlParameter> lst_params1 = new List<SqlParameter>();
            lst_params1.Add(new SqlParameter("@employeeId", employeeId));
            lst_params1.Add(new SqlParameter("@date", date));
            lst_params1.Add(new SqlParameter("@createdAt", DateTime.Now));

            try
            {
                using (DBDataHelper objDDBDataHelper = new DBDataHelper())
                {
                    objDDBDataHelper.ExecSQL("spAssignHalfDayLeaveRemovingShortDayLeave", SQLTextType.Stored_Proc, lst_params1);
                }
                return true;
            }
            catch (Exception)
            {
                return false;
            }
        }
    }

    /// <summary>
    /// 
    /// </summary>
    /// <param name="departmentId"></param>
    /// <returns></returns>
    public bool UpdateDepartment(int departmentId, string Department)
    {
        List<SqlParameter> lstDepartment = new List<SqlParameter>();
        lstDepartment.Add(new SqlParameter("@name", Department));
        lstDepartment.Add(new SqlParameter("@departmentId", departmentId));
        lstDepartment.Add(new SqlParameter("@updatedAt", DateTime.Now));
        DataTable dt = new DataTable();
        try
        {
            using (DBDataHelper objDDBDataHelper = new DBDataHelper())
            {
                objDDBDataHelper.ExecSQL("spUpdateDepartment", SQLTextType.Stored_Proc, lstDepartment);

            }
            return true;
        }
        catch(Exception)
        {
            return false;
        }
    }

 public bool HalfDayLeave(int employeeId, DateTime date, int leaveId)
 {
     DBDataHelper.ConnectionString = ConfigurationManager.ConnectionStrings["CSBiometricAttendance"].ConnectionString;
     DBDataHelper helper = new DBDataHelper();
     string query = "select Id from tblShifts where IsActive=1";
     DataSet ds;
     using (DBDataHelper objDDBDataHelper = new DBDataHelper())
     {
         ds = objDDBDataHelper.GetDataSet(query, SQLTextType.Query);
     }
     int shiftId = Convert.ToInt32(ds.Tables[0].Rows[0][0]);
     List<SqlParameter> lstHalfDayLeave = new List<SqlParameter>();
     lstHalfDayLeave.Add(new SqlParameter("@employeeId", employeeId));
     lstHalfDayLeave.Add(new SqlParameter("@leaveTypeId", leaveId));
     lstHalfDayLeave.Add(new SqlParameter("@date", date));
     lstHalfDayLeave.Add(new SqlParameter("@shiftId", shiftId));
     lstHalfDayLeave.Add(new SqlParameter("@shift", 1));
     lstHalfDayLeave.Add(new SqlParameter("@createdAt", DateTime.Now));
     lstHalfDayLeave.Add(new SqlParameter("@updatedAt", DateTime.Now));
     DataTable dt = new DataTable();
     try
     {
         using (DBDataHelper objDDBDataHelper = new DBDataHelper())
         {
             objDDBDataHelper.ExecSQL("spAssignHalfDayLeave", SQLTextType.Stored_Proc, lstHalfDayLeave);
         }
         return true;
     }
     catch (Exception)
     {
         return false;
     }
 }

    /// <summary>
    /// Add a new duration entry
    /// </summary>
    /// <param name="duration">TimeSpan value for duration</param>
    /// <returns>True if entry is successfull, else False</returns>
    /// 
    public bool AddDuration(TimeSpan duration, int leaveId)
    {
        DBDataHelper.ConnectionString = ConfigurationManager.ConnectionStrings["CSBiometricAttendance"].ConnectionString;
        DBDataHelper helper = new DBDataHelper();
        List<SqlParameter> lstDuration = new List<SqlParameter>();
        lstDuration.Add(new SqlParameter("@duration", duration));
        lstDuration.Add(new SqlParameter("@leaveId", leaveId));
        lstDuration.Add(new SqlParameter("@createdOn", DateTime.Now));
        lstDuration.Add(new SqlParameter("@updatedOn", DateTime.Now));
        lstDuration.Add(new SqlParameter("@isActive", false));
        lstDuration.Add(new SqlParameter("@isDeleted", false));
        DataTable dt = new DataTable();
        try
        {
            using (DBDataHelper objDDBDataHelper = new DBDataHelper())
            {
                objDDBDataHelper.ExecSQL("spAddDuration", SQLTextType.Stored_Proc, lstDuration);
            }
            return true;
        }
        catch(Exception)
        {
            return false;
        }

    }

    public bool DeleteDuration(int durationId)
    {
        List<SqlParameter> lstDuration = new List<SqlParameter>();
        lstDuration.Add(new SqlParameter("@durationId", durationId));
        lstDuration.Add(new SqlParameter("@updatedAt", DateTime.Now));
        DataTable dt = new DataTable();
        try
        {
            using (DBDataHelper objDDBDataHelper = new DBDataHelper())
            {
                objDDBDataHelper.ExecSQL("spDeleteDuration", SQLTextType.Stored_Proc, lstDuration);

            }
            return true;
        }
        catch(Exception)
        {
            return false;
        }
    }

    public bool AssignSessionWiseLeave(DateTime sessionStartDate)
    {
        DBDataHelper.ConnectionString = ConfigurationManager.ConnectionStrings["CSBiometricAttendance"].ConnectionString;
        MasterEntries objMasterEntries = new MasterEntries();

        List<Role> lstRole = new List<Role>();
        lstRole = objMasterEntries.GetAllRoles();

        DateTime sessionEndDate = new DateTime(sessionStartDate.Year + 1, 7, 31);

        #region Roles

        foreach (Role role in lstRole)
        {
            ManageEmployees objManageEmployees = new ManageEmployees();

            List<Employees> lstEmployees = objManageEmployees.GetEmployeesByRole(role.Id);
            List<LeavesCount> lstLeaveDetails = new List<LeavesCount>();
            ManageLeaves objManageLeaves = new ManageLeaves();
            lstLeaveDetails = objManageLeaves.GetLeavesCountAssignedByRole(role.Id);

            #region List of Employees

            foreach (Employees objEmployees in lstEmployees)
            {
                #region List of Leaves

                foreach (LeavesCount LeaveDetails in lstLeaveDetails)
                {

                    using (DBDataHelper objDDBDataHelper = new DBDataHelper())
                    {
                        string query = @"INSERT INTO [dbo].[tblLeaveAssignedPerSession]
                                 VALUES
                                (@employeeId,
                                 @leaveTypeId, 
                                 @noOfLeaves,
                                 @sessionStartDate,
                                 @sessionEndDate)";

                        List<SqlParameter> list_params = new List<SqlParameter>()
                        {
                            new SqlParameter("@employeeId", objEmployees.Id),
                            new SqlParameter("@leaveTypeId", LeaveDetails.LeaveId),
                            new SqlParameter("@noOfLeaves", LeaveDetails.LeaveCount),
                            new SqlParameter("@sessionStartDate", sessionStartDate),
                            new SqlParameter("@sessionEndDate", sessionEndDate),
                        };

                        objDDBDataHelper.ExecSQL(query, SQLTextType.Query, list_params);
                    }
                }

                #endregion
            }

            #endregion
        }
        #endregion

        return true;

    }