How Can I Execute an .SQL Script File with Multiple Statements in C#?

Execute .SQL script file using C#

In C#, executing a .SQL file containing multiple statements requires an alternative to ODP.NET's ExecuteNonQuery method. This article describes two methods of performing this task.

Method 1: Use SQL Server Management Objects (SMO)

1. Use SqlConnection to establish a database connection.
2. Initialize the Server object using the connection.
3. Use Server.ConnectionContext.ExecuteNonQuery(script) to execute the script.

<code class="language-csharp">using Microsoft.SqlServer.Management.Smo;
using Microsoft.SqlServer.Management.Common;
using System.IO;
using System.Data.SqlClient;

SqlConnection conn = new SqlConnection(sqlConnectionString);
Server server = new Server(new ServerConnection(conn));
string script = File.ReadAllText(@"path/to/script.sql");
server.ConnectionContext.ExecuteNonQuery(script);</code>

*Method 2: Generate a process to call SQLPlus**

1. Create a Process object and set UseShellExecute to false.
2. Redirect standard output to capture the results.
3. Set the FileName property to "sqlplus".
4. Set the Arguments property to contain database credentials and script path.
5. Set CreateNoWindow to true to hide the console window.
6. If the process returns exit code 1, retry the operation.

<code class="language-csharp">Process p = new Process();
p.StartInfo.UseShellExecute = false;
p.StartInfo.RedirectStandardOutput = true;
p.StartInfo.FileName = "sqlplus";
p.StartInfo.Arguments = string.Format("xx/xx@{0} @{1}", in_database, s);
p.StartInfo.CreateNoWindow = true;

bool started = p.Start();
while (p.HasExited == false)
{
    Application.DoEvents();
}

int exitCode = p.ExitCode;
if (exitCode != 0)
{
    ... // 错误处理
}</code>

The above is the detailed content of How Can I Execute an .SQL Script File with Multiple Statements in C#?. For more information, please follow other related articles on the PHP Chinese website!