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

Question

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?

Answer 1

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

Answer 2

Are you using Linq? If yes, that should be possible:

// Loading complete data into object
var myCompleteDataSource = (from c in ctx select c).ToList();

// Filtering the already loaded data
var myFilteredDataSource = myCompleteDataSource.Where(o=>o.MyField=="abc").ToList(); 

And then just set the datasources to your objects.

That will load your data only one time (the first .ToList() method) from the db and filter just the object list into the second object without accessing the db again.

Answer 3

As you said "since its the same datasource as the dropdowns, I get a smaller distinct list in the dropdown."

You can keep the Database Information in the ViewState. In this way, you can prevent the Request to Database for your client request. Thus Reducing the Access time.

Example

public DataTable Employees
{
    get
    {
        if (ViewState["Employees"] == null)
        {
            return FollowsDAL.GetAllEmployees();
        }
        return (DataTable)ViewState["Employees"];
    }
    set
    {
        ViewState["Employees"] = value;
    }
}

---

How can I make the ViewState Data filtering fast ?

Answer is - Please don't use Update Panel. I will use Page Method.

Please check the example below. I used Update Panel with Script Manager.

---

Output

To display the 22 character string you can check how much data is being received and sent to server. Just imagine following

1. If you would consider send each request to Database using Update Panel and your GridView is in Update Panel!!!!!!
2. If you would use ViewState data for each request and with GridView Inside the Update Panel.

Both the above techniques are worst as per my understanding.

---

Now I will describe you Page Methods

Page Method over Update panel

Page methods allow ASP.NET AJAX pages to directly execute a Page’s Static Methods, using JSON (JavaScript Object Notation). Instead of posting back and then receiving HTML markup to completely replace our UpdatePanel’s contents, we can use a web method to request only the information that we’re interested.

Sample Code

---

Output

---

So conclusion is that I will definitely use ViewState BUT with Page Methods.

Filtering Technique for on the ViewState Data.

public static class GetFilteredData
{
    public static DataTable FilterDataTable(this DataTable Dt, 
                                                         string FilterExpression)
    {
        using (DataView Dv = new DataView(Dt))
        {
            Dv.RowFilter = FilterExpression;
            return Dv.ToTable();
        }
    }
}

Example

DataTableObject.FilterDataTable("Search Expression")

---

Hi vpiTriumph... I found a bit improvement in the code. Below is the suggested approach.

Sample code in C Sharp

private void DsataBaseInteraction()
{
    using (SqlConnection con = new SqlConnection("Your Connection String"))
    {
        using (SqlCommand cmd = new SqlCommand())
        {
            cmd.Connection = con;
            cmd.CommandType = System.Data.CommandType.StoredProcedure;
            cmd.CommandText = "Your Stored Procedure name";
            using (SqlDataReader DR = cmd.ExecuteReader())
            {

            }
        }
    }
}

---

@KevinDeus - I am assuming that the Database being used is SQL Server. So below mentioned is my suggestion for Stored Procedure in Database.

Create Proc ProcedureName
@UserName Varchar(50),
@Password Varchar(50),
@Email Varchar(50)
As
SET NOCOUNT ON
SET XACT_ABORT ON

Begin Try
    Begin Tran
        Insert into Account (Username,Password, Email)
        Values(@UserName, @Password, @Email)
    Commit Tran 
End Try

Begin Catch
    Rollback Tran
End Catch

---

Reference is Here and Here