使用C＃SQL Server上运行SQL文件 [英] Execute sql file on SQL Server using C#

问题描述

我有一个过程，视图，功能等多个文件。

I have many files for procedures, views, functions, etc.

我要到SQL Server 2005/2008上执行这些文件（创建组件）在适当的数据库

I want to execute these files (creating components) in appropriate database on SQL Server 2005/2008.

还有一点就是我想用C＃来执行它们。

Also the point is I want to execute them using C#.

还有一点要提，我想应用程序是这样的，我可以在远程SQL Server上执行这个文件了。此外客户机可能没有OSQL，SQLCMD命令工具。

Another point to mention, I want the application to be such that I can execute this files on a remote SQL Server too. Also client machine may not have osql,sqlcmd command tool.

有人可以请指导我在此。

Can someone please guide me on this.

推荐答案

这取决于他们是什么样的文件。如果，例如，他们的只有的包含实际的T-SQL命令（而不是的批量的文件，您会运行，也就是说，SSMS，其中将包括一个批处理分离像 GO ），那么你只需要创建一个连接，命令，然后读取文件的内容，并用它来填充的CommandText 命令的属性。

This depends on what sort of files they are. If, for example, they only contain actual T-SQL commands (and aren't batch files that you'd run in, say, SSMS, which would contain a batch separator like GO), then you just need to create a connection, a command, then read the contents of the file and use that to populate the CommandText property of the command.

例如：

void ExecuteFile(string connectionString, string fileName)
{
    using(SqlConnection conn = new SqlConnection(connectionString))
    {
        string data = System.IO.File.ReadAllText(fileName);

        conn.Open();

        using(SqlCommand cmd = conn.CreateCommand())
        {
            cmd.CommandText = data;
            cmd.ExecuteNonQuery();
        }
    }
}

如果它是一个批处理文件，你需要分割的文件到个别批次和单独处理的。最简单的方法是只使用 string.Split ，但请记住，它不会尊重SQL解析规则，当分裂（例如，如果 GO 出现在SQL语句中，它会起来拆分命令分为两个批次，这显然会失败）。

If it's a batch file, you'll need to split the file into individual batches and process those individually. The simplest method is just to use string.Split, but bear in mind that it won't respect SQL parsing rules when it splits (for example, if GO appears within a SQL statement, it's going to split the command up into two batches, which will obviously fail).

更一般地，你可以看到你需要通过修改代码以这种方式在这里做什么：

More generally, you can see what you'd need to do here by modifying the code in this way:

string[] batches = SplitBatches(System.IO.File.ReadAllText(fileName));

conn.Open();

using(SqlCommand cmd = conn.CreateCommand())
{
    foreach(string batch in batches)
    {
        cmd.CommandText = batch;
        cmd.ExecuteNonQuery();
    }
}

函数的实现一个名为 SplitBatches 是由你。

这篇关于使用C＃SQL Server上运行SQL文件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！