locked
DataView of a DataView RRS feed

  • Question

  •  Hi everyone,

    I have a situation where I current use DataTable.Select to get a subset of data from a base datatable. There are a few levels of "subsetting" so I might do a select on the base datatable to produce an intermediate table and then do another select on this table to produce the final child data. I may perform this nested selecting a number of times for one step in my application and I am finding it quite slow (I originally cached the data at each select step, however I found that the memory usage of the application increased significantly). I am now considering using DataViews into the base datatable, however I am not sure I can do this kind of nested "dataviewing", ie. can I base my final child dataview on an intermediate dataview?

    My only guess at how to do it would be to somehow combine the filter and sort of the intermediate dataview with the child requirements and create a dataview on the base datatable again, however it would be much easier if it were possible to base a dataview on another dataview.

    Regards,

    Mark.

    Thursday, July 17, 2008 8:21 AM

All replies

  • Hi Mark,

    I haven't tried it in this way, but if you're using .Net 3.5 you might be able to use LINQ to do something like:

                DataTable peopleTable = null
     
                var people = peopleTable.Select(); 
     
                var adults = 
                    from person in people 
                    where ((int)person["Age"]) >= 19 
                    select person; 
     
                var women = 
                    from adult in adults 
                    where adult["Gender"].ToString() == "f" 
                    select adult; 
     
                var mothers = 
                    from woman in women 
                    where ((int)woman["NumberOfChildren"]) > 0 
                    select woman; 
     
                foreach (DataRow row in mothers) 
                    Console.WriteLine(string.Concat(row["Lastname"], ", ", row["firstName"])); 
     

    If it did work, I would expect it to be quite light in its use of resources.

    Cheers,

    John
    Thursday, July 17, 2008 7:46 PM
  • Hi John,

    Thanks for your reply and suggestion. Unfortunately we're stuck with 2.0 at the moment as we still have some Windows 2000 machines. This may change in the near future so we may be able to move to 3.5 and take advantage of some of the new features.

    Thanks,

    Mark.
    Saturday, July 19, 2008 7:33 AM
  • Hi John,

    Just as follow up, I found a sample similar to the one you suggested using Linq at http://msdn.microsoft.com/en-us/library/bb386998.aspx. I tried using this as a test using some of my application code, however I kept getting an InvalidCastException when trying to produce a DataTable from the filtered rows. I will search on why this error may be occuring.

    Mark.
    Monday, July 21, 2008 12:56 AM
  • Hi  Mark,

    Thanks for the updates,  good luck getting it working.  This might not help but there are couple of methods .ToArray() and .CopyToTable() that you might want to consider.

    Cheers,

    John

    var mothers = parents.Intersect(women); 
    DataTable newTable = mothers.CopyToDataTable(); 
    DataRow[] array = mothers.ToArray(); 
     

    Monday, July 21, 2008 6:24 AM
  • Hi John, thanks again for your reply.

    Actually CopyToDataTable() is the code that seems to be causing me an InvalidCastException. I have not had a chance to investigate what is going on (or most likely what I am doing wrong) but I will follow up as soon as I can.

    Mark.
    Tuesday, July 22, 2008 1:57 AM
  • is there a particular reason that you are doing everything in memory?  Is there a way that you can leverage the database to get better performance?
    Mitchel Sellers, MCITP, MCPD, MCTS - http://www.mitchelsellers.com
    Tuesday, July 22, 2008 2:17 PM
  • Hi Mitchel,

    Thanks for your reply. My application is distributed and uses remoting, so I make one remoting call to the server to hit the database and return a DataTable. From there the user can view a subset of the data depending on what they want to see. I find the remoting call cost higher than selecting/filtering the datatable.

    Regards,

    Mark.

    Tuesday, July 22, 2008 11:35 PM