我怎么能执行从C#一个.SQL? [英] How can I execute a .sql from C#?
问题描述
对于一些集成测试,我想连接到数据库,并运行具有所需的测试实际运行,包括GO语句模式的.sql文件。我怎么能执行.sql文件? (或这是完全错误的方式去?)
我发现<一个href=\"http://social.msdn.microsoft.com/Forums/en-US/adodotnetdataproviders/thread/43e8bc3a-1132-453b-b950-09427e970f31\">a张贴在MSDN论坛显示此code:
使用System.Data.SqlClient的;
使用System.IO;
使用Microsoft.SqlServer.Management.Common;
使用Microsoft.SqlServer.Management.Smo;命名空间的ConsoleApplication1
{
类节目
{
静态无效的主要(字串[] args)
{
字符串sqlConnectionString =数据源=(本地);初始目录= AdventureWorks的;集成安全性=真;
FileInfo的文件=新的FileInfo(C:\\\\ myscript.sql);
字符串脚本= file.OpenText()ReadToEnd的()。
康涅狄格州的SqlConnection =新的SqlConnection(sqlConnectionString);
Server服务器=新服务器(新ServerConnection(康涅狄格州));
server.ConnectionContext.ExecuteNonQuery(脚本);
}
}
}
但在最后一行我得到这个错误:
System.Reflection.TargetInvocationException:
异常被抛出的
调用的目标。 --->
System.TypeInitializationException:
对于''的类型初始值
引发了异常。 --->
.ModuleLoadException:
C ++的模块失败过程中加载
AppDomain中初始化。 --->
System.DllNotFoundException:无法
加载DLL MSVCR80.DLL:指定的
模块无法找到。 (例外
从HRESULT:0x8007007E)
块引用>有人告诉我去和从什么地方下载DLL,但是这听起来很哈克。是否有一个更清洁的方式吗?是否有另一种方式做到这一点?我在做什么错了?
我与Visual Studio 2008,SQL Server 2008中,净3.5SP1和C#3.0。
操作解决方案使用Microsoft.SqlServer.Management.Common;
使用Microsoft.SqlServer.Management.Smo;您不应该需要SMO执行查询。尝试使用SqlCommand对象来代替。删除这些using语句。使用此code来执行查询:
SqlConnection的康恩=新的SqlConnection(sqlConnectionString);
CMD的SqlCommand =新的SqlCommand(脚本,康涅狄格州);
cmd.ExecuteNonQuery();此外,除去项目引用SMO。注意:您将要妥善清理资源
更新:
在ADO.NET库<一个href=\"http://weblogs.asp.net/jgalloway/archive/2006/11/07/Handling-%5F2200%5FGO%5F2200%5F-Separators-in-SQL-Scripts-%5F2D00%5F-the-easy-way.aspx\">do不支持'GO'关键字。它看起来像你的选择是:
- 解析脚本。删除'GO'的关键字和脚本拆分为单独批次。执行每批作为自己的SqlCommand。
- 发送脚本SQLCMD在外壳(大卫·安德烈斯的答案)。
- 使用SMO像博客文章的code。
其实,在这种情况下,我认为SMO可能是最好的选择,但是你需要追踪为什么没有找到DLL。
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?)
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: 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).
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?
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;
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();
Also, remove the project reference to SMO. Note: you will want to clean up resources properly.
Update:
The ADO.NET libraries do not support the 'GO' keyword. It looks like your options are:
- 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.
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屋!