How to execute multiple SQL scripts in a single file from C#?

Question

I am trying to execute multiple SQL scripts in a single file from C#. In between the scripts I have GO statements. the script is like an incremental script and day by day that script will increase. I tried using ExecuteReader(), ExecuteScalar() and ExecuteNonQuery(), but it was throwing exception saying GO is incorrect syntax.

I then split the script with GO and in a loop, I tried to execute there also where ever script will have Declare @xyz DataType it throwing an exception.

Please suggest how I can execute such script from C#.

foreach (var sqlBatch in ScriptDetails.Split(new[] { "GO", "Go", "go", "gO" }, StringSplitOptions.RemoveEmptyEntries))
{
    try
    {
        string[] prefixes = { "--", "/*" };
        bool resultchk = prefixes.Any(prefix => sqlBatch.StartsWith(prefix));
        if (!resultchk)
        {
            connection.Open();
            command.CommandText = sqlBatch;
            command.ExecuteNonQuery();//ExecuteReader(); ExecuteScalar()
            connection.Close();
            Result.Add(new SQLResult { Final_message = "Success" });
        }
    }
    catch (SqlException ex)
    {
        Result.Add(new SQLResult { Final_message = ex.Message.ToString(), Line_No = ex.LineNumber });
        connection.Close();
    }
}

you can use SMO msdn.microsoft.com/en-us/library/ms162169(v=sql.105).aspx — Squirrel
– Squirrel, Commented Apr 4, 2018 at 5:11
SMO are an additional effort to deploy (especially when you have to support different SQL Server versions). I wouln't use SMO just to run SQL Scripts. — Stefan Steinegger
– Stefan Steinegger, Commented Apr 4, 2018 at 6:54

Stefan Steinegger · Accepted Answer · 2018-04-04 06:47:55Z

1

I would try to open the connection only once to run all the parts of the script.

Pseudo-code:

use (var connection ...)
{
  connection.Open();
  foreach (var sqlBatch in ...)
  {
    use (var command = new Command(..., connection))
    {
      command.CommandText = sqlBatch;
      ...
      command.ExecuteNonQuery();
    }
  }
}

answered Apr 4, 2018 at 6:47

Stefan Steinegger

64.8k17 gold badges132 silver badges193 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

shadow Over a year ago

Also a transaction implementation might be helpful. Just a thought. ;-)

Stefan Steinegger Over a year ago

@shadow: Hard to say. Most scripts are doing transaction themselves. But it's certainly a good point to consider.

Nandan hegde Over a year ago

Thank you @Stefan Steinegger

Collectives™ on Stack Overflow

How to execute multiple SQL scripts in a single file from C#?

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related