locked
FAQ Item: How to run a .sql script file from C#? RRS feed

  • Question

  • How to run a .sql script file from C#?
    Sunday, June 20, 2010 4:09 PM

Answers

  • We cannot execute a .sql script file using ADO.NET. To run a .sql script file using C#, we have to reference the SMO assembly and call its ServerConnection.ExecuteNonQuery(String script) function. The ExecuteNonQuery() method only accepts a string type parameter. So we first need to read the .sql script file into a C# string variable.

    ServerConnection.ExecuteNonQuery() executes a statement that does not return a result set, http://msdn.microsoft.com/en-us/library/ms199350.aspx

    The codes are like:

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

     

    Related Threads:

    http://social.msdn.microsoft.com/Forums/en/adodotnetdataproviders/thread/43e8bc3a-1132-453b-b950-09427e970f31
    • Marked as answer by MSDN FAQ Sunday, June 20, 2010 4:09 PM
    Sunday, June 20, 2010 4:09 PM