返回已处理记录数的 SQL Server 存储过程 [英] SQL Server stored procedure that returns processed records number

问题描述

我有一个 Winforms 应用程序，它执行一个检查多行(大约 500k)的存储过程.为了通知用户已经处理了多少行，我需要一个存储过程，它每 n 行返回一个值.例如，每处理 1000 行(大多数是 INSERT).

I have a Winforms application that executes a stored procedure which examines several rows (around 500k). In order to inform the user about how many rows have already been processed, I would need a stored procedure which returns a value every n rows. For example, every 1000 rows processed (most are INSERT).

否则我只能在处理所有行时通知.任何提示如何解决这个问题?

Otherwise I would be able only to inform when ALL rows are processed. Any hints how to solve this?

我认为使用触发器或某些计划任务会很有用，但我不知道如何实现它.

I thought it could be useful to use a trigger or some scheduled task, but I cannot figure out how to implement it.

推荐答案

所以这是一个非常有趣的问题.我大约 5 年前尝试过但没有成功，所以这对我来说是一个小挑战:) 好吧，这就是我为您准备的.

So this is very interesting question. I've tried it about 5 years ago with no success, so this is a little challenge for me :) Well, here's is what I've got for you.

要从 SQL Server 发送消息，您需要使用带有 nowait 选项的 raiserror 命令.所以我写了一个存储过程

To send message from SQL Server you need to use raiserror command with nowait option. So I've wrote a stored procedure

create procedure sp_test
as
begin
    declare @i bigint, @m nvarchar(max)

     select @i = 1

     while @i < 10
     begin
         waitfor delay '00:00:01'
         select @m = cast(@i as nvarchar(max))
         raiserror(@m, 0, 0) with nowait
         select @i = @i + 1
     end
end

如果您尝试在 SSMS 中执行它，您会看到该消息出现在消息部分，而过程仍然有效.好的，我们收到了来自服务器的消息.现在我们需要在客户端处理它.

If you try to execute it in SSMS, you'll see that message appearing in message section while procedure still works. Ok, we got messages from server. Now we need to process it on the client.

为此，我创建了一个这样的 SQLCommand

To do that, I've created a SQLCommand like this

SqlCommand cmd = new SqlCommand("sp_Test");
cmd.Connection = new SqlConnection("Server=HOME;Database=Test;Trusted_Connection=True;");

现在使用SqlConnection对象的InfoMessage来捕捉消息:

now to catch a messages we using InfoMessage of SqlConnection object:

cmd.Connection.InfoMessage += Connection_InfoMessage;

static void Connection_InfoMessage(object sender, SqlInfoMessageEventArgs e)
{
    Console.WriteLine(e.Message);
}

现在我们正在尝试显示消息

And now we're trying to display messages

cmd.Connection.Open();
try
{
    SqlDataReader r = cmd.ExecuteReader();
}
finally
{
    cmd.Connection.Close();
}

成功:)

顺便说一句，您不能使用 ExecuteNonQuery()，因为它在执行结束时返回连接的消息.此外，您可能希望在后台模式下运行您的查询，这样它就不会锁定您的 winform 客户端.

BTW, you cannot use ExecuteNonQuery(), because it returns concatenated messages at the end of execution. Also, you may want to run your query in background mode, so it will not lock you winform client.

这篇关于返回已处理记录数的 SQL Server 存储过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！