How to Execute an .SQL Script File Using C#?

Using C# to Execute SQL Script Files

This guide demonstrates how to execute an .SQL script file using C#. We'll utilize the Microsoft.SqlServer.Management.Smo assembly for this task.

Implementation

The following C# code provides a complete implementation:

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

public class SqlScriptExecutor
{
    public static void Main(string[] args)
    {
        string connectionString = "Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=ccwebgrity;Data Source=SURAJIT\SQLEXPRESS";
        string scriptPath = @"E:\Project Docs\MX462-PD\MX756_ModMappings1.sql";

        try
        {
            // Connect to the SQL Server database.
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                connection.Open();

                // Create a Server object.
                Server server = new Server(new ServerConnection(connection));

                // Read the SQL script.
                string script = File.ReadAllText(scriptPath);

                // Execute the script.
                server.ConnectionContext.ExecuteNonQuery(script);
                Console.WriteLine("SQL script executed successfully.");
            }
        }
        catch (Exception ex)
        {
            Console.WriteLine($"Error executing SQL script: {ex.Message}");
        }
    }
}</code>

Code Explanation

1. Namespaces: The code imports necessary namespaces for SQL Server interaction and file I/O.
2. Connection String: A connection string is defined to specify the SQL Server instance and database. Remember to replace this with your actual connection string.
3. Script Path: The path to your .SQL script file is specified. Replace this with the correct path.
4. Connection and Server Object: A SqlConnection is created and opened, then a Server object is instantiated to represent the database server. The using statement ensures the connection is properly closed.
5. Script Reading: File.ReadAllText reads the entire SQL script into a string.
6. Script Execution: server.ConnectionContext.ExecuteNonQuery executes the SQL script. This method handles multiple SQL statements within the script file.
7. Error Handling: A try-catch block handles potential exceptions during script execution.

This method provides a robust way to execute SQL scripts from within your C# application, managing multiple statements and line breaks effectively. Remember to adjust the connection string and script path to match your environment.

The above is the detailed content of How to Execute an .SQL Script File Using C#?. For more information, please follow other related articles on the PHP Chinese website!