If you want to run sql server scripts through code, you can’t just run a standard ExecuteNonQuery() with a SqlCommand. There’s problems with ExecuteNonQuery() recognizing multiple sql statements.
Don’t fear though, if you reference the Microsoft.SqlServer.Smo and Microsoft.SqlServer.ConnectionInfo assemblies you get access to some goodies that’ll help you on your way!
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using Microsoft.SqlServer.Management.Common;
using Microsoft.SqlServer.Management.Smo;
SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["DBName"].ConnectionString);
Server server = new Server(new ServerConnection(connection));
server.ConnectionContext.ExecuteNonQuery(File.ReadAllText("SqlScript.sql"));
