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);
}
