public DataSet FilterData(FilterModel filterData)
{
var connectionString = ConfigurationManager.ConnectionStrings["UCR_DataEntities"];
DataSet dsResult = new DataSet();
using (SqlConnection conn = new SqlConnection(connectionString.ConnectionString))
{
try
{
SqlCommand command = new SqlCommand();
command.Connection = conn;
command.CommandText = GenerateORQuery(filterData);
command.CommandType = System.Data.CommandType.Text;
SqlDataAdapter adapter = new SqlDataAdapter();
adapter.SelectCommand = command;
conn.Open();
adapter.Fill(dsResult);
conn.Close();
adapter.Dispose();
command.Dispose();
}
catch(Exception ex)
{
StorageClient.LogError(ex);
}
}
return dsResult;
}
private string GenerateORQuery(FilterModel filterData)
{
bool useAnd = false;
StringBuilder sbQuery = new StringBuilder("select * from NIBRSData_View");
sbQuery.Append(GetNumValOR(filterData.Year, ref useAnd, "Year"));
sbQuery.Append(GetBetValOR(filterData.MonthFrom, filterData.MonthTo, ref useAnd, "Month"));
sbQuery.Append(GetBetValOR(filterData.HourFrom, filterData.HourTo, ref useAnd, "IncidentHour"));
sbQuery.Append(GetStrValOR(filterData.OffenseName, ref useAnd, "OffenseName"));
sbQuery.Append(GetStrValOR(filterData.OffenseLocation, ref useAnd, "LocationName"));
sbQuery.Append(GetListValOR(filterData.OffenseWeapon, ref useAnd, "WeaponName"));
sbQuery.Append(GetListValOR(filterData.OffensePropertyLoss, ref useAnd, "PropertyLoss"));
sbQuery.Append(GetListValOR(filterData.OffensePropertyDesc, ref useAnd, "PropertyDesc"));
sbQuery.Append(GetListValOR(filterData.OffenseDrug, ref useAnd, "DrugName"));
sbQuery.Append(GetListValOR(filterData.VictimType, ref useAnd, "VictimType"));
sbQuery.Append(GetListValOR(filterData.VictimGender, ref useAnd, "VictimGender"));
sbQuery.Append(GetListValOR(filterData.VictimRace, ref useAnd, "VictimRace"));
sbQuery.Append(GetListValOR(filterData.VictimEthnicity, ref useAnd, "VictimEthnicity"));
sbQuery.Append(GetListValOR(filterData.VictimInjury, ref useAnd, "VictimInjury"));
sbQuery.Append(GetCompValOR(filterData.VictimAge, filterData.VictimAgeComp, ref useAnd, "VictimAge"));
sbQuery.Append(GetListValOR(filterData.OffenderGender, ref useAnd, "OffenderGender"));
sbQuery.Append(GetListValOR(filterData.OffenderRace, ref useAnd, "OffenderRace"));
sbQuery.Append(GetListValOR(filterData.OffenderEthnicity, ref useAnd, "OffenderEthnicity"));
sbQuery.Append(GetListValOR(filterData.OffenderRelation, ref useAnd, "Relationship"));
sbQuery.Append(GetCompValOR(filterData.OffenderAge, filterData.OffenderAgeComp, ref useAnd, "OffenderAge"));
sbQuery.Append(GetListValOR(filterData.ArresteeGender, ref useAnd, "ArrestGender"));
sbQuery.Append(GetListValOR(filterData.ArresteeRace, ref useAnd, "ArresteeRace"));
sbQuery.Append(GetListValOR(filterData.ArresteeEthnicity, ref useAnd, "ArresteeEthnicity"));
sbQuery.Append(GetListValOR(filterData.ArresteeOffense, ref useAnd, "ArrestOffenseName"));
sbQuery.Append(GetListValOR(filterData.ArresteeType, ref useAnd, "ArrestType"));
sbQuery.Append(GetListValOR(filterData.ArresteeWeapon, ref useAnd, "ArrestWeapon"));
sbQuery.Append(GetCompValOR(filterData.ArresteeAge, filterData.ArresteeAgeComp, ref useAnd, "ArresteeAge"));
return sbQuery.ToString();
}