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