Tuesday, 18 October 2011

Run .sql script file in c#.net


You can't run .sql files directly, with just specifying file name.
You must first read its content in a string and then execute CommandText command.
But this will work as long you don't have GO statement in your sql script.

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);
        }
    }
}

No comments:

Post a Comment