RSS

Backing Up Database from Remote Server to Local in VB.NET

28 Jan

 

 

Private Sub BackupMySql()

 

Dim localDir As String = “D:\”

Dim ftphost As String = “Mybkp”

Dim strDate As String = Date.Now.ToShortDateString ‘Prepend file with date for dated backups

 

Dim fileName As String = strDate.Replace(“/”, “-“) & “_” & ftphost & “.sql”

 

Dim saveFile As String = localDir & fileName

 

Dim DBServer As String = “192.168.10.40”

 

Dim DBServerPort As String = “mysql port”

 

Dim Database As String = “erpisto”

 

Dim DBUser As String = “root”

 

Dim DBPass As String = “root”

 

Dim MyConString As String = “SERVER=” & DBServer & “;DATABASE=” & Database & “;UID=” & DBUser & “;PASSWORD=” & DBPass

 

Dim connection As New MySqlConnection(MyConString)

 

Dim tablesCommand As MySqlCommand = connection.CreateCommand()

 

Dim rowsCommand As MySqlCommand = connection.CreateCommand()

 

Dim tablesReader As MySqlDataReader

 

Dim rowsReader As MySqlDataReader

tablesCommand.CommandText = “SHOW TABLES FROM ” & Database

connection.Open()

tablesReader = tablesCommand.ExecuteReader()

 

Dim query As String = “”

 

Dim tablesArray As New ArrayList()

 

While tablesReader.Read()

tablesArray.Add(tablesReader.GetValue(0).ToString())

 

End While

tablesReader.Close()

 

For i = 0 To tablesArray.Count – 1

query += “DROP TABLE IF EXISTS `” & tablesArray(i) & “`;” & vbCrLf ‘& Database & “.”

query += vbCrLf & “CREATE TABLE `” & tablesArray(i) & “` (” & vbCrLf

rowsCommand.CommandText = “DESCRIBE ” & tablesArray(i)

rowsReader = rowsCommand.ExecuteReader()

 

Dim temp As String = “”

 

While rowsReader.Read()

query += “`” & rowsReader.GetString(“Field”) & “` ” & rowsReader.GetString(“Type”)

 

If Not rowsReader.GetString(“Null”) = “YES” Then

query += ” NOT NULL”

 

End If

 

If IsDBNull(rowsReader.Item(“Default”)) = False Then

query += ” DEFAULT ‘” & rowsReader.GetString(“Default”) & “‘”

 

End If

 

If Not rowsReader.GetString(“Extra”) = Nothing Then

query += ” ” & rowsReader.GetString(“Extra”).ToUpper()

 

End If

 

If rowsReader.GetString(“Key”) = “PRI” Then

temp = “primary key(” & rowsReader.GetString(“Field”) & “)”

 

End If

query += “,” & vbCrLf

 

End While

query += temp & vbCrLf & “);” & vbCrLf & vbCrLf

rowsReader.Close()

rowsCommand.CommandText = “SELECT * FROM ” & tablesArray(i)

rowsReader = rowsCommand.ExecuteReader()

 

While rowsReader.Read()

query += “INSERT INTO `” & tablesArray(i) & “` (“

 

Dim count As Integer = rowsReader.FieldCount – 1

 

Dim keys(count) As String

 

Dim values(count) As String

 

For n = 0 To count

keys(n) = rowsReader.GetName(n)

Dim str As String = rowsReader.GetDataTypeName(n)
If Not rowsReader.GetDataTypeName(n) = “BLOB” Then
If (IsDBNull(rowsReader.Item(n))) Then
values(n) = If(IsDBNull(rowsReader.Item(n)), 0, rowsReader.Item(n))
Else
If Not str = “TIME” Then
values(n) = rowsReader.Item(n)
Else
If Not rowsReader.GetName(n) = “TimeSpan” Then
Dim interval As TimeSpan
interval = DirectCast(rowsReader.Item(n), System.TimeSpan)
values(n) = Convert.ToString(interval)
Else
values(n) = rowsReader.Item(n)

End If

 

End If

End If

Else
values(n) = Nothing

End If

 

Next

query += Join(keys, “, “) & “)” & vbCrLf & “VALUES (‘” & Join(values, “‘, ‘”) & “‘);” & vbCrLf

 

End While

rowsReader.Close()

query += vbCrLf & vbCrLf

 

Next

connection.Close()

connection.Dispose()

 

 

If File.Exists(saveFile) Then

File.Delete(saveFile)

 

End If

 

Dim objWriter As New System.IO.StreamWriter(saveFile)

objWriter.Write(query)

objWriter.Close()

 

End Sub

Advertisements
 
Leave a comment

Posted by on January 28, 2014 in My SQL

 

Tags: ,

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: