Executing SQL Script Files with C# Using SMO

This guide demonstrates how to execute SQL script files containing multiple statements (potentially spanning several lines) within a C# application. We'll leverage the Microsoft SQL Server Management Objects (SMO) for this task.

Here's a C# code example:

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

public class SqlScriptRunner
{
    public void RunScript(string scriptPath, string connectionString)
    {
        // Read the entire SQL script from the file.
        string sqlScript = File.ReadAllText(scriptPath);

        // Establish a database connection.
        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            // Create a Server object using the connection.
            Server server = new Server(new ServerConnection(connection));

            // Execute the script using SMO's ExecuteNonQuery.
            server.ConnectionContext.ExecuteNonQuery(sqlScript);
        }
    }
}

Implementation Steps:

1. Add SMO Reference: Include the Microsoft.SqlServer.Management.Smo and Microsoft.SqlServer.Management.Common assemblies in your project's references.
2. Instantiate and Execute: Create an instance of SqlScriptRunner and call the RunScript method, providing the full path to your SQL script file and a valid database connection string.

This method offers a clean and efficient way to handle complex SQL scripts within your C# applications. The using statement ensures proper resource management by automatically closing the database connection.