Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
607 views
in Technique[技术] by (71.8m points)

c# - SqlCommand() ExecuteNonQuery() truncates command text

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);
See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)

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.
    }
}

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

1.4m articles

1.4m replys

5 comments

57.0k users

...