public List <State> GetAllState(StateAdvanceSearch stateAdvanceSearch) { List <State> stateList = 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].[GetAllState]"; cmd.Parameters.Add("@SearchValue", SqlDbType.NVarChar, -1).Value = string.IsNullOrEmpty(stateAdvanceSearch.SearchTerm) ? "" : stateAdvanceSearch.SearchTerm; cmd.Parameters.Add("@RowStart", SqlDbType.Int).Value = stateAdvanceSearch.DataTablePaging.Start; if (stateAdvanceSearch.DataTablePaging.Length == -1) { cmd.Parameters.AddWithValue("@Length", DBNull.Value); } else { cmd.Parameters.Add("@Length", SqlDbType.Int).Value = stateAdvanceSearch.DataTablePaging.Length; } cmd.CommandType = CommandType.StoredProcedure; using (SqlDataReader sdr = cmd.ExecuteReader()) { if ((sdr != null) && (sdr.HasRows)) { stateList = new List <State>(); while (sdr.Read()) { State state = new State(); { state.Code = (sdr["Code"].ToString() != "" ? int.Parse(sdr["Code"].ToString()) : state.Code); state.Description = (sdr["Description"].ToString() != "" ? sdr["Description"].ToString() : state.Description); state.PSASysCommon = new PSASysCommon(); state.PSASysCommon.CreatedDateString = (sdr["CreatedDate"].ToString() != "" ? DateTime.Parse(sdr["CreatedDate"].ToString()).ToString(settings.DateFormat) : state.PSASysCommon.CreatedDateString); state.TotalCount = (sdr["TotalCount"].ToString() != "" ? int.Parse(sdr["TotalCount"].ToString()) : state.TotalCount); state.FilteredCount = (sdr["FilteredCount"].ToString() != "" ? int.Parse(sdr["FilteredCount"].ToString()) : state.FilteredCount); state.Country = new Country(); state.Country.Description = (sdr["Country"].ToString() != "" ? sdr["Country"].ToString() : state.Country.Description); //state.CountryCode = (sdr["CountryCode"].ToString() != "" ? int.Parse(sdr["CountryCode"].ToString()) : state.CountryCode); } stateList.Add(state); } } } } } } catch (Exception ex) { throw ex; } return(stateList); }
public List <State> GetAllState(StateAdvanceSearch stateAdvanceSearch) { return(_stateRepository.GetAllState(stateAdvanceSearch)); }