如何从 C# 执行 .sql？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1449646/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How can I execute a .sql from C#?
提问by pupeno
For some integration tests I want to connect to the database and run a .sql file that has the schema needed for the tests to actually run, including GO statements. How can I execute the .sql file? (or is this totally the wrong way to go?)
对于某些集成测试，我想连接到数据库并运行一个 .sql 文件，该文件具有实际运行测试所需的架构，包括 GO 语句。如何执行 .sql 文件？（或者这完全是错误的方式？）
I've found a post in the MSDN forumshowing this code:
我在 MSDN 论坛中找到了一个帖子，显示了此代码：
using System.Data.SqlClient;
using System.IO;
using Microsoft.SqlServer.Management.Common;
using Microsoft.SqlServer.Management.Smo;
namespace ConsoleApplication1
{
class Program
{
static void Main(string[] args)
{
string sqlConnectionString = "Data Source=(local);Initial Catalog=AdventureWorks;Integrated Security=True";
FileInfo file = new FileInfo("C:\myscript.sql");
string script = file.OpenText().ReadToEnd();
SqlConnection conn = new SqlConnection(sqlConnectionString);
Server server = new Server(new ServerConnection(conn));
server.ConnectionContext.ExecuteNonQuery(script);
}
}
}
but on the last line I'm getting this error:
但在最后一行我收到此错误：
System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. ---> System.TypeInitializationException: The type initializer for '' threw an exception. ---> .ModuleLoadException: The C++ module failed to load during appdomain initialization. ---> System.DllNotFoundException: Unable to load DLL 'MSVCR80.dll': The specified module could not be found. (Exception from HRESULT: 0x8007007E).
System.Reflection.TargetInvocationException：调用的目标已抛出异常。---> System.TypeInitializationException: '' 的类型初始值设定项引发异常。---> .ModuleLoadException：C++ 模块在应用程序域初始化期间加载失败。---> System.DllNotFoundException: 无法加载 DLL 'MSVCR80.dll': 找不到指定的模块。（来自 HRESULT 的异常：0x8007007E）。
I was told to go and download that DLL from somewhere, but that sounds very hacky. Is there a cleaner way to? Is there another way to do it? What am I doing wrong?
有人告诉我去某个地方下载那个 DLL，但这听起来很hacky。有没有更干净的方法？还有另一种方法吗？我究竟做错了什么？
I'm doing this with Visual Studio 2008, SQL Server 2008, .Net 3.5SP1 and C# 3.0.
我正在使用 Visual Studio 2008、SQL Server 2008、.Net 3.5SP1 和 C# 3.0 执行此操作。
采纳答案by Matt Brunell
using Microsoft.SqlServer.Management.Common;
using Microsoft.SqlServer.Management.Smo;
You shouldn't need SMO to execute queries. Try using the SqlCommand object instead. Remove these using statements. Use this code to execute the query:
您不应该需要 SMO 来执行查询。尝试改用 SqlCommand 对象。删除这些 using 语句。使用此代码执行查询：
SqlConnection conn = new SqlConnection(sqlConnectionString);
SqlCommand cmd = new SqlCommand(script, conn);
cmd.ExecuteNonQuery();
Also, remove the project reference to SMO. Note: you will want to clean up resources properly.
此外，删除对 SMO 的项目引用。注意：您需要正确清理资源。
Update:
更新：
The ADO.NET libraries do not support the 'GO' keyword. It looks like your options are:
ADO.NET 库不支持“GO”关键字。看起来您的选择是：
- Parse the script. Remove the 'GO' keywords and split the script into separate batches. Execute each batch as its own SqlCommand.
- Send the script to SQLCMD in the shell (David Andres's answer).
- Use SMO like the code from the blog post.
- 解析脚本。删除“GO”关键字并将脚本拆分为单独的批次。将每个批处理作为其自己的 SqlCommand 执行。
- 将脚本发送到 shell 中的 SQLCMD（David Andres 的回答）。
- 像博客文章中的代码一样使用 SMO。
Actually, in this case, I think that SMO may be the best option, but you will need to track down why the dll wasn't found.
实际上，在这种情况下，我认为 SMO 可能是最佳选择，但您需要找出未找到 dll 的原因。
回答by Chris Thompson
Have you tried running this with a very, very basic script in the .sql file? Maybe something that just inserts one row or creates an arbitrary table? Something that is very easy to verify? Essentially, this code is like hard coding the sql, except you're reading it from a file. If you can get it to work with a very simple file, then I would say that there is likely something wrong with the file structure itself. The post alluded to the fact that there are some stipulations regarding what can and cannot be in the file. If nothing else, it's a good place to start troubleshooting.
您是否尝试过在 .sql 文件中使用非常非常基本的脚本来运行它？也许只是插入一行或创建一个任意表？很容易验证的东西？本质上，这段代码就像对 sql 进行硬编码，只不过您是从文件中读取它。如果您可以让它与一个非常简单的文件一起工作，那么我会说文件结构本身可能有问题。该帖子暗示了这样一个事实，即对文件中可以和不可以包含的内容有一些规定。如果不出意外，这是开始故障排除的好地方。
回答by David Andres
MSVCR80 is the Visual C++ 2005 runtime. You may need to install the runtime package. See http://www.microsoft.com/downloads/details.aspx?FamilyID=200b2fd9-ae1a-4a14-984d-389c36f85647&displaylang=enfor more details.
MSVCR80 是 Visual C++ 2005 运行时。您可能需要安装运行时包。有关更多详细信息，请参阅http://www.microsoft.com/downloads/details.aspx?FamilyID=200b2fd9-ae1a-4a14-984d-389c36f85647&displaylang=en。
In addition to resolving the DLL issue and Matt Brunell's answer (which I feel is more appropriate for what you're trying to do), you can use the SQLCMD command line tool (from the SQL Client tools installation) to execute these SQL scripts. Just be sure it's on your path so you don't struggle with path locations.
除了解决 DLL 问题和 Matt Brunell 的回答（我觉得这更适合您尝试执行的操作）之外，您还可以使用 SQLCMD 命令行工具（来自 SQL 客户端工具安装）来执行这些 SQL 脚本。只要确保它在您的路径上，这样您就不会在路径位置上挣扎。
This would play out like so:
这将是这样的：
Actual command:
实际命令：
SQLCMD -S myServer -D myDatabase -U myUser -P myPassword -i myfile.sql
Parameters (case matters):
参数（案例很重要）：
S: server
d: database
U: User name, only necessary if you don't want to use Windows authentication
P: Password, only necessary if you don't want to use Windows authentication
i: File to run
Code to execute SQL files:
执行SQL文件的代码：
var startInfo = new ProcessStartInfo();
startInfo.FileName = "SQLCMD.EXE";
startInfo.Arguments = String.Format("-S {0} -d {1}, -U {2} -P {3} -i {4}",
server,
database,
user,
password,
file);
Process.Start(startInfo);
See http://msdn.microsoft.com/en-us/library/ms162773.aspxfor more information on the SQLCMD tool.
有关SQLCMD 工具的详细信息，请参阅http://msdn.microsoft.com/en-us/library/ms162773.aspx。
回答by Thomas Weller
You may be interested in this: http://geekswithblogs.net/thomasweller/archive/2009/09/08/automating-database-script-execution.aspx
您可能对此感兴趣：http: //geekswithblogs.net/thomasweller/archive/2009/09/08/automating-database-script-execution.aspx
It presents a general-purpose 'test fixture' to automatically execute sql-scripts. There is also sample code available, and there are no dependencies to any unusual assemblies whatsoever...
它提供了一个通用的“测试装置”来自动执行 sql 脚本。还有可用的示例代码，并且不依赖任何不寻常的程序集......
回答by Miguel
If you add following references in your project, then original code will work fine.
如果您在项目中添加以下引用，那么原始代码将正常工作。
I use SQL 2008 Express.
我使用 SQL 2008 Express。
Path: C:\Program Files (x86)\Microsoft SQL Server\100\SDK\Assemblies\
路径：C:\Program Files (x86)\Microsoft SQL Server\100\SDK\Assemblies\
Files: microsoft.sqlserver.smo.dll, microsoft.sqlserver.connectioninfo.dll and Microsoft.SqlServer.Management.Sdk.Sfc.dll
文件：microsoft.sqlserver.smo.dll、microsoft.sqlserver.connectioninfo.dll 和 Microsoft.SqlServer.Management.Sdk.Sfc.dll
回答by Anlo
Having the same need to automatically run a generated database script from code, I set out to parse the SQL script to remove GO statements and split the script into separate commands (as suggested by @MattBrunell). Removing the GO statements was easy, but splitting the statements on "\r\n"did not work since that screwed up the multiline-statements. Testing a few different approaches, I was quite surprised to find out that the script doesn't have to be split into separate commands at all. I just removed all "GO" statements and sent the whole script (including comments) into SqlCommand:
由于同样需要从代码自动运行生成的数据库脚本，我开始解析 SQL 脚本以删除 GO 语句并将脚本拆分为单独的命令（如@MattBrunell 所建议的那样）。删除 GO 语句很容易，但拆分语句"\r\n"不起作用，因为这会破坏多行语句。测试了几种不同的方法，我很惊讶地发现脚本根本不需要拆分成单独的命令。我刚刚删除了所有“GO”语句并将整个脚本（包括注释）发送到 SqlCommand：
using System.Data.SqlClient;
using(SqlConnection connection = new SqlConnection(connectionString))
using(SqlCommand command = connection.CreateCommand())
{
string script = File.ReadAllText("script.sql");
command.CommandText = script.Replace("GO", "");
connection.Open();
int affectedRows = command.ExecuteNonQuery();
}
This code has been tested with SQL Server 2008 R2 and the script generated by "Database -> Tasks -> Generate Scripts...". Below are some examples of the commands in the script:
此代码已使用 SQL Server 2008 R2 和“数据库 -> 任务 -> 生成脚本...”生成的脚本进行了测试。以下是脚本中命令的一些示例：
USE [MyDatabase]
ALTER TABLE [MySchema].[MyTable] DROP CONSTRAINT [FK_MyTable_OtherTable]
DROP TABLE [MySchema].[MyTable]
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
/****** Object: Table [MySchema].[MyTable] Script Date: 01/23/2013 13:39:29 ******/
CREATE TABLE [MySchema].[MyTable](
[Id] [int] IDENTITY(1,1) NOT NULL,
[Subject] [nvarchar](50) NOT NULL,
[Body] [nvarchar](max) NOT NULL,
CONSTRAINT [PK_MyTable] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
SET IDENTITY_INSERT [MySchema].[MyTable] ON
INSERT [MySchema].[MyTable] ([Id], [Subject], [Body]) VALUES (1, N'MySubject', N'Line 1
Line 2
Line 3
Multi-line strings are also OK.
')
SET IDENTITY_INSERT [MySchema].[MyTable] OFF
I guess there might be some maximum length for a single SqlCommand, above which the script have to be split. My script, which execute without problems, contains around 1800 statements and is 520 kB.
我想单个 SqlCommand 可能有一些最大长度，超过该长度的脚本必须被拆分。我的脚本可以毫无问题地执行，包含大约 1800 条语句，大小为 520 kB。
