Putting a datatable into a dataset problem

[Elfish]

I am trying to put a datatable into a dateset, which I then export to a CSV file. I am getting this error

DataTable already belongs to another DataSet. on the following line dsExport.Tables.Add(dtExport)

Using dtExport As DataTable = DirectCast(dgvSafetyGlasses.DataSource, DataTable)
            Using dsExport As New DataSet()
                dsExport.Tables.Add(dtExport)
                ExportDatasetToCsv(dsExport)
            End Using
        End Using

What am I doing wrong?

[jmcilhinney]

As the error message suggests, you're trying to put the table into a DataSet when it's already in another DataSet. Why can't you use the existing DataSet? If there is a legitmate reason then you'll need to either remove the table from the original DataSet first or else create a copy to put in the new DataSet.

That said, do you even need the DataSet? Can you not just export the DataTable to a CSV? I'm guessing that your export code only works on one table anyway so what's the DataSet actually for?

[Elfish]

I don't think I can use the existing Dataset because I use a filter on the grid and I only way to export the data that is currently in the grid. I did try to use the datatable as it was but I couldn't get the code right in the sub. Although I think I have it now. I will post back if I get it to work correctly.

[jmcilhinney]

If you're filtering and/or sorting data then you should be getting your data to export from the DataTable's DefaultView rather than from its Rows collection. Sorting and filtering affects only the DataView, not the DataTable.

[Elfish]

It will not export after I filter the data out and I read where you said I need to use the default view but I am not exactly sure where to put it. I can put it where I have it commented out below but I can run and test because I error on this error Unable to cast object of type 'System.Data.DataView' to type 'System.Data.DataTable'. on this line dtExport As DataTable = DirectCast(dgvSafetyGlasses.DataSource, DataTable). I am pretty confident that this is the line I need to change but I haven't got it figured out quite yet.

'
    Private Sub exportFromDatagrid()
        If My.Computer.FileSystem.FileExists(FilePath) = True Then
            My.Computer.FileSystem.DeleteFile(FilePath)
        End If
        '
        Using dtExport As DataTable = DirectCast(dgvSafetyGlasses.DataSource, DataTable)
            ExportDataTableToCsv(dtExport)
        End Using
 
        My.Computer.FileSystem.WriteAllText(FilePath, str.ToString, True)
        System.Diagnostics.Process.Start("notepad.exe", FilePath)
    End Sub
 
    Private Sub ExportDataTableToCsv(ByVal dt As DataTable)
        For Each dr As DataRow In dt.Rows 'dt.defaultview
            For Each field As Object In dr.ItemArray
                str.Append(field.ToString & ",")
            Next
            str.Replace(",", vbNewLine, str.Length - 1, 1)
        Next
    End Sub

[Elfish]

Should I continue with this and convert it into a Dataview

Using dtExport As DataTable = DirectCast(dgvSafetyGlasses.DataSource, DataTable)
            ExportDataTableToCsv(dtExport)
        End Using

Or do I need to start all over with a Dataview as my first step (Datagridview > Dataview)?

[jmcilhinney]

Using a DataView is very easy. Every DataTable already has a DataView associated with it by default, which you access via its DefaultView property. In fact, when you bind a DataTable to a control, the data you see is actually from the DefaultView. That's how, for example, you're able to bind a DataTable to a DataGridView and sort it. A Datatable can't be sorted, but a DataView can.

So, when binding, you should generally just go ahead and bind your DataTable. There's no need to bind the DefaultView because that's where the data comes from anyway. In your case, if you want to export the filtered and sorted data then you should rewrite your ExportDataTableToCsv method. Either change it to ExportDataViewToCsv and make the parameter type DataView, or else use the table's DefaultView inside the method. So, if your method was currently like this:

Private Sub ExportDataTableToCsv(ByVal table As DataTable)
    Using writer As New IO.StreamWriter("file path here")
        For rowIndex As Integer = 0 To table.Rows.Count - 1
            Dim row As DataRow = table.Rows(rowIndex)
 
            'Write a line break before all but the first line.
            If rowIndex > 0 Then
                writer.WriteLine()
            End If
 
            For columnIndex As Integer = 0 To table.Columns.Count - 1
                'Write a comma before all but the first field.
                If columnIndex > 0 Then
                    writer.Write(",")
                End If
 
                If table.Columns(columnIndex).DataType Is GetType(String) Then
                    'Quote text values.
                    writer.Write("""{0}""", row(columnIndex))
                Else
                    writer.Write(row(columnIndex))
                End If
            Next
        Next
    End Using
End Sub

then you should change it to this:

Private Sub ExportDataViewToCsv(ByVal view As DataView)
    Using writer As New IO.StreamWriter("file path here")
        For rowIndex As Integer = 0 To view.Count - 1
            Dim row As DataRowView = view(rowIndex)
 
            'Write a line break before all but the first line.
            If rowIndex > 0 Then
                writer.WriteLine()
            End If
 
            For columnIndex As Integer = 0 To view.Table.Columns.Count - 1
                'Write a comma before all but the first field.
                If columnIndex > 0 Then
                    writer.Write(",")
                End If
 
                If view.Table.Columns(columnIndex).DataType Is GetType(String) Then
                    'Quote text values.
                    writer.Write("""{0}""", row(columnIndex))
                Else
                    writer.Write(row(columnIndex))
                End If
            Next
        Next
    End Using
End Sub

or this:

Private Sub ExportDataTableToCsv(ByVal table As DataTable)
    Using writer As New IO.StreamWriter("file path here")
        For rowIndex As Integer = 0 To table.DefaultView.Count - 1
            Dim row As DataRowView = table.DefaultView(rowIndex)
 
            'Write a line break before all but the first line.
            If rowIndex > 0 Then
                writer.WriteLine()
            End If
 
            For columnIndex As Integer = 0 To table.Columns.Count - 1
                'Write a comma before all but the first field.
                If columnIndex > 0 Then
                    writer.Write(",")
                End If
 
                If table.Columns(columnIndex).DataType Is GetType(String) Then
                    'Quote text values.
                    writer.Write("""{0}""", row(columnIndex))
                Else
                    writer.Write(row(columnIndex))
                End If
            Next
        Next
    End Using
End Sub