Complete Guide to Executing SQL Script Files Using C#

Fundamental Principles of SQL Script Execution

Executing SQL script files in C# applications is a common requirement, particularly in scenarios such as database migration, data initialization, or batch operations. SQL script files typically contain multiple SQL statements, which may include Data Definition Language (DDL) and Data Manipulation Language (DML) commands. These statements may span multiple lines and may contain specific delimiters (such as the GO command).

Using Microsoft.SqlServer.Management.Smo Library

The Microsoft.SqlServer.Management.Smo (SQL Server Management Objects) library provides a robust framework for managing SQL Server. Through the Server class and ServerConnection class, we can directly execute entire SQL script files.

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

public class SqlScriptExecutor
{
    public void ExecuteSqlScript(string connectionString, string scriptFilePath)
    {
        // Read SQL script file content
        string scriptContent = File.ReadAllText(scriptFilePath);
        
        // Create database connection
        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            // Create Server object
            Server server = new Server(new ServerConnection(connection));
            
            // Execute SQL script
            server.ConnectionContext.ExecuteNonQuery(scriptContent);
        }
    }
}

The main advantage of this approach is its ability to handle complex SQL scripts, including scenarios requiring multiple statement execution such as stored procedure creation and function definitions. The Server.ConnectionContext.ExecuteNonQuery method is specifically designed to execute scripts containing multiple SQL statements.

Handling Encoding Issues

In practical applications, SQL script files may contain special characters or non-ASCII characters (such as accent marks). This can lead to encoding problems, particularly when file encoding doesn't match the system's default encoding.

// Improved version handling encoding issues
public void ExecuteSqlScriptWithEncoding(string connectionString, string scriptFilePath)
{
    // Read file with correct encoding specification
    string scriptContent = File.ReadAllText(scriptFilePath, System.Text.Encoding.GetEncoding(1252));
    
    using (SqlConnection connection = new SqlConnection(connectionString))
    {
        Server server = new Server(new ServerConnection(connection));
        server.ConnectionContext.ExecuteNonQuery(scriptContent);
    }
}

By specifying the correct encoding (such as code page 1252 for ANSI encoding), special characters can be properly parsed and executed. Developers should choose the appropriate encoding method based on the actual file encoding.

Alternative Approach Using System.Data.SqlClient

For simple scenarios that don't require the Microsoft.SqlServer.Management.Smo library, a pure System.Data.SqlClient implementation can be used:

using System;
using System.Data;
using System.Data.SqlClient;
using System.IO;
using System.Text.RegularExpressions;

public class SimpleSqlScriptExecutor
{
    public void ExecuteScript(string connectionString, string scriptFilePath)
    {
        string script = File.ReadAllText(scriptFilePath);
        
        // Split GO commands using regular expressions
        string[] commands = Regex.Split(script, @"^\s*GO\s*$", 
            RegexOptions.Multiline | RegexOptions.IgnoreCase);
        
        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            connection.Open();
            
            foreach (string command in commands)
            {
                if (!string.IsNullOrWhiteSpace(command.Trim()))
                {
                    using (SqlCommand sqlCommand = new SqlCommand(command, connection))
                    {
                        sqlCommand.ExecuteNonQuery();
                    }
                }
            }
            
            connection.Close();
        }
    }
}

Error Handling and Best Practices

In real production environments, a robust SQL script executor should include comprehensive error handling mechanisms:

public class RobustSqlScriptExecutor
{
    public bool ExecuteScriptWithErrorHandling(string connectionString, string scriptFilePath)
    {
        try
        {
            string scriptContent = File.ReadAllText(scriptFilePath, System.Text.Encoding.UTF8);
            
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                connection.Open();
                
                Server server = new Server(new ServerConnection(connection));
                
                // Set execution timeout
                server.ConnectionContext.StatementTimeout = 300; // 5 minutes
                
                server.ConnectionContext.ExecuteNonQuery(scriptContent);
                
                return true;
            }
        }
        catch (Exception ex)
        {
            // Log error details
            Console.WriteLine($"SQL script execution failed: {ex.Message}");
            return false;
        }
    }
}

Performance Optimization Considerations

For large SQL script files, consider the following performance optimization strategies:

• Wrap the entire script execution process in a transaction
• Set appropriate command timeout values
• Process very large script files in batches
• Monitor memory usage to avoid issues caused by large files

Cross-Database Compatibility

While this article primarily focuses on SQL Server, similar approaches can be adapted for other database systems:

• Oracle: Use Oracle.DataAccess or ODP.NET
• MySQL: Use MySql.Data
• PostgreSQL: Use Npgsql

Each database system has its specific SQL script execution requirements and best practices, and developers should adjust implementation strategies according to the target database.