How to fetch only some rows from a SqlDataReader?

Question

i'm fetching values from a table with datareader like this:

    string query = @"SELECT XMLConfig, Enable FROM TableCfg";
    using (SqlConnection cnction = new SqlConnection(cnnstr))
    {
      cnction.Open();
      using (SqlCommand sqlCmd = new SqlCommand(query, cnction))
      {
         SqlDataReader dtRead = sqlCmd.ExecuteReader();
         while (dtRead.Read())
         {
           xmlConf = dtRead.GetString(0);
           enabl = dtRead.GetString(1);
         }
         dtRead.Close();
      }
    }

The Enable field is a boolean(True/False). Is there a way to fetch only the rows, where field enable="True"? I tried using LINQ, but i'm new to this and i must be doing something wrong.

using (SqlCommand sqlCmd = new SqlCommand(query, cnction))
{
  SqlDataReader dtRead = sqlCmd.ExecuteReader();
  var ob =(from IDataRecord r in sqlCmd.ExecuteReader()
           where r.GetString(3).ToString() == "True"
           select "Enable");   
}

Help me please. Best Regards.

"The Enable field is a boolean(True/False). Is there a way to fetch only the rows, where field enable="True"?" - why don't you just add that as a WHERE clause in the SQL? — Jon Skeet
– Jon Skeet, Commented May 6, 2014 at 15:45

Jon Skeet · Accepted Answer · 2014-05-06 15:46:21Z

7

You should really do as much filtering as possible at the database side rather than client-side:

string query = "SELECT XMLConfig FROM TableCfg WHERE Enable = True";

Notice how now you don't even need to fetch Enable, as you already know it will be True for all the matching rows.

You should also consider using LINQ to SQL or Entity Framework rather than the rather low-level stack you're currently using. It's not always appropriate, but it does make things cleaner where it's suitable.

answered May 6, 2014 at 15:46

Jon Skeet

1.5m893 gold badges9.3k silver badges9.3k bronze badges

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

1 Comment

Emmanuel Tavares Over a year ago

I could try to find something to cover up why i asked this (now) stupid question, but the truth is that i'm tired and this never cross through my mind. Thanks for the patience. This solved my problem.

Collectives™ on Stack Overflow

How to fetch only some rows from a SqlDataReader?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related