How to get the number of records in a query in SQLite?

3

I'm listing the records of a table with the code below:

SQLiteCommand comando = new SQLiteCommand("SELECT * FROM Cadastro " +filtro, conn);
SQLiteDataReader receber = comando.ExecuteReader();

But before listing, I need to check if there is a record in the table.

How would you tell the table records with SQLite and C #?

    
asked by anonymous 19.08.2015 / 21:13

4 answers

2

It is impossible to know how many rows were returned before scrolling through the data reader because the ExecuteReader command creates the reader before all rows are returned from the server.

Lines are being returned as the DbDataReader.Read method is invoked.

So the ideal way to know how many columns have been returned is to go through them.

var reader = comando.ExecuteReader();

var counter = 0;
while(reader.Read())
{
    counter++;

    // ler linha, transforma-la e guarda-la numa lista, por exemplo
}

or

var reader = comando.ExecuteReader();    
var models = new List<Model>();

while(reader.Read())
{
    // ler linha, transforma-la e guarda-la numa lista, por exemplo

    var model = new Model(reader["coluna"]);
    models.Add(model);    
}

var count = models.Count;
    
19.08.2015 / 22:02
3

You can do a query to get the amount of records before doing the main query.

SELECT COUNT(*) FROM Cadastro

After receiving the result, you can use your logic to check if the quantity returned is greater than 0.

    
19.08.2015 / 21:19
2

What you are trying to do is not feasible. It's not that it can not do (can not even, directly) that doing would be a mistake. You are potentially causing a race condition , that is, between one query and another it may be that the condition is not the same. This is more or less how you check if a file exists to open it. When it opens, it may no longer exist.

If you read what you wrote in the question you will already have the solution: "I need to check if there is a record in the table". So do this. Is the filtro variable what you need to check? Then it is ready, if it is not, add to the SQL expression.

You should only count if you need the count. Clearly you're saying you want something else. Apparently you're hurting the performance thinking you're getting better. Improving robustness. And doing something semantically wrong.

Even though I need the count afterwards, I would still do it via C # code to avoid the mentioned race condition. But apparently you do not even need this.

As much as you want to do otherwise, you're making a mistake. You may not like this answer but it's the only one valid for your situation.

    
19.08.2015 / 22:02
0

I used the following code based on CaiqueC and it looks like it worked:

SQLiteCommand contar = new SQLiteCommand("SELECT COUNT(*)FROM Cadastro " + filtro, conn);
contar.CommandType = CommandType.Text;

long numeroRegistros = (long)contar.ExecuteScalar();
contar.Dispose();

if(numeroRegistros > 0){
List<DadosGB> listar = new List<DadosGB>();

 while (receber.Read())
 {
    // Mostrar o resultado
}
}else{
MessageBox.Show("Nenhum registro foi encontrado"); 
}
    
19.08.2015 / 22:39