Running scripts in SQL Server from Visual Studio

I needed to run an sql server script from visual studio for deployment purpose at client end, which later i realized receiving extra exceptional errors which wasn’t found running them directly on SQL Server.

One way is to run each script individually through Visual Studio as in procedure below

Public Sub LoopDirectory()
Dim strFileSize As String = ""
Dim di As New IO.DirectoryInfo("C:\PInventoryScripts")
Dim aryFi As IO.FileInfo() = di.GetFiles("*.sql")
Dim fi As IO.FileInfo
For Each fi In aryFi
Dim con As New SqlConnection("Data Source = " + ConfigurationSettings.AppSettings.Get("Server") & ";Initial Catalog=" & _
ConfigurationSettings.AppSettings.Get("Database") & "; User Id=" & ConfigurationSettings.AppSettings.Get("UserId") & ";Password=" & ConfigurationSettings.AppSettings.Get("Password") & ";")

Dim command As New SqlCommand(fi.OpenText.ReadToEnd(), con)
command.Connection.Open()
Dim a As Integer = command.ExecuteScalar()
con.Close()
command.Dispose()
con.Dispose()
Next
End Sub

But this may take time making each object`s script individually and keeping it in a separate file. A better option I found is by referencing Sqldmo.dll file which is in C:\Program Files\Microsoft SQL Server\80\Tools\Binn folder

Public Sub ExecuteDMOScript()
Try
Dim sServer As SQLDMO.SQLServer = New SQLDMO.SQLServer
sServer.Connect(ConfigurationSettings.AppSettings.Get("Server"), ConfigurationSettings.AppSettings.Get("UserId"), ConfigurationSettings.AppSettings.Get("Password"))
Dim oDataBase As SQLDMO.Database
Dim strSQL As String
strSQL = File.OpenText(ConfigurationSettings.AppSettings.Get("FileLoc").ToString).ReadToEnd()
sServer.Databases().Item("PINV").ExecuteImmediate(strSQL)
Catch ex As Exception
lblError.Text = ex.Message
End Try
End Sub