Filter datatable based on another datatable

Reply

eric_tcp Posted: 10-04-2007 2:14 PM

rated by 0 users

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

Thanks. 

Reply

eric_tcp replied on 10-04-2007 4:19 PM

rated by 0 users

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. 

Reply

bronkman replied on 10-04-2007 4:37 PM

rated by 0 users

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

Reply

eric_tcp replied on 10-04-2007 7:31 PM

rated by 0 users

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. 

Reply

bronkman replied on 10-06-2007 12:05 AM

rated by 0 users

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.

Reply

eric_tcp replied on 10-06-2007 10:59 AM

rated by 0 users

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. 

Reply

eric_tcp replied on 10-06-2007 3:36 PM

rated by 0 users

Any example?

Thanks. 

Reply

icelava replied on 10-07-2007 4:12 PM

rated by 0 users

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.

Reply

bronkman replied on 10-08-2007 12:33 AM

rated by 0 users

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.

Reply

eric_tcp replied on 10-08-2007 9:09 AM

rated by 0 users

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

Thanks. 

Reply

eric_tcp replied on 10-08-2007 9:15 AM

rated by 0 users

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. 

Reply

bronkman replied on 10-08-2007 11:45 PM

rated by 0 users

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.

Reply

eric_tcp replied on 10-09-2007 8:45 AM

rated by 0 users

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

Thanks.