This is a free "Helper Method" to convert a SQL query to CSV format for easy output to a file, the console, or via a HttpResponse (for you ASP.NETers out there looking for an easy way to dynamically output a CSV for real-time data download).  You have a lot of control over the output by specifying which columns (from the query) you want to include, "friendly" captions for each column, and a format string for controlling the look of the output data.

Just provide the query in the form of a DataReader (the method closes it for you), provide column properties via a jagged array (an array of arrays) representing column name/caption/format, and finally provide a TextWriter into which the (double-quoted) CSV is written out.  Why a TextWriter?  Because it gives you a lot of flexibility as to where you want your data to be output.  For instance, Console.Out, HttpResponse.Output, and StreamWriter (for writing out to a file) are all TextWriters!  It sounds a little complicated, but it will become much simpler with an usage example --  the first method shown is NUnit test code (even if you don't use NUnit) should illustrate how easy it is to use (assumes QueryToCSV() method is placed in a "Utility" class):

<Test()> Public Sub Output_QueryToCSV()

    Dim names() As String = {"ID", "FirstName", "LastName", "CreditBalance"}
    Dim captions() As String = {"User ID", "First Name", "Last Name", "Credit Balance"}
    Dim formats() As String = {"G", "G", "G", "F"} '.NET Format Specifiers -- search "Formatting Overview" in .NET help
    Dim colProperties()() As String = {names, captions, formats} 'array of arrays (a.k.a. jagged array)

    Dim conn As New SqlConnection(ConfigurationSettings.AppSettings("ConnectionString"))
    Dim cmd As New SqlCommand("SELECT * FROM Users", conn)
    conn.Open()
    Dim reader As SqlDataReader

    'write directly to console
    reader = cmd.ExecuteReader()
    Utility.QueryToCSV(reader, colProperties, Console.Out)

    'write to file
    reader = cmd.ExecuteReader()
    Dim sw As StreamWriter = New StreamWriter("c:\temp\TestFile.csv")
    Utility.QueryToCSV(reader, colProperties, sw)
    Console.Out.WriteLine()
    Console.Out.WriteLine("Wrote out to file c:\temp\TestFile.csv")
    Console.Out.WriteLine()

    'write to string
    reader = cmd.ExecuteReader()
    Dim stw As New StringWriter
    Utility.QueryToCSV(reader, colProperties, stw)
    Console.Out.WriteLine(stw.ToString())

  End Sub

  Public Shared Sub QueryToCSV(ByVal dr As IDataReader, ByVal colProperties As String()(), ByVal tw As TextWriter)

    If colProperties.Length > 0 Then

      Dim counter As Integer = 0

      'write out column names
      tw.WriteLine("""" & String.Join(""",""", colProperties(1)) & """")

      'write out rows
      While dr.Read()

        counter = 0

        tw.Write("""")

        For Each column As String In colProperties(0)

          tw.Write(String.Format("{0:" & colProperties(2)(counter) & "}", dr(column)))
          If counter < colProperties(0).Length - 1 Then
            tw.Write(""",""")
          End If

          counter += 1

        Next

        tw.WriteLine("""")

      End While

      dr.Close()
      tw.Close()

    End If

  End Sub

If you found this post helpful, please "Kick" it so others can find it too: