How do I run my .sql script file through ADO.NET?

Question

I want to run my .sql script file using my ASP.NET website through ADO.NET. How it could be it is not working?

When I try

'dbScript is a string and contains contents of the .sql file'
Dim cmd As New SqlCommand(dbScript, con)
Try
    con.Open()
    cmd.ExecuteNonQuery()
Catch ex As Exception
Finally
    con.Close()
    cmd.Dispose()
End Try

I get exceptions when GO statement executed in script. How can I fix this problem?

Answer 1

GO is not a Transact-SQL statement, is a tools batch delimiter. The server rightfully complain of a syntax error when GO is encountered in a batch. You need to split the file into batches and then execute individual batches. Use a regular expression that splits the file inot batches and recognizes GO case insensitive on a single line.

Answer 2

It's because GO isn't actually a native TSQL statement, it's used in Management Studio/Enterprise Manager to divide the script into batches.

You either need to:
1) split it into multiple individual scripts on each GO statement
2) use the Server class within SQL Management Objects, as exampled here

Answer 3

See my blog post about Handling GO Separators in SQL - The Easy Way. The trick is to use SMO's ExecuteNonQuery() method. For example, here's some code that will run all scripts in a directory, regardless of GO separators:

 using System;
 using System.IO;
 using System.Data.SqlClient;
 using System.Collections.Generic;

 //Microsoft.SqlServer.Smo.dll
 using Microsoft.SqlServer.Management.Smo;
 //Microsoft.SqlServer.ConnectionInfo.dll
 using Microsoft.SqlServer.Management.Common;

 public class RunAllSqlSriptsInDirectory
 {
     public static void Main()
     {
         string scriptDirectory = "c:\\temp\\sqltest\\";
         string sqlConnectionString = "Integrated Security=SSPI;" + 
             "Persist Security Info=True;Initial Catalog=Northwind;Data Source=(local)";
         DirectoryInfo di = new DirectoryInfo(scriptDirectory);
         FileInfo[] rgFiles = di.GetFiles("*.sql");
         foreach (FileInfo fi in rgFiles)
         {
             FileInfo fileInfo = new FileInfo(fi.FullName);
             string script = fileInfo.OpenText().ReadToEnd();
             SqlConnection connection = new SqlConnection(sqlConnectionString);
             Server server = new Server(new ServerConnection(connection));
             server.ConnectionContext.ExecuteNonQuery(script);
         }
     }
 }

Answer 4

Nice one Please Visit lasatalks.blogspot.com lasatalks.blogspot.com