SSMS SMO对象：获取查询结果 [英] SSMS SMO Objects: Get query results

问题描述

我碰到<一href="http://weblogs.asp.net/jgalloway/archive/2006/11/07/Handling-_2200_GO_2200_-Separators-in-SQL-Scripts-_2D00_-the-easy-way.aspx"相对=nofollow>这个的教程，以了解如何与GO语句执行SQL脚本。
现在，我想知道我能得到消息选项卡的输出。

I came across this tutorial to understand how to execute SQL scripts with GO statements.
Now I want to know what can I get the output of the messages TAB.

通过几个GO语句，输出会是这样：
1行受到影响
912行受到影响
......

With several GO statements, the output would be like this:
1 rows affected
912 rows affected
...

但server.ConnectionContext.ExecuteNonQuery（）只能返回一个int，而我需要的所有文本。万一有在查询的一些部分的一些错误，应该把那个也在输出中。 任何帮助将是AP preciated。

But server.ConnectionContext.ExecuteNonQuery() can return only an int, while I need all the text. In case there is some error in some part of query, it should put that also in the output. Any help would be appreciated.

推荐答案

最简单的事情是可能只打印你回来的次数的ExecuteNonQuery ：

The easiest thing is possibly to just print the number you get back for ExecuteNonQuery:

int rowsAffected = server.ConnectionContext.ExecuteNonQuery(/* ... */);
if (rowsAffected != -1)
{
     Console.WriteLine("{0} rows affected.", rowsAffected);
}

这应该工作，但不会兑现本届会议/范围 SET NOCOUNT 设置。

This should work, but will not honor the SET NOCOUNT setting of the current session/scope.

否则你会像你那样做将与普通ADO.NET做。不要使用 ServerConnection.ExecuteNonQuery（）的方法，而是通过访问底层<$ C创建一个的SqlCommand 对象$ C>的SqlConnection 对象。在那个订阅 StatementCompleted 事件。

Otherwise you would do it like you would do with "plain" ADO.NET. Don't use the ServerConnection.ExecuteNonQuery() method, but create an SqlCommand object by accessing the underlying SqlConnection object. On that subscribe to the StatementCompleted event.

using (SqlCommand command = server.ConnectionContext.SqlConnectionObject.CreateCommand())
{
    // Set other properties for "command", like StatementText, etc.

    command.StatementCompleted += (s, e) => {
         Console.WriteLine("{0} row(s) affected.", e.RecordCount);
    };

    command.ExecuteNonQuery();
}

使用 StatementCompleted （而不是，比方说，手工印刷的的ExecuteNonQuery（）返回的值）的优点在于它的工作原理完全一样SSMS或SQLCMD.EXE将：

Using StatementCompleted (instead, say, manually printing the value that ExecuteNonQuery() returned) has the benefit that it works exactly like SSMS or SQLCMD.EXE would:

• 有关命令没有一个ROWCOUNT将不会被调用所有（如GO，使用）。
• 如果 SET NOCOUNT ON 设置，也不会叫的。
• 如果 SET NOCOUNT OFF 设置，它会被称为一个批次内的每个语句。

• For commands that do not have a ROWCOUNT it will not be called at all (e.g. GO, USE).
• If SET NOCOUNT ON was set, it will not be called at all.
• If SET NOCOUNT OFF was set, it will be called for every statement inside a batch.

（边栏：它看起来像 StatementCompleted 正是对TDS协议谈判时， DONE_IN_PROC 事件被提及;看到SET NOCOUNT命令备注 MSDN上。）

(Sidebar: it looks like StatementCompleted is exactly what the TDS protocol talks about when DONE_IN_PROC event is mentioned; see Remarks of the SET NOCOUNT command on MSDN.)

就个人而言，我已经用这种方法成功的SQLCMD.EXE我自己的克隆。

Personally, I have used this approach with success in my own "clone" of SQLCMD.EXE.

更新：应该指出，这种方法（当然）需要您手动拆分输入脚本/在 GO语句分离器，因为你回使用 SqlCommand.Execute *（）不能同时处理多个批次。对于这一点，可以有多种选择：

UPDATE: It should be noted, that this approach (of course) requires you to manually split the input script/statements at the GO separator, because you're back to using SqlCommand.Execute*() which cannot handle multiple batches at a time. For this, there are multiple options:

• 手动拆分输入上开始 GO行（警告： GO 可以被称为像 GO 5 ，例如，执行previous一批5次）。
• 使用 ManagedBatchParser 类/库，帮助你分割输入进入单批次，特别是落实 ICommandExecutor.ProcessBatch 与code以上（或者类似的东西它）。

• Manually split the input on lines starting with GO (caveat: GO can be called like GO 5, for example, to execute the previous batch 5 times).
• Use the ManagedBatchParser class/library to help you split the input into single batches, especially implement ICommandExecutor.ProcessBatch with the code above (or something resembling it).

我选择后者选项，这是相当一些工作，因为它不是pretty的有据可查和例子并不多见（google了一下，你会发现一些东西，或者使用反射来看看如何SMO -Assemblies使用这个类）。

I choose the later option, which was quite some work, given that it is not pretty well documented and examples are rare (google a bit, you'll find some stuff, or use reflector to see how the SMO-Assemblies use that class).

好处采用的（也许负担）的 ManagedBatchParser 是，它也将分析的T-SQL脚本中的所有其他结构（用于 SQLCMD.EXE ）为您服务。其中包括：：SETVAR ，：连接，：退出，等你不必执行相应的 ICommandExecutor 会员，如果你的脚本不使用他们，当然。但介意你，你可能无法执行乱的脚本。

The benefit (and maybe burden) of using the ManagedBatchParser is, that it will also parse all other constructs of T-SQL scripts (intended for SQLCMD.EXE) for you. Including: :setvar, :connect, :quit, etc. You don't have to implement the respective ICommandExecutor members, if your scripts don't use them, of course. But mind you that you'll may not be able to execute "arbitrary" scripts.

嗯，是这样做就把你。从如何打印......受影响的行这样的事实，这不是容易做到的一个强大的和通用的方式简单问题（给予必要的工作背景）。情况因人而异，祝你好运。

Well, were did that put you. From the "simple question" of how to print "... rows affected" to the fact that it is not trivial to do in a robust and general manner (given the background work required). YMMV, good luck.

在ManagedBatchParser使用的更新

Update on ManagedBatchParser Usage

目前似乎没有很好的documenation或示例有关如何实施 IBatchSource ，这里是我去。

There seems to be no good documenation or example about how to implement IBatchSource, here is what I went with.

internal abstract class BatchSource : IBatchSource
{
    private string m_content;

    public void Populate()
    {
        m_content = GetContent();
    }

    public void Reset()
    {
        m_content = null;
    }

    protected abstract string GetContent();

    public ParserAction GetMoreData(ref string str)
    {
        str = null;

        if (m_content != null)
        {
            str = m_content;
            m_content = null;
        }

        return ParserAction.Continue;
    }
}

internal class FileBatchSource : BatchSource
{
    private readonly string m_fileName;

    public FileBatchSource(string fileName)
    {
        m_fileName = fileName;
    }

    protected override string GetContent()
    {
        return File.ReadAllText(m_fileName);
    }
}

internal class StatementBatchSource : BatchSource
{
    private readonly string m_statement;

    public StatementBatchSource(string statement)
    {
        m_statement = statement;
    }

    protected override string GetContent()
    {
        return m_statement;
    }
}

这是你将如何使用它：

And this is how you would use it:

var source = new StatementBatchSource("SELECT GETUTCDATE()");
source.Populate();

var parser = new Parser(); 
parser.SetBatchSource(source);
/* other parser.Set*() calls */

parser.Parse();

请注意，这两个实现，无论是直接声明（ StatementBatchSource ）或文件（ FileBatchSource ）有他们阅读的完整文本一次问题 到存储器中。我有一个情况下，即炸毁，有一个巨大的（！）脚本生成插入语句gazillions。虽然我不认为这是一个实际的问题， SQLCMD.EXE 可以处理它。但对我的生活，我无法弄清楚到底如何， 你需要形成返回 IBatchParser.GetContent（）这样的块的 解析器仍然可以与他们的工作（它看起来像他们将需要完整的陈述， 排序它会破坏解析的目的摆在首位...）。

Note that both implementations, either for direct statements (StatementBatchSource) or for a file (FileBatchSource) have the problem that they read the complete text at once into memory. I had one case where that blew up, having a huge(!) script with gazillions of generated INSERT statements. Even though I don't think that is a practical issue, SQLCMD.EXE could handle it. But for the life of me, I couldn't figure out how exactly, you would need to form the chunks returned for IBatchParser.GetContent() so that the parser can still work with them (it looks like they would need to be complete statements, which would sort of defeat the purpose of the parse in the first place...).

这篇关于SSMS SMO对象：获取查询结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！