public static CustomList<UserProfile> GetAllUserProfile(String userCode)
 {
     ConnectionManager conManager = new ConnectionManager(ConnectionName.SysMan);
     CustomList<UserProfile> UserProfileCollection = new CustomList<UserProfile>();
     IDataReader reader = null;
     String sql = "select  *from UserProfile Where UserCode='" + userCode + "'";
     try
     {
         conManager.OpenDataReader(sql, out reader);
         while (reader.Read())
         {
             UserProfile newUserProfile = new UserProfile();
             newUserProfile.SetData(reader);
             UserProfileCollection.Add(newUserProfile);
         }
         return UserProfileCollection;
     }
     catch(Exception ex)
     {
         throw (ex);
     }
     finally
     {
         if (reader != null && !reader.IsClosed)
             reader.Close();
     }
 }

 public DataSet LoadMailInfo(Int32 ReportID)
 {
     ConnectionManager conManager = new ConnectionManager(ConName);
     IDataReader reader = null;
     try
     {
         DataSet dsRef = new DataSet();
         const String spName = "spGetMailInfo";
         conManager.OpenDataReader(out reader, spName, ReportID);
         dsRef = Util.DataReaderToDataSet(reader);
         return dsRef;
     }
     catch (Exception ex)
     {
         throw (ex);
     }
     finally
     {
         if (conManager != null)
         {
             conManager.CloseConnection();
             conManager.Dispose();
         }
         if (reader.IsNotNull() && reader.IsClosed.IsFalse())
             reader.Close();
     }
 }

 public static CustomList<LatestNews> GetAllLatestNews()
 {
     ConnectionManager conManager = new ConnectionManager(ConnectionName.HR);
     CustomList<LatestNews> LatestNewsCollection = new CustomList<LatestNews>();
     IDataReader reader = null;
     const String sql = "select *from LatestNews";
     try
     {
         conManager.OpenDataReader(sql, out reader);
         while (reader.Read())
         {
             LatestNews newLatestNews = new LatestNews();
             newLatestNews.SetData(reader);
             LatestNewsCollection.Add(newLatestNews);
         }
         LatestNewsCollection.InsertSpName = "spInsertLatestNews";
         LatestNewsCollection.UpdateSpName = "spUpdateLatestNews";
         LatestNewsCollection.DeleteSpName = "spDeleteLatestNews";
         return LatestNewsCollection;
     }
     catch (Exception ex)
     {
         throw (ex);
     }
     finally
     {
         if (reader != null && !reader.IsClosed)
             reader.Close();
     }
 }

 public static CustomList<GroupRule> GetAllGroupSecurityRule(string groupCode)
 {
     ConnectionManager conManager = new ConnectionManager(ConnectionName.SysMan);
     CustomList<GroupRule> GroupSecurityRuleCollection = new CustomList<GroupRule>();
     String sql = "Select  *from GroupRule where GroupCode='" + groupCode + "'";
     IDataReader reader = null;
     try
     {
         conManager.OpenDataReader(sql, out reader);
         while (reader.Read())
         {
             GroupRule newGroupSecurityRule = new GroupRule();
             newGroupSecurityRule.SetData(reader);
             GroupSecurityRuleCollection.Add(newGroupSecurityRule);
         }
         reader.Close();
         GroupSecurityRuleCollection.InsertSpName = "spInsertGroupRule";
         GroupSecurityRuleCollection.SelectSpName = "spDeleteGroupRule";
         return GroupSecurityRuleCollection;
     }
     catch (Exception ex)
     {
         throw (ex);
     }
     finally
     {
         if (reader != null && !reader.IsClosed)
             reader.Close();
     }
 }

        public void DeleteSecurityRule(ref CustomList<RuleDetails> objSecurityRuleDetailList, ref CustomList<SecurityRule> securityRuleList)
        {
            ConnectionManager conManager = new ConnectionManager(ConnectionName.SysMan);
            Boolean blnTranStarted = false;

            try
            {
                ReSetSPNameForDelete(securityRuleList, objSecurityRuleDetailList);

                conManager.BeginTransaction();
                blnTranStarted = true;
                conManager.SaveDataCollectionThroughCollection(blnTranStarted, objSecurityRuleDetailList, securityRuleList);
                objSecurityRuleDetailList.AcceptChanges();
                securityRuleList.AcceptChanges();
                conManager.CommitTransaction();
                blnTranStarted = false;
                conManager.Dispose();
            }
            catch (Exception ex)
            {
                conManager.RollBack();
                throw (ex);
            }
            finally
            {
                if (conManager.IsNotNull())
                {
                    conManager.Dispose();
                }
            }
        }

 public static CustomList<Configuration> GetAllConfiguration()
 {
     ConnectionManager conManager = new ConnectionManager(ConnectionName.SysMan);
     CustomList<Configuration> ConfigurationCollection = new CustomList<Configuration>();
     const String sql = "Select *from tblConfiguration";
     IDataReader reader = null; ;
     try
     {
         conManager.OpenDataReader(sql, out reader);
         while (reader.Read())
         {
             Configuration newConfiguration = new Configuration();
             newConfiguration.SetData(reader);
             ConfigurationCollection.Add(newConfiguration);
         }
         reader.Close();
         ConfigurationCollection.SelectSpName = "spSelectConfiguration";
         ConfigurationCollection.InsertSpName = "spInsertConfiguration";
         ConfigurationCollection.UpdateSpName = "spUpdateConfiguration";
         ConfigurationCollection.SelectSpName = "spDeleteConfiguration";
         return ConfigurationCollection;
     }
     catch (Exception ex)
     {
         throw (ex);
     }
     finally
     {
         if (reader != null && !reader.IsClosed)
             reader.Close();
     }
 }

        public static CustomList<ParameterFilterValue> GetReportValue(string parent)
        {
            ConnectionManager conManager = new ConnectionManager(ConnectionName.HR);
            CustomList<ParameterFilterValue> ParameterFilterValueCollection = new CustomList<ParameterFilterValue>();
            IDataReader reader = null;
            String sql = "select OrgKey As ActualValues, OrgName As DisplayMember, OrgName As [Values]  from Gen_Org Where OrgParentKey in(" + parent + ")";//'" + userCode + "'";
            try
            {
                conManager.OpenDataReader(sql, out reader);
                while (reader.Read())
                {
                    ParameterFilterValue newParameterFilterValue = new ParameterFilterValue();
                    newParameterFilterValue.SetData(reader);
                    ParameterFilterValueCollection.Add(newParameterFilterValue);
                }
                return ParameterFilterValueCollection;
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                if (conManager != null)
                {
                    conManager.CloseConnection();
                    conManager.Dispose();
                }

                if (reader != null && !reader.IsClosed)
                    reader.Close();
            }
        }

        public void SaveMailSetup(CustomList<MailSetup> MailSetupList)
        {
            ConnectionManager conManager = new ConnectionManager(ConnectionName.HR);
               Boolean blnTranStarted = false;

               try
               {
               conManager.BeginTransaction();
               blnTranStarted = true;
               ReSetSPName(ref MailSetupList);

               conManager.SaveDataCollectionThroughCollection(blnTranStarted, MailSetupList);

               MailSetupList.AcceptChanges();

               conManager.CommitTransaction();
               blnTranStarted = false;
               conManager.Dispose();
               }
               catch (Exception Ex)
               {
               conManager.RollBack();
               throw Ex;
               }
               finally
               {
               if (conManager.IsNotNull())
               {
                   conManager.Dispose();
               }
               }
        }

 public static CustomList<ItemSubGroup> GetAllItemSubGroup()
 {
     ConnectionManager conManager = new ConnectionManager(ConnectionName.HR);
     CustomList<ItemSubGroup> ItemSubGroupCollection = new CustomList<ItemSubGroup>();
     IDataReader reader = null;
     const String sql = "select S.ItemGroupID, S.ItemSubGroupID, S.SubGroupName from ItemGroup I inner join ItemSubGroup S on S.ItemGroupID=I.ItemGroupID order by I.GroupName desc";
     try
     {
         conManager.OpenDataReader(sql, out reader);
         while (reader.Read())
         {
             ItemSubGroup newItemSubGroup = new ItemSubGroup();
             newItemSubGroup.SetData(reader);
             ItemSubGroupCollection.Add(newItemSubGroup);
         }
         ItemSubGroupCollection.InsertSpName = "spInsertItemSubGroup";
         ItemSubGroupCollection.UpdateSpName = "spUpdateItemSubGroup";
         ItemSubGroupCollection.DeleteSpName = "spDeleteItemSubGroup";
         return ItemSubGroupCollection;
     }
     catch (Exception ex)
     {
         throw (ex);
     }
     finally
     {
         if (reader != null && !reader.IsClosed)
             reader.Close();
     }
 }

 public static CustomList<Signature> GetSignatureForUniqueCode(String sql)
 {
     ConnectionManager conManager = new ConnectionManager(ConnectionName.SysMan);
     CustomList<Signature> signatureCollection = new CustomList<Signature>();
     IDataReader reader = null;
     try
     {
         conManager.OpenDataReader(sql, out reader);
         if (reader.NextResult())
         {
             while (reader.Read())
             {
                 Signature newSignature = new Signature();
                 newSignature.SetData(reader);
                 signatureCollection.Add(newSignature);
             }
         }
         return signatureCollection;
     }
     catch (Exception ex)
     {
         throw (ex);
     }
     finally
     {
         if (reader != null && !reader.IsClosed)
             reader.Close();
     }
 }

        public void SaveBankAccount(ref CustomList<CmnBankAccount> BankAccountList)
        {
            ConnectionManager conManager = new ConnectionManager(ConnectionName.HR);
               Boolean blnTranStarted = false;

               try
               {
               conManager.BeginTransaction();

               ReSetSPName(BankAccountList);
               Int32 accountID = BankAccountList[0].AccountID;
               blnTranStarted = true;
               if (BankAccountList[0].IsAdded)
                   accountID = Convert.ToInt32(conManager.InsertData(blnTranStarted, BankAccountList));
               else
                   conManager.SaveDataCollectionThroughCollection(blnTranStarted, BankAccountList);

               BankAccountList.AcceptChanges();

               conManager.CommitTransaction();
               blnTranStarted = false;
               }
               catch (Exception Ex)
               {
               conManager.RollBack();
               throw Ex;
               }
               finally
               {
               if (conManager.IsNotNull())
               {
                   conManager.Dispose();
               }
               }
        }

 public static CustomList<UserGroupList> GetAllUserGroupWithUserCode(string userCode)
 {
     ConnectionManager conManager = new ConnectionManager(ConnectionName.SysMan);
     CustomList<UserGroupList> UserGroupListCollection = new CustomList<UserGroupList>();
     String sql = "Exec spUserGroupList'" + userCode + "'";
     IDataReader reader = null;
     try
     {
         conManager.OpenDataReader(sql, out reader);
         while (reader.Read())
         {
             UserGroupList newUserGroup = new UserGroupList();
             newUserGroup.SetData(reader);
             UserGroupListCollection.Add(newUserGroup);
         }
         reader.Close();
         return UserGroupListCollection;
     }
     catch (Exception ex)
     {
         throw (ex);
     }
     finally
     {
         if (reader != null && !reader.IsClosed)
             reader.Close();
     }
 }

        public void SaveFiscalYear(ref CustomList<Gen_FY> Gen_FYList)
        {
            ConnectionManager conManager = new ConnectionManager(ConnectionName.HR);
            Boolean blnTranStarted = false;

            try
            {
                conManager.BeginTransaction();

                ReSetSPName(Gen_FYList);

                blnTranStarted = true;

                conManager.SaveDataCollectionThroughCollection(blnTranStarted, Gen_FYList);
                //object scope_Identity = conManager.InsertData(blnTranStarted, BankKist);

                Gen_FYList.AcceptChanges();

                conManager.CommitTransaction();
                blnTranStarted = false;
                conManager.Dispose();
            }
            catch (Exception Ex)
            {
                conManager.RollBack();
                throw Ex;
            }
            finally
            {
                if (conManager.IsNotNull())
                {
                    conManager.Dispose();
                }
            }
        }

        public void DeleteVoucher(ref CustomList<Acc_VoucherDet> VoucherDetList, ref CustomList<Acc_Voucher> VoucherList)
        {
            ConnectionManager conManager = new ConnectionManager(ConnectionName.HR);
            Boolean blnTranStarted = false;

            try
            {
                ReSetSPName(ref VoucherList, ref VoucherDetList);

                conManager.BeginTransaction();
                blnTranStarted = true;
                conManager.SaveDataCollectionThroughCollection(blnTranStarted, VoucherDetList, VoucherList);
                conManager.CommitTransaction();
                VoucherDetList.AcceptChanges();
                VoucherList.AcceptChanges();
            }
            catch (Exception ex)
            {
                conManager.RollBack();
                throw (ex);
            }
            finally
            {
                if (conManager.IsNotNull())
                {
                    blnTranStarted = false;
                    conManager.Dispose();
                }
            }
        }

        public static Int32 GetUniqueCodeWithoutSignature(ref  ConnectionManager objCon, Boolean requiredTransaction, String strTableName, String strFieldName,
            String strPrefix)
        {
            String sql = "";
            Int32 strMaxId = 0;

            try
            {
                sql = "Exec spGetIDWithoutSignature '" +
                    strTableName + "','" + strFieldName + "','" +
                    strPrefix + "'";

                IDataReader reader;
                objCon.OpenDataReader(sql, out reader, CommandBehavior.Default, requiredTransaction);
                while (reader.Read())
                {
                    strMaxId = Convert.ToInt32(reader["MaxId"]);
                }

                reader.Close();
                return strMaxId;

            }
            catch (Exception ex)
            {
                throw (ex);
            }
        }

        public void SaveBankReconciliation(ref CustomList<Acc_Voucher> VoucherList)
        {
            ConnectionManager conManager = new ConnectionManager(ConnectionName.HR);
            Boolean blnTranStarted = false;

            try
            {
                conManager.BeginTransaction();
                VoucherList.UpdateSpName = "spUpdateAcc_VoucherBankReconciliation";
                blnTranStarted = true;

                conManager.SaveDataCollectionThroughCollection(blnTranStarted, VoucherList);

                VoucherList.AcceptChanges();

                conManager.CommitTransaction();
                blnTranStarted = false;
            }
            catch (Exception Ex)
            {
                conManager.RollBack();
                throw Ex;
            }
            finally
            {
                if (conManager.IsNotNull())
                {
                    conManager.Dispose();
                }
            }
        }

        public void DeleteHKEntry(ref CustomList<HouseKeepingValue> HKList, ref CustomList<HousekeepingHierarchy> lstChild)
        {
            ConnectionManager conManager = new ConnectionManager(ConnectionName.HR);
            Boolean blnTranStarted = false;
            try
            {
                conManager.BeginTransaction();

                ReSetSPName(HKList, lstChild);

                blnTranStarted = true;

                conManager.SaveDataCollectionThroughCollection(blnTranStarted, lstChild, HKList);

                lstChild.AcceptChanges();
                HKList.AcceptChanges();

                conManager.CommitTransaction();
                blnTranStarted = false;
                conManager.Dispose();
            }
            catch (Exception Ex)
            {
                conManager.RollBack();
                throw Ex;
            }
            finally
            {
                if (conManager.IsNotNull())
                {
                    conManager.Dispose();
                }
            }
        }

        public void SaveUnitSetup(ref CustomList<ItemSubGroup> lstItemSubGroup)
        {
            ConnectionManager conManager = new ConnectionManager(ConnectionName.HR);
            Boolean blnTranStarted = false;

            try
            {
                conManager.BeginTransaction();

                ReSetSPName(lstItemSubGroup);

                blnTranStarted = true;

                conManager.SaveDataCollectionThroughCollection(blnTranStarted, lstItemSubGroup);

                lstItemSubGroup.AcceptChanges();

                conManager.CommitTransaction();
                blnTranStarted = false;
                conManager.Dispose();
            }
            catch (Exception Ex)
            {
                conManager.RollBack();
                throw Ex;
            }
            finally
            {
                if (conManager.IsNotNull())
                {
                    conManager.Dispose();
                }
            }
        }

 public static CustomList<EmployeeHKInfo> GetAllEmployeeHKInfo(Int64 empKey)
 {
     ConnectionManager conManager = new ConnectionManager(ConnectionName.HR);
     CustomList<EmployeeHKInfo> EmployeeHKInfoCollection = new CustomList<EmployeeHKInfo>();
     IDataReader reader = null;
     String sql = "select *from EmployeeHKInfo where EmpKey=" + empKey;
     try
     {
         conManager.OpenDataReader(sql, out reader);
         while (reader.Read())
         {
             EmployeeHKInfo newEmployeeHKInfo = new EmployeeHKInfo();
             newEmployeeHKInfo.SetData(reader);
             EmployeeHKInfoCollection.Add(newEmployeeHKInfo);
         }
         EmployeeHKInfoCollection.InsertSpName = "spInsertEmployeeHKInfo";
         EmployeeHKInfoCollection.UpdateSpName = "spUpdateEmployeeHKInfo";
         EmployeeHKInfoCollection.DeleteSpName = "spDeleteEmployeeHKInfo";
         return EmployeeHKInfoCollection;
     }
     catch(Exception ex)
     {
         throw (ex);
     }
     finally
     {
         if (reader != null && !reader.IsClosed)
             reader.Close();
     }
 }

 public static CustomList<UnitSetup> GetAllUnitSetup()
 {
     ConnectionManager conManager = new ConnectionManager(ConnectionName.HR);
     CustomList<UnitSetup> UnitSetupCollection = new CustomList<UnitSetup>();
     IDataReader reader = null;
     const String sql = "select *from UnitSetup";
     try
     {
         conManager.OpenDataReader(sql, out reader);
         while (reader.Read())
         {
             UnitSetup newUnitSetup = new UnitSetup();
             newUnitSetup.SetData(reader);
             UnitSetupCollection.Add(newUnitSetup);
         }
         UnitSetupCollection.InsertSpName = "spInsertUnitSetup";
         UnitSetupCollection.UpdateSpName = "spUpdateUnitSetup";
         UnitSetupCollection.DeleteSpName = "spDeleteUnitSetup";
         return UnitSetupCollection;
     }
     catch (Exception ex)
     {
         throw (ex);
     }
     finally
     {
         if (reader != null && !reader.IsClosed)
             reader.Close();
     }
 }