C# DBDataHelper.GetDataSet方法代码示例

 public List<AssignLeaveViewModel> BindGridData()
 {
     MasterEntries objMasterEntries = new MasterEntries();
     DBDataHelper.ConnectionString = ConfigurationManager.ConnectionStrings["CSBiometricAttendance"].ConnectionString;
     DataTable dt = new DataTable();
     List<SqlParameter> lstData = new List<SqlParameter>();
     lstData.Add(new SqlParameter("@employeeId", Convert.ToInt32(Session["employeeId"])));
     lstData.Add(new SqlParameter("@dateMonth", Calendar1.SelectedDate.Month));
     lstData.Add(new SqlParameter("@dateYear", Calendar1.SelectedDate.Year));
     lstData.Add(new SqlParameter("@IsDeleted", Convert.ToInt32(0)));
     DataSet ds;
     int i = 0;
     string query = "SELECT Id , LeaveTypeId , [Date] FROM tblLeave WHERE MONTH([Date]) = @dateMonth AND YEAR([Date]) = @dateYear And EmployeeId = @employeeId And IsDeleted = @IsDeleted";
     using (DBDataHelper objDDBDataHelper = new DBDataHelper())
     {
         ds = objDDBDataHelper.GetDataSet(query, SQLTextType.Query, lstData);
         List<AssignLeaveViewModel> lstLeaves = new List<AssignLeaveViewModel>();
         foreach (DataRow row in ds.Tables[0].Rows)
         {
             AssignLeaveViewModel objLeaves = new AssignLeaveViewModel();
             int Id = Convert.ToInt32(ds.Tables[0].Rows[i][0]);
             int leaveId = Convert.ToInt32(ds.Tables[0].Rows[i][1]);
             Leaves objLeaves1 = new Leaves();
             objMasterEntries.GetLeavesById(leaveId, out objLeaves1);
             objLeaves.leaveId = Id;
             objLeaves.Id = objLeaves1.Id;
             objLeaves.LeaveName = objLeaves1.LeaveName;
             objLeaves.EmployeeId = Convert.ToInt32(Session["employeeId"]);
             objLeaves.Date = (Convert.ToDateTime(ds.Tables[0].Rows[i][2])).Date;
             lstLeaves.Add(objLeaves);
             i++;
         }
         return lstLeaves;
     }
 }

 public List<Employees> GetEmployeesPresentDateWise(DateTime dateTime)
 {
     DBDataHelper.ConnectionString = ConfigurationManager.ConnectionStrings["CSBiometricAttendance"].ConnectionString;
     List<Employees> lstEmployees = new List<Employees>();
     List<SqlParameter> lstAttendanceDetail = new List<SqlParameter>();
     lstAttendanceDetail.Add(new SqlParameter("@date", dateTime));
     int i = 0;
     DataSet ds = new DataSet();
     try
     {
         using (DBDataHelper objDDBDataHelper = new DBDataHelper())
         {
             ds = objDDBDataHelper.GetDataSet("spGetEmployeesPresentDateWise", SQLTextType.Stored_Proc, lstAttendanceDetail);
         }
     }
     catch (Exception ex)
     {
         return lstEmployees;
     }
     foreach (DataRow rows in ds.Tables[0].Rows)
     {
         Employees objEmployees = new Employees();
         objEmployees.Id = Convert.ToInt32(ds.Tables[0].Rows[i][0]);
         objEmployees.FirstName = ds.Tables[0].Rows[i][1].ToString();
         objEmployees.MiddleName = ds.Tables[0].Rows[i][2].ToString();
         objEmployees.LastName = ds.Tables[0].Rows[i][3].ToString();
         lstEmployees.Add(objEmployees);
         i++;
     }
     return lstEmployees;
 }

    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 List<TypeOfLeave> LeaveFromGivenMasterLeaveType(int masterLeaveType)
    {
        DBDataHelper.ConnectionString = ConfigurationManager.ConnectionStrings["CSBiometricAttendance"].ConnectionString;
        DBDataHelper helper = new DBDataHelper();
        List<SqlParameter> lstLeaveDetail = new List<SqlParameter>();
        string query = "select Id,Name from tblTypeOfLeave where [email protected]";
        lstLeaveDetail.Add(new SqlParameter("@masterleavetype", masterLeaveType));
        DataTable dt = new DataTable();
        DataSet ds;
        int i = 0;
        using (DBDataHelper objDDBDataHelper = new DBDataHelper())
        {
            ds = objDDBDataHelper.GetDataSet(query, SQLTextType.Query, lstLeaveDetail);
            List<TypeOfLeave> lstLeaveType = new List<TypeOfLeave>();
            foreach (DataRow rows in ds.Tables[0].Rows)
            {
                TypeOfLeave objLeaveType = new TypeOfLeave();
                objLeaveType.Id = Convert.ToInt32(ds.Tables[0].Rows[i][0]);
                objLeaveType.Name = (ds.Tables[0].Rows[i][1]).ToString();
                lstLeaveType.Add(objLeaveType);
                i++;
            }
            return lstLeaveType;
        }

    }

    public List<AssignLeaveViewModel> GetDataForGridview()
    {
        MasterEntries objMasterEntries = new MasterEntries();
        DBDataHelper.ConnectionString = ConfigurationManager.ConnectionStrings["CSBiometricAttendance"].ConnectionString;
        DateTime sessionStartDate = new DateTime();
        DateTime sessionEndDate = new DateTime();
        if (DateTime.Now.Month <= 7)
        {
            sessionStartDate = new DateTime(DateTime.Now.Year - 1, 8, 1);
            sessionEndDate = new DateTime(DateTime.Now.Year, 7, 31);
        }
        else
        {
            sessionStartDate = new DateTime(DateTime.Now.Year, 8, 1);
            sessionEndDate = new DateTime(DateTime.Now.Year + 1, 7, 31);
        }

        List<SqlParameter> lstData = new List<SqlParameter>();
        lstData.Add(new SqlParameter("@employeeId", Convert.ToInt32(Session["employeeId"])));
        lstData.Add(new SqlParameter("@sessionStartDate", sessionStartDate));
        lstData.Add(new SqlParameter("@sessionEndDate", sessionEndDate));
        lstData.Add(new SqlParameter("@IsDeleted", Convert.ToInt32(0)));

        DataSet ds;
        int i = 0;
        string query = @"SELECT Id , LeaveTypeId , [Date] 
                         FROM tblLeave 
                         WHERE [Date]  >= @sessionStartDate
                         AND [Date] <= @sessionEndDate 
                         AND EmployeeId = @employeeId 
                         AND IsDeleted = @IsDeleted";

        using (DBDataHelper objDDBDataHelper = new DBDataHelper())
        {
            ds = objDDBDataHelper.GetDataSet(query, SQLTextType.Query, lstData);
            List<AssignLeaveViewModel> lstLeaves = new List<AssignLeaveViewModel>();
            foreach (DataRow row in ds.Tables[0].Rows)
            {
                AssignLeaveViewModel objLeaves = new AssignLeaveViewModel();
                int Id = Convert.ToInt32(ds.Tables[0].Rows[i][0]);
                int leaveId = Convert.ToInt32(ds.Tables[0].Rows[i][1]);
                Leaves objLeaves1 = new Leaves();
                objMasterEntries.GetLeavesById(leaveId, out objLeaves1);
                objLeaves.leaveId = Id;
                objLeaves.Id = objLeaves1.Id;
                objLeaves.LeaveName = objLeaves1.LeaveName;
                objLeaves.EmployeeId = Convert.ToInt32(Session["employeeId"]);
                objLeaves.Date = (Convert.ToDateTime(ds.Tables[0].Rows[i][2])).Date;
                lstLeaves.Add(objLeaves);
                i++;
            }
            return lstLeaves;
        }
    }

    public bool IsLeaveExist(int employeeId, DateTime date)
    {
        DBDataHelper.ConnectionString = ConfigurationManager.ConnectionStrings["CSBiometricAttendance"].ConnectionString;
        DBDataHelper helper = new DBDataHelper();
        List<SqlParameter> lstLeaveDetail = new List<SqlParameter>();
        lstLeaveDetail.Add(new SqlParameter("@employeeId", employeeId));
        lstLeaveDetail.Add(new SqlParameter("@date", date));

        List<SqlParameter> lstLeaveDetail1 = new List<SqlParameter>();
        lstLeaveDetail1.Add(new SqlParameter("@employeeId", employeeId));
        lstLeaveDetail1.Add(new SqlParameter("@date", date));

        List<SqlParameter> lstLeaveDetail2 = new List<SqlParameter>();
        lstLeaveDetail2.Add(new SqlParameter("@employeeId", employeeId));
        lstLeaveDetail2.Add(new SqlParameter("@date", date));

        List<SqlParameter> lstLeaveDetail3 = new List<SqlParameter>();
        lstLeaveDetail3.Add(new SqlParameter("@employeeId", employeeId));
        lstLeaveDetail3.Add(new SqlParameter("@date", date));

        DataTable dt = new DataTable();
        DataSet ds, ds1, ds2, ds3;
        // string query = "insert into tblLeave values(@employeeId,@leaveTypeId,@createdOn,@updatedOn,@isDeleted)";
        using (DBDataHelper objDDBDataHelper = new DBDataHelper())
        {
            //  ds = objDDBDataHelper.GetDataSet("IsEmployeeOnMultidayLeaveByDate", SQLTextType.Stored_Proc, lstLeaveDetail);
            ds1 = objDDBDataHelper.GetDataSet("spIsEmployeeOnHalfDayLeaveByDate", SQLTextType.Stored_Proc, lstLeaveDetail1);
            ds2 = objDDBDataHelper.GetDataSet("spIsEmployeeOnFullDayLeaveByDate", SQLTextType.Stored_Proc, lstLeaveDetail2);
            ds3 = objDDBDataHelper.GetDataSet("spIsEmployeeOnDurationalLeaveByDate", SQLTextType.Stored_Proc, lstLeaveDetail3);
        }
        int count = Convert.ToInt32(ds1.Tables[0].Rows[0][0]);
        int count1 = Convert.ToInt32(ds1.Tables[0].Rows[0][0]);
        int count2 = Convert.ToInt32(ds2.Tables[0].Rows[0][0]);
        int count3 = Convert.ToInt32(ds3.Tables[0].Rows[0][0]);
        if (count == 1 || count1 == 1 || count2 >= 1 || count3 == 1)
            return false;
        else
            return true;
    }

 /// <summary>
 /// 
 /// </summary>
 /// <param name="departmentId"></param>
 /// <returns></returns>
 public string GetDepartmentById(int departmentId)
 {
     DBDataHelper.ConnectionString = ConfigurationManager.ConnectionStrings["CSBiometricAttendance"].ConnectionString;
     List<SqlParameter> lstDepartment = new List<SqlParameter>();
     lstDepartment.Add(new SqlParameter("@departmentId", departmentId));
     DataTable dt = new DataTable();
     DataSet ds;
     string department;
     using (DBDataHelper objDDBDataHelper = new DBDataHelper())
     {
         ds = objDDBDataHelper.GetDataSet("spGetDepartmentById", SQLTextType.Stored_Proc, lstDepartment);
         department = ds.Tables[0].Rows[0][0].ToString();
     }
     return department;
 }

    protected void btnUpdate_Click(object sender, EventArgs e)
    {
        List<LeaveAssignedRecord> lstLeaveAssignedRecord = new List<LeaveAssignedRecord>();
        DBDataHelper.ConnectionString = ConfigurationManager.ConnectionStrings["CSBiometricAttendance"].ConnectionString;
        DataSet ds;
        int leaveId;
        LeaveAssignedPerSession objLeaveAssignedPerSession = new LeaveAssignedPerSession();
        ManageReports objManageReports = new ManageReports();
        foreach (RepeaterItem i in EditgvLeaves.Items)
        {
            TextBox txtLeaveCount = (TextBox)i.FindControl("txtLeaveCount");
            Label txtLeaveName = (Label)i.FindControl("txtLeave");
            string leaveName = txtLeaveName.Text;
            List<SqlParameter> lstParams = new List<SqlParameter>();
            lstParams.Add(new SqlParameter("@name", leaveName));
            string query = "Select Id from tblTypeOfLeave where Name = @name";
            using (DBDataHelper objDDBDataHelper = new DBDataHelper())
            {
                ds = objDDBDataHelper.GetDataSet(query, SQLTextType.Query, lstParams);
                leaveId = Convert.ToInt32(ds.Tables[0].Rows[0][0]);
            }

            DateTime SessionStartDate, SessionEndDate;
            if (DateTime.Now.Month >= 8)
            {
                SessionStartDate = new DateTime(DateTime.Now.Year, 08, 01);
                SessionEndDate = new DateTime(DateTime.Now.Year+1, 07, 31);
            }
            else
            {
                SessionStartDate = new DateTime(DateTime.Now.Year-1, 08, 01);
                SessionEndDate = new DateTime(DateTime.Now.Year, 07, 31);
            }
            objLeaveAssignedPerSession.EmployeeId = Convert.ToInt32(Session["empId"]);
            objLeaveAssignedPerSession.leaveCount = Convert.ToInt32(txtLeaveCount.Text);
            objLeaveAssignedPerSession.leaveType = leaveId;
            objManageReports.UpdateLeavesAssignedPerSessionEmployeeWise(objLeaveAssignedPerSession, SessionStartDate,SessionEndDate);
        }

        popupEditLeaveAssigned.Hide();

        int departmentId = Convert.ToInt32(ddlShowDepartment.SelectedValue);
        ManageReports objManageReports1 = new ManageReports();
        lstLeaveAssignedRecord = objManageReports1.GetLeavesAssignedPerSession(departmentId, DateTime.Now);
        //grid1.DataSource = lstLeaveAssignedRecord;
        //grid1.DataBind();
    }

 protected void btnAddSession_Click(object sender, EventArgs e)
 {
     int shiftId = Convert.ToInt32(ddlShift.SelectedValue);
     DateTime date = DateTime.Parse(txtDate.Text);
     int employeeId = Convert.ToInt32(txtEmployeeId.Text);
     DBDataHelper.ConnectionString = ConfigurationManager.ConnectionStrings["CSBiometricAttendance"].ConnectionString;
     List<SqlParameter> lstparameter = new List<SqlParameter>();
     lstparameter.Add(new SqlParameter("@shiftId", shiftId));
     lstparameter.Add(new SqlParameter("@date", date));
     lstparameter.Add(new SqlParameter("@employeeId", employeeId));
     string query = "Insert into tblDateWiseShift values(@shiftId,@employeeId,@date)";
     DataTable dt = new DataTable();
     DataSet ds;
     using (DBDataHelper objDDBDataHelper = new DBDataHelper())
     {
         ds = objDDBDataHelper.GetDataSet(query, SQLTextType.Query, lstparameter);
     }
 }

    //public int GetEntryStatus(Attendance objAttendance)
    //{
    //    DBDataHelper.ConnectionString = ConfigurationManager.ConnectionStrings["CSBiometricAttendance"].ConnectionString;
    //    DBDataHelper helper = new DBDataHelper();
    //    List<SqlParameter> lstAttendanceDetail = new List<SqlParameter>();
    //    lstAttendanceDetail.Add(new SqlParameter("@employeeId", objAttendance.EmployeeId));
    //    lstAttendanceDetail.Add(new SqlParameter("@dateTime", objAttendance.Date));
    //    DataTable dt = new DataTable();
    //    DataSet ds;
    //    using (DBDataHelper objDDBDataHelper = new DBDataHelper())
    //    {
    //       ds = objDDBDataHelper.GetDataSet("spGetAttendanceStatus", SQLTextType.Stored_Proc, lstAttendanceDetail);
    //        Employees objEmployee = new Employees();
    //        List<Employees> lstEmployee = new List<Employees>();
    //        return Convert.ToInt32(ds.Tables[0].Rows[0][0]);
    //    }
    //}

    //public int GetExitStatus(Attendance objAttendance)
    //{
    //    DBDataHelper.ConnectionString = ConfigurationManager.ConnectionStrings["CSBiometricAttendance"].ConnectionString;
    //    DBDataHelper helper = new DBDataHelper();
    //    List<SqlParameter> lstAttendanceDetail = new List<SqlParameter>();
    //    lstAttendanceDetail.Add(new SqlParameter("@employeeId", objAttendance.EmployeeId));
    //    lstAttendanceDetail.Add(new SqlParameter("@dateTime", objAttendance.Date));
    //    DataSet ds;
    //    using (DBDataHelper objDDBDataHelper = new DBDataHelper())
    //    {
    //        ds = objDDBDataHelper.GetDataSet("spGetExitStatus", SQLTextType.Stored_Proc, lstAttendanceDetail);
    //    }
    //    var x = ds.Tables[0].Rows[0][0].ToString();
    //    return (String.IsNullOrEmpty(x)) ? 0 : 1;
    //}

    public bool EntryOrExitEmployee(Attendance objAttendance, out int status)
    {
        DBDataHelper.ConnectionString = ConfigurationManager.ConnectionStrings["CSBiometricAttendance"].ConnectionString;
        List<SqlParameter> lstAttendanceDetail = new List<SqlParameter>();
        lstAttendanceDetail.Add(new SqlParameter("@employeeId", objAttendance.EmployeeId));
        lstAttendanceDetail.Add(new SqlParameter("@dateTime", objAttendance.Date));
        DataSet ds = new DataSet();
        try
        {
            using (DBDataHelper objDDBDataHelper = new DBDataHelper())
            {
                ds = objDDBDataHelper.GetDataSet("spAssignAttendance", SQLTextType.Stored_Proc, lstAttendanceDetail);
            }
            status = Convert.ToInt32(ds.Tables[0].Rows[0][0]);
            return true;
        }
        catch(Exception ex)
        {
            status = 0;
            return false;
        }
    }

    /// <summary>
    /// Gives All Departments
    /// </summary>
    /// <returns> List of Department Objects</returns>
    public List<Departments> GetAllDepartments()
    {
        DBDataHelper.ConnectionString = ConfigurationManager.ConnectionStrings["CSBiometricAttendance"].ConnectionString;
        DataTable dt = new DataTable();
        DataSet ds;
        int i = 0;
        using (DBDataHelper objDDBDataHelper = new DBDataHelper())
        {
            ds = objDDBDataHelper.GetDataSet("spGetAllDepartments", SQLTextType.Stored_Proc);
            List<Departments> lstDepartment = new List<Departments>();

            foreach (DataRow rows in ds.Tables[0].Rows)
            {
                Departments objDepartment = new Departments();
                objDepartment.Id = Convert.ToInt32(ds.Tables[0].Rows[i][0]);
                objDepartment.Name = (ds.Tables[0].Rows[i][1]).ToString();
                lstDepartment.Add(objDepartment);
                i++;
            }
            return lstDepartment;
        }
    }

 public List<Employees> GetEmployeesByDepartment(int departmentId)
 {
     DBDataHelper.ConnectionString = ConfigurationManager.ConnectionStrings["CSBiometricAttendance"].ConnectionString;
     DataSet ds;
     using (DBDataHelper objDDBDataHelper = new DBDataHelper())
     {
         List<SqlParameter> lstEmployeeDetail = new List<SqlParameter>();
         lstEmployeeDetail.Add(new SqlParameter("@departmentId", departmentId));
         ds = objDDBDataHelper.GetDataSet("spGetAllEmployeesByDepartment", SQLTextType.Stored_Proc, lstEmployeeDetail);
         List<Employees> lstEmployee = new List<Employees>();
         int i = 0;
         foreach (DataRow rows in ds.Tables[0].Rows)
         {
             Employees objEmployee = new Employees();
             objEmployee.Id = Convert.ToInt32(ds.Tables[0].Rows[i][0]);
             objEmployee.Name = ds.Tables[0].Rows[i][1].ToString();
             objEmployee.RoleId = Convert.ToInt32(ds.Tables[0].Rows[i][2]);
             objEmployee.RoleName = ds.Tables[0].Rows[i][3].ToString();
             lstEmployee.Add(objEmployee);
             i++;
         }
         return lstEmployee;
     }
 }

 public bool UpdateDuration(int durationId, TimeSpan duration) // To be Edited
 {
     DBDataHelper.ConnectionString = ConfigurationManager.ConnectionStrings["CSBiometricAttendance"].ConnectionString;
     List<SqlParameter> lstDuration = new List<SqlParameter>();
     lstDuration.Add(new SqlParameter("@durationId", durationId));
     lstDuration.Add(new SqlParameter("@duration", duration));
     lstDuration.Add(new SqlParameter("@updatedAt", DateTime.Now));
     DataTable dt = new DataTable();
     DataSet ds;
     try
     {
         using (DBDataHelper objDDBDataHelper = new DBDataHelper())
         {
             ds = objDDBDataHelper.GetDataSet("spUpdateDuration", SQLTextType.Stored_Proc, lstDuration);
         }
         return true;
     }
     catch(Exception)
     {
         return false;
     }
 }

    public TimeSpan GetDurationById(int Id)
    {
        DBDataHelper.ConnectionString = ConfigurationManager.ConnectionStrings["CSBiometricAttendance"].ConnectionString;
        DataTable dt = new DataTable();
        DataSet ds;
        List<SqlParameter> lstDurations = new List<SqlParameter>();
        lstDurations.Add(new SqlParameter("@id", Id));
        int i = 0;
        TimeSpan duration = TimeSpan.Parse("00:00");
        using (DBDataHelper objDDBDataHelper = new DBDataHelper())
        {
            ds = objDDBDataHelper.GetDataSet("spGetDurationById", SQLTextType.Stored_Proc, lstDurations);
            List<Duration> lstDuration = new List<Duration>();
            Leaves objLeaves = new Leaves();

            foreach (DataRow rows in ds.Tables[0].Rows)
            {
                duration = (TimeSpan)(ds.Tables[0].Rows[i][2]);
            }
        }
        return duration;
    }

 /// <summary>
 /// Get all  Durations
 /// </summary>
 /// <returns>List of Duration objects</returns>
 public List<Duration> GetAllDurations()
 {
     DBDataHelper.ConnectionString = ConfigurationManager.ConnectionStrings["CSBiometricAttendance"].ConnectionString;
     DataTable dt = new DataTable();
     DataSet ds;
     int i = 0;
     using (DBDataHelper objDDBDataHelper = new DBDataHelper())
     {
         ds = objDDBDataHelper.GetDataSet("spGetAllDurations", SQLTextType.Stored_Proc);
         List<Duration> lstDuration = new List<Duration>();
         Leaves objLeaves = new Leaves();
         foreach (DataRow rows in ds.Tables[0].Rows)
         {
             Duration objDuration = new Duration();
             objDuration.Id = Convert.ToInt32(ds.Tables[0].Rows[i][0]);
             objDuration.leaveId = Convert.ToInt32(ds.Tables[0].Rows[i][1]);
             GetLeavesById(objDuration.leaveId, out objLeaves);
             objDuration.leaveName = objLeaves.LeaveName;
             objDuration.duration = (TimeSpan)(ds.Tables[0].Rows[i][2]);
             lstDuration.Add(objDuration);
             i++;
         }
         return lstDuration;
     }
 }