Run multiple commans or sql script using OLE DB in SQL Server

Question

It is possible to run multiple commands or sql script using OLE DB? For example to run sql script to create database and its structure (tables, indexes, stored procedures...).

When I use CCommand class, I can run only one command. How can I run SQL script with multiple commands?

Thanks, Martin

Answer 1

Martin - I have had this same issue. I assume that when you load a script with one or more "go" in it you get an error?

I have used SQL DMO in the past to run scripts with GO in them. That does require that SQLDMO be installed on your target computer.

Another option is to use the .NET "String.Split("GO")" function, and loop the resulting array of strings, exexuting them one by one.

Like this:

StreamReader file = File.OpenText("c:\\MyScript.sql");
SqlConnection conn = new SqlConnection("My connection string here...");
string fullCommand = file.ReadToEnd();
string[] splitOptions = new string[] {"go", "GO", "gO", "Go" };
foreach (string individualCommand in fullCommand.Split(splitOptions, StringSplitOptions.RemoveEmptyEntries))
{
    SqlCommand comm = new SqlCommand(individualCommand, conn);
    comm.ExecuteNonQuery();
}

Disclaimer: I have not tested the above code, but it should give you an idea of what is required :-)