如何计算 C# 中 SQLite 阅读器中返回的行数？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/962032/
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 do I count the number of rows returned in my SQLite reader in C#?
提问by adeena
I'm working in Microsoft Visual C# 2008 Express and with SQLite.
我正在使用 Microsoft Visual C# 2008 Express 和 SQLite。
I'm querying my database with something like this:
我用这样的东西查询我的数据库：
SQLiteCommand cmd = new SQLiteCommand(conn);
cmd.CommandText = "select id from myTable where word = '" + word + "';";
cmd.CommandType = CommandType.Text;
SQLiteDataReader reader = cmd.ExecuteReader();
Then I do something like this:
然后我做这样的事情：
if (reader.HasRows == true) {
while (reader.Read()) {
// I do stuff here
}
}
What I want to do is count the number of rows beforeI do "reader.Read()" since the number returned will affect what I want/need to do. I know I can add a count within the while statement, but I really need to know the count before.
我想要做的是在执行“reader.Read()”之前计算行数，因为返回的数字会影响我想要/需要做的事情。我知道我可以在 while 语句中添加一个计数，但我真的需要之前知道计数。
Any suggestions?
有什么建议？
采纳答案by Eric
The DataReader runs lazily, so it doesn't pick up the entirety of the rowset before beginning. This leaves you with two choices:
DataReader 运行缓慢，因此它不会在开始之前获取整个行集。这让您有两个选择：
- Iterate through and count
- Count in the SQL statement.
- 迭代并计数
- 在 SQL 语句中计数。
Because I'm more of a SQL guy, I'll do the count in the SQL statement:
因为我更像一个 SQL 人，我将在 SQL 语句中进行计数：
cmd.CommandText = "select count(id) from myTable where word = '" + word + "';";
cmd.CommandType = CommandType.Text;
int RowCount = 0;
RowCount = Convert.ToInt32(cmd.ExecuteScalar());
cmd.CommandText = "select id from myTable where word = '" + word + "';";
SQLiteDataReader reader = cmd.ExecuteReader();
//...
Note how I counted *, not id in the beginning. This is because count(id) will ignore id's, while count(*) will only ignore completely null rows. If you have no null id's, then use count(id) (it's a tad bit faster, depending on your table size).
请注意我如何计算 *，而不是一开始的 id。这是因为 count(id) 会忽略 id，而 count(*) 只会忽略完全空的行。如果您没有空 id，则使用 count(id) （它会快一点，具体取决于您的表大小）。
Update: Changed to ExecuteScalar, and also count(id) based on comments.
更新：更改为 ExecuteScalar，并根据评论计数（id）。
回答by Jeremy McGee
Do a second query:
做第二个查询：
cmd.CommandText = "select count(id) from myTable where word = '" + word + "';"; cmd.CommandType = CommandType.Text; SQLiteDataReader reader = cmd.ExecuteReader();
Your reader will then contain a single row with one column containing the number of rows in the result set. The count will have been performed on the server, so it should be nicely quick.
然后，您的阅读器将包含一行，其中一列包含结果集中的行数。计数将在服务器上执行，因此应该很快。
回答by jerryjvl
If you are only loading an idcolumn from the database, would it not be easier to simply load into a List<string>and then work from there in memory?
如果您只是id从数据库加载一列，那么简单地加载到 aList<string>然后在内存中从那里工作不是更容易吗？
回答by Alex Martelli
What you request is not feasible -- to quote Igor Tandetnik, my emphasis:
你的要求是不可行的——引用Igor Tandetnik 的话，我的重点是：
SQLite produces records one by one, on request, every time you call
sqlite3_step. It simply doesn't know how many there are going to be, until on somesqlite3_stepcall it discovers there are no more.
每次调用 .sqlite 时，都会根据请求一一生成记录
sqlite3_step。 它只是不知道会有多少，直到在某个sqlite3_step电话中它发现没有更多了。
(sqlite3_stepis the function in SQLite's C API that the C# interface is calling here for each row in the result).
（sqlite3_step是 SQLite 的 C API 中的函数，C# 接口在此处为结果中的每一行调用该函数）。
You could rather do a "SELECT COUNT(*) from myTable where word = '" + word + "';"first, before your "real" query -- thatwill tell you how many rows you're going to get from the real query.
您宁愿"SELECT COUNT(*) from myTable where word = '" + word + "';"在“真实”查询之前先执行第一次操作——这将告诉您将从真实查询中获得多少行。
回答by Thomas Levesque
but I really need to know the count before
但我真的需要先知道计数
Why is that ? this is usually not necessary, if you use adequate in-memory data structures (Dataset, List...). There is probably a way to do what you want that doesn't require to count the rows beforehand.
这是为什么 ？如果您使用足够的内存数据结构（数据集、列表...），这通常是没有必要的。可能有一种方法可以做你想做的事，不需要事先计算行数。
回答by Ed Sykes
Normally i would do
通常我会做
select count(1) from myTable where word = '" + word + "';";
to get the result as fast as possible. In the case where id is an int then it won't make much difference. If it was something a bit bigger like a string type then you'll notice a difference over a large dataset.
以尽快获得结果。在 id 是 int 的情况下，它不会有太大区别。如果它有点像字符串类型那么大，那么您会注意到与大型数据集的差异。
Reasoning about it count(1) will include the null rows. But i'm prepared to be corrected if i'm wrong about that.
推理它 count(1) 将包括空行。但如果我错了，我准备被纠正。
回答by tuinstoel
You do have to count with select count... from...
你必须计算 select count... from...
This will make your application slower. However there is an easy way to make your app faster and that way is using parameterized queries.
这将使您的应用程序变慢。但是，有一种简单的方法可以使您的应用程序更快，即使用参数化查询。
See here: How do I get around the "'" problem in sqlite and c#?
请参阅此处：如何解决 sqlite 和 c# 中的“'”问题？
(So besides speed parameterized queries have 2 other advantages too.)
（所以除了速度参数化查询还有另外两个优点。）
回答by Anokha Ladla
Try this,
尝试这个，
SQLiteCommand cmd = new SQLiteCommand(conn);
cmd.CommandText = "select id from myTable where word = '" + word + "';";
SQLiteDataReader reader = cmd.ExecuteReader();
while (reader.HasRows)
reader.Read();
int total_rows_in_resultset = reader.StepCount;
total_rows_in_resultset gives you the number of rows in resultset after processing query
total_rows_in_resultset 为您提供处理查询后结果集中的行数
remember that if you wanna use the same reader then close this reader and start it again.
请记住，如果您想使用相同的阅读器，请关闭此阅读器并重新启动。
回答by Eros Ar
SQLiteCommand cmd = new SQLiteCommand(conn);
cmd.CommandText = "select id from myTable where word = '" + word + "';";
SQLiteDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
total_rows_in_resultset++;
}
回答by Pete
Surely a better way to get a row count would be something like this:-
当然，获得行数的更好方法是这样的：-
SQLiteDataReader reader = SendReturnSQLCommand(dbConnection, "SELECT COUNT(*) AS rec_count FROM table WHERE field = 'some_value';");
if (reader.HasRows) {
reader.Read();
int count = Convert.ToInt32(reader["rec_count"].ToString());
...
}
That way you don't have to iterate over the rows
这样你就不必遍历行
