How to run a .sql script file through ADO.NET?

Question

I want to run my .sql script file using my ASP.NET site through ADO.NET. How could this be, it does not work?

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 the GO statement is executed in a script. How can I fix this problem?

Answer 1

See my blog on SQL GO Separator Handling - The Easy Way . The trick is to use the SMO ExecuteNonQuery () method. For example, there is code that will run all the scripts in the directory, regardless of the 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 2

GO is not a Transact-SQL statement; it is a batch separator of tools. The server rightly complains about a syntax error when GO is encountered in a packet. You need to split the file into batches and then execute separate batches. Use a regular expression that splits the file in packages and recognizes the GO case as one line insensitive.

Answer 3

There is one small problem with using the split method to execute packages. The problem is in the comments. Say you do not have authority over the contents of the files. You just need to execute it. GO in multi-line comments will be a problem in every example solution here, which breaks the sql code, using "GO" as a delimiter. Example:

 [some sql code] GO /* start of commented out sql code *********** [some sql code] GO end of commented out sql code ****************/ [some sql code] GO 

This will require more complex parsing than just separation. This will no longer work:

 Regex regex = new Regex("^GO", RegexOptions.IgnoreCase | RegexOptions.Multiline); string[] lines = regex.Split(sql); 

This code also ignores that spaces can lead to GO.

Answer 4

This is because GO is actually not a native TSQL instruction; it is used in Management Studio / Enterprise Manager to split the script into batches.

You either need:
1) break it into several separate scripts in each GO statement
2) use the Server class in SQL management objects, as shown here

Answer 5

You will need to complete two parsing sessions. The first pass is to delete all comments and build a new line. The second pass is to use the REGEX partition based on the GO keyword.