SqlDataReader is not returning all rows

Question

My SQL command returns 3 rows which is verified in a SQL Server GUI. I run the exact same code and the SqlDataReader only returns 2 of them. The same sql command returns 3 rows with SqlDataAdapter.

Here is my code - ds has 3 rows. Just to show the difference, I have added SqlDataAdapter.

Thanks in advance.

using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["VPO"].ConnectionString))
{
 string sql = "SELECT DISTINCT A.account_id, A.fname, A.lname, 
 FROM T_Test1 A WITH (NOLOCK) 
 JOIN T_Test2 AF WITH (NOLOCK) ON A.Account_id=AF.Account_id
 WHERE account_status = 'A' AND A.card IS NOT NULL
 AND A.dateFrom >= '09-02-2013 00:00:00' 
 AND A.dateFrom <= '09-30-2013 00:00:00' 
 AND AF.code = 'INE'";
 SqlCommand command = new SqlCommand(sql.ToString(), connection);
 command.CommandTimeout = 3600;
 connection.Open();
 using (SqlDataReader reader = command.ExecuteReader())
 { 
 while (reader.Read())
 {}
 }
 DataSet ds = new DataSet();
 SqlDataAdapter da = new SqlDataAdapter(command.CommandText, connection);
 da.Fill(ds);
}

I found the solution: One of the line in using section is reading the first record. In while loop, it is reading from second record. I removed the below if condition and it worked fine. Thank you all for your replies. Sorry for not posting that line, as I thought that line is handling only exception.

if (!reader.Read()) 
 throw new ApplicationException("MISSING Transaction Returned By Financial Institution. Transaction was not found in the database."); 
while (reader.Read()) {}

• Satinder singh: your code look fine, can you post what you have written in your while loop ?
• Zeeshan: I always populated my listviews using the same code. it's perfect! you might be missing something in your while loop.
• Suraj Singh: @RajVish Post the code inside while (reader.Read()) .
• usr: The reader is returning all rows, you are using it correctly. Your query does not produce the correct rows.

Answer 1

You could create a class..

public class AccountDetails
{
 public int AccountId {get; set;} 
 public string FName {get; set;} 
 public string LName {get; set;} 
}

And then return a List of AccontDetails like this...

public List<AccountDetails> GetAccountDetails()
{
 using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["VPO"].ConnectionString))
 {
 string sql = "SELECT DISTINCT A.account_id, A.fname, A.lname, 
 FROM T_Test1 A WITH (NOLOCK) 
 JOIN T_Test2 AF WITH (NOLOCK) ON A.Account_id=AF.Account_id
 WHERE account_status = 'A' AND A.card IS NOT NULL
 AND A.dateFrom >= '09-02-2013 00:00:00' 
 AND A.dateFrom <= '09-30-2013 00:00:00' 
 AND AF.code = 'INE'";
 SqlCommand command = new SqlCommand(sql.ToString(), connection);
 command.CommandTimeout = 3600;
 connection.Open();
 var accDetails = new List<AccountDetails>();
 using (var rdr = command.ExecuteReader())
 {
 while (rdr.Read())
 {
 var accountDetails = new AccountDetails{
 AccountId = rdr.GetInt32(0),
 FName = rdr.GetString(1),
 LName = rdr.GetString(2)
 };
 accDetails.Add(accountDetails);
 } 
 }
 }
 return accDetails;
}

syntax might be out as I did some of this freehand.

This is lighter than using a DataSet, unless you specifically need to use a DataSet. If so, let me know and I will update the code.

Answer 2

You can not use the command when it is out of scope (after the using block), but maybe it was just an example for the SqlDataAdapter.

Anyway, there are some guidelines when using the SqlDatareader,

try to use this code:

using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["VPO"].ConnectionString))
{
 connection.Open();
 string sql = "SELECT DISTINCT A.account_id, A.fname, A.lname, 
 FROM T_Test1 A WITH (NOLOCK) 
 JOIN T_Test2 AF WITH (NOLOCK) ON A.Account_id=AF.Account_id
 WHERE account_status = 'A' AND A.card IS NOT NULL
 AND A.dateFrom >= '09-02-2013 00:00:00' 
 AND A.dateFrom <= '09-30-2013 00:00:00' 
 AND AF.code = 'INE'";
 using(SqlCommand command = new SqlCommand(sql, connection))
 { 
 command.CommandTimeout = 3600; 
 using (SqlDataReader reader = command.ExecuteReader())
 { 
 while (reader.Read())
 {
 // Read the data here and do your thing...
 }
 reader.Close(); // We should close this reader - This line is for readability
 }
 } 
}

Works for me every time.

Answer 3

Using SqlDataAdapter is wrong. You have to use like belov: This solution can solve your problem.

DataSet ds = new DataSet();
SqlDataAdapter da = new SqlDataAdapter(sql, connection);
da.Fill(ds);

• AsitK: I guess he has done the same thing... by passing the CommandText

Answer 4

 con.Open(); 
 SqlDataReader reader = command.ExecuteReader(); 
 while (reader.Read()) 
 {
 // Do stuff here 
 }
 con.close();

Answer 5

One of the line in using section is reading the first record. In while loop, it is reading from second record. I removed the below if condition and it worked fine. Thank you all for your replies. Sorry for not posting that line, as I thought that line is handling only exception.

if (!reader.Read()) throw new ApplicationException("MISSING Transaction Returned By Financial Institution. Transaction was not found in the database."); while (reader.Read()) {}

• AsitK: you should update your question with the answer. That will help users get the correct answer or mark the post as the accepted answer.