如何计算 C# 中 SQLite 阅读器中返回的行数? [英] How do I count the number of rows returned in my SQLite reader in C#?

问题描述

我正在使用 Microsoft Visual C# 2008 Express 和 SQLite.

I'm working in Microsoft Visual C# 2008 Express and with 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();

然后我做这样的事情:

if (reader.HasRows == true) {
    while (reader.Read()) {
        // I do stuff here
    }
}

我想做的是计算行数之前我做reader.Read()"，因为返回的数字会影响我想要/需要做的事情.我知道我可以在 while 语句中添加计数，但我真的需要在之前知道计数.

What I want to do is count the number of rows before I 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.

有什么建议吗?

推荐答案

DataReader 运行缓慢，因此它不会在开始之前获取整个行集.这给您留下了两个选择:

The DataReader runs lazily, so it doesn't pick up the entirety of the rowset before beginning. This leaves you with two choices:

1. 迭代并计数
2. 在 SQL 语句中计数.

因为我更喜欢 SQL，所以我会在 SQL 语句中进行计数:

Because I'm more of a SQL guy, I'll do the count in the SQL statement:

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();

//...

注意我是如何计算 * 的，而不是一开始的 id.这是因为 count(id) 会忽略 id，而 count(*) 只会忽略完全空的行.如果您没有空 ID，则使用 count(id)(速度稍快，具体取决于您的表大小).

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).

更新:更改为 ExecuteScalar，并根据评论计数(id).

Update: Changed to ExecuteScalar, and also count(id) based on comments.

这篇关于如何计算 C# 中 SQLite 阅读器中返回的行数?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！