Issue with SqlDataReader

Question

I am using a SqlDataReader and while trying to read the reader I am having rows.

When I check .HasRows, the rows are not available.

List<EmployeeTimings> empTimingsList = new List<EmployeeTimings>();

// Creates a SQL connection
using (var connection = new SqlConnection(ConfigurationManager.ConnectionStrings["WINPAKContext"].ToString()))
{
    // Creates a SQL command
    using (var command = new SqlCommand("SELECT * FROM SEED_VIEW WHERE empid is not null and DateTime > '" + dtlastpunch + "' order by datetime", connection))
    {
        connection.Open();

        // Loads the query results into the table
        var reader = command.ExecuteReader();

        if (reader.HasRows)
        {
            while (reader.Read())
            {
                EmployeeTimings empTime = new EmployeeTimings();
                empTime.CardNumber = reader["CardNO"].ToString();
                empTime.EMPId = reader["EMPID"].ToString();
                empTime.FirstName = reader["FirstName"].ToString();
                empTime.LastName = reader["LastName"].ToString();
                empTime.Location = reader["Location"].ToString();
                empTime.Trans_DateTime = Convert.ToDateTime(reader["DateTime"]);

                empTimingsList.Add(empTime);
            }

            reader.Close();
        }

        command.ExecuteNonQuery();
    }

    connection.Close();
}

return empTimingsList;

Could you please let me know whats the issue?

What's happening after reader.HasRows() call, the redaer does not have any rows....

What is that command.ExecuteNonQuery(); call towards the end of your code?? Totally pointless - you've already executed the SQL statement and read in all the rows returned from it...... — marc_s
– marc_s, Commented Jan 8, 2016 at 9:25

Pranay Rana · Accepted Answer · 2016-01-08 08:37:01Z

5

there is no need to check HasRow property just Read is enough, if you use just read method and try to read row from reader which is not having any row than it will just exist while loop

you can write like , remove hasRow there is no need of it

while (reader.Read())
                            {
                                EmployeeTimings empTime = new EmployeeTimings();
                                empTime.CardNumber = reader["CardNO"].ToString();
                                empTime.EMPId = reader["EMPID"].ToString();
                                empTime.FirstName = reader["FirstName"].ToString();
                                empTime.LastName = reader["LastName"].ToString();
                                empTime.Location = reader["Location"].ToString();
                                empTime.Trans_DateTime = Convert.ToDateTime(reader["DateTime"]);
                                empTimingsList.Add(empTime);
                            }

Avoid SQLInjection

apart form this your code will allow SQLInjection as you are directly passing value in query...Make use of parameterize query rather than code like this.

Issue with datetime condition in query

There might be issue as you are comparing datetime value in where condition without applying any conversion. This issue will get resolve if you use parameterized sql query.

answered Jan 8, 2016 at 8:37

Pranay Rana

177k37 gold badges244 silver badges266 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

AMDI Over a year ago

I found the issue That I am looking the reader in debugger window

Pranay Rana Over a year ago

@AJAYcHIGURUPATI - ok but I recommend you should avoid sql injection and for that use parameterized query....

Nigrimmist · Accepted Answer · 2016-01-08 08:37:13Z

0

As you can see here https://msdn.microsoft.com/ru-ru/library/system.data.datatablereader.hasrows%28v=vs.110%29.aspx - your code looks good. Try to investigate returned sets of data.

answered Jan 8, 2016 at 8:37

Nigrimmist

12.8k4 gold badges61 silver badges61 bronze badges

Comments

Yaugen Vlasau · Accepted Answer · 2016-01-08 08:38:02Z

0

Just remove entire wrapping block

if (reader.HasRows)
{
...
}

Your code will keep the same functionality and you do not have to bother about the HasRows :)

one more comment. please remove the following line in your code:

command.ExecuteNonQuery();

answered Jan 8, 2016 at 8:38

Yaugen Vlasau

2,2381 gold badge20 silver badges41 bronze badges

1 Comment

AMDI Over a year ago

No I am facing one more issue while deploying to Server. "There is already an open DataReader associated with this Command which must be closed first."

Collectives™ on Stack Overflow

Issue with SqlDataReader

3 Answers 3

2 Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related