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
– Dan Puzey, Commented Oct 2, 2012 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
– user1166905, Commented Oct 2, 2012 at 12:51

Community · Accepted Answer · 2017-05-23 12:09:02Z

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, 2017 at 12:09

CommunityBot

11 silver badge

answered Oct 2, 2012 at 12:39

SeToY

5,89512 gold badges57 silver badges97 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

SeToY Over a year ago

@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.

user1166905 Over a year ago

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

SeToY Over a year ago

@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.

user1166905 Over a year ago

What References do you need to add for Server above, doesn't like it in the code whatever I do.

SeToY Over a year ago

@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

|

Collectives™ on Stack Overflow

WPF App run SQL Server Scripts

1 Answer 1

7 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related