run .sql file over a collection of databases on an instance

by

//reference the following assemplyies of SMO

Microsoft.SqlServer.Management.Sdk.Sfc

Microsoft.SqlServer.ConnectionInfo

Microsoft.SqlServer.Smo

// using Microsoft.SqlServer.Management.Common;

//using Microsoft.SqlServer.Management.Smo;

    public static void  ExecuteScript(string fname, List<string> databases)
    {
        string script = File.ReadAllText(fname);
        ServerConnection conn = new ServerConnection("server-name/instance", "user", "password");

        Server SMOServer = new Server(conn);

        // foreach (Database db in SMOServer.Databases) //for all databases in server
        foreach (var dbname in databases)
        {               
            var db = SMOServer.Databases[dbname];
            var ds = db.ExecuteWithResults(script); //if you want query result as a Dataset
          //db.ExecuteNonQuery(script); // if you run non return query result, e.g update/insert/delete
        }
        conn.Disconnect();
    }

More Related Contents:

  1. C#: SQL Server Connection String
  2. Procedure or function xxxxx has too many arguments specified [duplicate]
  3. An unhandled exception of type ‘System.InvalidOperationException’ occurred in System.Data.dll?
  4. How to execute a stored procedure within C# program
  5. Using stored procedure output parameters in C#
  6. How can I set an SQL Server connection string?
  7. Insert entire DataTable into database at once instead of row by row?
  8. Writing Recursive CTE using Entity Framework Fluent syntax or Inline syntax
  9. I get a “An attempt was made to load a program with an incorrect format” error on a SQL Server replication project
  10. SQL error: Incorrect syntax near the keyword ‘User’
  11. How to execute an SSIS package from .NET?
  12. Passing List to SQL Stored Procedure
  13. How to retrieve data from a SQL Server database in C#?
  14. Bulk Update in C#
  15. How to run multiple SQL commands in a single SQL connection?
  16. Queuing in OneWay WCF Messages using Windows Service and SQL Server
  17. Any way to SQLBulkCopy “insert or update if exists”?
  18. EF 6 – How to correctly perform parallel queries
  19. What SqlDbType maps to varBinary(max)?
  20. How to keep single SQL Server connection instance open for multiple request in C#?
  21. Delete a single record from Entity Framework?
  22. How to pass XML from C# to a stored procedure in SQL Server 2008?
  23. How to use SqlCommand to CREATE DATABASE with parameterized db name?
  24. ExecuteNonQuery returning -1 when using sql COUNT despite the query string
  25. Failed to update .mdf database because the database is read-only (Windows application)
  26. GO statements blowing up sql execution in .NET
  27. How to Identify the primary key duplication from a SQL Server 2008 error code?
  28. Provider named pipes provider error 40 could not open a connection to SQL Server error 2 [duplicate]
  29. Is it possible to use SqlGeography with Linq to Sql?
  30. Watch for a table new records in sql database

Leave a Comment