获取结果集或 ADO 记录集中行数的简单方法 [英] Simple way to get number of rows in result set or ADO recordset
问题描述
我的执行 sql 任务返回一个完整结果集",我将其存储在对象 objResults 中.我想要一种简单的方法来获取 objResults 中的行数.我该怎么做 ?我想使用 c# 代码来获取该数字.
My execute sql task returns a "full result set" which I store in an Object objResults. I want a simple way to get the number of rows in objResults. How do I do it ? I want to use c# code to get that number.
我可以将整个 objResults 加载到 DataTable 中,并使用 myDataTable.Rows.Count;
获取 numberOfRows,但这种方法显然效率低下.
I could load the whole objResults into a DataTable and get the numberOfRows with myDataTable.Rows.Count;
, but that approach is obviously inefficient.
注意 - 我正在使用 .NET 3.5 和 Visual Studio 2008.我知道有一个 4.0/visual studio 方法.但这对我不起作用.
NOTE - I am using .NET 3.5 and visual studio 2008. I know there is a 4.0/visual studio method for this. But it won't work for me.
这是 4.0 的解决方案 -
Here is the solution for 4.0 -
该对象实际上是一个 ADO 记录集.它是一个 COM 对象,因此要从 C# 使用它,您需要互操作程序集或动态程序集.动态更容易.
The object is actually an ADO recordset. It's a COM object so to use it from C# you either need the interop assembly or dynamic. dynamic is easier.
在您的脚本任务中添加对 Microsoft.CSharp 的引用,然后像这样访问记录集:
In your Script Task add a reference to Microsoft.CSharp and then access the recordset like this:
public void Main()
{
dynamic result = Dts.Variables["result"].Value;
int rowCount = Convert.ToInt32(result.RecordCount);
Dts.TaskResult = (int)ScriptResults.Success;
}
您还可以通过添加一个带有表达式 COUNT(*) over ()
的列来向 SQL 查询添加行计数列,例如:
You can also add a row count column to a SQL query by adding a colulmn with the expression COUNT(*) over ()
, eg:
select *, count(*) over () row_count
from whatever
感谢 -大卫大卫 http://blogs.msdn.com/b/dbrowne/
推荐答案
添加对Microsoft ActiveX 数据对象"库的引用.然后,您将拥有 COM 互操作类型定义以在 C# 中使用 ADO.
Add a reference to "Microsoft ActiveX Data Objects" Library. Then you will have the COM interop type definitions to work with ADO in C#.
使用 C# 脚本:
public void Main()
{
ADODB.Recordset result = (ADODB.Recordset) Dts.Variables["result"].Value;
int rowCount = result.RecordCount;
Dts.TaskResult = (int)ScriptResults.Success;
}
如何添加引用 -
在此处编辑脚本并添加引用(菜单项目)步骤 - http://support.microsoft.com/kb/308611/en-us.然后,不要忘记保存.单击全部保存"以保存添加引用!
Edit the script and add a reference (menu project) Steps here - http://support.microsoft.com/kb/308611/en-us. Then, don't forget to save afterwards. Click Save All to save adding the reference!
ctrl + s 只会保存您的代码.要全部保存,请使用 ctrl+shift+s.哈哈 !太不直观了!
ctrl + s will only save your codes. To do save all, use ctrl+shift+s. LOL ! So unintuitive !
这篇关于获取结果集或 ADO 记录集中行数的简单方法的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!