public String GetStates(String searchTerm) { // Create JavaScript Serializer object. JavaScriptSerializer jss = new JavaScriptSerializer(); // Filter by state code and name. StateFilter filter = new StateFilter() { Code = searchTerm, Name = searchTerm }; // Load the states into a list. List <State> states = State.LoadCollection(filter); // Return with a JSON string containing results. return(jss.Serialize(states)); }
/// <summary> /// Load a single state. /// </summary> /// <param name="connectionString">The connection string to the database.</param> /// <param name="filter">The filter used to select states.</param> /// <returns></returns> public static State Load(StateFilter filter) { List <State> states = State.LoadCollection(filter); return(states.Count > 0 ? states[0] : null); }
/// <summary> /// Load a list of states from the database. /// </summary> /// <param name="connectionString">The connection string to the database.</param> /// <param name="filter">The filter used to select states.</param> /// <returns></returns> public static List <State> LoadCollection(StateFilter filter) { List <State> states = new List <State>(); SqlCommand cmd = new SqlCommand(); StringBuilder select = new StringBuilder(1000); if (!String.IsNullOrEmpty(filter.Code)) { select.Append("SM.state_ansi_code LIKE '%' + @state_ansi_code + '%'"); cmd.Parameters.Add(new SqlParameter("@state_ansi_code", SqlDbType.NVarChar) { Value = filter.Code }); } if (!String.IsNullOrEmpty(filter.Name)) { if (select.Length > 0) { select.Append(" OR "); } select.Append("SM.state_name LIKE '%' + @state_name + '%'"); cmd.Parameters.Add(new SqlParameter("@state_name", SqlDbType.NVarChar) { Value = filter.Name }); } if (!String.IsNullOrEmpty(filter.Capital)) { if (select.Length > 0) { select.Append(" OR "); } select.Append("SM.state_capital LIKE '%' + @state_capital + '%'"); cmd.Parameters.Add(new SqlParameter("@state_capital", SqlDbType.NVarChar) { Value = filter.Capital }); } if (!String.IsNullOrEmpty(filter.LargestCity)) { if (select.Length > 0) { select.Append(" OR "); } select.Append("SM.state_largest_city LIKE '%' + @state_largest_city + '%'"); cmd.Parameters.Add(new SqlParameter("@state_largest_city", SqlDbType.NVarChar) { Value = filter.LargestCity }); } if (!String.IsNullOrEmpty(filter.LargestMetro)) { if (select.Length > 0) { select.Append(" OR "); } select.Append("SM.state_largest_metro LIKE '%' + @state_largest_metro + '%'"); cmd.Parameters.Add(new SqlParameter("@state_largest_metro", SqlDbType.NVarChar) { Value = filter.LargestMetro }); } select.Insert(0, String.Format("SELECT * FROM State_Master SM {0} ", cmd.Parameters.Count > 0 ? "WHERE" : String.Empty)); select.Append(" ORDER BY SM.state_name"); cmd.CommandText = select.ToString(); using (SqlConnection cn = new SqlConnection(HConfig.DBConnectionString)) { using (DataTable dt = HDatabase.FillDataTable(cn, cmd)) { foreach (DataRow dr in dt.Rows) { states.Add(new State(dr)); } } } return(states); }