How Can I Efficiently Execute Multi-Line SQL Script Files in C#?

Execute .SQL script file using C#

In C#, executing .SQL script files requires special considerations. This question discusses the difficulty of running such files, which may contain segmented statements spanning multiple lines.

Challenges and Solutions

Attempts to execute the script using ODP.NET's ExecuteNonQuery proved to be ineffective. Spawning a process to call sqlplus had problems with hanging and output redirection.

Solution using Microsoft SQL Server Management Objects (SMO)

The solution presented here leverages SMO, a managed code API for managing and configuring SQL Server. Here is a code snippet demonstrating this approach:

<code class="language-csharp">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);
    }
}</code>

In this method:

• SMO is initialized with a connection to the target database.
• The script is read from the file and passed to SMO for execution.
• ExecuteNonQuery is used to execute scripts on SQL Server instances.

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