如何从 C# 执行 .sql? [英] How can I execute a .sql from C#?

问题描述

对于某些集成测试，我想连接到数据库并运行一个 .sql 文件，该文件具有实际运行测试所需的架构，包括 GO 语句.如何执行 .sql 文件?(或者这完全是错误的方式?)

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?)

我发现 MSDN 论坛中的一篇帖子显示了此代码:

I've found a post in the MSDN forum showing this code:

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:异常已被抛出调用的目标.--->System.TypeInitializationException:'' 的类型初始值设定项抛出异常.--->.ModuleLoadException:C++ 模块加载失败应用域初始化.--->System.DllNotFoundException:无法加载 DLL 'MSVCR80.dll':指定的找不到模块.(例外来自 HRESULT:0x8007007E).

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).

有人告诉我去某个地方下载那个 DLL，但这听起来很hacky.有没有更干净的方法?还有另一种方法吗?我做错了什么?

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?

我正在使用 Visual Studio 2008、SQL Server 2008、.Net 3.5SP1 和 C# 3.0 执行此操作.

I'm doing this with Visual Studio 2008, SQL Server 2008, .Net 3.5SP1 and C# 3.0.

推荐答案

using Microsoft.SqlServer.Management.Common;
using Microsoft.SqlServer.Management.Smo;

您不应该需要 SMO 来执行查询.尝试改用 SqlCommand 对象.删除这些 using 语句.使用此代码执行查询:

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:

 SqlConnection conn = new SqlConnection(sqlConnectionString);
 SqlCommand cmd = new SqlCommand(script, conn);
 cmd.ExecuteNonQuery();

另外，删除对 SMO 的项目引用.注意:您需要正确清理资源.

Also, remove the project reference to SMO. Note: you will want to clean up resources properly.

ADO.NET 库 不支持'GO'关键字.看起来您的选择是:

The ADO.NET libraries do not support the 'GO' keyword. It looks like your options are:

1. 解析脚本.删除GO"关键字并将脚本拆分为单独的批次.将每个批处理作为其自己的 SqlCommand 执行.
2. 将脚本发送到 shell 中的 SQLCMD(David Andres 的回答).
3. 像博客文章中的代码一样使用 SMO.

实际上，在这种情况下，我认为 SMO 可能是最好的选择，但您需要找出找不到 dll 的原因.

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.

这篇关于如何从 C# 执行 .sql?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！