Run a .sql script files in C#
If you have placed in situation to execute .sql script files from .NET code on SQL Server, you will see that SqlCommand class is not that useful. In other words, you can't execute batch commands that contains 'GO' (batch finalizer command). So you can think of several options, like split that script to several commands, and execute one by one. But you realize how complex is that task.
If you wanted to do that prior to SQL Server 2005, the only option is using osql utility. Since SQL Server 2005 there is another option which is much better and preferred. That option is using SMO library which comes with SQL Server and can be used for managing everything on SQL Server 2005. You can backup, restore databases, configure permissions, replication, etc.
I plan to post about how to backup and restore database using this SMO library in near future. Here will write only about how to read sql file and execute the content on SQL Server.
using System.Data.SqlClient;
using System.IO;
using Microsoft.SqlServer.Management.Common;
using Microsoft.SqlServer.Management.Smo;
namespace ConsoleApplication1
{
class Program
{
static void Main(string[] args)
{
string sqlConnectionString =
"Data Source=(local);Initial Catalog=AdventureWorks;Integrated Security=True";
FileInfo file = new FileInfo("C:\\myscript.sql");
string script = file.OpenText().ReadToEnd();
SqlConnection conn = new SqlConnection(sqlConnectionString);
Server server = new Server(new ServerConnection(conn));
server.ConnectionContext.ExecuteNonQuery(script);
}
}
}
This post is based from my answer on one MSDN forums' question located here.