.NET Conversions (vs 2003) - DataTable CSV Export

DataTable CSV Export - C# to VB.NET

Synopsis:

The code listed below is a simple class used to save / export an ado.net dataTable to CSV(comma separated values) format. CSV is simply a comma delimited format. To use the "How to Use" portion of the code replace the sql statement and the connectionstring text with valid information. (The original article and C# code can be found at http://www.dotnetspider.com/kb/Article963.aspx and was submitted by Abishek Bellamkonda)

Solution:

Download : 
complete VS solution

DataTableHelper.vb

Imports System
Imports System.Data
Imports System.IO

Public Class DataTableHelper

    ' 
    ' Can stream DataTable to Browser, directly, you need to set 
    ' 
    ' Response.Clear();
    ' Response.Buffer= true;
    ' Response.ContentType = "application/vnd.ms-excel";
    ' Response.AddHeader("Content-Disposition", "inline;filename=Clientes.xls"); 
    '	Response.Charset = "";
    ' this.EnableViewState = false
    '  ACTUAL CODE 
    ' ProduceCSV(dt, Response.Output, true);
    ' 

    Public Shared Sub ProduceCSV(ByVal dt As DataTable, _
	ByVal httpStream As System.IO.TextWriter, ByVal WriteHeader As Boolean)
        Dim i As Int32
        Dim j As Int32
        If WriteHeader Then
            
            Dim arr(dt.Columns.Count) As String

            For i = 0 To dt.Columns.Count - 1
                arr(i) = dt.Columns(i).ColumnName
                arr(i) = GetWriteableValue(arr(i))
            Next
            httpStream.WriteLine(String.Join(",", arr))
        End If

        For j = 0 To dt.Rows.Count - 1
            Dim dataArr(dt.Columns.Count) As String
            For i = 0 To dt.Columns.Count - 1
                Dim o As Object = dt.Rows(j)(i)
                dataArr(i) = GetWriteableValue(o)
            Next
            httpStream.WriteLine(String.Join(",", dataArr))
        Next

    End Sub

#Region "CSVProducer"
    Public Shared Sub ProduceCSV(ByVal dt As DataTable, _
	ByVal file As System.IO.StreamWriter, ByVal WriteHeader As Boolean)

        Dim i As Int32
        Dim j As Int32
        If (WriteHeader) Then
            Dim arr(dt.Columns.Count) As String
            For i = 0 To dt.Columns.Count - 1
                arr(i) = dt.Columns(i).ColumnName
                arr(i) = GetWriteableValue(arr(i))
            Next
            file.WriteLine(String.Join(",", arr))
        End If

        For j = 0 To dt.Rows.Count - 1
            Dim dataArr(dt.Columns.Count) As String
            For i = 0 To dt.Columns.Count
                Dim o As Object = dt.Rows(j)(i)
                dataArr(i) = GetWriteableValue(o)
            Next
            file.WriteLine(String.Join(",", dataArr))
        Next
    End Sub

    Public Shared Function GetWriteableValue(ByVal o As Object) As String
        If o Is Nothing OrElse IsDBNull(o) Then
            Return ""
        ElseIf (o.ToString().IndexOf(",") = -1) Then
            Return o.ToString()
        Else
            Return "\"" + o.ToString() + " \ ""

        End If
    End Function
#End Region

end class

How To Use: (Windows Form with one Button named btnTest and Northwind sample database)

 Private Sub btnTest_Click(ByVal sender As System.Object, _
	 ByVal e As System.EventArgs) Handles btnTest.Click
        
	Dim conn As New SqlClient.SqlConnection
        Dim da As New SqlClient.SqlDataAdapter
        Dim cmdSel As New SqlClient.SqlCommand("Select * FROM PRODUCTS")
        Dim ds As New DataSet



        conn.ConnectionString = "connectionstringinfo+initial catalog=Northwind"
        da.SelectCommand = cmdSel
        cmdSel.Connection = conn

        conn.Open()
        da.Fill(ds)


        Dim dt As DataTable = ds.Tables(0)
        
        DataTableHelper.ProduceCSV(dt, Console.Out, True)
    End Sub