' Database backup utility:
' ========================
' Copyright (C) 2007 Shabdar Ghata (Email : ghata2002@gmail.com)
' This program is free software: you can redistribute it and/or modify
' it under the terms of the GNU General Public License as published by
' the Free Software Foundation, either version 3 of the License, or
' (at your option) any later version.
' This program is distributed in the hope that it will be useful,
' but WITHOUT ANY WARRANTY; without even the implied warranty of
' MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
' GNU General Public License for more details.
' You should have received a copy of the GNU General Public License
' along with this program. If not, see <http://www.gnu.org/licenses/>.
' This program comes with ABSOLUTELY NO WARRANTY.
Imports System.Data
Imports System.Data.OleDb
Public Class frmGenerateScripts
Dim OledbConnectionString As String
Private Sub frmBackupDatabase_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
Load_Default_Values()
End Sub
Private Sub cmdExport_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdGenerateScripts.Click
Dim file As System.IO.StreamWriter
file = My.Computer.FileSystem.OpenTextFileWriter("c:\SQLBackup.bat", False)
If Not IsNothing(dgExportTables) Then
Dim DT As DataTable = CType(dgExportTables.DataSource, DataTable)
Dim i As Integer
For i = 0 To DT.Rows.Count - 1
Dim sBCPCommand As String = Generate_BCPCommand(txtServerName.Text, txtDatabaseName.Text, txtUsername.Text, txtPassword.Text, CStr(DT.Rows(i)("TABLENAME")), "", txtNoOfRows.Text)
DT.Rows(i)("BCPCommand") = sBCPCommand
file.WriteLine(sBCPCommand)
Next
End If
file.Close()
'MessageBox.Show("Export complete", "Export", MessageBoxButtons.OK, MessageBoxIcon.Information)
Shell("c:\sqlbackup.bat", AppWinStyle.NormalFocus)
End Sub
Sub Fill_Export_Table_Names()
Dim tableNames As New DataTable
Dim oScript As New clsScript
'Dim fieldNames As New DataTable
'OledbConnectionString = Get_Connection_String()
'Dim CN As New OleDbConnection(OledbConnectionString)
Try
'CN.Open()
'Dim n() As Object = {Nothing, Nothing, Nothing, "TABLE"}
''Dim n() As Object = {System.DBNull.Value, System.DBNull.Value, System.DBNull.Value, "TABLE"}
''tableNames = CN.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, n)
'tableNames = oScript.GetTableNames(txtServerName.Text, txtDatabaseName.Text, txtUsername.Text, txtPassword.Text)
'n(3) = Nothing
'fieldNames = CN.GetOleDbSchemaTable(OleDbSchemaGuid.Columns, n)
'CN.Close()
Generate_Table_Names_Grid(tableNames)
'dgExportTables.DisplayMember = "TABLE_NAME"
Catch ex As Exception
MessageBox.Show(ex.ToString())
End Try
End Sub
Sub Load_Default_Values()
txtServerName.Text = "SHABDAR\SQLEXPRESS"
txtDatabaseName.Text = "SMS"
txtUsername.Text = "safdar"
txtPassword.Text = "ghata"
rdSQLServerAuthentication.Checked = True
txtBackupDir.Text = "C:\DBBackup\"
End Sub
Function Get_Connection_String()
Return "Provider=SQLOLEDB.1;data source=" + txtServerName.Text + ";user id=" + txtUsername.Text + ";Password=" + txtPassword.Text + ";database=" + txtDatabaseName.Text + ""
End Function
Sub Generate_Table_Names_Grid(ByRef tableNames As DataTable)
'Create datatable, which will be used to fill grid
Dim i As Integer
Dim DT As New DataTable
DT.Columns.Add("ObjectName", "".GetType)
DT.Columns.Add("Select", True.GetType)
For i = 0 To tableNames.Rows.Count - 1
Dim sBCPCommand As String = Generate_BCPCommand(txtServerName.Text, txtDatabaseName.Text, txtUsername.Text, txtPassword.Text, CStr(tableNames.Rows(i)("TABLE_NAME")), "", txtNoOfRows.Text)
DT.Rows.Add(New Object() {tableNames.Rows(i)("TABLE_NAME"), True, txtNoOfRows.Text, "", sBCPCommand})
Next
dgExportTables.DataSource = Nothing
dgExportTables.DataSource = DT
'dgExportTables.AutoResizeColumns()
dgExportTables.Columns("TableName").Width = 150
dgExportTables.Columns("Export").Width = 50
dgExportTables.Columns("TotalRows").Width = 120
dgExportTables.Columns("Condition").Width = 300
dgExportTables.Columns("BCPCommand").Width = 300
End Sub
Private Sub cmdConnect_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdConnect.Click
Fill_Export_Table_Names()
End Sub
Private Sub cmdSelectAll_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdSelectAll.Click
If Not IsNothing(dgExportTables.DataSource) Then
Dim i As Integer
Dim DT As DataTable
DT = CType(dgExportTables.DataSource, DataTable)
For i = 0 To DT.Rows.Count - 1
DT.Rows(i)("Export") = True
Next
End If
End Sub
Private Sub cmdDeSelectAll_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdDeselectAll.Click
If Not IsNothing(dgExportTables.DataSource) Then
Dim i As Integer
Dim DT As DataTable
DT = CType(dgExportTables.DataSource, DataTable)
For i = 0 To DT.Rows.Count - 1
DT.Rows(i)("Export") = False
Next
End If
End Sub
Function Generate_BCPCommand(ByVal sServerName As String, ByVal sDatabaseName As String, ByVal sUserName As String, ByVal sPassword As String, ByVal sTableName As String, ByVal sCondition As String, ByVal sTotalRows As String)
Dim sCommand As String = " bcp ""select " + sTotalRows + " from " + sDatabaseName + ".dbo." + sTableName + """ queryout """ + txtBackupDir.Text + sTableName + ".dat"" -S " + sServerName + " -U " + sUserName + " -P " + sPassword + " " + txtBCPOptions.Text
Return sCommand
End Function
End Class