How to get the amount of records from a Datareader?

Asked

Viewed 1,276 times

1

I am doing a query in a database, but need to know how many records I will get from this query, before I even start crocheting.

Note: I don’t want to put a counter inside the mesh, I need the value before*

cmd = New OleDbCommand(sql, conexao)
myDR = cmd.ExecuteReader(CommandBehavior.CloseConnection)
while myDR.read 
...
end while
c# sql .net vb.net ado.net

  • Did the answer solve what you wanted? Do you think it is possible to accept it now? It needs something more to be improved?

4 answers

2

The only way is to ask SQL to tell you, something like that:

SELECT COUNT(id) FROM tabela WHERE alguma condição aqui

I put in the Github for future reference.

This may not be ideal because it generates 2 queries to the bank and should be avoided if there is some other way.

  • I understand, I know this method, but I wanted something more effective.

  • will a select @@ROWCOUNT just after he opened the DataReader wouldn’t give either.

  • @Pablovargas Depending on what you wish, you can.

  • @Felipewalleg is effective, only it is not the most efficient. But outside some very specific solution and that is probably not the ideal either, it is the only way. What I would do is try not to need that information right now. In general it is unnecessary in the situation described.

  • True, but I need it because when I get to the last record of read, I need to add some values, but it has to be on the last record, I wanted to take this loop to do, because I’m writing a report, and it would be at the end of each day.

  • Something tells me you’re doing something wrong. But I have no say without seeing.

Show 1 more comment

1

Well, I used SQL Server and made the queries below, to avoid two database processing - one for testing and one for data - consider the examples below that I tested and see if it helps you or is what you search for:

I created a table:

CREATE TABLE #dados 
( 
     nome VARCHAR(20) 
);

Then I insert some data into it:

INSERT INTO #dados (nome) 
SELECT 'A' UNION ALL 
SELECT 'B' UNION ALL 
SELECT 'C'

Option 1:

SELECT nome, Count(*) OVER (partition BY 1) AS QtdRegistros 
  FROM #dados

Option 2:

SELECT a.*, b.QtdRegistros
  FROM #dados a 
       CROSS JOIN (SELECT Count(*) AS QtdRegistros 
                     FROM #dados) b

Then I released the memory:

DROP TABLE #dados

I hope I’ve helped.

0

By changing the execution behavior of your command, you can get the total of rows your query has executed, through the property RecordsAffected.

See below:

using (var reader = command.ExecuteReader(CommandBehavior.CloseConnection | CommandBehavior.SingleResult))
{
    var totalDeLinhas = reader.RecordsAffected;
    while (reader.Read())
    {
        //TODO: sua implementação aqui
    }
}

-1

Counting Records of a Datareader however depending on the amount becomes slow.

dataReader.Cast<Object>(). Count();

Browser other questions tagged c# sql .net vb.net ado.net

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