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....

• marc_s: 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......

Answer 1

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.

• AMDI: I found the issue That I am looking the reader in debugger window
• Pranay Rana: @AJAYcHIGURUPATI - ok but I recommend you should avoid sql injection and for that use parameterized query....

Answer 2

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.

Answer 3

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

• AMDI: 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."