如何在C#中使用SqlDataReader获取行数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1383315/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to get number of rows using SqlDataReader in C#
提问by Tomasz Iniewicz
My question is how to get the number of rows returned by a query using SqlDataReaderin C#. I've seen some answers about this but none were clearly defined except for one that states to do a while loop with Read()method and increment a counter.
我的问题是如何获取SqlDataReader在 C# 中使用的查询返回的行数。我已经看到了一些关于这个的答案，但没有一个是明确定义的，除了一个声明用Read()方法做一个 while 循环并增加一个计数器的答案。
My problem is that I am trying to fill a multi-dimensional array with the first row being the column header names and every row after that to the be the row data.
我的问题是我试图填充一个多维数组，第一行是列标题名称，之后的每一行都是行数据。
I know that I can just dump the stuff in a List control and not worry about it, but for my own personal edification and I would also like to pull the data in and out of the array as I choose and display it in different formats.
我知道我可以将这些东西转储到 List 控件中而不用担心，但为了我个人的启发，我还想在我选择时将数据拉入和拉出数组并以不同的格式显示它。
So I think I can't do the Read()and then increment ++ way because that means that I would have to open Read()and then open Read()again to get amount of rows and then column data.
所以我想我不能做Read()然后递增 ++ 的方式，因为这意味着我必须打开Read()然后Read()再次打开以获取行数和列数据。
Just a small example of what I'm talking about:
只是我正在谈论的一个小例子：
int counter = 0;
while (sqlRead.Read())
{
//get rows
counter++
}
and then a for loop to run through the columns and pop
然后 for 循环遍历列并弹出
something.Read();
int dbFields = sqlRead.FieldCount;
for (int i = 0; i < dbFields; i++)
{
// do stuff to array
}
采纳答案by Henk Holterman
There are only two options:
只有两种选择：
Find out by reading all rows (and then you might as well store them)
run a specialized SELECT COUNT(*) query beforehand.
通过阅读所有行来找出（然后你也可以存储它们）
事先运行一个专门的 SELECT COUNT(*) 查询。
Going twice through the DataReader loop is really expensive, you would have to re-execute the query.
两次通过 DataReader 循环非常昂贵，您将不得不重新执行查询。
And (thanks to Pete OHanlon) the second option is only concurrency-safe when you use a transaction with a Snapshot isolation level.
并且（感谢 Pete OHanlon）当您使用具有 Snapshot 隔离级别的事务时，第二个选项仅是并发安全的。
Since you want to end up storing all rows in memory anyway the only sensible option is to read all rows in a flexible storage (List<>or DataTable) and then copy the data to any format you want. The in-memory operation will always be much more efficient.
由于无论如何您都希望最终将所有行存储在内存中，唯一明智的选择是读取灵活存储（List<>或DataTable）中的所有行，然后将数据复制为您想要的任何格式。内存中的操作总是会更有效率。
回答by Pete OHanlon
You can't get a count of rows directly from a data reader because it's what is known as a firehose cursor - which means that the data is read on a row by row basis based on the read being performed. I'd advise against doing 2 reads on the data because there's the potential that the data has changed between doing the 2 reads, and thus you'd get different results.
您无法直接从数据读取器获取行数，因为它就是所谓的流水游标——这意味着数据是根据正在执行的读取逐行读取的。我建议不要对数据进行 2 次读取，因为在进行 2 次读取之间数据有可能发生变化，因此您会得到不同的结果。
What you could do is read the data into a temporary structure, and use that in place of the second read. Alternatively, you'll need to change the mechanism by which you retrieve the data and use something like a DataTable instead.
您可以做的是将数据读入一个临时结构，并用它代替第二次读取。或者，您需要更改检索数据的机制并改用类似 DataTable 的内容。
回答by Daniel Segan
Per above, a dataset or typed dataset might be a good temorary structure which you could use to do your filtering. A SqlDataReader is meant to read the data very quickly. While you are in the while() loop you are still connected to the DB and it is waiting for you to do whatever you are doing in order to read/process the next result before it moves on. In this case you might get better performance if you pull in all of the data, close the connection to the DB and process the results "offline".
如上所述，数据集或类型化数据集可能是一个很好的临时结构，您可以使用它来进行过滤。SqlDataReader 旨在非常快速地读取数据。当您处于 while() 循环中时，您仍然连接到数据库，并且它正在等待您做任何您正在做的事情，以便在它继续之前读取/处理下一个结果。在这种情况下，如果您提取所有数据，关闭与数据库的连接并“离线”处理结果，您可能会获得更好的性能。
People seem to hate datasets, so the above could be done wiht a collection of strongly typed objects as well.
人们似乎讨厌数据集，因此也可以使用强类型对象的集合来完成上述操作。
回答by Pit Ming
If you do not need to retrieve all the row and want to avoid to make a double query, you can probably try something like that:
如果您不需要检索所有行并希望避免进行双重查询，您可以尝试这样的操作：
using (var sqlCon = new SqlConnection("Server=127.0.0.1;Database=MyDb;User Id=Me;Password=glop;"))
{
sqlCon.Open();
var com = sqlCon.CreateCommand();
com.CommandText = "select * from BigTable";
using (var reader = com.ExecuteReader())
{
//here you retrieve what you need
}
com.CommandText = "select @@ROWCOUNT";
var totalRow = com.ExecuteScalar();
sqlCon.Close();
}
You may have to add a transaction not sure if reusing the same command will automatically add a transaction on it...
您可能必须添加一个事务，不确定重用相同的命令是否会自动在其上添加一个事务...
回答by Pit Ming
I also face a situation when I needed to return a top result but also wanted to get the total rows that where matching the query. i finaly get to this solution:
当我需要返回最高结果但又想获得与查询匹配的总行数时，我也面临着这样的情况。我终于得到了这个解决方案：
public string Format(SelectQuery selectQuery)
{
string result;
if (string.IsNullOrWhiteSpace(selectQuery.WherePart))
{
result = string.Format(
@"
declare @maxResult int;
set @maxResult = {0};
WITH Total AS
(
SELECT count(*) as [Count] FROM {2}
)
SELECT top (@maxResult) Total.[Count], {1} FROM Total, {2}", m_limit.To, selectQuery.SelectPart, selectQuery.FromPart);
}
else
{
result = string.Format(
@"
declare @maxResult int;
set @maxResult = {0};
WITH Total AS
(
SELECT count(*) as [Count] FROM {2} WHERE {3}
)
SELECT top (@maxResult) Total.[Count], {1} FROM Total, {2} WHERE {3}", m_limit.To, selectQuery.SelectPart, selectQuery.FromPart, selectQuery.WherePart);
}
if (!string.IsNullOrWhiteSpace(selectQuery.OrderPart))
result = string.Format("{0} ORDER BY {1}", result, selectQuery.OrderPart);
return result;
}
回答by mehdi
to complete of Pit answer and for better perfromance : get all in one query and use NextResult method.
完成 Pit 答案并获得更好的性能：在一个查询中获取所有内容并使用 NextResult 方法。
using (var sqlCon = new SqlConnection("Server=127.0.0.1;Database=MyDb;User Id=Me;Password=glop;"))
{
sqlCon.Open();
var com = sqlCon.CreateCommand();
com.CommandText = "select * from BigTable;select @@ROWCOUNT;";
using (var reader = com.ExecuteReader())
{
while(reader.read()){
//iterate code
}
int totalRow = 0 ;
reader.NextResult(); //
if(reader.read()){
totalRow = (int)reader[0];
}
}
sqlCon.Close();
}
