'
' * Asks a filename and location from the user to export the data, and
' * runs the export operation.
Private Sub exportToCSV()
''Asks the filenam with a SaveFileDialog control.
Dim saveFileDialogCSV As New SaveFileDialog()
saveFileDialogCSV.InitialDirectory = Application.ExecutablePath.ToString()
saveFileDialogCSV.Filter = "CSV files (*.csv)|*.csv|All files (*.*)|*.*"
saveFileDialogCSV.FilterIndex = 1
saveFileDialogCSV.RestoreDirectory = True
Dim RowsCount As Integer = 0
If saveFileDialogCSV.ShowDialog() = DialogResult.OK Then
'' Runs the export operation if the given filenam is valid.
exportToCSVfile(saveFileDialogCSV.FileName.ToString(), RowsCount)
End If
End Sub
'
' * Exports data to the CSV file.
'
Private Sub exportToCSVfile(fileOut As String, ByRef RowsCount As Integer)
' Connects to the database, and makes the select command.
Dim conn As New SqlConnection(prop.sqlConnString)
Dim sqlQuery As String = "select * from " + Me.lbxTables.SelectedItem.ToString()
Dim command As New SqlCommand(sqlQuery, conn)
conn.Open()
' Creates a SqlDataReader instance to read data from the table.
Dim dr As SqlDataReader = command.ExecuteReader()
' Retrives the schema of the table.
Dim dtSchema As DataTable = dr.GetSchemaTable()
' Creates the CSV file as a stream, using the given encoding.
Dim sw As New StreamWriter(fileOut, False, Me.encodingCSV)
Dim dtTable As DataTable = dr.GetSchemaTable()
' string strRow; // represents a full row
' Writes the column headers if the user previously asked that.
If Me.chkFirstRowColumnNames.Checked Then
sw.WriteLine(columnNames(dtSchema, Me.separator))
End If
While dr.Read()
RowsCount = RowsCount + 1
Dim strRow As String = ""
Dim sDummy As String = ""
' ProgressBar met pecentage aanwijzing
ProgressBarX1.Value = (RowsCount / Counter) * (100)
Dim percent As Integer = CInt(((CDbl(ProgressBarX1.Value) / CDbl(ProgressBarX1.Maximum)) * 100))
ProgressBarX1.CreateGraphics().DrawString(percent.ToString() & "%", New System.Drawing.Font("Arial", CSng(8.25), FontStyle.Regular), Brushes.Black, New PointF(ProgressBarX1.Width / 2 - 10, ProgressBarX1.Height / 2 - 7))
' Reads the rows one by one from the SqlDataReader
' transfers them to a string with the given separator character and
' writes it to the file.
For i As Integer = 0 To dr.FieldCount - 1
Try
' if Setting value to the sDummy variable blows up, we go catch...
sDummy = dr.GetValue(i)
' In case the value on the field has a comma (,), put a "" around the value...
strRow += If(sDummy.IndexOf(","), """"c & dr.GetValue(i) & """"c, dr.GetValue(i))
If i >= dr.FieldCount - 1 Then
Continue For
End If
strRow += separator
Catch
If i < dr.FieldCount - 1 Then
strRow += separator
End If
End Try
Next
sw.WriteLine(strRow)
End While
' Closes the text stream and the database connenction.
sw.Close()
conn.Close()
' Notifies the user.
LabelX1.Text = Counter & " Rows copied ready!"
',MessageBox.Show("ready")
End Sub