CODEWITHSUNDEEP
c#sql.netsql-serverdbconnection
Mr. Boy
Mr. Boy

Reputation: 63720

DBReader has rows but Read() returns false

using (var conn = new SqlConnection(connectionString))
{
    var cmd = new SqlCommand("SELECT * FROM mySchema.MyTable", conn);
    conn.Open();
    var reader = cmd.ExecuteReader();
    while(reader.Read())
    {...

In the debugger I can see my reader has one row - I can see the data returned - but reader.Read is returning false so my processing code is not getting called.

This seems pretty basic "read rows from a database table" stuff so what am I missing? Should I be looking at the reader's row data directly or something?

Upvotes: 1

Views: 7916

Answers (5)

user18529761
user18529761

Reputation: 11

Changing the reader type var to SqlDataReader reader = command.ExecuteReader(); resolved the issue, even I was facing same issue. Thanks!

Upvotes: 1

Samir
Samir

Reputation: 1

It might be helpful for someone. In my case I forgot to use

 cmd.CommandType = System.Data.CommandType.StoredProcedure

So, reader.HasRows() or reader.Read() was returning false.

Upvotes: 0

René Vogt
René Vogt

Reputation: 43876

If you let the debugger show you the results, it will read out the reader and enumerate the result.

See the comment in the debugger window:

Results View: Expanding the Results View will enumerate the IEnumerable

So your debugger already read out all results and if you step to reader.Read() there are no more rows to read and Read() returns false.

I just reproduced it with a litte test. When my debugger reads the results, my code can't read them anymore. If I don't let the debugger show them, my code can read them. (qed)

Upvotes: 12

Anderson Borba
Anderson Borba

Reputation: 11

You can try SELECT 1; to test you reader. Do you have SqlManagement Studio? To test query.

It is work.

 using (var conn = new SqlConnection(connectionString))
        {
            var cmd = new SqlCommand("select 1", conn);
            conn.Open();
            var reader = cmd.ExecuteReader();
            while(reader.Read())
            {
                string a = "teste";
            }
        }

Upvotes: 0

DInesh AG
DInesh AG

Reputation: 316

Change the reader type var to SqlDataReader reader = command.ExecuteReader(); and try this.It will work.

I have sample code for MYSQL

using (MYSQLCON)
        {
            using (MySqlDataReader sdr = sqlcmd.ExecuteReader())
            using (YourWriter)
            {
                String Header = null;
                String Content = null;

                for (int i = 0; i <= sdr.FieldCount - 1; i++)
                {
                    Header = Header + sdr.GetName(i).ToString() + ",";
                }
                YourWriter.WriteLine(Header);
                while (sdr.Read())

                    for (int i = 0; i <= sdr.FieldCount - 1; i++)
                    {
                        Content = Content + sdr[i].ToString() + ",";
                        if (i == sdr.FieldCount - 1)
                        {
                            YourWriter.WriteLine(Content);
                            Content = null;
                        }
                    }
            }
        }

Upvotes: -3

Related Questions