In .Net, how to execute a SQL Server script against an instance?

Question

I know there is a class system.data.sqlclient.sqlcommand that can execute a batch at a time. Since I have a lot if SQL Server scripts (in which there is some GO key words in it), I need to find a way that can throw this script to SQL Server for executing. How to do this ? Thanks.

Edit:

Hi Enrico, thanks for the information you provided. The executenonquery method will return a int[] type. How can I get all the text result? For example, I have this SQL script called sql.sql.

PRINT 'blah blah blah';
GO
SELECT @@SERVERNAME AS instance;

When I use sqlcmd to execute it, I get the following result.

C:\TEMP>sqlcmd -S (local)\instance1 -U a -P a -i sql.sql
blah blah blah
instance

-------------------------------------------------------------------
THESIMPSONS\INSTANCE1


(1 rows affected)

C:\TEMP>

How can I get the similar result using SMO or ADO.NET classes? Thanks.

Enrico Campidoglio · Accepted Answer · 2012-03-14 22:02:55Z

5

The ServerConnection.ExecuteNonQuery method part of the SQL Server Management Objects (SMO) API will correctly execute T-SQL scripts that contain multiple batches separated by the GO keyword.

As a side note, you can even decide to use your own batch separator by setting the ServerConnection.BatchSeparator property, which by default is "GO".

If your script returns multiple result sets that you wish to get back in your application, you can use either the ServerConnection.ExecuteReader or the ServerConnection.ExecuteWithResults method. However, keep in mind that those methods do not support T-SQL scripts with multiple batches. At this point you have a couple of options:

Split the script on the "GO" separator into multiple strings, each containing a single batch, and execute them through one of the methods mentioned above.
Invoke SQLCMD from your code by spawning a new process and passing the script to execute as a parameter.

edited Mar 14, 2012 at 22:02

answered Mar 14, 2012 at 8:20

Enrico Campidoglio

60.8k13 gold badges137 silver badges159 bronze badges

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

7 Comments

Marc Gravell Over a year ago

There is a comment on those overloads: "The sqlCommand parameter contains a single Transact-SQL statement." - if this works as you describe, then the documentation is misleading.

Tao Over a year ago

@MarkGravell: You're right, the documentation is misleading, but it definitely works.

Enrico Campidoglio Over a year ago

I've used this method to programmatically execute T-SQL scripts that would normally run with SQLCMD. Check out the remarks for this overload.

Enrico Campidoglio Over a year ago

@MarcGravell I have to agree that the documentation could be clearer when it comes to batch processing.

Just a learner Over a year ago

Hi Enrico, thanks for answering. I've edited my question, please take a look. Thanks.

|

Community · Accepted Answer · 2017-05-23 12:27:47Z

1

What about SMO as suggested in the selected answer in this question: From .net how to I send many “batches” of SQL to Sql-Server without lots of round trips?

edited May 23, 2017 at 12:27

CommunityBot

11 silver badge

answered Mar 14, 2012 at 8:27

RidingTheRails

1,1051 gold badge12 silver badges23 bronze badges

Comments

Thorsten Dittmar · Accepted Answer · 2012-03-14 08:24:57Z

0

The SqlCommand can execute more than one statement in one go as long as the statement is wrapped in BEGIN and END, like a stored procedure. I've done that, but I think only data manipulation commands are allowed.

There are the SQL Server Management Objects which may be able to do what you want, as they can also be used to issue data definition commands.

answered Mar 14, 2012 at 8:24

Thorsten Dittmar

56.9k10 gold badges95 silver badges145 bronze badges

Collectives™ on Stack Overflow

In .Net, how to execute a SQL Server script against an instance?

3 Answers 3

7 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

7 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related