C# DBDataHelper.GetDataTable方法代码示例

    public LeavesOldStockViewModel getDataForOldLeavesByEmployeeId(DateTime sessionStartDate, DateTime sessionEndDate, int employeeId)
    {
        List<LeavesOldStockViewModel> lstLeavesOldStockViewModel = new List<LeavesOldStockViewModel>();
        DBDataHelper.ConnectionString = ConfigurationManager.ConnectionStrings["CSBiometricAttendance"].ConnectionString;
        DBDataHelper helper = new DBDataHelper();
        List<SqlParameter> lst_params = new List<SqlParameter>();
        lst_params.Add(new SqlParameter("@employeeId", employeeId));
        DataTable dt = new DataTable();
        string query = "SELECT tblEmployeesMaster.Id, Name ,[SLCount],[ELCount],[SessionStartDate],[SesssionEndDate] FROM[tblLeavesOldStock] right outer join tblEmployeesMaster On tblEmployeesMaster.Id = tblLeavesOldStock.EmployeeId Where tblLeavesOldStock.EmployeeId = @employeeId";
        LeavesOldStockViewModel objLeavesOldStockViewModel = new LeavesOldStockViewModel();
        using (DBDataHelper objDDBDataHelper = new DBDataHelper())
        {
            dt = objDDBDataHelper.GetDataTable(query, SQLTextType.Query, lst_params);
            foreach (DataRow row in dt.Rows)
            {
                objLeavesOldStockViewModel.employeeId = row[0] == DBNull.Value ? 0 : Int32.Parse(row[0].ToString());
                objLeavesOldStockViewModel.employeeName = row[1] == DBNull.Value ? "" : row[1].ToString();
                objLeavesOldStockViewModel.slCount = row[2] == DBNull.Value ? 0 : Int32.Parse(row[2].ToString());
                objLeavesOldStockViewModel.elCount = row[3] == DBNull.Value ? 0 : Int32.Parse(row[3].ToString());
                objLeavesOldStockViewModel.sessionStartDate = row[4] == DBNull.Value ? DateTime.Now : DateTime.Parse(row[4].ToString());
                objLeavesOldStockViewModel.sessionEndDate = row[5] == DBNull.Value ? DateTime.Now : DateTime.Parse(row[5].ToString());
                
            }
        }

        return objLeavesOldStockViewModel;
    }

    protected void Calendar1_SelectionChanged(object sender, EventArgs e)
    {
        BindData();
        lblDate.Text = Calendar1.SelectedDate.Date.ToString("d");
        txtHoliday.Text = "";
        btnAddHoliday.Text = "Add Holiday";
        btnAddHoliday.Enabled = true;

        DBDataHelper.ConnectionString = ConfigurationManager.ConnectionStrings["CSBiometricAttendance"].ConnectionString;
        DBDataHelper helper = new DBDataHelper();
        string query = @"SELECT [NameOfHoliday]
                         FROM [tblHolidays]
                         WHERE Date = @date";
        List<SqlParameter> lst_params = new List<SqlParameter>();
        lst_params.Add(new SqlParameter("@date", Calendar1.SelectedDate));
        DataTable dt = new DataTable();
        using (DBDataHelper objDDBDataHelper = new DBDataHelper())
        {
            dt = objDDBDataHelper.GetDataTable(query, SQLTextType.Query, lst_params);
            if (dt != null && dt.Rows.Count > 0)
            {
                btnAddHoliday.Enabled = false;
                btnAddHoliday.Text = "Holiday Exists";
                txtHoliday.Text = dt.Rows[0][0] == DBNull.Value ? "" : dt.Rows[0][0].ToString();
            }
        }
    }

 public bool GetEmployeesOnLeaveByDate(DateTime date, out List<Reports> objEmployees)
 {
     DataTable dt, dt1, dt2, dt3, dt4;
     List<Reports> objEmployees1 = new List<Reports>();
     DBDataHelper.ConnectionString = ConfigurationManager.ConnectionStrings["CSBiometricAttendance"].ConnectionString;
     List<SqlParameter> list_params = new List<SqlParameter>() { new SqlParameter("@date", date) };
     try
     {
         using (DBDataHelper helper = new DBDataHelper())
         {
             dt = helper.GetDataTable("spGetEmployeesOnLeaveByDate", SQLTextType.Stored_Proc, list_params);
             for (int i = 0; i < dt.Rows.Count; i++)
             {
                 int EmployeeId = Convert.ToInt32(dt.Rows[i]["EmployeeId"]);
                 List<SqlParameter> list_params2 = new List<SqlParameter>() { new SqlParameter("@employeeId", EmployeeId) };
                 dt1 = helper.GetDataTable("select FirstName,MiddleName,LastName from tblEmployeesMaster where [email protected]", SQLTextType.Query, list_params2);
                 Reports objReports = new Reports();
                 objReports.FirstName = Convert.ToString(dt1.Rows[0]["FirstName"]);
                 objReports.MiddleName = Convert.ToString(dt1.Rows[0]["MiddleName"]);
                 objReports.LastName = Convert.ToString(dt1.Rows[0]["LastName"]);
                 List<SqlParameter> list_params3 = new List<SqlParameter>() { new SqlParameter("@employeeId", EmployeeId) };
                 dt2 = helper.GetDataTable("Select RoleId,DepartmentId from tblEmployees where [email protected]", SQLTextType.Query, list_params3);
                 objReports.RoleId = Convert.ToInt32(dt2.Rows[0]["RoleId"]);
                 objReports.DepartmentId = Convert.ToInt32(dt2.Rows[0]["DepartmentId"]);
                 List<SqlParameter> list_params4 = new List<SqlParameter>() { new SqlParameter("@roleId", objReports.RoleId) };
                 dt3 = helper.GetDataTable("spGetRoleById", SQLTextType.Stored_Proc, list_params4);
                 objReports.RoleName = dt3.Rows[0][0].ToString();
                 List<SqlParameter> list_params5 = new List<SqlParameter>() { new SqlParameter("@departmentId", objReports.DepartmentId) };
                 dt4 = helper.GetDataTable("spGetDepartmentById", SQLTextType.Stored_Proc, list_params5);
                 objReports.DepartmentName = dt4.Rows[0][0].ToString();
                 objEmployees1.Add(objReports);
             }
         }
         objEmployees = objEmployees1;
         return true;
     }
     catch (Exception)
     {
         objEmployees = null;
         return false;
     }
 }

    protected void BindData()
    {
        DBDataHelper.ConnectionString = ConfigurationManager.ConnectionStrings["CSBiometricAttendance"].ConnectionString;
        DBDataHelper helper = new DBDataHelper();
        string query = @"SELECT [Id],[SessionStartDate],[SessionEndDate]
                         FROM [tblSession] ORDER BY [SessionStartDate] DESC";

        using (DBDataHelper objDDBDataHelper = new DBDataHelper())
        {
            grdSession.DataSource = objDDBDataHelper.GetDataTable(query, SQLTextType.Query);
            grdSession.DataBind();
        }
    }

 public bool GetEmployeesByDate(DateTime date, out List<Employees> objEmployees)
 {
     DataTable dt, dt1, dt2;
     List<Employees> objEmployees1 = new List<Employees>();
     DBDataHelper.ConnectionString = ConfigurationManager.ConnectionStrings["CSBiometricAttendance"].ConnectionString;
     List<SqlParameter> list_params = new List<SqlParameter>() { new SqlParameter("@date", date) };
     try
     {
         using (DBDataHelper helper = new DBDataHelper())
         {
             dt = helper.GetDataTable("Select EmployeeId from tblAttendance where [email protected]", SQLTextType.Query, list_params);
             for (int i = 0; i < dt.Rows.Count; i++)
             {
                 int EmployeeId = Convert.ToInt32(dt.Rows[i]["EmployeeId"]);
                 List<SqlParameter> list_params2 = new List<SqlParameter>() { new SqlParameter("@employeeId", EmployeeId) };
                 dt1 = helper.GetDataTable("select FirstName,MiddleName,LastName from tblEmployeesMaster where [email protected]", SQLTextType.Query, list_params2);
                 Employees objEmployee = new Employees();
                 objEmployee.FirstName = Convert.ToString(dt1.Rows[0]["FirstName"]);
                 objEmployee.MiddleName = Convert.ToString(dt1.Rows[0]["MiddleName"]);
                 objEmployee.LastName = Convert.ToString(dt1.Rows[0]["LastName"]);
                 List<SqlParameter> list_params3 = new List<SqlParameter>() { new SqlParameter("@employeeId", EmployeeId) };
                 dt2 = helper.GetDataTable("Select RoleId,DepartmentId from tblEmployees where [email protected]", SQLTextType.Query, list_params3);
                 objEmployee.RoleId = Convert.ToInt32(dt2.Rows[0]["RoleId"]);
                 objEmployee.DepartmentId = Convert.ToInt32(dt2.Rows[0]["DepartmentId"]);
                 objEmployees1.Add(objEmployee);
             }
         }
         objEmployees = objEmployees1;
         return true;
     }
     catch (Exception)
     {
         objEmployees = null;
         return false;
     }
 }

 /// <summary>
 /// This method gets all the tasks that are assigned to the faculty based on the id of the faculty.
 /// </summary>
 /// <param name="facultyID">An integer parameter containing the ID of the faculty.</param>
 /// <returns>It returns a dictionary of tasks assigned to the faculty along with the status of each task.</returns>
 public Dictionary<Task, TaskStatusType> GetAllTasksAssignedToAFaculty(int facultyID)
 {
     SqlParameter FacultyID = new SqlParameter("facultyID", facultyID);
     List<SqlParameter> parameterCollection = new List<SqlParameter>(){FacultyID};
     DataTable taskTable = new DataTable();
     using(DBDataHelper helper = new DBDataHelper())
     {
         taskTable = helper.GetDataTable("dbo.GetAllTasksAssignedToAFaculty", SQLTextType.Stored_Proc, parameterCollection);
     }
     //List<Task> tasks = new List<Task>();
     Dictionary<Task, TaskStatusType> tasks = new Dictionary<Task, TaskStatusType>();
     foreach (DataRow row in taskTable.Rows)
     {
         Task task = new Task()
         {
             ID = int.Parse(row["TaskID"].ToString()),
             Name = row["Name"].ToString(),
             Priority = (PriorityType)(Enum.Parse(typeof(PriorityType), row["PriorityID"].ToString())),
             StartDate = DateTime.Parse(row["StartDate"].ToString()),
             EndDate = DateTime.Parse(row["EndDate"].ToString()),
             Description = row["Description"].ToString(),
             Type = (TaskType)(Enum.Parse(typeof(TaskType), row["TaskTypeID"].ToString()))
         };
         TaskStatusType taskStatus = (TaskStatusType)(Enum.Parse(typeof(TaskStatusType),row["TaskStatusID"].ToString()));
         DataTable table = new DataTable();
         SqlParameter HODID = new SqlParameter("ID",row["HODID"]);
         List<SqlParameter> collection = new List<SqlParameter>(){HODID};
         using(DBDataHelper helper = new DBDataHelper())
         {
             table = helper.GetDataTable("dbo.GetFacultyByID", SQLTextType.Stored_Proc, collection);
         }
         HOD hod = new HOD()
         {
             ID = int.Parse(table.Rows[0]["FacultyID"].ToString()),
             Name = table.Rows[0]["Name"].ToString(),
             EmailID = table.Rows[0]["EmailID"].ToString(),
             Designation = DesignationType.HOD,
             Department = (DepartmentType)(Enum.Parse(typeof(DepartmentType),table.Rows[0]["DepartmentID"].ToString())),
             ImageURL = table.Rows[0]["ImageURL"].ToString(),
             ContactNo = table.Rows[0]["ContactNo"].ToString()
         };
         task.AssignedBy = hod;
         task.AssignedTo = GetAllFacultiesHavingTheTask(task.ID);
         tasks.Add(task,taskStatus);
     }
     return tasks;
 }

    protected void BindData()
    {
        DBDataHelper.ConnectionString = ConfigurationManager.ConnectionStrings["CSBiometricAttendance"].ConnectionString;
        DBDataHelper helper = new DBDataHelper();
        string query = @"SELECT [Id],[NameOfHoliday],[Status],[Date]
                             FROM [tblHolidays]
                             WHERE year([Date]) = year(@date)";

        List<SqlParameter> lst_params = new List<SqlParameter>();
        lst_params.Add(new SqlParameter("@date", Calendar1.SelectedDate));

        using (DBDataHelper objDDBDataHelper = new DBDataHelper())
        {
            grdHoliday.DataSource = objDDBDataHelper.GetDataTable(query, SQLTextType.Query, lst_params);
            grdHoliday.DataBind();
        }
    }

    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 [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
    }

    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
    }

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

        List<SqlParameter> list_params = new List<SqlParameter>()
            {
            new SqlParameter("@employeeId", Convert.ToInt32(txtEmployeeId.Text)),
            new SqlParameter("@password",txtPassword.Text)
            };
        try
        {
            using (DBDataHelper helper = new DBDataHelper())
            {
                string query = "select * from tblEmployees where [email protected] and [email protected]";
                dt = helper.GetDataTable(query, SQLTextType.Query, list_params);
                if (dt.Rows.Count == 1)
                {
                    int RoleId = Convert.ToInt32(dt.Rows[0]["RoleId"]);
                    Session["employeeId"] = txtEmployeeId.Text;
                    Session["roleId"] = RoleId;
                    if (RoleId == 1)
                        Response.Redirect("~/Admin/ManageMasterEntries.aspx", false);
                    else
                        Response.Redirect("masterEntry.aspx", false);
                }
                else
                {
                    lblMessage.Text = "Invalid User Id or Password";
                }
            }
        }

        catch (Exception ex)
        {
            Console.WriteLine("{0}", ex);
        }

    }

 public TimeSpan GetShortLeaveDurationByShiftId(int shiftId)
 {
     DataTable dt;
     TimeSpan dr = new TimeSpan();
     string query = "SELECT SHLDuration FROM tblMasterShifts WHERE [email protected] AND isDeleted = 0";
     List<SqlParameter> lst_params = new List<SqlParameter> { 
         new SqlParameter("@shiftId",shiftId)
     };
     try
     {
         using (DBDataHelper helper = new DBDataHelper())
         {
             dt = helper.GetDataTable(query, SQLTextType.Query, lst_params);
             dr = dt.Rows[0][0] == DBNull.Value ? new TimeSpan(0, 0, 0) : TimeSpan.Parse(dt.Rows[0][0].ToString());
         }
     }
     catch (Exception)
     {
         dr = new TimeSpan(0, 0, 0);
     }
     return dr;
 }

    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;
            }
        }
    }

    public List<LeavesOldStockViewModel> getDataForOldLeaves(DateTime sessionStartDate, DateTime sessionEndDate)
    {
        List<LeavesOldStockViewModel> lstLeavesOldStockViewModel = new List<LeavesOldStockViewModel>();
        DBDataHelper.ConnectionString = ConfigurationManager.ConnectionStrings["CSBiometricAttendance"].ConnectionString;
        DBDataHelper helper = new DBDataHelper();
        List<SqlParameter> lst_params = new List<SqlParameter>();
        DataTable dt = new DataTable();
        using (DBDataHelper objDDBDataHelper = new DBDataHelper())
        {
            dt = objDDBDataHelper.GetDataTable("spGetDataForOldLeaves", SQLTextType.Query, lst_params);
            foreach (DataRow row in dt.Rows)
            {
                LeavesOldStockViewModel objLeavesOldStockViewModel = new LeavesOldStockViewModel();
                objLeavesOldStockViewModel.employeeId = row[0] == DBNull.Value ? 0 : Int32.Parse(row[0].ToString());
                objLeavesOldStockViewModel.employeeName = row[1] == DBNull.Value ? "" : row[1].ToString();
                objLeavesOldStockViewModel.slCount = row[2] == DBNull.Value ? 0 : Int32.Parse(row[2].ToString());
                objLeavesOldStockViewModel.elCount = row[3] == DBNull.Value ? 0 : Int32.Parse(row[3].ToString());
                objLeavesOldStockViewModel.sessionStartDate = row[4] == DBNull.Value ? DateTime.Now : DateTime.Parse(row[4].ToString());
                objLeavesOldStockViewModel.sessionEndDate = row[5] == DBNull.Value ? DateTime.Now : DateTime.Parse(row[5].ToString());
                lstLeavesOldStockViewModel.Add(objLeavesOldStockViewModel);
            }
        }

        return lstLeavesOldStockViewModel;
    }

    public bool IsEmployeeOnLeave(int employeeId, DateTime date, out int LeaveType)
    {
        List<SqlParameter> list_params = new List<SqlParameter>() { new SqlParameter("@date", date), new SqlParameter("@employeeId", employeeId) };
        DataTable dt;
        int count = 0;

        using (DBDataHelper helper = new DBDataHelper())
        {
            dt = helper.GetDataTable("spGetTypeOfLeaveOfEmployeeByDate", SQLTextType.Stored_Proc, list_params);
            foreach (DataRow row in dt.Rows)
            {
                count = int.Parse(row[0].ToString());
                break;
            }
        }

        if (count == 0)
        {
            LeaveType = 0;
            return false;
        }
        else
        {
            LeaveType = count;
            return false;
        }
    }

    public TimeSpan GetHalfDayLeaveDuration()
    {
        DataTable dt;
        TimeSpan dr = new TimeSpan();
        string query = "Select Duration From tblDuration Where IsActive= 1 AND IsDeleted = 0 AND TypeOfLeave = 2";
        try
        {
            using (DBDataHelper helper = new DBDataHelper())
            {
                dt = helper.GetDataTable(query, SQLTextType.Query);
                dr = dt.Rows[0][0] == DBNull.Value ? new TimeSpan(0, 0, 0) : TimeSpan.Parse(dt.Rows[0][0].ToString());

            }
        }
        catch (Exception)
        {
            new TimeSpan(0, 0, 0);
        }
        return dr;
    }