C＃：如何以编程方式将SQL脚本导入数据库？

Do I have to parse the SQL-script manually and execute each statement separately or are there better ways? Iam looking for a programmatically solution, I know there are tools which are already able to do this. It would be good if the solution would work for all database systems, not just sqlite.

我是否必须手动解析SQL脚本并单独执行每个语句,还是有更好的方法?我寻找一个以编程方式解决方案,我知道有些工具已经能够做到这一点。如果解决方案适用于所有数据库系统,而不仅仅是sqlite,那将是一件好事。

5 个解决方案

#1

I'm not sure how this applies to SqlLite, but I've used code like the following with SqlServer:

我不确定这是如何适用于SqlLite的,但我在SqlServer中使用了以下代码:

protected virtual void ExecuteScript(SqlConnection connection, string script)
{
    string[] commandTextArray = script.Split(new string[] { "GO" }, StringSplitOptions.RemoveEmptyEntries); // See EDIT below!
    connection.Open();
    foreach (string commandText in commandTextArray)
    {
        if (commandText.Trim() == string.Empty) continue;
        SqlCommand command = new SqlCommand(commandText, connection);
        command.ExecuteNonQuery();
    }
    connection.Close();
}

(Warning: I had to modify my original code a little bit so this is untested code.)

(警告:我不得不稍微修改我的原始代码,所以这是未经测试的代码。)

I found that the word "GO" was a problem when running scripts via ADO.NET, so the code does a Split() operation on the overall script with "GO" as the delimiter, then loops through the array of commands and executes them one at a time.

我发现在通过ADO.NET运行脚本时“GO”这个词是个问题,因此代码对整个脚本执行Split()操作,并使用“GO”作为分隔符,然后遍历命令数组并执行它们一次一个。

EDIT:

@Mark's comment below that "GO" might appear in a script as part of another word is definitely a valid concern. I remember when I wrote the above code, I did a search of my scripts to ensure that "GO" only appeared as a batch separator. Another way to go (no pun intended) would be to edit all your scripts so that "GO" is always followed by the same comment e.g., " -- SPLIT HERE!" and change your delimiter to "GO -- SPLIT HERE!". This requires you to edit your scripts, however. Yet another option is Regex.Split(), which would allow you to check for white space before GO. In my scripts, my batch separators always go on their own line. If the same rule applies to your script, something like the following ought to work:

@Mark在下面的评论中,“GO”可能会出现在脚本中,因为另一个词的一部分肯定是一个有效的问题。我记得当我编写上面的代码时,我搜索了我的脚本以确保“GO”仅作为批处理分隔符出现。另一种方法(没有双关语)将编辑所有脚本,以便“GO”始终跟随相同的注释,例如“ - SPLIT HERE!”并将分隔符更改为“GO - SPLIT HERE!”。但是,这需要您编辑脚本。另一个选项是Regex.Split(),它允许您在GO之前检查空白区域。在我的脚本中,我的批处理分隔符总是独立存在。如果相同的规则适用于您的脚本,则以下内容应该起作用:

string[] commandTextArray = System.Text.RegularExpressions.Regex.Split(script, "\r\n[\t ]*GO");

The bottom line is that some formatting consistency for batch separators is required to accurately split up your script.

最重要的是,批量分隔符的某些格式一致性是准确分割脚本所必需的。

#2

Using the DbCommand.ExecuteNonQuery method you are able execute any SQL against the database.

使用DbCommand.ExecuteNonQuery方法,您可以对数据库执行任何SQL。

Sample with a SqlCommand:

使用SqlCommand的示例:

var command = new SqlCommand("CREATE TABLE foo ...", connection);
command.ExecuteNonQuery();

As suggested in another answer you may need to split the command if it contains 'GO', since that will split the command in multiple batches.

正如在另一个答案中建议的那样,如果命令包含'GO',则可能需要拆分命令,因为这将分批命令分割命令。

#3

I'm not familiar with SqlLite, but most RDBMS' will let you send multiple statements if you end them with semicolons. Some, like MSSQL, will also accept newline as a statement terminator. Semicolon + newline makes most happy, and leads to well formatted code as well.

我不熟悉SqlLite,但是如果用分号结束,大多数RDBMS都会让你发送多个语句。有些像MSSQL一样,也会接受newline作为语句终止符。分号+换行符使得最开心,并且还可以生成格式良好的代码。

If you have batch separators - in MSSQL, it's usually "GO" - then you have to do a bit more work, as that's parsed by the client. There's been a couple of solutions to handle MSSQL - but I don't know how portable (if at all) they are. I'd suspect Haack's would be the best starting point.

如果你有批处理分隔符 - 在MSSQL中,它通常是“GO” - 那么你必须做更多的工作,因为它是由客户端解析的。有几种处理MSSQL的解决方案 - 但我不知道它们是多么便携(如果有的话)。我怀疑哈克是最好的起点。

#4

In the following code Config.Database.CreateCommandText is a collection of semicolon separated statements for creating tables and indexes in a database. The following creates a DB from nothing using System.Data.SQLite.

在以下代码中,Config.Database.CreateCommandText是用于在数据库中创建表和索引的分号分隔语句的集合。以下使用System.Data.SQLite从无创建数据库。

private SQLiteCommand command;
private SQLiteConnection connection;

connection = new SQLiteConnection(Config.Database.ConnectionString.ToString());
connection.Open();

command = connection.CreateCommand();

SQLiteConnection.CreateFile(Config.Database.Path);

command.CommandText = Config.Database.CreateCommandText;

command.ExecuteNonQuery();

#5

I am agree with Damm's answer: Handling "GO" Separators in SQL Scripts - the easy way http://weblogs.asp.net/jgalloway/archive/2006/11/07/Handling-_2200_GO_2200_-Separators-in-SQL-Scripts-2D00-the-easy-way.aspx you will require DDLs that can be found in SQL Server folder "C:\Program Files (x86)\Microsoft SQL Server\100\SDK\Assemblies\"

我同意Damm的回答:在SQL脚本中处理“GO”分隔符 - 简单的方法http://weblogs.asp.net/jgalloway/archive/2006/11/07/Handling-_2200_GO_2200_-Separators-in-SQL-Scripts -2D00-the-easy-way.aspx你将需要可以在SQL Server文件夹“C:\ Program Files(x86)\ Microsoft SQL Server \ 100 \ SDK \ Assemblies”中找到的DDL

Microsoft.SqlServer.ConnectionInfo.dll
Microsoft.SqlServer.Management.Sdk.Sfc.dll
Microsoft.SqlServer.Smo.dll

Also required compatible Framwork, so add this line in your config file.

还需要兼容的Framwork,因此请在配置文件中添加此行。

<startup useLegacyV2RuntimeActivationPolicy="true">
    <supportedRuntime version="v4.0" />
</startup>

The answer given in this post need to be more refined. for instance, handdling exceptions and be able to rollback... please check http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqltransaction.aspx for more information in SQL Transactions.

这篇文章中给出的答案需要更加精炼。例如,处理异常并能够回滚...请查看http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqltransaction.aspx以获取有关SQL事务的更多信息。

#1