How to get the number of records in a DataReader?

Question

How to get the number of records in a DataReader?

Navigation

#1 by (2 votes)
#2 by (0 votes)

1

I'm doing a query in a database, however I need to know how many records I'll get from this query, before I even start the loop.

Note: I do not 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

sql c# .net vb.net ado.net

asked by anonymous 09.03.2016 / 15:43

2 answers

Keep variable value between layers of a function How to remove an alert after selecting another in a select

score 2 · Answer 1

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

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

This may not be ideal for generating 2 bank queries and should be avoided if you have any other way.

score 0 · Answer 2

Well, I used SQL Server and I made the following queries to avoid two processing in the database - one for testing and one for the data - consider the examples below that I tested and see if it helps or is what you are looking 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 have helped.