Form1.vb
=======Function RunScriptsFiles(ByVal n As Integer, ByVal worker As BackgroundWorker, ByVal e As DoWorkEventArgs) As Long
Try
btnOK.Enabled = False
btnCancel.Enabled = True
Dim filecount As Integer = IO.Directory.GetFiles(Helper.GetSqlPath & "SqlScripts").Length
pb.Minimum = 0
pb.Value = 0
pb.Maximum = filecount * gCount
Dim intversion As Integer
Dim databasename As String = ""
Dim bool As Boolean = True
Dim islatestversion As Boolean = True
'below for loop will loop throught all the checked database names
For i As Integer = 0 To dgvDatabases.Rows.Count - 1
If dgvDatabases.Rows(i).Cells("select").Value = True Then
bool = True
databasename = dgvDatabases.Rows(i).Cells("DataBaseName_Text").Value.ToString()
txtStatus.Text &= "Status Log for " & databasename & " database." & vbCrLf
DBUtil.DatabaseName = databasename
'below line to make a back up of the current database on to which the scripts are running.
Try
intversion = DBUtil.GetDatabaseVersion
Catch sqlex As SqlException
txtStatus.Text &= "Sql Exception " & sqlex.Message & vbCrLf
bool = False
If sqlex.Message.Contains("Could not open a connection to SQL Server") Then
txtStatus.Text &= vbCrLf & "Errors occured while running scripts for the database " & databasename & ". please verify them in the above logs." & vbCrLf & vbCrLf
'increment the progress bar value with one percent every time.
pb.Value += 1
lblProgress.Text = "Please wait... Verifying scripts for " & databasename & " database " & Math.Round(pb.Value / (filecount * gCount) * 100) & " % completed."
Continue For
End If
Catch ex As Exception
txtStatus.Text &= ex.Message & vbCrLf
bool = False
End Try
'if file exist then load XML file and Append Child
If txtBrowsePath.Text <> "" Then
If File.Exists(txtBrowsePath.Text & "\" & databasename & "-" & intversion & ".bak") Then
File.Delete(txtBrowsePath.Text & "\" & databasename & "-" & intversion & ".bak")
End If
Try
BackUpDatabase(databasename, txtBrowsePath.Text & "\" & databasename & "-" & intversion & ".bak", ConfigurationManager.AppSettings.Item("SqlServer"), False, ConfigurationManager.AppSettings.Item("SqlUsername"), ConfigurationManager.AppSettings.Item("SqlPassword"))
txtStatus.Text &= "The database for " & databasename & " has been backed up successfully at " & txtBrowsePath.Text & vbCrLf
Catch ex As Exception
bool = False
txtStatus.Text &= "Error at Database backup " & ex.Message & vbCrLf
End Try
End If
Dim strPathToFile As String
'below for loop will loop through the files specified in the SqlScripts folder fo this application
For k As Integer = 1 To filecount
If worker.CancellationPending Then
e.Cancel = True
Exit Function
End If
'increment the progress bar value with one percent every time.
pb.Value += 1
lblProgress.Text = "Please wait... Verifying scripts for " & databasename & " database " & Math.Round(pb.Value / (filecount * gCount) * 100) & " % completed."
strPathToFile = Helper.GetSqlPath & "SqlScripts\Version" & (intversion + k) & ".sql"
'to check fi the script file version number is greater than the current version fo the database
'If (intversion + k) = intversion Then
'txtStatus.Text &= "Database " & databasename & " is already on the latest version." & vbCrLf & vbCrLf
'Else
If (intversion + k) > intversion Then
If IO.File.Exists(strPathToFile) Then
islatestversion = False
lblProgress.Text = "Please wait... Running script for " & databasename & " database " & Math.Round(pb.Value / (filecount * gCount) * 100) & " % completed."
txtStatus.Text &= "Started Running Script No." & (intversion + k) & "." & vbCrLf
'calling the DBUtil class for executing the script statements.
Try
DBUtil.Upgrade(strPathToFile)
txtStatus.Text &= "Script No." & (intversion + k) & " has been ran successfully." & vbCrLf
Catch ex As Exception
bool = False
txtStatus.Text &= "Error at executing script " & (intversion + k) & ex.Message & vbCrLf
End Try
Else
'in case if any script file misses in the Sqlscript folder, then try to get the next file
' try this until four time. else skip the database from executing the scripts.
intversion += k
For j As Integer = 1 To 4
strPathToFile = Helper.GetSqlPath & "SqlScripts\Version" & (intversion + j) & ".sql"
If IO.File.Exists(strPathToFile) Then
islatestversion = False
lblProgress.Text = "Please wait... Running script for " & databasename & " database " & Math.Round(pb.Value / (filecount * gCount) * 100) & " % completed."
'calling the DBUtil class for executing the script statements
Try
DBUtil.Upgrade(strPathToFile)
txtStatus.Text &= "Script No." & (intversion + k) & " has been ran successfully." & vbCrLf
Catch ex As Exception
bool = False
txtStatus.Text &= "Error at executing script " & (intversion + k) & ex.Message & vbCrLf
End Try
End If
Next
End If
Else
lblProgress.Text = "Please wait... Verifying scripts for " & databasename & " database " & Math.Round(pb.Value / (filecount * gCount) * 100) & " % completed."
txtStatus.Text &= "Script No." & (intversion + k) & " has been skipped from from execution." & vbCrLf
End If
Next
If bool Then
If islatestversion Then
txtStatus.Text &= "It seems like " & databasename & " is already in latest version." & vbCrLf & vbCrLf
Else
txtStatus.Text &= "Scripts ran successfully for the database " & databasename & vbCrLf & vbCrLf
End If
Else
txtStatus.Text &= "Errors occured while running scripts for the database " & databasename & " please verify them in the above logs." & vbCrLf & vbCrLf
End If
End If
Next
lblProgress.Text = "Script process 100% completed."
pb.Value = pb.Maximum
Catch ex As Exception
MsgBox(ex.Message)
btnOK.Enabled = True
errorbool = False
Throw ex
End Try
End Function
------------------------------
Public Shared Sub BackUpDatabase(ByVal DataBase As String, ByVal FilePath As String, ByVal strSQLServerName As String, ByVal blnTrustedConnection As Boolean, Optional ByVal strSQLUsername As String = "", Optional ByVal strSQLPassword As String = "")
Dim sql As String = "BACKUP DATABASE [" & DataBase & "] TO DISK = '" & FilePath & "'"
'To Generate A Backup Of The Specifed database and save it in a specifeid file
'Dim dmoServer As New SQLDMO.SQLServer
Dim dmoServer As Server
Dim objSvrConn As ServerConnection
Try
'dmoServer.LoginSecure = blnTrustedConnection
'dmoServer.Connect(strSQLServerName, strSQLUsername, strSQLPassword)
objSvrConn = New ServerConnection()
objSvrConn.LoginSecure = False
objSvrConn.ServerInstance = strSQLServerName
objSvrConn.Login = strSQLUsername
objSvrConn.Password = strSQLPassword
dmoServer = New Server(objSvrConn)
dmoServer.ConnectionContext.ExecuteNonQuery(sql)
Catch ex As System.Runtime.InteropServices.COMException 'ErrorCode -2147218303 Integer
If ex.ErrorCode = -2147218303 Then 'When The Server And The File System Are In Different Systems
' Throw New Exception("Please make sure the Database and the file selected reside in same Terminal")
Throw New Exception(ex.Message)
End If
Catch ex As Exception
Throw ex
End Try
End Sub
=====================================================================
Helper.vb
=========Public Shared Function GetSqlPath() As String
Return Application.StartupPath & "\" 'This is like server.mappath
End Function
======================================================================
DBUtil.vb
========Public Shared Sub Upgrade(Optional ByVal strPathToFile As String = "")
Dim dr As IO.StreamReader 'which reads the script
Dim dbServer As New SQLServer
Try
Dim strSQL As String = ""
dbServer.LoginSecure = UseTrustedConnection
dbServer.Connect(ServerName, ServerUserName, ServerPassword)
If IO.File.Exists(strPathToFile) Then
dr = IO.File.OpenText(strPathToFile) 'opening the file to read the SQL script
strSQL &= "USE [" & DatabaseName & "]" & vbCrLf
strSQL &= " GO " & vbCrLf
strSQL &= dr.ReadToEnd
dbServer.ExecuteImmediate(strSQL) 'executes the script
dr.Close()
End If
Catch ex As System.Exception
Throw New Exception(ex.Message)
Finally
dbServer.DisConnect()
End Try
End Sub
No comments:
Post a Comment