Missing Rows Property in SqlDataReader

Question

public bool ValidateUser(string uName)
{
  SqlCommand cmd = new SqlCommand();
  if (connection == null)
  {
    connection = connectToDB();
  }
  cmd.Connection = connection;
  cmd.CommandText = "Select * from Users where UserName='" + uName + "'";
  cmd.CommandType = CommandType.Text;
  SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
  if (dr.Rows.Count > 0) 
  {
    return true;
  }
  else
  {
    return false;
  }

I wrote the code in my data access layer but it was giving error on rows to count the columns.

Error:

'System.Data.SqlClient.SqlDataReader' does not contain a definition for 'Rows' and no extension method 'Rows' accepting a first argument of type 'System.Data.SqlClient.SqlDataReader' could be found (are you missing a using directive or an assembly reference?)

Error 4 'System.Data.SqlClient.SqlDataReader' does not contain a definition for 'Rows' and no extension method 'Rows' accepting a first argument of type 'System.Data.SqlClient.SqlDataReader' could be found (are you missing a using directive or an assembly reference?) — mohan naidu, Sep 06 '13 at 15:19

overloading · Answer 1 · 2013-09-06T15:21:33.117

3

Use HasRows instead because SqlDataReader doesn't have a property call Rows.

if (dr.HasRows) 
{
    return true;
}

However, if you want the count instead you may load it into a datatable

DataTable dt = new DataTable();
dt.Load(dr);
int num = dt.Rows.Count;

edited Sep 06 '13 at 15:21

answered Sep 06 '13 at 15:16

overloading

1,210
4
25
46

score 2 · Answer 2 · answered Sep 06 '13 at 15:15

2

SqlDataReader does not have a Rows Property.

Perhaps consider the HasRows property of SqlDataReader http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqldatareader.hasrows.aspx

answered Sep 06 '13 at 15:15

tim

526
2
9

score 1 · Answer 3 · edited May 23 '17 at 11:56

There is no Rows property in an SqlDataReader.
But your code has many problems.
I would change your code in this way:

    public bool ValidateUser(string uName)
    {
        using(SqlConnection cn = connectToDB())
        using(SqlCommand cmd = new SqlCommand("Select count(*) from Users where UserName=@name", cn))
        {
           cmd.Parameters.AddWithValue("@name", uName);
           return (Convert.ToInt32(cmd.ExecuteScalar()) > 0)
        }
    }

The connection object is no more global and it is destroyed in closing of the using statement.
No need to use a DataReader just to find out if the user exists or not
Using a parameterized query to avoid SQL Injection on the input data

Avoid a global connection object. There is the connection pooling infrastructure that removes any performance problem and you are safe from excessive resource usage.
The SqlDataReader is a good choice when you need to retrieve sequentially a lot of records, but to get just the information if the user exists or not the best approach is through the ExecuteScalar method and an appropriate sql.
The parameterized query is a must for every serious database work. It will pass the work to format your input to the framework and you don't risk an Sql Injection

Missing Rows Property in SqlDataReader

3 Answers3