的SqlCommand（）的ExecuteNonQuery（）截断命令文本 [英] SqlCommand() ExecuteNonQuery() truncates command text
问题描述
我建立一个自定义的数据库部署实用工具，我需要阅读包含SQL脚本的文本文件，并执行它们对数据库。
很简单的东西，越远越好。
不过我遇到了阻碍，内容文件成功的，完全读取，但一旦传入的SqlCommand，然后用SqlCommand.ExecuteNonQuery仅执行脚本的一部分执行。
我发射了探查并确认我的代码是不是通过所有的脚本。
私人无效ExecuteScript（字符串CMD，SqlConnection的sqlConn，的SqlTransaction反式）
{
的SqlCommand SQLCMD =新的SqlCommand（CMD，sqlConn，反式）;
sqlCmd.CommandType = CommandType.Text;
sqlCmd.CommandTimeout = 9000000; //测试
sqlCmd.ExecuteNonQuery（）;
}
//我这样称呼它，readDMLScript包含543线T-SQL
串readDMLScript = ReadFile的（dmlFile）的;
ExecuteScript（readDMLScript，sqlConn，反式）;
是的，每个人都打这个障碍，他们第一次开始发送的SQL脚本文件的内容添加到数据库中。
GO 不是T-SQL命令。这是由所有Microsoft SQL交互工具（Management Studio中，ISQL，OSQL）认识到结束批量标记。为了处理它，你将不得不写自己的解析器来摆脱文本的每个块的文件中的 GO之间语句和它们提供给数据库作为单独的命令。
您如何实现您的解析器取决于你。这可能是简单的（在一段时间每一行读取，检测包括什么，但 GO 和空白行）或复杂的（tokenising所有语句和工作出是否 GO 是一个真正的语句或位串或多行注释）里面的文字。
我个人去了与第一个选项。它处理你可能永远没有大惊小怪遇到的所有SQL文件的99％。如果你要干到底，并写了tokeniser，我敢肯定，很多人已经做了一个已经，只是谷歌它
例如：
使用（VAR读卡器=新SqlBatchReader（新的StreamReader（dmlFile）））{
串批;
，而（（批号= reader.ReadBatch（））！= NULL）{
变种CMD =新的SqlCommand（批次，康涅狄格州，反）{=的CommandType} CommandType.Text;
cmd.ExecuteNonQuery（）;
}
}
类SqlBatchReader：IDisposable的{
私人的TextReader _reader;
公共SqlBatchReader（TextReader的读者）{
_reader =阅读器;
}
///<总结>
///返回下一个命令批处理文件中，或NULL如果文件结束，达成。
///< /总结>
公共字符串ReadBatch（）{
// TODO：在这里实现你的分析逻辑。
}
}
I'm building a custom db deployment utility, I need to read text files containing sql scripts and execute them against the database.
Pretty easy stuff, so far so good.
However I've encountered a snag, the contents of the file are read successfully and entirely, but once passed into the SqlCommand and then executed with SqlCommand.ExecuteNonQuery only part of the script is executed.
I fired up Profiler and confirmed that my code is not passing all of the script.
private void ExecuteScript(string cmd, SqlConnection sqlConn, SqlTransaction trans)
{
SqlCommand sqlCmd = new SqlCommand(cmd, sqlConn, trans);
sqlCmd.CommandType = CommandType.Text;
sqlCmd.CommandTimeout = 9000000; // for testing
sqlCmd.ExecuteNonQuery();
}
// I call it like this, readDMLScript contains 543 lines of T-SQL
string readDMLScript = ReadFile(dmlFile);
ExecuteScript(readDMLScript, sqlConn, trans);
Yep, everyone hits this snag the first time they start sending the contents of SQL script files to the database.
GO is not a T-SQL command. It's the end-of-batch marker recognised by all the Microsoft interactive SQL tools (Management Studio, isql, osql). In order to handle it, you will have to write your own parser to break out every block of text in the file between GO statements and feed them to the database as separate commands.
How you implement your parser is up to you. It could be simple (read in each line at a time, detect lines that consist of nothing but GO and whitespace) or complex (tokenising all the statements and working out whether a GO is a genuine statement or a bit of text inside a string or multi-line comment).
Personally I went with the first option. It handles 99% of all SQL files you are ever likely to encounter with no fuss. If you want to go the whole hog and write a tokeniser, I'm sure lots of people have done one already, just Google for it.
Example:
using(var reader = new SqlBatchReader(new StreamReader(dmlFile))) {
string batch;
while((batch = reader.ReadBatch()) != null) {
var cmd = new SqlCommand(batch, conn, trans) { CommandType = CommandType.Text };
cmd.ExecuteNonQuery();
}
}
class SqlBatchReader : IDisposable {
private TextReader _reader;
public SqlBatchReader(TextReader reader) {
_reader = reader;
}
/// <summary>
/// Return the next command batch in the file, or null if end-of-file reached.
/// </summary>
public string ReadBatch() {
// TODO: Implement your parsing logic here.
}
}
这篇关于的SqlCommand（）的ExecuteNonQuery（）截断命令文本的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
