CODEWITHSUNDEEP
c#linqfetchdatareader
Emmanuel Tavares
Emmanuel Tavares

Reputation: 23

How to fetch only some rows from a SqlDataReader?

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.

Upvotes: 2

Views: 612

Answers (1)

Jon Skeet
Jon Skeet

Reputation: 1501003

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.

Upvotes: 7

Related Questions