从.NET如何我发送SQL很多“批”到SQL服务器没有大量往返？ [英] From .net how to I send many “batches” of SQL to Sql-Server without lots of round trips?

问题描述

我们有code，它读取一组SQL脚本文件，并做一些处理在他们之后把他们分为批次通过寻找走出去的关键字，然后将每一批到SQL Server使用单独的SqlCommon。

We have code that reads in a set of SQL script file, and after doing some processing on them splits them into batches by finding the "GO" keyword and then sends each batch to Sql Server using a separate SqlCommon.

是否有这样做，所以我们一个更好的方式：

Is there a better way of doing this so we:

• 请不要有尽可能多的往返
• 从不具有SQL Server等待下一批
• 运行得更快。

（该批次多是创建表，索引，视图和存储的特效，速度是一个问题，因为我们的集成测试时会调用code通常的SQL服务器的公用线工具可能无法在机器上安装运行此code）

(The batches are mostly creating tables, index, views and stored procs. Speed is an issue as our integration tests calls the code often. The sql-server common line tools may not be installed on the machine that is running this code.)

推荐答案

最快的解决办法是在GO事实上分裂。然而，另一种选择是使用SQL管理对象（SMO）和发送整个脚本到SQL Server一举因为如果你使用Management Studio。

The fastest solution is in fact splitting on GO. However, another alternative is to use the SQL Management Objects (SMO) and send 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管理对象（SMO）

这篇关于从.NET如何我发送SQL很多“批”到SQL服务器没有大量往返？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！