C#:如何以编程方式将SQL脚本导入数据库? [英] C#: How to import SQL-script into database programmatically?

问题描述

我是否必须手动解析SQL脚本并分别执行每个语句，还是有更好的方法? 我正在寻找一个编程解决方案，我知道已经有一些工具可以做到这一点. 如果该解决方案适用于所有数据库系统，而不仅适用于sqlite，那就太好了.

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.

推荐答案

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

I'm not sure how this applies to SqlLite, but I've used code like the following with 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.)

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

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.

@Mark在下面的评论中，"GO"可能作为另一个词的一部分出现在脚本中绝对是一个有效的问题.我记得当我编写上面的代码时，我对脚本进行了搜索，以确保"GO"仅作为批处理分隔符出现.另一种可行的方法(不要双关语)是编辑所有脚本，以便始终在"GO"后面加上相同的注释，例如--SPLIT HERE！".并将您的分隔符更改为"GO-SPLIT HERE！".但是，这需要您编辑脚本.另一个选项是Regex.Split()，它将允许您在GO之前检查空白.在我的脚本中，我的批处理分隔符总是按自己的方式行.如果对您的脚本应用相同的规则，则应执行以下操作:

@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:

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.

这篇关于C#:如何以编程方式将SQL脚本导入数据库?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！