How to Get Subset DataTable from Main DataTable, but Preserve Primary Key & RowErrors?

Question

I came across a problem with using a BindingSource as my DataGridViews.DataSource.  Whenever I applied a filter to a column in the BindingSource and the user makes changes that don't match the column filter the DataGridViewRows would automatically disappear.  A similar thing would happen when applying a Sort to a column.  If the user made any changes the DridGirdViewRows would automatically sort causing rows to be moved around.  This was not ideal for my application and there isn't anyway to stop this from happening with the BindingSource.

To correct this issue I have to use subsets of data.  I use a DataView to apply the filter and sort to the main DataTable, which creates the subset DataTable.  The problem is when I use the DataView.ToTable method I loose the Primary Key and RowError information.  So I have to reapply this information everytime the user filters or sorts the DataGridView.  Is there a better way to get a subset DataTable?

' get the subset data table from the original table
dtCurrentSubset = New DataView(Me.ds.Tables("Current"), filter, sort, DataViewRowState.CurrentRows).ToTable

' Set primary key for table so it can be used to Merge the subset table with the original table.
' The primary key does not transfer from the original DataTable thru DataView to the new DataTable.
dtCurrentSubset.PrimaryKey = New DataColumn() {dtCurrentSubset.Columns("FinalResultID")}

' get the rows that contain errors in the master table and transfer the row errors if the row exists in the subset
If Me.ds.Tables("Current").HasErrors Then
For Each dr As DataRow In Me.ds.Tables("Current").GetErrors
' see if dr is in the subset, if so add the error
Dim rw As DataRow = dtCompletedSubset.Rows.Find(Me.ds.Tables("Current").PrimaryKey)
If rw IsNot Nothing Then rw.RowError = dr.RowError
Next dr
End If

' AcceptChanges because all rows are marked as Added when creating the subset table from the dataview
dtCurrentSubset.AcceptChanges()

' set the new table as the binding sources datasource
Me.dgvCurrent.DataSource = dtCurrentSubset

Thanks,

---

Ryan

Answer 1

Ryan,

A dataView is a table with references to the original table, the copy (To means almost forever a deep copy) is completely new.

Why not using that DataView for what you want by removing the ToTable (and then call it dvCurrentSubSet)

An item of the dataview is a datarowview

---

Success
Cor

Answer 2

I wish it was that simple.  I have tried using a DataView object in the past as you suggested as my DataGridView.DataSource, but I get the same behavior as the BindingSource which I described in my original post.  This is why I have to create a copy then reapply the Primary Key and RowErrors.  Any other ideas?

I'm surprised there is not a way to suspend the DataView filter and sort as the user makes changes to the DataGirdView.  MS Excel allows you to edit a filtered column without automatically filtering out rows.  The filter doesn't get reapplied till the user reapplies the filter.

Thanks,

---

Ryan

Answer 3

You would have bind the dataview to the DataGridView (datasource), if you want to do this.

The manipulations like sorting from the user are stored in that one.  

Or even better,  the build in dataview of every datatable the Defaultview property.

---

Success
Cor

Answer 4

My apologies, I don't think I understand your suggestion.  Let me give you an example of my problem.  Create a Form, add a Button and DataGridView to the form, then place this code in the Button_Click event.

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

        ' create a datatable
        Dim dtMaster As New DataTable
        dtMaster.Columns.Add(New DataColumn("Letters", GetType(String)))

        For i As Integer = 0 To 2
            Dim rw As DataRow = dtMaster.NewRow
            rw("Letters") = "A" & i
            dtMaster.Rows.Add(rw)
        Next i

        Dim sort As String = "Letters ASC"
        Dim filter As String = "Letters LIKE 'A%'"

        With Me.DataGridView1
            .AutoGenerateColumns = False
            .Columns.Add("clmLetters", "Letters")
            .Columns("clmLetters").DataPropertyName = "Letters"
        End With

        ' get the subset of the original table
        Dim dv As DataView = New DataView(dtMaster, filter, sort, DataViewRowState.CurrentRows)
        Me.DataGridView1.DataSource = dv

    End Sub

Once, you click the button, try changing the first row from "A0" to "A3".  See how the column is automatically sorted again?  Then try changing "A3" to "B".  See how the row disappears?

I don't want the DataGridView to automatically re-sort or filter until the user changes the sort or filter variables.  Can this be done?  Can you show me a code example of your suggestion?

Thanks,

---

Ryan

Answer 5

Many other possibilities

Public Class Form1
    Private dv As DataView
    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        ' create a datatable
        Dim dtMaster As New DataTable
        dtMaster.Columns.Add(New DataColumn("Letters", GetType(String)))
        For i As Integer = 0 To 2
            Dim rw As DataRow = dtMaster.NewRow
            rw("Letters") = "A" & i
            dtMaster.Rows.Add(rw)
        Next i
        With Me.DataGridView1
            .AutoGenerateColumns = False
            .Columns.Add("clmLetters", "Letters")
            .Columns("clmLetters").DataPropertyName = "Letters"
        End With

        ' get the subset of the original table
        dv = New DataView(dtMaster, "", "", DataViewRowState.CurrentRows)
        Me.DataGridView1.DataSource = dv

    End Sub

    Private Sub Button2_Click(sender As System.Object, e As System.EventArgs) Handles Button2.Click
        dv.Sort = "Letters ASC"
        dv.RowFilter = "Letters LIKE 'A%'"

    End Sub

    Private Sub Button3_Click(sender As System.Object, e As System.EventArgs) Handles Button3.Click
        dv.Sort = ""
        dv.RowFilter = ""
    End Sub

    Private Sub Button4_Click(sender As System.Object, e As System.EventArgs) Handles Button4.Click
        Dim dt = dv.ToTable
    End Sub
End Class

---

Success
Cor

Answer 6

But now you are using the DataView.ToTable method, which I was using in my original post.  I guess there is not a way to answer this question directly and a workaround is needed.

Maybe I will have to use the ToTable method to create a subset, then when the sort or filter changes I'll have to merge the subset with the master table using DataTable.Merge method to ensure dtMaster gets any changes the user did to dtSubset.

Thanks,

---

Ryan

Answer 7

I tested your sample, the reply you gave that you did nothing with what I made for you only looked at it.

I use the toTable at the end of a the 4th button.

Be aware in your own sample is not a primary key set, so that will not be copied.

But that are enough seconds I've spent now on your problem. You want no solution.

---

Success
Cor

Answer 8

Hi Ryan,

Nice to see you.

How about this idea?

1. Make a deep copy: http://msdn.microsoft.com/en-us/library/system.data.datatable.copy.aspx 

2. Remove the columns which is unnecessary: http://msdn.microsoft.com/en-us/library/tcdez2aw.aspx 

3. Remove the rows which is unnecessary: http://msdn.microsoft.com/en-us/library/system.data.datarowcollection.remove.aspx 

I hope this will be helpful.

Best regards,

---

Mike Feng
MSDN Community Support | Feedback to us
Please remember to mark the replies as answers if they help and unmark them if they provide no help.

Answer 9

Edited my previous reply

---

Success
Cor

Answer 10

Thanks Mike, I forgot about the DataTable.Clone method.  This will help me get the table schema for the subset table from the master table schema.  This way I can apply the primary key to my subset.  I'm still loosing the RowError data but, I guess I'm stuck looping through the error rows of the master table and applying them to my subset.  This may not be a big deal because the master table won't usually have many row errors.

Public Class Form1
    Private dtMaster As DataTable
    Private clsDFS As New DataGridViewFilterAndSorter

    Public Sub GetSubset()

        Dim dtSubset As New DataTable

        Call clsDFS.SetDGVDataSource(dtMaster, dtSubset)

    End Sub
End Class


Public Class DataGridViewFilterAndSorter

    Private dv As DataView
    Private dtMaster As DataTable
    Private dtSubset As DataTable
    Private strSort As String
    Private strFilter As String

    Public SetDGVDataSource(ByRef dtMaster As DataTable, ByRef dtSubset As DataTable)

            ' run this block of code when the DataGridView does not have a DataSource yet
            If Me.dv Is Nothing Then
                Me.dtMaster = dtMaster

                ' apply the schema of the master to the subset
                dtSubset = Me.dtMaster.Clone
                Me.dv = New DataView(Me.dtMaster, Nothing, Nothing, DataViewRowState.CurrentRows)
            End If

            ' apply the current filter and sort strings
            With Me.dv
                .Sort = Me.strSort
                .RowFilter = Me.strFilter
            End With

            ' add the filtered and sorted data rows to the subset DataTable
            dtSubset.Merge(dv.ToTable)

            ' AcceptChanges because all rows are marked as Added when using the DataView.ToTable method
            dtSubset.AcceptChanges()

    End Sub
End Class

---

Ryan