MSSQL export as CSV

mssql-csv.aspx.vb

Imports CompanyLibrary
Imports System.Data.DataTable
Imports System.Data.SqlClient
Imports System.Data.Sql
Imports System.Data.SqlTypes
Imports System.Configuration

Partial Class viewcsv
    Inherits System.Web.UI.Page
    Dim db As New CompanyDataContext

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        If Not Page.IsPostBack Then
            BindCompanies()
        End If
    End Sub

    Private Sub BindCompanies()
		'ConfigurationManager.ConnectionStrings("tps_write").ConnectionString()
    Dim constr As String = "Data Source=DB_IP;Initial Catalog=DB_NAME;uid=DB_USER;pwd=DB_PASSWORD;" 'ConfigurationManager.ConnectionStrings("constr").ConnectionString
       Using con As New SqlConnection(constr)
    Using cmd As New SqlCommand("SELECT * FROM Table_name")
            Using sda As New SqlDataAdapter()
                cmd.Connection = con
                sda.SelectCommand = cmd
                Using dt As New System.Data.DataTable()
                    sda.Fill(dt)
 
                    'Build the CSV file data as a Comma separated string.
                    Dim csv As String = String.Empty
 
                    For Each column As System.Data.DataColumn In dt.Columns
                        'Add the Header row for CSV file.
                        csv += column.ColumnName + ","c
                    Next
 
                    'Add new line
                    csv += vbCr & vbLf
 
                    For Each row As System.Data.DataRow In dt.Rows
                        For Each column As System.Data.DataColumn In dt.Columns
                            'Add the Data rows
                            csv += """" + row(column.ColumnName).ToString().Replace(vbCr, "").Replace(vbLf, "").Replace("""", """""") + """" + ","c
                        Next
 
                        'Add new line
                        csv += vbCr & vbLf
                    Next
 
                    'Download the CSV file.
                    Response.Clear()
                    Response.Buffer = True
                    Response.AddHeader("content-disposition", "attachment;filename=Data.csv")
                    Response.Charset = ""
                    Response.ContentType = "application/text"
                    Response.Output.Write(csv)
                    Response.Flush()
                    Response.End()
                End Using
            End Using
        End Using
    End Using
    End Sub

End Class