Create useful extensions to DataTables to save time and re-use code

[kanaida]

I wrote the module below, and often include it in projects to save CSV files, etc... this is how simple to make your own:

just call DataTable.OneOfMyExtensionMethods()

It's nice as they are generic to any datatable.
I save a csv file with My.computer.filesystem.WriteAllText(DataTable.csv()) for example.

Imports System.Text
Imports System.Runtime.CompilerServices
Imports System.ComponentModel
Imports System.IO
Imports System.IO.Compression

Public Module DataTableExtensions

'Create a CSV formatted string that you can save as a file
    <Extension()> _
    Public Function CSV(ByVal Expression As DataTable) As String
        Dim sb As New StringBuilder
        For Each col As DataColumn In Expression.Columns
            Dim Comma As String = IIf(Expression.Columns.IndexOf(col) + 1 = Expression.Columns.Count, "", ",")
            sb.Append(col.ColumnName & Comma)
        Next
        sb.Append(vbCrLf)

        For Each Row As DataRow In Expression.Rows
            For Each C As DataColumn In Expression.Columns
                Dim Comma As String = IIf(Expression.Columns.IndexOf(C) + 1 = Expression.Columns.Count, "", ",")
                sb.Append(Row.Item(C.ColumnName) & Comma)
            Next

            If Not Expression.Rows.IndexOf(Row) + 1 = Expression.Rows.Count Then
                sb.Append(vbCrLf)
            End If

        Next
        Return sb.ToString
    End Function


'A nice way to get specific errors from datatable rows
<Extension()> _
    Public Sub WriteErrorsToConsole(ByVal Expression As DataTable, ByVal Ex As Exception)
        Console.ForegroundColor = ConsoleColor.Red
        Console.WriteLine("ERRORS FOUND IN " & Expression.TableName.ToUpper & " TABLE.")
Console.WriteLine("Fix the items below:")

        Console.ForegroundColor = ConsoleColor.Yellow

        Dim LastRowError As String
        For Each _ERROR In Expression.GetErrors

            If Not LastRowError = _ERROR.RowError Then
                Dim ErrorToDisplay As String = _ERROR.RowError
                If ErrorToDisplay.Contains("is already present") Then
                    ErrorToDisplay = ErrorToDisplay.Replace(" is already present.", " DELETE DUPLICATE RECORDS")
                    ErrorToDisplay = ErrorToDisplay.Replace(" is constrained to be unique.", vbCrLf)
                End If

                Console.WriteLine(ErrorToDisplay)
            End If
            LastRowError = _ERROR.RowError
        Next
        Console.ForegroundColor = ConsoleColor.Gray
        Console.WriteLine("ERROR MESSAGE: " & Ex.Message)
    End Sub

'Useful for web services, if network is slow
    <Extension()> _
    Public Function ToCompressedStream(ByVal Expression As DataSet) As GZipStream
        Dim stream As New MemoryStream()
        Dim binaryFormatter As New Runtime.Serialization.Formatters.Binary.BinaryFormatter()
        binaryFormatter.Serialize(stream, Expression)

        Dim C As New System.IO.Compression.GZipStream(stream, Compression.CompressionMode.Compress)

        stream.Seek(0, 0)
        Return C

    End Function

'Useful for web services, if network is slow
    <Extension()> _
 Public Function FromCompressedStream(ByVal Expression As DataSet, ByVal CompressedStream As GZipStream) As DataSet
        Dim stream As New GZipStream(CompressedStream, CompressionMode.Decompress)

        Dim binaryFormatter As New Runtime.Serialization.Formatters.Binary.BinaryFormatter()
        Dim result As DataSet = binaryFormatter.Deserialize(stream, Nothing)
        'Expression = result
        Return result

    End Function
End Module

 

[cjard]

It really bugs me when people try to get other people to use badly broken code without a conscience for what theyre actually proposing..

Congratulations that you can write the contents of a datatable to csv by interspersing all the data with commas, but you have clearly given no thought to the condition that the data itself can contain commas.. Don't just add quotes, because not every CSV is delimited using quotes, and the data itself can contain quotes..

Hopefully this is an eye opener that a problem can be much more complex than it first appears and that proposing a quick and dirty solution that's applicable to your case only, might not be that helpful..

 

[kanaida]

the point of the thread

I'm sure the data can contain all kinds of characters, the program i happen to use this with does validation during data entry to not allow those characters so i don't have to worry about it (so yes believe me i know about that). That part however is up to you guys to modify if you decide to use that exact example. There's a million ways to screw up a csv.

None the less, the point was not wether it exports a csv really well under all conditions. It's trying to show people real world examples of how to "extend" built in objects in a way that's very simple and can be used from project to project with ease, and modified in one place.

 

[Tom]

An additional suggestion; its good that your using a string builder instead of concatenating strings but its even more efficient and faster to create seperate append statements then concatenating within the string builder append itself.

sb.Append(Row.Item(C.ColumnName) & Comma)

To:

sb.Append(Row.Item(C.ColumnName))
sb.Append(Comma)

Or

sb.AppendFormat("{0}{1}", Row.Item(C.ColumnName), Comma)

Although I havent done any speed tests using this last example to say if this runs more efficiently then the former 2 examples or not.