How to use multiple controls using the same ObjectDataSource, but with different filters

Question

I play with optimization in ASP.Net WebForms.

in my case, I have two dropdowns and a grid.

I want the drop-down lists to act as a filter for the grid, each of which contains a separate list of data in one grid column (for use as a filter).

This is good .. I got it working, but I can’t use the same data source as the grid for drop-down lists, because I apply a filter expression to the data source to filter information in the grid.

since it has the same data source as the drop-down lists, I get a smaller separate list in the drop-down list.

now I can use several data sources, each of which uses the same data object, but I see in Sql Profiler that 2 data accesses are done, but I really would like to use the same one so that I can have one call data.

Is it possible for one ObjectDataSource to be filtered for the grid and at the same time provide unfiltered data for another control?

Answer 1

If you focus on not making 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 could not understand what one of them is), but this should provide the desired functionality using one ObjectDataSource .

If your ObjectDataSource declaration looks like this:

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

In your SelectMethod you can 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

Do you use Linq? If so, this 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 data sources for your objects.

This will load your data only once (the first .ToList() method) from db and filter only the list of objects in the second object, without accessing the db again.

Answer 3

As you said, "from the same data sources as the drop-down lists, I get a smaller separate list in the drop-down list."

You can save Database Information to ViewState . This way you can prevent 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 to quickly filter ViewState data?

The answer is do not use the Update Panel . I will use the Page Method .

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

---

Exit

To display a 22 character string, you can check how much data is received and sent to the server. Imagine the following

• If you want to send each request to the database using the update panel, and your GridView is in the Update Panel !!!!!!
• If you use ViewState data for each request and GridView inside Update Panel .

Both of the above methods are the worst in my understanding.

---

Now I will describe you Page Methods

Method Page Above Update Panel

Page methods enable ASP.NET AJAX pages to directly execute 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 the web method to request only the information of interest.

Code example

---

Exit

---

So, the conclusion is that I will definitely use ViewState BUT with Page methods .

Filter method for 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 of improvement in the code. The proposed approach is presented below.

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 assume that Database SQL Server . Below is my suggestion for the 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 

---

Link Here and Here