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

问题描述

我想通过 ADO.NET 使用我的 ASP.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?

推荐答案

查看我关于在 SQL 中处理 GO 分隔符 - 简单方法.诀窍是使用 SMO 的 ExecuteNonQuery() 方法.例如，以下代码将运行目录中的所有脚本，而不管 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屋！