How can I execute an SQL script file containing multiple statements using C#?

Execute .SQL script file using C#

The goal of this programming scenario is to execute a SQL script file containing multiple SQL statements, including statements that span multiple lines. Although you have tried methods like ODP.NET and spawning SQLplus via process, you are having difficulty with execution and output capture. Here's how to solve your problem using C#:

In order to effectively execute .SQL script files using C#, please consider the following improvements:

1. Establish database connection:

Establish a database connection using a connection string that specifies the server name, database name, and authentication credentials.

2. Read script file content:

Read the contents of the .SQL script file into a string variable. This allows you to read the file once and store the contents for later use.

3. Create server instance:

Instantiate the Server object by providing the connection object as a parameter. This will create a representation of the SQL Server instance you want to use.

4. Execute script:

Create a command object using the ConnectionContext property of the Server object. The script is then executed by passing the SQL string to the ExecuteNonQuery method of the command object. This will execute all statements in the script sequentially.

5. Close connection:

After the script execution is completed, close the database connection to release resources.

Here is a code snippet demonstrating the above approach:

<code class="language-csharp">using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
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";

        // 读取SQL脚本文件的内容
        string script = File.ReadAllText(@"E:\Project Docs\MX462-PD\MX756_ModMappings1.sql");

        // 建立数据库连接
        SqlConnection conn = new SqlConnection(sqlConnectionString);

        // 创建服务器实例
        Server server = new Server(new ServerConnection(conn));

        // 执行SQL脚本
        server.ConnectionContext.ExecuteNonQuery(script);

        // 关闭连接
        conn.Close();
    }
}</code>

By following these steps, you can programmatically execute .SQL script files using C#, ensuring correct execution of all statements and accurate results.

The above is the detailed content of How can I execute an SQL script file containing multiple statements using C#?. For more information, please follow other related articles on the PHP Chinese website!