Executing SQL scripts with GO statements without SMO

Question

One thing I’m having problems with, is deploying app to machine not having SMO installed on it. I want to execute scripts having GOstatements, but also would like to avoid the need for installing SMO package on a target machine or parsing SQL script file (since I don't know if sql script will contain functions or SPs).

I tried manually copying SMO relevenat dlls to bin folder of executable but there are always some other dlls needed to run the app.

Any help with this?

You need to provide much more detail about what you are trying to do, your expected result, and your specific issue. — jrummell, Jun 13 '12 at 13:06
I have MSSQL scripts I need to execute, but don't know their format and have to support executing scripts that contain GO statement — dragan.stepanovic, Jun 13 '12 at 13:16
`GO` is just an indicator to split up scripts. Why not take the query text, split it into an array or collection of your choosing with `GO` as the separator, then iterate through running each script in turn? — Bridge, Jun 13 '12 at 13:49

score 3 · Answer 1 · edited Jun 20 '20 at 09:12

Alternatively to SMO, the sqlcmd utility can understand GO statements. You will need to use System.Diagnostics.Process in order to call out to it with your script as an argument.

_{By no means am I saying this is the way to do it, but it is a valid alternative. You still have the same issues as before if the utility doesn't exist on the target machine.}

You might also be able to deploy SMO with the installer package without "installing" it on the target machine:

Embedding SMO dlls in Setup and deployment

score 0 · Answer 2 · edited Jun 07 '18 at 09:37

I find below solution well tested

using (SqlConnection cn = new SqlConnection(connectionString))
  {
    try
    {
      cn.Open();
      FileInfo file = new FileInfo(fileName);
      string script = file.OpenText().ReadToEnd();
      string[] splitChar = { "\r\nGO\r\n" };
      var sqlLines = script.Split(splitChar, StringSplitOptions.RemoveEmptyEntries);
      int res = 0;
      SqlCommand cmd = null;
      foreach (var query in sqlLines)
      {
          cmd = new SqlCommand(query, cn)
          {
              CommandTimeout = 5400
          };
          res = cmd.ExecuteNonQuery();
      }
      cn.Close();
    }
    catch (Exception ex)
    {
      msg = ex.Message;
    }
    finally
    {
      cn.Close();
    }
  }

Executing SQL scripts with GO statements without SMO

2 Answers2