Geek Answers Handbook

From .net how can I send a lot of SQL batches to Sql-Server without a lot of round trip?

We have the code that is read in the SQL script file set, and after doing some processing on them, they break them into groups by typing the keyword "GO" and then send each batch to Sql Server using a separate SqlCommon.

Is there a better way to do this so that we:

  • Dont have so many round trips
  • Never expect SQL Server to expect the following command
  • It is faster.

(Packages mainly create tables, indexes, views, and stored processes. Speed ​​is a problem, because our integration tests often type code. The standard sql server line tool cannot be installed on the machine that runs this code.)

+5
source share
2 answers

The fastest solution actually splits into GO. However, another alternative is to use SQL Management Objects (SMOs) and submit the entire script to SQL Server in one fell swoop, as if you were using Management Studio.

var connectionString = ConfigurationManager.ConnectionStrings[ connectionStringName ].ConnectionString;
using ( var sqlConnection = new SqlConnection( connectionString ) )
{
    var server = new Server( new ServerConnection( sqlConnection ) );
    server.ConnectionContext.Connect();
    server.ConnectionContext.ExecuteNonQuery( sqlFileContents );
    server.ConnectionContext.Disconnect();
}

SQL Server Management Objects (SMOs)

+2
source

2 ideas ...

Build the script and change it as executable as a dynamic SQL fragment. Download the entire batch in the stream using nvarchar (max) and run it using sp_executesql on the server side. Since you control it for integration testing, dynamic SQL is not a big problem.

nvarchar (max). SQL Server xp_cmdshell CLR . xp_cmdshell, sqlcmd script.

, , - .

+3

More articles:


All Articles