' Database backup utility:
' ========================
' Copyright (C) 2007 Shabdar Ghata
' Email : ghata2002@gmail.com
' URL : http://www.shabdar.org
' 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.Collections.Specialized
Imports System.Data.SqlClient
Imports System.Reflection
Public Class clsScript
Dim i As Integer
'Dim oServerSMO As Microsoft.SqlServer.Management.Smo.Server
'*Dim oServer As New SQLServer2
Dim objBCPExport As New SQLDMO.BulkCopy2
Public db As SQLDMO.Database2
'Public dbSMO As Microsoft.SqlServer.Management.Smo.Database
'Function Generate_Object_Script() As String
Dim oServer As New SQLDMO.SQLServer2
'oServer.Connect("SHABDAR\SQLExpress", "safdar", "ghata")
''Dim db As SQLDMO.Database2 = oServer.Databases.Item("SMS")
' Return db.Tables.Item(1).Script()
'End Function
'This procedure will get all table names from a database
Public Function ConnectDatabaseWithRefresh(ByVal pServer As String, ByVal pDatabase As String, ByVal pUserName As String, ByVal pPassword As String) As Boolean
'Try
'oServerSMO = New Microsoft.SqlServer.Management.Smo.Server
oServer = New SQLDMO.SQLServer2
oServer.EnableBcp = True
oServer.Connect(pServer, pUserName, pPassword)
'oServerSMO = New Microsoft.SqlServer.Management.Smo.Server(pServer)
'oServerSMO.UserOptions.
'oServer.Databases.Refresh()
db = Nothing
Try
db = oServer.Databases.Item(pDatabase)
db.DBOption.SelectIntoBulkCopy = True
'dbSMO = oServerSMO.Databases(pDatabase)
Catch
'If database does not exists then ignore it.
End Try
Return True
'Catch ex As Exception
' 'MessageBox.Show(ex.ToString)
' Return False
'End Try
End Function
Sub ConnectDatabase(ByVal pServer As String, ByVal pDatabase As String, ByVal pUserName As String, ByVal pPassword As String)
'Try
' If oServer.ConnectionID <= 0 Then
' End If
'Catch
' '''oServer.EnableBcp = True
' '''oServer.Connect(pServer, pUserName, pPassword)
' oServerSMO = New Microsoft.SqlServer.Management.Smo.Server(pServer)
'End Try
'If IsNothing(dbSMO) Then
' '''db = oServer.Databases.Item(pDatabase)
' dbSMO = oServerSMO.Databases(pDatabase)
' 'db.DBOption.SelectIntoBulkCopy = True
'End If
End Sub
Sub CreateNewDatabase(ByVal pServer As String, ByVal pDatabase As String, ByVal pUserName As String, ByVal pPassword As String)
Dim CN As New SqlConnection("Data Source=" + pServer + ";Persist Security Info=True;User ID=" + pUserName + ";Password=" + pPassword)
Dim COM As New SqlCommand("create database " + pDatabase, CN)
CN.Open()
COM.ExecuteNonQuery()
CN.Close()
'dbSMO.ExecuteNonQuery("create database " + pDBName)
'oServerSMO.Databases.Refresh()
'dbSMO = oServerSMO.Databases(pDatabase)
'oServer.ExecuteImmediate("create database " + pDatabase, SQLDMO.SQLDMO_EXEC_TYPE.SQLDMOExec_Default)
oServer.Databases.Refresh()
db = oServer.Databases.Item(pDatabase)
End Sub
Sub DropDatabase(ByVal pServer As String, ByVal pDatabase As String, ByVal pUserName As String, ByVal pPassword As String)
Try
db = Nothing
oServer.DisConnect()
'oServer = Nothing
Catch ex As Exception
End Try
Dim CN As New SqlConnection("Data Source=" + pServer + ";Persist Security Info=True;User ID=" + pUserName + ";Password=" + pPassword)
Dim COM As New SqlCommand("DROP DATABASE " + pDatabase, CN)
CN.Open()
COM.ExecuteNonQuery()
CN.Close()
Try
oServer.Connect()
Catch ex As Exception
End Try
'dbSMO.ExecuteNonQuery("drop database " + pDBName)
'dbSMO = Nothing
'db = Nothing
'oServer.ExecuteImmediate("drop database " + pDBName, SQLDMO_EXEC_TYPE.SQLDMOExec_Default)
End Sub
Sub ExportData(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, ByVal sBackUpDir As String)
objBCPExport.IncludeIdentityValues = True
objBCPExport.DataFilePath = sBackUpDir + sTableName + ".dat"
objBCPExport.DataFileType = SQLDMO.SQLDMO_DATAFILE_TYPE.SQLDMODataFile_NativeFormat
objBCPExport.UseExistingConnection = True
'objBCP.DataFileType = SQLDMO_DATAFILE_TYPE.SQLDMODataFile_SpecialDelimitedChar
'objBCP.ColumnDelimiter = vbTab
'objBCP.RowDelimiter = vbCrLf
'objBCP.MaximumErrorsBeforeAbort = 1
'objBCPExport.UseBulkCopyOption = True
'objBCPExport.ServerBCPKeepIdentity = True
'objBCP.ServerBCPDataFileType = SQLDMO_SERVERBCP_DATAFILE_TYPE.SQLDMOBCPDataFile_WideNative
'objBCP.ExportWideChar = True
'objBCP.TableLock = True
If InStr(sTotalRows, "TOP 100 PERCENT *", CompareMethod.Text) > 0 And Trim(sCondition = "") Then
'If db.Tables.Item(sTableName).Rows > 0 Then
db.Tables.Item(sTableName).ExportData(objBCPExport)
'End If
Else
Dim sSelectCommand As String = "select " + sTotalRows + " from " + sDatabaseName + ".." + sTableName
If Trim(sCondition) <> "" Then
sSelectCommand += " WHERE " + sCondition
End If
'Create a temporary view to hold data
Try
db.ExecuteImmediate("drop view Temp_View_For_Backup_001", SQLDMO.SQLDMO_EXEC_TYPE.SQLDMOExec_Default)
Catch
End Try
db.ExecuteImmediate("create view Temp_View_For_Backup_001 as " + sSelectCommand, SQLDMO.SQLDMO_EXEC_TYPE.SQLDMOExec_Default)
db.Views.Refresh()
'db.Views.Item("Temp_View_For_Backup_001").ExportData(objBCP)
'Export data to a file
db.Views.Item("Temp_View_For_Backup_001").ExportData(objBCPExport)
Try
db.ExecuteImmediate("drop view Temp_View_For_Backup_001", SQLDMO.SQLDMO_EXEC_TYPE.SQLDMOExec_Default)
Catch
End Try
End If
If My.Computer.FileSystem.GetFileInfo(objBCPExport.DataFilePath).Length <= 0 Then
'Delete 0 byte size files, to remove error when unzipping
My.Computer.FileSystem.DeleteFile(objBCPExport.DataFilePath)
' Debug.Write("Length=0")
End If
'objBCPExport = Nothing
End Sub
Function ImportData(ByVal sTableName As String, ByVal sFilePath As String, ByVal bDeleteExistingData As Boolean) As String
Try
Dim objBCP As New SQLDMO.BulkCopy2
objBCP.DataFilePath = sFilePath + sTableName + ".dat"
If My.Computer.FileSystem.FileExists(objBCP.DataFilePath) Then
'objBCP.ColumnDelimiter = vbTab
objBCP.DataFileType = SQLDMO.SQLDMO_DATAFILE_TYPE.SQLDMODataFile_NativeFormat
'objBCP.RowDelimiter = vbCrLf
objBCP.UseExistingConnection = True
objBCP.ServerBCPKeepIdentity = True
objBCP.IncludeIdentityValues = True
'objBCP.ExportWideChar = True
'objBCP.TableLock = True
'objBCP.UseBulkCopyOption = True
'objBCP.ServerBCPDataFileType = SQLDMO_SERVERBCP_DATAFILE_TYPE.SQLDMOBCPDataFile_WideNative
'objBCP.SetCodePage()
If bDeleteExistingData Then
db.ExecuteImmediate("delete from " + sTableName)
End If
db.Tables.Refresh()
db.Tables.Item(sTableName).ImportData(objBCP)
objBCP = Nothing
Return "Data imported."
Else
'Throw New Exception("File '" + objBCP.DataFilePath + "' does not exists")
Return "No data found."
End If
Catch ex As Exception
'MessageBox.Show(ex.Message)
Return (ex.Message)
End Try
Return "Pending"
End Function
Function GetTableNames(ByVal pServer As String, ByVal pDatabase As String, ByVal pUserName As String, ByVal pPassword As String, ByVal pNoOfRows As String) As DataTable
ConnectDatabase(pServer, pDatabase, pUserName, pPassword)
Dim DT As New DataTable
DT.Columns.Add("TableName", "".GetType)
DT.Columns.Add("Select", True.GetType)
DT.Columns.Add("TotalRows", "".GetType)
DT.Columns.Add("Condition", "".GetType)
DT.Columns.Add("BCPCommand", "".GetType)
'DT.Columns.Add("Status", "".GetType)
'Dim dtTables As DataTable = dbSMO.EnumObjects(DatabaseObjectTypes.Table)
'dtTables.DefaultView.RowFilter = "schema<>'sys' and schema<>'INFORMATION_SCHEMA'"
'For i = 0 To dtTables.DefaultView.Count - 1
' Dim sBCPCommand As String = "" 'Generate_BCPCommand(pServer, pDatabase, pUserName, pPassword, db.Tables.Item(i).Name, "", pNoOfRows)
' DT.Rows.Add(New Object() {dtTables.DefaultView.Item(i)("Name").ToString(), True, pNoOfRows, "", sBCPCommand})
'Next
For i = 1 To db.Tables.Count
If Not db.Tables.Item(i).SystemObject Then
Dim sBCPCommand As String = "" 'Generate_BCPCommand(pServer, pDatabase, pUserName, pPassword, db.Tables.Item(i).Name, "", pNoOfRows)
DT.Rows.Add(New Object() {db.Tables.Item(i).Name, True, pNoOfRows, "", sBCPCommand})
End If
Next
DT.TableName = "Tables"
Return DT
End Function
'Function Generate_BCPImportCommand(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,byval )
'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
Function GetViewNames(ByVal pServer As String, ByVal pDatabase As String, ByVal pUserName As String, ByVal pPassword As String) As DataTable
''Dim oServer As New SQLServer2
ConnectDatabase(pServer, pDatabase, pUserName, pPassword)
Dim DT As New DataTable
DT.Columns.Add("ViewName", "".GetType)
DT.Columns.Add("Select", True.GetType)
'DT.Columns.Add("Status", "".GetType)
For i = 1 To db.Views.Count
If Not db.Views.Item(i).SystemObject Then
DT.Rows.Add(New Object() {db.Views.Item(i).Name, True})
End If
Next
'Dim dtTables As DataTable = dbSMO.EnumObjects(DatabaseObjectTypes.View)
'dtTables.DefaultView.RowFilter = "schema<>'sys' and schema<>'INFORMATION_SCHEMA'"
'For i = 0 To dtTables.DefaultView.Count - 1
' Dim sBCPCommand As String = "" 'Generate_BCPCommand(pServer, pDatabase, pUserName, pPassword, db.Tables.Item(i).Name, "", pNoOfRows)
' DT.Rows.Add(New Object() {dtTables.DefaultView.Item(i)("Name").ToString(), True})
'Next
DT.TableName = "Views"
Return DT
End Function
Function GetUDFNames(ByVal pServer As String, ByVal pDatabase As String, ByVal pUserName As String, ByVal pPassword As String) As DataTable
'Dim oServer As New SQLServer2
ConnectDatabase(pServer, pDatabase, pUserName, pPassword)
Dim DT As New DataTable
DT.Columns.Add("UDFName", "".GetType)
DT.Columns.Add("Select", True.GetType)
For i = 1 To db.UserDefinedFunctions.Count
If Not db.UserDefinedFunctions.Item(i).SystemObject Then
DT.Rows.Add(New Object() {db.UserDefinedFunctions.Item(i).Name, True})
End If
Next
'Dim dtTables As DataTable = dbSMO.EnumObjects(DatabaseObjectTypes.UserDefinedFunction)
'dtTables.DefaultView.RowFilter = "schema<>'sys' and schema<>'INFORMATION_SCHEMA'"
'For i = 0 To dtTables.DefaultView.Count - 1
' Dim sBCPCommand As String = "" 'Generate_BCPCommand(pServer, pDatabase, pUserName, pPassword, db.Tables.Item(i).Name, "", pNoOfRows)
' DT.Rows.Add(New Object() {dtTables.DefaultView.Item(i)("Name").ToString(), True})
'Next
Return DT
End Function
Function GetSPNames(ByVal pServer As String, ByVal pDatabase As String, ByVal pUserName As String, ByVal pPassword As String) As DataTable
'Dim oServer As New SQLServer2
ConnectDatabase(pServer, pDatabase, pUserName, pPassword)
Dim DT As New DataTable
DT.Columns.Add("SPName", "".GetType)
DT.Columns.Add("Select", True.GetType)
For i = 1 To db.StoredProcedures.Count
If Not db.StoredProcedures.Item(i).SystemObject Then
DT.Rows.Add(New Object() {db.StoredProcedures.Item(i).Name, True})
End If
Next
'dbSMO.Views.
'Dim dtTables As DataTable = dbSMO.EnumObjects(DatabaseObjectTypes.StoredProcedure)
'dtTables.DefaultView.RowFilter = "schema<>'sys' and schema<>'INFORMATION_SCHEMA'"
'For i = 0 To dtTables.DefaultView.Count - 1
' Dim sBCPCommand As String = "" 'Generate_BCPCommand(pServer, pDatabase, pUserName, pPassword, db.Tables.Item(i).Name, "", pNoOfRows)
' DT.Rows.Add(New Object() {dtTables.DefaultView.Item(i)("Name").ToString(), True})
'Next
Return DT
End Function
Function GetUDDNames(ByVal pServer As String, ByVal pDatabase As String, ByVal pUserName As String, ByVal pPassword As String) As DataTable
'Dim oServer As New SQLServer2
ConnectDatabase(pServer, pDatabase, pUserName, pPassword)
Dim DT As New DataTable
DT.Columns.Add("UDDName", "".GetType)
DT.Columns.Add("Select", True.GetType)
For i = 1 To db.UserDefinedDatatypes.Count
DT.Rows.Add(New Object() {db.StoredProcedures.Item(i).Name, True})
Next
'Dim dtTables As DataTable = dbSMO.EnumObjects(DatabaseObjectTypes.UserDefinedDataType)
'dtTables.DefaultView.RowFilter = "schema<>'sys' and schema<>'INFORMATION_SCHEMA'"
'For i = 0 To dtTables.DefaultView.Count - 1
' Dim sBCPCommand As String = "" 'Generate_BCPCommand(pServer, pDatabase, pUserName, pPassword, db.Tables.Item(i).Name, "", pNoOfRows)
' DT.Rows.Add(New Object() {dtTables.DefaultView.Item(i)("Name").ToString(), True})
'Next
Return DT
End Function
Function GetUserNames(ByVal pServer As String, ByVal pDatabase As String, ByVal pUserName As String, ByVal pPassword As String) As DataTable
'Dim oServer As New SQLServer2
ConnectDatabase(pServer, pDatabase, pUserName, pPassword)
Dim DT As New DataTable
DT.Columns.Add("UserName", "".GetType)
DT.Columns.Add("Select", True.GetType)
For i = 1 To db.Users.Count
If Not db.Users.Item(i).SystemObject Then
If (db.Users.Item(i).Name.ToUpper() <> "GUEST" And db.Users.Item(i).Name.ToUpper() <> "INFORMATION_SCHEMA" And db.Users.Item(i).Name.ToUpper() <> "SYS") Then
DT.Rows.Add(New Object() {db.Users.Item(i).Name, True})
End If
End If
Next
'Dim dtTables As DataTable = dbSMO.EnumObjects(DatabaseObjectTypes.User)
'dtTables.DefaultView.RowFilter = "schema<>'sys' and schema<>'INFORMATION_SCHEMA'"
'For i = 0 To dtTables.DefaultView.Count - 1
' If (dtTables.DefaultView.Item(i)("Name").ToString().ToUpper() <> "GUEST" And dtTables.DefaultView.Item(i)("Name").ToString().ToUpper() <> "INFORMATION_SCHEMA" And dtTables.DefaultView.Item(i)("Name").ToString().ToUpper() <> "SYS" And dtTables.DefaultView.Item(i)("Name").ToString().ToUpper() <> "DBO") Then
' Dim sBCPCommand As String = "" 'Generate_BCPCommand(pServer, pDatabase, pUserName, pPassword, db.Tables.Item(i).Name, "", pNoOfRows)
' DT.Rows.Add(New Object() {dtTables.DefaultView.Item(i)("Name").ToString(), True})
' End If
'Next
Return DT
End Function
Function Generate_BCPBackupCommand(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, ByVal sBackUpDir As String, ByVal sBCPPath As String, ByVal sBCPOptions As String)
Dim sCommand As String = "BCP ""select " + sTotalRows + " from " + sDatabaseName + ".dbo." + sTableName + """ queryout """ + sBackUpDir + sTableName + ".dat"" -S " + sServerName + " -U " + sUserName + " -P " + sPassword + " " + sBCPOptions
Return sCommand
End Function
',byref pObjectDropScript as String
Function GenerateScript(ByVal pServer As String, ByVal pDatabase As String, ByVal pUserName As String, ByVal pPassword As String, ByVal pObjectType As String, ByVal pObjectName As String) As String
Dim sSQL As String = ""
If (pObjectType.ToLower() = "user") Then
If Not IsNothing(db.Users.Item(pObjectName)) Then
sSQL = db.Users.Item(pObjectName).Script(SQLDMO.SQLDMO_SCRIPT_TYPE.SQLDMOScript_Drops)
sSQL = sSQL + db.Users.Item(pObjectName).Script()
End If
Else
If Not IsNothing(db.GetObjectByName(pObjectName)) Then
sSQL = db.GetObjectByName(pObjectName).Script(SQLDMO.SQLDMO_SCRIPT_TYPE.SQLDMOScript_Drops)
sSQL = sSQL + db.GetObjectByName(pObjectName).Script()
End If
End If
'Select Case pObjectType.ToLower
' Case "table"
' Dim sp As Table = dbSMO.Tables(pObjectName, dbSMO.UserName)
' Dim so As ScriptingOptions = New ScriptingOptions()
' so.ScriptDrops = True
' so.WithDependencies = True
' so.AllowSystemObjects = False
' so.ClusteredIndexes = True
' so.Default = True
' so.DriAll = True
' so.Indexes = True
' so.NoIdentities = False
' so.PrimaryObject = True
' so.Triggers = True
' so.IncludeIfNotExists = True
' Dim sc As StringCollection = sp.Script(so)
' Dim s As String
' sSQL = ""
' For Each s In sc
' sSQL += " " + s
' 'Console.WriteLine(s)
' Next
' so.IncludeIfNotExists = False
' so.ScriptDrops = False
' sc = sp.Script(so)
' For Each s In sc
' sSQL += " " + s
' 'Console.WriteLine(s)
' Next
' Case "view"
' Dim sp As View = dbSMO.Views(pObjectName, ".")
' Dim so As ScriptingOptions = New ScriptingOptions()
' so.ScriptDrops = True
' so.WithDependencies = True
' so.AllowSystemObjects = False
' so.ClusteredIndexes = True
' so.Default = True
' so.DriAll = True
' so.Indexes = True
' so.NoIdentities = False
' so.PrimaryObject = True
' so.Triggers = True
' so.IncludeIfNotExists = True
' Dim sc As StringCollection = sp.Script(so)
' Dim s As String
' sSQL = ""
' For Each s In sc
' sSQL += " " + s
' 'Console.WriteLine(s)
' Next
' so.IncludeIfNotExists = False
' so.ScriptDrops = False
' sc = sp.Script(so)
' For Each s In sc
' sSQL += " " + s
' 'Console.WriteLine(s)
' Next
' Case "sp"
' Dim sp As StoredProcedure = dbSMO.StoredProcedures(pObjectName, dbSMO.UserName)
' Dim so As ScriptingOptions = New ScriptingOptions()
' so.ScriptDrops = True
' so.WithDependencies = True
' so.AllowSystemObjects = False
' so.ClusteredIndexes = True
' so.Default = True
' so.DriAll = True
' so.Indexes = True
' so.NoIdentities = False
' so.PrimaryObject = True
' so.Triggers = True
' so.IncludeIfNotExists = True
' Dim sc As StringCollection = sp.Script(so)
' Dim s As String
' sSQL = ""
' For Each s In sc
' sSQL += " " + s
' 'Console.WriteLine(s)
' Next
' so.IncludeIfNotExists = False
' so.ScriptDrops = False
' sc = sp.Script(so)
' For Each s In sc
' sSQL += " " + s
' 'Console.WriteLine(s)
' Next
' Case "udf"
' Dim sp As UserDefinedFunction = dbSMO.UserDefinedFunctions(pObjectName, dbSMO.UserName)
' Dim so As ScriptingOptions = New ScriptingOptions()
' so.ScriptDrops = True
' so.WithDependencies = True
' so.AllowSystemObjects = False
' so.ClusteredIndexes = True
' so.Default = True
' so.DriAll = True
' so.Indexes = True
' so.NoIdentities = False
' so.PrimaryObject = True
' so.Triggers = True
' so.IncludeIfNotExists = True
' Dim sc As StringCollection = sp.Script(so)
' Dim s As String
' sSQL = ""
' For Each s In sc
' sSQL += " " + s
' 'Console.WriteLine(s)
' Next
' so.IncludeIfNotExists = False
' so.ScriptDrops = False
' sc = sp.Script(so)
' For Each s In sc
' sSQL += " " + s
' 'Console.WriteLine(s)
' Next
' Case "udd"
' Dim sp As UserDefinedDataType = dbSMO.UserDefinedDataTypes(pObjectName, dbSMO.UserName)
' Dim so As ScriptingOptions = New ScriptingOptions()
' so.ScriptDrops = True
' so.WithDependencies = True
' so.AllowSystemObjects = False
' so.ClusteredIndexes = True
' so.Default = True
' so.DriAll = True
' so.Indexes = True
' so.NoIdentities = False
' so.PrimaryObject = True
' so.Triggers = True
' so.IncludeIfNotExists = True
' Dim sc As StringCollection = sp.Script(so)
' Dim s As String
' sSQL = ""
' For Each s In sc
' sSQL += " " + s
' 'Console.WriteLine(s)
' Next
' so.IncludeIfNotExists = False
' so.ScriptDrops = False
' sc = sp.Script(so)
' For Each s In sc
' sSQL += " " + s
' 'Console.WriteLine(s)
' Next
' Case "user"
' Dim sp As User = dbSMO.Users(pObjectName)
' Dim so As ScriptingOptions = New ScriptingOptions()
' so.ScriptDrops = True
' 'so.WithDependencies = True
' 'so.AllowSystemObjects = False
' 'so.ClusteredIndexes = True
' 'so.Default = True
' 'so.DriAll = True
' 'so.Indexes = True
' 'so.NoIdentities = False
' 'so.PrimaryObject = True
' 'so.Triggers = True
' 'so.IncludeIfNotExists = True
' Dim sc As StringCollection = sp.Script(so)
' Dim s As String
' sSQL = ""
' For Each s In sc
' sSQL += " " + s
' 'Console.WriteLine(s)
' Next
' so.IncludeIfNotExists = False
' so.ScriptDrops = False
' so.WithDependencies = True
' so.DriAll = False
' so.Permissions = True
' so.ExtendedProperties = False
' so.LoginSid = True
' so.WithDependencies = False
' sc = sp.Script(so)
' For Each s In sc
' sSQL += " " + s
' 'Console.WriteLine(s)
' Next
'End Select
Return sSQL
End Function
'Function ExecuteScript(ByVal pScript As String) As String
' Try
' If Trim(pScript) <> "" Then
' db.ExecuteImmediate(pScript, SQLDMO_EXEC_TYPE.SQLDMOExec_ContinueOnError)
' End If
' Catch ex As Exception
' Return Find_DependantObject(ex.ToString)
' End Try
' Return ""
'End Function
Function ExecuteScriptWithDependency(ByRef DTObjects As DataTable, ByRef lblObjectName As Label, ByRef progressbar1 As ProgressBar) As Boolean
Dim sScript As String = ""
Dim sObjectName As String = ""
'progressbar1.Minimum = 0
SetControlPropertyValue(progressbar1, "Minimum", 0)
SetControlPropertyValue(progressbar1, "Maximum", DTObjects.Rows.Count)
For i = 0 To DTObjects.Rows.Count - 1
sObjectName = DTObjects.Rows(i)("ObjectName")
sScript = DTObjects.Rows(i)("ScriptSQL")
If sScript <> "" Then
Try
lblObjectName.Text = sObjectName
db.ExecuteImmediate(sScript)
'dbSMO.ExecuteNonQuery(sScript)
DTObjects.Rows(i)("Executed") = True
DTObjects.Rows(i)("Status") = "Created."
Catch ex As Exception
DTObjects.Rows(i)("Executed") = False
DTObjects.Rows(i)("Status") = ex.Message
End Try
End If
SetControlPropertyValue(progressbar1, "Value", i)
Next
Dim repeat As Integer
For repeat = 0 To 10
'Execute again with failed objects, this to ensure dependency. Repeat 10 times to include all dependency
'DTObjects.DefaultView.RowFilter = "Executed=False"
For i = 0 To DTObjects.Rows.Count - 1
If DTObjects.Rows(i)("Executed") = False Then
sObjectName = DTObjects.Rows(i)("ObjectName")
sScript = DTObjects.Rows(i)("ScriptSQL")
If sScript <> "" Then
Try
'dbSMO.ExecuteNonQuery(sScript)
db.ExecuteImmediate(sScript, SQLDMO.SQLDMO_EXEC_TYPE.SQLDMOExec_Default)
DTObjects.Rows(i)("Executed") = True
DTObjects.Rows(i)("Status") = "Created."
Catch ex As Exception
DTObjects.Rows(i)("Executed") = False
DTObjects.Rows(i)("Status") = ex.Message
End Try
End If
End If
Next
Next
Return True
End Function
'Declare delegate for making thread safe calls
Delegate Sub SetControlValueCallback(ByVal oControl As Control, ByVal propName As String, ByVal propValue As Object)
'Method which invokes thread safe call
Private Sub SetControlPropertyValue(ByVal oControl As Control, ByVal propName As String, ByVal propValue As Object)
If oControl.InvokeRequired Then
Dim d As New SetControlValueCallback(AddressOf SetControlPropertyValue)
oControl.Invoke(d, New Object() {oControl, propName, propValue})
Else
Dim t As Type = oControl.[GetType]()
Dim props As PropertyInfo() = t.GetProperties()
For Each p As PropertyInfo In props
If p.Name.ToUpper() = propName.ToUpper() Then
p.SetValue(oControl, propValue, Nothing)
End If
Next
End If
End Sub
End Class