使用ADO.NET时如何最好地显示进度信息？ [英] How to best show progress info when using ADO.NET?

问题描述

在执行可能冗长的数据库操作时，我想向用户显示详细的进度信息。具体来说，当插入/更新可能大约数百KB或MB的数据时。

I want to show the user detailed progress information when performing potentially lengthy database operations. Specifically, when inserting/updating data that may be on the order of hundreds of KB or MB.

当前，我使用的是内存中的DataTables和DataRows通过TableAdapter.Update调用与数据库同步。这可以正常工作，但一次调用几乎没有机会收集任何类型的进度信息以显示给用户。我不知道有多少数据通过网络传递到远程数据库或其进度。基本上，我所知道的只是Update返回的时间，并且假定更新已完成（除非有任何错误或异常）。但这意味着我只能显示0％，然后是暂停，然后是100％。

Currently, I'm using in-memory DataTables and DataRows which are then synced with the database via TableAdapter.Update calls. This works fine and dandy, but the single call leaves little opportunity to glean any kind of progress info to show to the user. I have no idea how much data is passing through the network to the remote DB or its progress. Basically, all I know is when Update returns and it is assumed complete (barring any errors or exceptions). But this means all I can show is 0% and then a pause and then 100%.

我可以计算行数，甚至可以算出行数是实际修改或添加的，我什至可以根据每个列的数据类型为每个DataRow计算其估计大小，对像int这样的值类型使用sizeof，对诸如字符串或字节数组之类的东西使用length进行检查。这样，我可能可以在更新之前确定估计的总传输大小，但是一旦在TableAdapter上调用Update，我仍然会停留在没有任何进度信息的地方。

I can count the number of rows, even going so far to cound how many are actually Modified or Added, and I could even maybe calculate per DataRow its estimated size based on the datatype of each column, using sizeof for value types like int and checking length for things like strings or byte arrays. With that, I could probably determine, before updating, an estimated total transfer size, but I'm still stuck without any progress info once Update is called on the TableAdapter.

我是否只能使用不确定的进度栏或鼠标等待光标停留？我是否需要从根本上改变我们的数据访问层，以便能够获取此类信息？即使我无法将其精确到传输的确切KB（例如Web浏览器文件下载进度栏），我至少也可以知道每个DataRow / DataTable完成的时间或其他东西吗？

Am I stuck just using an indeterminate progress bar or mouse waiting cursor? Would I need to radically change our data access layer to be able to hook into this kind of information? Even if I can't get it down to the precise KB transferred (like a web browser file download progress bar), could I at least know when each DataRow/DataTable finishes or something?

您如何最好地使用ADO.NET显示这种进度信息？

How do you best show this kind of progress info using ADO.NET?

推荐答案

有一个半解决方案 SELECT 部分，该部分首先发出 COUNT 查询以获取您期望接收的行数。仅当 COUNT 查询可以非常快速地（即不到一秒钟的时间）返回结果时，这才是实用的-另一方面，如果要花费几秒钟来运行，那么查询执行本身（而不是结果枚举）可能要比数据传输花费更长的时间，在这种情况下，根本不值得尝试显示离散的进度条。

There's a semi-solution for the SELECT part, which is to issue a COUNT query first to get the number of rows you expect to receive. This is only practical if the COUNT query can return a result very fast (i.e. in a fraction of a second) - on the other hand, if it takes several seconds to run, then the query execution itself (as opposed to result enumeration) might take longer than the data transfer, in which case it's not worth trying to show a discrete progress bar at all.

对于 UPDATE 和 INSERT -不，确实没有任何简单的解决方案，尤其是使用TableAdapters。如果要发送大量数据，则可能要考虑使用 SqlBulkCopy 类上传到登台表，然后在服务器上执行实际更新。该类提供 NotifyAfter 属性以及 SqlRowsCopied 事件，它可以为您提供当前进度的合理近似值（在这种情况下，您已经知道了总行数，因为它们在内存中）。

As for UPDATE and INSERT - no, there's not really any simple solution for that, especially using TableAdapters. If you have a huge amount of data to send, you might want to consider using the SqlBulkCopy class to upload to a staging table and then perform the actual update on the server. That class offers the NotifyAfter property along with the SqlRowsCopied event which can give you a reasonable approximation of the current progress (you already know the total number of rows in this case, since they're in memory).

这当然需要对当前的TableAdapter实现进行重大更改，但是.NET中的类型化数据集系统实际上并非旨在通过LAN以外的任何类型来处理该大小的记录集。

That will of course require significant changes from your current TableAdapter implementation, but the typed dataset system in .NET really wasn't designed to handle recordsets of that size over anything other than a LAN connection.

我认为大多数人只会选择使用选取框进度栏。如今，用户对此表示期待；即使您可以准确地预测行数和数据传输率，您仍然不知道查询实际执行需要多长时间，尤其是在服务器负载沉重的情况下，提供< a href = http://msdn.microsoft.com/zh-cn/library/system.data.sqlclient.sqlbulkcopy.sqlrowscopied%28v=VS.90%29.aspx rel = nofollow noreferrer>错误的估算值比没有估计。

I think that most people would simply choose to use a marquee progress bar. Users expect this nowadays; even if you could accurately predict the number of rows and the data transfer rate, you still have no idea how long it's going to take for the query to actually execute, especially if the server is under heavy load, and it's arguably worse to provide bad estimates than no estimates.

如果有实际的机会，查询（或更新）本身将需要很长时间才能在服务器上运行，而不计算任何内容时间上载/下载记录，那么我肯定会使用选取框进度条。否则...祝你好运。

If there's a realistic chance that the query (or update) itself will take a long time to run on the server itself, not counting any time to upload/download the records, then I would definitely use a marquee progress bar instead. Otherwise... good luck.

这篇关于使用ADO.NET时如何最好地显示进度信息？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！