This is something I have wanted for a while now.  Today I finally got around to doing something about it. I created a simple console application that can execute a SQL script file that you would normally use in Management Studio or SQLCMD.  You might well ask why I would do such a thing. 

1. SSMS and SQLCMD is not always available or in the right place
2. I don't have the code for SSMS and SQLCMD so I cant change the way they work

In you read my previous post on deploying to SQL Azure then the first point will make sense.  The next step is to make this script processing code work in Azure so I don't have to create databases from my client.  Hopefully this will speed things up a little or a LOT.

Anyways, since I've always wanted someone to write this code and never found any, here it is in case you are in the same boat.

class Program

  {

    static int Main(string[] args)

    {

 

      string connectionstring = args[0];

      string sql;

      try

      {

        using (FileStream fs = new FileStream(args[1], FileMode.Open, FileAccess.Read))

        {

          byte[] bytes = new byte[fs.Length];

          fs.Read(bytes, 0, (int)fs.Length);

          sql = Encoding.UTF8.GetString(bytes);

        }

      }

      catch (Exception ex)

      {

        Console.WriteLine("Unable to read sql script: " + ex.Message);

        return 1;

      }

 

      string batchTerminator = @"##GO##";

 

      //Regex regex = new Regex(@"(^|\s+)" + batchTerminator + @"\s", RegexOptions.IgnoreCase | RegexOptions.Singleline | RegexOptions.Compiled);

      string[] strings = sql.Split(new string[] {batchTerminator}, StringSplitOptions.None);

 

      using (SqlConnection con = new SqlConnection(connectionstring))

      {

        con.Open();

 

        SqlCommand cmd = new SqlCommand();

        cmd.CommandType = System.Data.CommandType.Text;

        cmd.Connection = con;

 

        for (int i = 0; i < strings.Length; i++)

        {

 

          Console.Title = "Processing " + i.ToString() + " of " + strings.Length.ToString();

 

          //if ((!regex.IsMatch(strings[i])) && (!string.IsNullOrEmpty(strings[i].Trim())))

          if (!string.IsNullOrEmpty(strings[i].Trim()))

          {

            Console.WriteLine(strings[i]);

            try

            {

              cmd.CommandText = strings[i];

              cmd.ExecuteNonQuery();

              Console.ForegroundColor = ConsoleColor.Green;

              Console.WriteLine("O.K.");

              Console.ForegroundColor = ConsoleColor.Gray;

            }

            catch (Exception ex)

            {

 

              Console.ForegroundColor = ConsoleColor.Red;

              Console.WriteLine("Error: " + ex.Message);

              Console.ForegroundColor = ConsoleColor.Gray;

 

 

              return 2;

            }

            Console.WriteLine();

            Console.WriteLine();

          }

        }

        return 0;

        //Console.WriteLine("Press ENTER when complete");

        //Console.ReadLine();

 

      }

    }

  }

Ok, so it’s not the prettiest or most bullet proof code on the planet, but it works.  The RegEx is the key to the process.  It’s not perfect – it expects  GO commands to separate the batches and you need to have GO on a line by itself.  It’s easy to extend this should you wish. [Update] Regex don't work!  It’s just too hard to find all the GO’s accurately.  Instead, I manually search and replace Go with ##GO##.  This is a lot easier/safer to split with.  I also removed all PRINT statements from my scripts but you could easily parse these out when processing and have then display as progress messages (no need to send them to the server).

Enjoy!