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