public List <ReferencePerson> GetAllReferencePerson(ReferencePersonAdvanceSearch referencePersonAdvanceSearch) { return(_referencePersonRepository.GetAllReferencePerson(referencePersonAdvanceSearch)); }
public List <ReferencePerson> GetAllReferencePerson(ReferencePersonAdvanceSearch referencePersonAdvanceSearch) { List <ReferencePerson> referencePersonList = null; try { using (SqlConnection con = _databaseFactory.GetDBConnection()) { using (SqlCommand cmd = new SqlCommand()) { if (con.State == ConnectionState.Closed) { con.Open(); } cmd.Connection = con; cmd.CommandText = "[PSA].[GetAllReferencePerson]"; cmd.Parameters.Add("@SearchValue", SqlDbType.NVarChar, -1).Value = string.IsNullOrEmpty(referencePersonAdvanceSearch.SearchTerm) ? "" : referencePersonAdvanceSearch.SearchTerm.Trim(); cmd.Parameters.Add("@RowStart", SqlDbType.Int).Value = referencePersonAdvanceSearch.DataTablePaging.Start; if (referencePersonAdvanceSearch.DataTablePaging.Length == -1) { cmd.Parameters.AddWithValue("@Length", DBNull.Value); } else { cmd.Parameters.Add("@Length", SqlDbType.Int).Value = referencePersonAdvanceSearch.DataTablePaging.Length; } cmd.Parameters.Add("@ReferenceTypeCode", SqlDbType.Int).Value = referencePersonAdvanceSearch.AdvReferenceTypeCode; cmd.Parameters.Add("@AreaCode", SqlDbType.Int).Value = referencePersonAdvanceSearch.AdvAreaCode; cmd.CommandType = CommandType.StoredProcedure; using (SqlDataReader sdr = cmd.ExecuteReader()) { if ((sdr != null) && (sdr.HasRows)) { referencePersonList = new List <ReferencePerson>(); while (sdr.Read()) { ReferencePerson referencePerson = new ReferencePerson(); { referencePerson.Code = (sdr["Code"].ToString() != "" ? int.Parse(sdr["Code"].ToString()) : referencePerson.Code); referencePerson.Name = (sdr["Name"].ToString() != "" ? sdr["Name"].ToString() : referencePerson.Name); referencePerson.ReferenceType = new ReferenceType(); referencePerson.ReferenceType.Description = (sdr["ReferenceType"].ToString() != "" ? (sdr["ReferenceType"].ToString()) : referencePerson.ReferenceType.Description); referencePerson.Area = new Area(); referencePerson.Area.Description = (sdr["Area"].ToString() != "" ? (sdr["Area"].ToString()) : referencePerson.Area.Description); referencePerson.Organization = (sdr["Organization"].ToString() != "" ? sdr["Organization"].ToString() : referencePerson.Organization); referencePerson.Address = (sdr["Address"].ToString() != "" ? sdr["Address"].ToString() : referencePerson.Address); referencePerson.Email = (sdr["Email"].ToString() != "" ? sdr["Email"].ToString() : referencePerson.Email); referencePerson.PhoneNos = (sdr["PhoneNos"].ToString() != "" ? sdr["PhoneNos"].ToString() : referencePerson.PhoneNos); referencePerson.FaxNos = (sdr["FaxNos"].ToString() != "" ? sdr["FaxNos"].ToString() : referencePerson.FaxNos); referencePerson.GeneralNotes = (sdr["GeneralNotes"].ToString() != "" ? sdr["GeneralNotes"].ToString() : referencePerson.GeneralNotes); // referencePerson.PSASysCommon = new PSASysCommon(); // referencePerson.PSASysCommon.CreatedDateString = (sdr["CreatedDate"].ToString() != "" ? DateTime.Parse(sdr["CreatedDate"].ToString()).ToString(settings.DateFormat) : referencePerson.PSASysCommon.CreatedDateString); referencePerson.TotalCount = (sdr["TotalCount"].ToString() != "" ? int.Parse(sdr["TotalCount"].ToString()) : referencePerson.TotalCount); referencePerson.FilteredCount = (sdr["FilteredCount"].ToString() != "" ? int.Parse(sdr["FilteredCount"].ToString()) : referencePerson.FilteredCount); } referencePersonList.Add(referencePerson); } } } } } } catch (Exception ex) { throw ex; } return(referencePersonList); }