我如何运行我通过ADO.NET .SQL脚本文件？ [英] How do I run my .sql script file through ADO.NET?

问题描述

我想用我的ASP.NET网站通过ADO.NET来运行我的.SQL脚本文件。怎么可能是它不工作？

I want to run my .sql script file using my ASP.NET website through ADO.NET. How it could be it is not working?

当我尝试

'dbScript is a string and contains contents of the .sql file'
Dim cmd As New SqlCommand(dbScript, con)
Try
    con.Open()
    cmd.ExecuteNonQuery()
Catch ex As Exception
Finally
    con.Close()
    cmd.Dispose()
End Try

我得到异常的时候GO语句在脚本执行。我该如何解决这个问题呢？

I get exceptions when GO statement executed in script. How can I fix this problem?

推荐答案

请参阅有关<一个我的博客文章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">Handling GO分离器在SQL - 轻松的途径。诀窍是使用<一个href="http://msdn2.microsoft.com/en-us/library/microsoft.sqlserver.management.common.serverconnection.executenonquery.aspx">SMO's的ExecuteNonQuery（）方法。例如，这里的一些code将运行GO分离器中的所有脚本的目录，无论：

See my blog post about Handling GO Separators in SQL - The Easy Way. The trick is to use SMO's ExecuteNonQuery() method. For example, here's some code that will run all scripts in a directory, regardless of GO separators:

	using System;
	using System.IO;
	using System.Data.SqlClient;
	using System.Collections.Generic;

	//Microsoft.SqlServer.Smo.dll
	using Microsoft.SqlServer.Management.Smo;
	//Microsoft.SqlServer.ConnectionInfo.dll
	using Microsoft.SqlServer.Management.Common;

	public class RunAllSqlSriptsInDirectory
	{
	    public static void Main()
	    {
	        string scriptDirectory = "c:\\temp\\sqltest\\";
	        string sqlConnectionString = "Integrated Security=SSPI;" + 
	            "Persist Security Info=True;Initial Catalog=Northwind;Data Source=(local)";
	        DirectoryInfo di = new DirectoryInfo(scriptDirectory);
	        FileInfo[] rgFiles = di.GetFiles("*.sql");
	        foreach (FileInfo fi in rgFiles)
	        {
	            FileInfo fileInfo = new FileInfo(fi.FullName);
	            string script = fileInfo.OpenText().ReadToEnd();
	            SqlConnection connection = new SqlConnection(sqlConnectionString);
	            Server server = new Server(new ServerConnection(connection));
	            server.ConnectionContext.ExecuteNonQuery(script);
	        }
	    }
	}

这篇关于我如何运行我通过ADO.NET .SQL脚本文件？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！