WPF App run SQL Server Scripts

Question

I often have to 'Generate Scripts' for stored procedures on SQL Server Management Studio. I need to create a WPF app that will take the file produced and execute it. I know you can do this:

SqlCommand insert = new SqlCommand(sql, conn);
insert.ExecuteNonQuery();

If I pass the entire scripts file text into sql, will this execute everything?

Why would you expect it to execute less than the Sql you passed it? — Dan Puzey, Oct 02 '12 at 12:42
When I've used the above it's been to execute a stored procedure not CREATE, DROP, etc. I assume it will run the entirety of the script but until I get the chance to test I wanted to know if SqlCommand would run anything. — user1166905, Oct 02 '12 at 12:51

score 2 · Accepted Answer · edited May 23 '17 at 12:09

2

Look at this: How to execute an .SQL script file using c#

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

[...]

private void Execute(object sender, EventArgs e)
{
    string sqlConnectionString = @"Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=ccwebgrity;Data Source=SURAJIT\SQLEXPRESS";

    FileInfo file = new FileInfo(@"E:\Project Docs\MX462-PD\MX756_ModMappings1.sql");

    string script = file.OpenText().ReadToEnd();

    SqlConnection conn = new SqlConnection(sqlConnectionString);

    Server server = new Server(new ServerConnection(conn));

    server.ConnectionContext.ExecuteNonQuery(script);
    file.OpenText().Close();    
}

edited May 23 '17 at 12:09

Community

1
1

answered Oct 02 '12 at 12:39

SeToY

5,777
12
54
94

@user1166905 `SqlCommand` will work as long you don't have a `GO` statement in your sql script. If you strictly want to use `SqlCommand`, you'd have to split your file into separate statements and use them using a loop. Remove the 'GO' keywords and split the script into separate batches. Then execute each batch as its own `SqlCommand`. – SeToY Oct 02 '12 at 13:00
Won't have the luxury of time to split it, have to try with Server above, doesn't seem to like it though despite adding Microsoft.SqlServer – user1166905 Oct 02 '12 at 13:05
@user1166905 The ADO.NET classes sadly don't understand `GO`-Statements. So if your script contains `GO`, this is the most efficient way to go, I'm afraid. – SeToY Oct 02 '12 at 13:07
1

What References do you need to add for Server above, doesn't like it in the code whatever I do. – user1166905 Oct 02 '12 at 13:13
@user1166905 Take a look at the usings – SeToY Oct 02 '12 at 13:32
I don't have the .Management, which reference contains this? – user1166905 Oct 02 '12 at 13:40
1

@user1166905 By default the DLL files are in `C:\Program Files\Microsoft SQL Server\90\SDK\Assemblies`. Files: `Microsoft.SqlServer.Smo.dll` and `Microsoft.SqlServer.ConnectionInfo.dll` – SeToY Oct 02 '12 at 13:41

WPF App run SQL Server Scripts

1 Answers1