Filter datatable based on another datatable

Is it possible to filter datatable based on another datatable or dataset?

 

Thanks. 

You can try using DataTable.Select method or if the tables are related, add a DataRelation and use the GetChildRows method.

Erm can show some example?

Actually I tried to go through the datatable and filter row by row, and found it is too slow. Tried with DataView.Filter, the processing still slow as iterate through the datatable. I am trying to filter data which will be increase, it takes 22 + second to filter only 1000 data.

Thanks. 

Not sure if this would help in the performance, but here are the examples of DataTable.Select and DataRow.GetChildRows from msdn library. In terms of performance, you might be better off if you can do this via sql statement and proper indexes.

'DataTable.Select
Private Sub GetRowsByFilter()
   
    Dim table As DataTable = DataSet1.Tables("Orders")

    ' Presuming the DataTable has a column named Date.
    Dim expression As String
    expression = "Date > #1/1/00#"
    Dim foundRows() As DataRow

    ' Use the Select method to find all rows matching the filter.
    foundRows = table.Select(expression)

    Dim i As Integer
    ' Print column 0 of each returned row.
    For i = 0 to foundRows.GetUpperBound(0)
       Console.WriteLine(foundRows(i)(0))
    Next i
End Sub

'DataRow.GetChildRows
Dim customerOrdersRelation As DataRelation = _
   customerOrders.Relations.Add("CustOrders", _
   customerOrders.Tables("Customers").Columns("CustomerID"), _
   customerOrders.Tables("Orders").Columns("CustomerID"))

Dim custRow, orderRow As DataRow

For Each custRow In customerOrders.Tables("Customers").Rows
    Console.WriteLine("Customer ID:" & custRow("CustomerID").ToString())

    For Each orderRow In custRow.GetChildRows(customerOrdersRelation)
        Console.WriteLine(orderRow("OrderID").ToString())
    Next
Next

Is there anyway without iterating as you know iterate through data more than 1000 is very slow.

Or something like Inner Join in SQL to join 2 datatable? 

Thanks. 

Using Orders and Order Details as an example, you can write:

SELECT * FROM tblOrder INNER JOIN tblOrderDetails ON tblOrderID = tblOrderDetails.OrderID
WHERE tblOrder.OrderID = @OrderID

Using SELECT * IS NOT advisable, for it returns all row. Specify the data columns you require by using tblOrder.OrderID, tblOrder.OrderDate or a table alias like o.OrderID if you write it as ...FROM tblOrder AS o.

You can even also join two (or three) queries of the same table. Check out my answer to your previous post on recursive stored procedure.

Actually what I am trying to do is develop a filtering function based on some logic, this function take in a datatable and filter it and pass back the filter datatable. The problem now is how I can filter the datable in the fastest way? Tried import all the rows from datatable.Select, and datatview.Filter, but still it is slow.

Thanks. 

Not too sure what you are using to make the comparison, but I don't think a DataTable was designed to perform diff comparisons. Without serious consideration (sorry no time), what I'd probably do is index the filter DataTable's rows into a Dictionary<unique key value, DataRow>. Then check each DataRow in the source DataTable to see if a retrieval to the Dictionary<> results in a valid DataRow (from the filter DataTable), or a null (meaning, it does not pass the filter).

Any example?

Thanks. 

Dictionary<string, DataRow> filterList = new Dictionary<string, DataRow>();

foreach (DataRow filterRow in filterTable.Rows)
{
  filterList[filterRow["filterValue"].ToString()] = filterRow;
}

foreach (DataRow sourceRow in sourceTable.Rows)
{
  string filterValue = sourceRow["filterValue"].ToString();

// If the source row's value does not exist in the filtering list, remove it.
  if (filterList[filterValue] == null)
    sourceRow.Delete();
}

// This is assuming it is a simple single value that can be used to determine the filtering.

It really depends on what are you trying to achieve, what is the amount of data you are looking at and how fast you want? Even with dictionary, you can only do so much. If in a web-based anvironment, this will make the server work a lot, and cost memory, and these goes up when you are catering to quite a number of users at the same time. Even in a windows environment, iterating on the client side can be very slow, especially when there really is a lot of data.

Is it really not possible to query the database again? You can have a form that gets the filter criteria and then run the query based on the criteria? It is definitely much faster.

Else, you can have the initial data saved and stored on the client side, and running sql statements against it.

Will this iterating faster than datatview.Filter, which take more than 10 seconds to filter 1000 row of data?

Thanks. 

Yes it is possible to query the database again. But, is there anyway to query against the database without affecting the data passed to the function?

 

My skeleton of application:

In any page, the system will query the database to get what is wanted, then call my vb file to filter.

In my vb file,  take datatable as parameter. Filter the datatable then return the filtered datatable.

 

Thanks. 

I am not sure of the reason why the need to preserve the original datatable. If you like the original data not to be modified, you can always get the funtion to return another DataTable to stored the "filtered" results. You will not be filtering programmatically, but executing that SQL statement with added parameters for filtering.

My filtering function actually is to take off the rows which is not needed. You means function in SQL? Can show some example?

Thanks.