How Can I Reliably Execute Multi-Line .SQL Script Files from C#?

Reliable execution of multi-line .SQL script files in C#

Executing SQL scripts in C# is useful for automating database operations and managing database objects. This article explores a solution for executing .SQL files that contain multiple SQL statements and addresses some of the challenges that arise along the way.

The code provided in the article attempts to execute a SQL file using ODP.NET and the Process object, but the ExecuteNonQuery method often does not handle multi-line SQL statements correctly, and the WaitForExit method may hang when using UseShellExecute.

However, a more reliable and complete solution can be achieved using the Microsoft.SqlServer.Management.Smo namespace in C#. Here is an improved code snippet:

using Microsoft.SqlServer.Management.Smo;
using Microsoft.SqlServer.Management.Common;
using System.IO;
using System.Data.SqlClient;

public partial class ExcuteScript : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        string sqlConnectionString = @"Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=ccwebgrity;Data Source=SURAJIT\SQLEXPRESS";

        string script = File.ReadAllText(@"E:\Project Docs\MX462-PD\MX756_ModMappings1.sql");

        SqlConnection conn = new SqlConnection(sqlConnectionString);

        Server server = new Server(new ServerConnection(conn));

        server.ConnectionContext.ExecuteNonQuery(script);
    }
}

In this code:

• We first create a connection to the SQL Server database using a connection string.
• Then we read the SQL script file into a string.
• Using the Server and ServerConnection classes, we create a connection to SQL Server.
• Finally, we execute the SQL script using the ExecuteNonQuery method on the ServerConnection object.

This method should be able to successfully execute multi-line SQL statements from a script file and provide a cleaner and more powerful solution to your SQL scripting needs.

The above is the detailed content of How Can I Reliably Execute Multi-Line .SQL Script Files from C#?. For more information, please follow other related articles on the PHP Chinese website!