RSS

Monthly Archives: January 2014

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


 

 

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

 
Leave a comment

Posted by on January 28, 2014 in My SQL

 

Tags: ,

Restore on Local Mysql Database through vb.net


if you want to restore mysql data on local mysql database then use below code.

1. Just Change “C:\wamp\bin\mysql\mysql5.6.12\bin\ ” path url according to your install wamp sever or mysql sever.

2. Backup file or script data path…… E:\db\mpostest.sql

3. “mpostest”  mysql database name

 

Try

Dim myProcess As New Process()
myProcess.StartInfo.FileName = “cmd.exe”
myProcess.StartInfo.UseShellExecute = False
myProcess.StartInfo.WorkingDirectory = “C:\wamp\bin\mysql\mysql5.6.12\bin\”
myProcess.StartInfo.RedirectStandardInput = True
myProcess.StartInfo.RedirectStandardOutput = True
myProcess.Start()
Dim myStreamWriter As StreamWriter = myProcess.StandardInput
Dim mystreamreader As StreamReader = myProcess.StandardOutput
myStreamWriter.WriteLine(“mysql -u root  -proot mpostest < E:\db\mpostest.sql “)
myStreamWriter.Close()
myProcess.WaitForExit()
myProcess.Close()
Catch ex As Exception

End Try

 

You can also  find here article  restore mysql database on remote or network sever

 

https://humrahimcs.wordpress.com/2014/01/28/restore-on-remote-server-mysql-database-using-vb-net/

 
Leave a comment

Posted by on January 28, 2014 in My SQL

 

restore on remote server mysql database using vb.net


if you want to restore mysql database on remote sever or network deploy server then this code very help you.i also wast lot of time for that at the end this solution resolve my problem.

1. Just Add Remoter Sever Name/IP   -h192.168.10.40

2. In WorkingDirectory  give your Install Dir mysql Path.

3. E:\db\mpostest.sql you can use Filediaglog for dynamic path

4. You must create Empty db with name mpostest  in mysql for testing else give your orignal db name

Dim myProcess As New Process()
myProcess.StartInfo.FileName = “cmd.exe”
myProcess.StartInfo.UseShellExecute = False
myProcess.StartInfo.WorkingDirectory = “C:\wamp\bin\mysql\mysql5.6.12\bin\”
myProcess.StartInfo.RedirectStandardInput = True
myProcess.StartInfo.RedirectStandardOutput = True
myProcess.Start()
Dim myStreamWriter As StreamWriter = myProcess.StandardInput
Dim mystreamreader As StreamReader = myProcess.StandardOutput
myStreamWriter.WriteLine(“mysql -u root -h192.168.10.40 -proot mpostest < E:\db\mpostest.sql “)
myStreamWriter.Close()
myProcess.WaitForExit()
myProcess.Close()
Catch ex As Exception

End Try

***** Also you can find Article on Local db & Backup mysql db(which resolve issue of blob , time,timespan type issue).because mostly backup article give Error for these type.

 
1 Comment

Posted by on January 28, 2014 in My SQL

 

Tags:

Could not load file or assembly ‘file:///C:\Program Files (x86)\SAP BusinessObjects\Crystal Reports for .NET Framework 4.0\Common\SAP BusinessObjects Enterprise XI 4.0\win32_x86\dotnet1\crdb_adoplus.dll’ or one of its dependencies. The system cannot find the file specified.


This Error give this message…..

This stuff basically happens, if you have correct setup of SBO (I wish you so:)), that between different components of your SBO SDK there are assemblies with different .NET version. Check if you have someold version of SAP BO SDK installed on your machine too. Hope this helps.

 

 

add this to your .config file (usually app.config)
<startup useLegacyV2RuntimeActivationPolicy=”true”>
<supportedRuntime version=”v4.0″ sku=”.NETFramework,Version=v4.0″/>

</startup>

 

RptError

 

 
Leave a comment

Posted by on January 17, 2014 in Crystal Report, WinForm