VB.Net MySQL save datatable as CSV


Imports System
Imports System.Collections.Generic
Imports System.ComponentModel
Imports System.Data
Imports System.Drawing
Imports System.Linq
Imports System.Text
Imports System.Windows.Forms
Imports System.Data.SqlClient
Imports System.IO
Public Class Form1

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim Csv As New Exporter()
        Dim c As New common
        Using CsvWriter As New StreamWriter(Application.StartupPath() & "\" & "milestones.csv")
            Dim q = "SELECT m.project,projectcode1,m.unit,milestone,ext_sch_date,int_sch_date,act_ant_date FROM `Projects_ntpc` p, Units u , " & _
              " MileStones m WHERE p.projectname = u.project and u.project = m.project and u.unit = m.unit and u.completed !=0 and  u.zerodate is not null"
            CsvWriter.Write(Csv.CsvFromDatatable(c.executeDB(q)))
        End Using
        ' System.Diagnostics.Process.Start("C:\TestFile.csv")
        MessageBox.Show("done")
    End Sub


    

    'Private Function GetSampleData() As DataTable
    '    Dim con As New SqlConnection("Data Source=.\SQLEXPRESS;Initial Catalog=Northwind;Persist Security Info=True;Integrated Security = True")
    '    Dim CategoryAdapter As New SqlDataAdapter("SELECT CategoryID,CategoryName,Description FROM Categories", con)
    '    Dim ProductData As New DataSet()
    '    CategoryAdapter.Fill(ProductData, "Categories")
    '    Return ProductData.Tables(0)
    'End Function

End Class

Public Class Exporter
    Public Sub New()
        TextDelimiter = ","c
        TextQualifiers = """"c
        HasColumnHeaders = True
    End Sub
    Private _TextDelimiter As Char
    Public Property TextDelimiter() As Char
        Get
            Return _TextDelimiter
        End Get
        Set(ByVal value As Char)
            _TextDelimiter = value
        End Set
    End Property
    Private _TextQualifiers As Char
    Public Property TextQualifiers() As Char
        Get
            Return _TextQualifiers
        End Get
        Set(ByVal value As Char)
            _TextQualifiers = value
        End Set
    End Property
    Private _HasColumnHeaders As Boolean
    Public Property HasColumnHeaders() As Boolean
        Get
            Return _HasColumnHeaders
        End Get
        Set(ByVal value As Boolean)
            _HasColumnHeaders = value
        End Set
    End Property
    Public Function CsvFromDatatable(ByVal InputTable As DataTable) As String
        Dim CsvBuilder As New StringBuilder()
        If HasColumnHeaders Then
            CreateHeader(InputTable, CsvBuilder)
        End If
        CreateRows(InputTable, CsvBuilder)
        Return CsvBuilder.ToString()
    End Function
    Private Sub CreateRows(ByVal InputTable As DataTable, ByVal CsvBuilder As StringBuilder)
        For Each ExportRow As DataRow In InputTable.Rows
            For Each ExportColumn As DataColumn In InputTable.Columns
                Dim ColumnText As String = ExportRow(ExportColumn.ColumnName).ToString()
                ColumnText = ColumnText.Replace(TextQualifiers.ToString(), TextQualifiers.ToString() + TextQualifiers.ToString())
                CsvBuilder.Append(TextQualifiers + ColumnText + TextQualifiers)
                CsvBuilder.Append(TextDelimiter)
            Next
            CsvBuilder.AppendLine()
        Next
    End Sub
    Private Sub CreateHeader(ByVal InputTable As DataTable, ByVal CsvBuilder As StringBuilder)
        For Each ExportColumn As DataColumn In InputTable.Columns
            Dim ColumnText As String = ExportColumn.ColumnName.ToString()
            ColumnText = ColumnText.Replace(TextQualifiers.ToString(), TextQualifiers.ToString() + TextQualifiers.ToString())
            CsvBuilder.Append(TextQualifiers + ExportColumn.ColumnName + TextQualifiers)
            CsvBuilder.Append(TextDelimiter)
        Next
        CsvBuilder.AppendLine()
    End Sub
End Class

common.vb

Imports MySql.Data.MySqlClient
Imports System.IO


Public Class common

    Public connE As New MySqlConnection


    Public Sub ConnectEntertrackDatabase()

        Try
            If connE.State = ConnectionState.Closed Then
                connE.ConnectionString = "DATABASE=entertrack;" _
                  & "SERVER=191.254.1.42;user id=@@@@;password=@@@;port=3306;charset=utf8"
                connE.Open()
            End If

        Catch myerror As Exception
            MessageBox.Show("Error Connecting to the database", "Error Database Server", _
                            MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
            End
        End Try
    End Sub


    Public Sub DisconnectEntertrackDatabase()
        Try
            connE.Close()
        Catch myerror As MySql.Data.MySqlClient.MySqlException

        End Try
    End Sub
    Public Function executeDB(ByVal query As String) As DataTable
        'return true if record exist
        ConnectEntertrackDatabase()

        Try
            Dim cmd As MySqlCommand = New MySqlCommand(query, connE)
            Dim reader As MySqlDataReader
            Dim dt As New DataTable
            reader = cmd.ExecuteReader   ''cmd.ExecuteNonQuery()  for update operation
            dt.Load(reader)
            reader.Close()
            DisconnectEntertrackDatabase()
            Return dt

        Catch myerror As Exception
            DisconnectEntertrackDatabase()
            MessageBox.Show("Error at executeDB" & myerror.Message, "Error Database Server", _
                            MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
            Return New DataTable
        End Try
    End Function
End Class

Comments

Popular Posts