When I have a smaller rows return from my sql stored procedure, I tend to think of not recreating another stored procedure to do filtering or to use optional parameter. My idea is to do filtering from the code base and not recreating/adding the stored procedure.
The idea was to create a generic stored procedure without any filter and apply the filter from the code base. The example given is by applying filter to dataset and then after that add the datatable to dataset and then cast it back again to the datatable.
public tdsEvent.EventSummaryDataTable TodayEventSummaryFilter(string filter)
{
tdsEvent.EventSummaryDataTable table = TodayEventSummaryCache();
DataRow[] rows = table.Select(filter); //apply the filter first
if (rows.Length != 0)
{
DataSet ds = new DataSet();
tdsEvent.EventSummaryDataTable eventTable = new tdsEvent.EventSummaryDataTable();
ds.Tables.Add(eventTable); // add to the filter
ds.Merge(rows, false, MissingSchemaAction.Ignore);
// cast it back to the data table
table = ds.Tables[0] as tdsEvent.EventSummaryDataTable;
}
else
{
table = new tdsEvent.EventSummaryDataTable();
}
return table;
}
This is the alternative code which doing the same thing as above
Dim table as tdsEvent.EventSummaryDataTable = TodayEventSummaryCache() //create dataview instance based on datatable dim dv as DataView = new DataView(table) //create the filter to select the valid only dv.RowFilter = "Valid = 1" //bind to the grid or repeater rptEvent.Datasource = dv rptEvent.DataBind()