title: ADO.NET and ExecuteNonQuery: how to use DDL
body: <p>I execute SQL scripts to change the database schema. It looks something like this:</p>
  
  <pre><code>using (var command = connection.CreateCommand())
  {
      command.CommandText = script;
      command.ExecuteNonQuery();
  }
  </code></pre>
  
  <p>Additionally, the commands are executed within a transaction.</p>
  
  <p>The scrip looks like this:</p>
  
  <pre><code>Alter Table [TableName]
  ADD [NewColumn] bigint NULL
  
  Update [TableName]
  SET [NewColumn] = (SELECT somevalue FROM anothertable)
  </code></pre>
  
  <p>I get an error, because <code>NewColumn</code> does not exist. It seems to parse and validate it before it is executed.</p>
  
  <p>When I execute the whole stuff in the Management Studio, I can put <code>GO</code> between the statements, then it works. When I put <code>GO</code> into the script, ADO.NET complains (Incorrect syntax near 'GO').</p>
  
  <p>I could split the script into separate scripts and execute it in separate commands, this would be hard to handle. I could split it on every <code>GO</code>, parsing the script myself. I just think that there should be a better solution and that I didn't understand something. How should scripts like this be executed?</p>
  
  <hr>
  
  <p>My implementation if anyone is interested in, according to John Saunders' answer:</p>
  
  <pre><code>List&lt;string&gt; lines = new List&lt;string&gt;();
  while (!textStreamReader.EndOfStream)
  {
      string line = textStreamReader.ReadLine();
      if (line.Trim().ToLower() == "go" || textStreamReader.EndOfStream)
      {
          ExecuteCommand(
              string.Join(Environment.NewLine, lines.ToArray()));
  
          lines.Clear();
      }
      else
      {
          lines.Add(line);
      }
  }
  </code></pre>