How do I use Multiple controls using the same ObjectDataSource, but with different filters

asp.net, c#, c#-4.0, webforms

Solution

If you're focused on not having to make multiple SQL calls, one option would be to use LINQ to query your `DataSet` in your `SelectMethod`. There may be a more elegant way to do this syntactically (I couldn't figure one out) but this should provide your desired functionality using a single `ObjectDataSource`.

If your `ObjectDataSource` declaration looks like:

<asp:ObjectDataSource ID="myObjectDataSource" runat="server" SelectMethod="myObjectDataSource_Select" OnFiltering="myObjectDataSource_Filtering">

In your `SelectMethod` you could do something like:

public DataSet myObjectDataSource_Select()
{
   string sqlQuery = "SELECT col1, col2, col3 FROM foo";
   SqlDataAdapter da = new SqlDataAdapter(sqlQuery, myConnectionString);
   DataSet ds = new DataSet();
   using (da) {
        da.Fill(ds);
   }

   //Perform your secondary filtering here
   object [] unfilteredQuery= (from r in ds.Tables[0].AsEnumerable()
        select r.Field<string>(“col1”)).ToArray();
   myUnfilteredComboBox.Items.Clear();
   myUnfilteredComboBox.Items.AddRange(unfilteredQuery);

   return ds;    
}

Problem

I'm playing with optimization in ASP.Net WebForms. in my case I have 2 dropdowns and a grid. I want the dropdowns to act as a filter for the grid, each containing a distinct list of the data in one column of the grid (to use as a filter) That's fine.. I got it working, but I can't use the same datasource as the grid for the dropdowns anymore because I'm applying a filterexpression to the datasource to filter whats in the grid. since its the same datasource as the dropdowns, I get a smaller distinct list in the dropdown. now I can use multiple datasources, each consuming the same data object, but I see in Sql Profiler that 2 data calls are made, but I'd really like to use the same one so I can have a single data call. is it even possible to have a single ObjectDataSource be filtered for the grid, and at the same time and provide unfiltered data for another control?

Original source