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()) {}

your code look fine, can you post what you have written in your while loop ? — Satinder singh
– Satinder singh, Commented Sep 2, 2013 at 11:57
I always populated my listviews using the same code. it's perfect! you might be missing something in your while loop. — Zeeshan
– Zeeshan, Commented Sep 2, 2013 at 11:59
The reader is returning all rows, you are using it correctly. Your query does not produce the correct rows. — usr
– usr, Commented Sep 2, 2013 at 16:07

Christian Phillips · Accepted Answer · 2013-09-02 13:28:37Z

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.

Nir Kornfeld · Accepted Answer · 2013-09-02 13:53:49Z

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.

Mahmut EFE · Accepted Answer · 2013-09-02 12:20:15Z

0

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);

answered Sep 2, 2013 at 12:20

Mahmut EFE

5,2885 gold badges50 silver badges59 bronze badges

1 Comment

AsitK Over a year ago

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

usr · Accepted Answer · 2013-09-02 13:18:30Z

0

 con.Open();    
    SqlDataReader reader = command.ExecuteReader();    
         while (reader.Read())    
      {

         // Do stuff here     
      }
    con.close();

edited Sep 2, 2013 at 13:18

usr

172k38 gold badges251 silver badges380 bronze badges

answered Sep 2, 2013 at 12:58

Suraj Singh

4,0871 gold badge23 silver badges37 bronze badges

Comments

RajVish · Accepted Answer · 2013-09-03 05:40:46Z

0

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()) {}

answered Sep 3, 2013 at 5:40

RajVish

1912 silver badges14 bronze badges

1 Comment

AsitK Over a year ago

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

Collectives™ on Stack Overflow

SqlDataReader is not returning all rows

5 Answers 5

Comments

Comments

1 Comment

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

Comments

Comments

1 Comment

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related