How to get the amount of records in a query in Sqlite?

Asked

Viewed 4,241 times

3

I am 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 record in the table.

How would you count table records with Sqlite and C#?

c# .net sqlite ado.net
  • 1

    It is necessary that the count be made before to go through the results? Why?

  • So dcastro. The need for counting is to check if there are records, otherwise a message will appear "There are no records at the moment".

  • I understand the need to count, my question was not about that. The question was about the need for that count to be made before the results (and not afterward or during).

4 answers

3

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

SELECT COUNT(*) FROM Cadastro

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

  • I even thought about doing this, but for example: in php we use mysqli_num_rows($result) to return the number of lines. No sqlite/C# would not have a similar option?

  • I understood, the problem would be how to accomplish this in c#, I have no deep knowledge in c#, but I believe that the second line of your example already returns the result of the query, in this SELECT will always return you a single record (numeric) already with the value of the number of lines that has.

  • 2

    There is no similar method in Sqlite. Do as said, use SELECT COUNT(*) FROM Register, but when executing the command, use the Executeescalar method, I think this is it... it will return only one result. Convert to int and check.

  • 1

    There is no guarantee that the result of select count will be equal to the number of Rows returned by select * which happens. Lines can be deleted/entered concurrently between both selects.

2


It is impossible to know how many rows were returned before going through the Reader date, because the command ExecuteReader creates the Reader before of all rows being returned from the server.

The lines will be returned as the method is invoked DbDataReader.Read.

Therefore, the ideal way to know how many columns have been returned is to scroll 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;

  • Right. But in case I need to show a message stating that no record has been found, as I would?

  • @Jose.Marcos then, easy. if(count ==0) mostrar erro

  • Thanks dcastro. I will use your model.

2

What you’re trying to do is not feasible. It’s not that you can’t do (it can’t even, directly) it’s that doing it would be a mistake. You are potentially provoking a running condition, that is, between a consultation and another may be that the condition is not the same. This is more or less like you checking if a file exists to open it. When opening it, it may no longer exist.

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

You should only do a count if you need the count. Clearly you’re saying you want something else. Apparently you’re hurting performance by thinking you’re getting better. You’re making robustness worse. And you’re doing something semantically wrong.

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

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

  • 1

    +1 Good explanation, and by analogy with reading a file/checking if the file exists. This is an error I see too often.

0

I used the following code below based on Caiquec and it seems that 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"); 
}
  • 1

    As me and Bigown explained, this method is incorrect and is subject to concurrency errors.

  • I understood dcastro. I use very little C# and am learning in mistakes ; )

  • 1

    looks like that worked.

  • The dcastro code served what I was needing. Thank you all!

Browser other questions tagged c# .net sqlite ado.net

You are not signed in. Login or sign up in order to post.