How to run sql script with 'Go' statements from C#

Question

I am trying to run SQL script which contains 'GO' statements as batch separators. I came across Jon Galloway's way to handle this type situation which makes use of SMO(nuget package Microsoft.SQlServer.SQLManagementObjects).

con = new SqlConnection(connectionString);//1
var serverConnection = new ServerConnection(con);//2
var server = new Server(serverConnection);//3
StreamReader streamReader = new StreamReader(@"D:\Scripts\Install-Northwind-Script.sql");//4
string script = streamReader.ReadToEnd();//5
server.ConnectionContext.ExecuteNonQuery(script);//6

The above code is throwing an exception at line 6.

System.IO.FileNotFoundException: 'Could not load file or assembly 'Microsoft.SqlServer.BatchParser, Version=15.100.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91' or one of its dependencies. The system cannot find the file specified.'

It is giving FileNotFound exception but it successfully read all the contents of file at line 5. I tried adding the Microsoft.SqlServer.BatchParser reference but could not find one.

Can anyone help me know what am I doing wrong here?

Siavash Rostami · Accepted Answer · 2019-12-30 07:38:09Z

3

This error does not pick at your query but the fact that assemblies for the Sql Server BatchParser are missing. To fix that you have to install

x64\SharedManagementObjects.msi and
x64\SQLSysClrTypes.msi

With smo assemblies installed you should be good to go.
Yet better option is to use this Nuget package. It contains all you need for smo objects version 15.
Command to isntall (just enter this in nuget package manager console)

Install-Package Microsoft.SqlServer.SqlManagementObjects -Version 150.18208.0

edited Dec 30, 2019 at 7:38

answered Dec 30, 2019 at 7:31

Siavash Rostami

1,9334 gold badges18 silver badges33 bronze badges

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

2 Comments

Aditya Over a year ago

Thankyou for the suggestion @Siavash I did install Microsoft.SqlServer.SqlManagementObjects from nuget and also using namespace Microsoft.SqlServer.Management.Smo; Microsoft.SqlServer.Management.Common; Still getting the missing assembly exception

Siavash Rostami Over a year ago

@Aditya You're welcome. You might mark the answer as best answer as well if it solved your problem, so it remains useful for future on SO.

Collectives™ on Stack Overflow

How to run sql script with 'Go' statements from C#

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related