Execute multiple .sql scripts using c#

Question

hi i want to run a script that calls multiple *.sql scripts i have multiple server in lots off sub office will need error control on script if the *.sql crashes also like to use a config file so i can edit for each site

ta Gerard

here some source code i have found

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

namespace ConsoleApplication1
{
    class Program
    {
        static void Main(string[] args)
        {
            string sqlConnectionString = "Data Source=TECHSUP4\\SQLEXPRESS;Initial Catalog=TESTCU;Integrated Security=True";
            FileInfo file = new FileInfo("C:\\Database\\Scripts\\test.sql");
            string script = file.OpenText().ReadToEnd();
            SqlConnection conn = new SqlConnection(sqlConnectionString);
            Server server = new Server(new ServerConnection(conn));
            server.ConnectionContext.ExecuteNonQuery(script);
        }
    }
}

Answer 1

How about using powershell to run that?

$cmd = get-content "C:\temp\CombinedScripts.sql"
foreach ($svr in get-content "C:\temp\ServerList.txt")
{
$con = "server=$svr;database=master;Integrated Security=sspi"
$da = new-object System.Data.SqlClient.SqlDataAdapter ($cmd, $con)
$dt = new-object System.Data.DataTable
$da.fill($dt) | out-null
$svr
$dt | format-table
}

Answer 2

If one of the lines in your test.sql file encounters a problem, the code you've posted will throw an exception and leave the database in an unknown state.

I personally would parse each SQL statement out of the file and execute it one statement at a time. Around each execution, I would wrap an exception handler that logs the exception and the line that caused it. Depending on exactly what you’re trying to do, you may be able to continue executing statements or you may want to terminate execution there.

It should be noted that it really depends what’s in “test.sql”. If its simple one-line commands that are autonomous from one another, you should be able to execute them one at a time as I’ve described. If its string of commands that create variables, execute stored procedures and so on, this may not be the correct approach. In this case, you may want to move more logic into the .sql file to do what needs to be done.

Answer 3

if your server is MS SQL Server 2005 or higher you could use sqlcmd utility to run your sql statements. It can also act as a scripting language and run a set of sql statements as a script.

The sqlcmd utility lets you enter Transact-SQL statements, system procedures, and script files at the command prompt, in Query Editor in SQLCMD mode, in a Windows script file or in an operating system (Cmd.exe) job step of a SQL Server Agent job. This utility uses OLE DB to execute Transact-SQL batches.

the rest is here: sqlcmd Utility