CODEWITHSUNDEEP
c#.netvb.netado.netsqldatareader
FullStack-.NetMVC-BizMule-Dev
FullStack-.NetMVC-BizMule-Dev

Reputation: 803

Filter SqlDataReader with Where Clause

I have the Client as below 

Id  Name status  
1   A      Y  
2   B      Y  
3   C      Y  
4   D      N

My requirement to retrieve the results and filter them, through SqlDataReader only.

Here what I'm doing,

I'm executing the select statement from SqlDataReader.
Once the results are returned to my SqlDataReader, then I'm not able retrieve the results by keeping where clause on SqlDataReader.

May I know, how can I read the SqlDataReaderwith condition based?

SqlCommand command = new SqlCommand("SELECT ID, Name , Status FROM Client;",connection);
connection.Open();

SqlDataReader reader = command.ExecuteReader();

if (reader.HasRows)
{
    while (reader.Read())
    {
        //Here, I have to to filter the results like
        //Select * from Client where status = 'N'                
    }
}

Please suggest??

Upvotes: 0

Views: 8009

Answers (2)

paparazzo
paparazzo

Reputation: 45096

Reader cannot filter until it reads but you can continue.

while (reader.Read())
{
    status = rdr.getstring(1);
    if (status != 'N') continue;
    // process 
}

Upvotes: 3

Steve
Steve

Reputation: 216313

A normal SQL WHERE clause is the right path for this. It will retrieve only the records required and no more testing with if are necessary to filter out the unwanted rows. Of course, a parameterized query will help a lot with the possibile variance of the filter

SqlCommand command = new SqlCommand(@"SELECT ID, Name , Status FROM Client
                                      WHERE status = @st;",connection);    
command.Parameters.AddWithValue("@st", "N");  // Or use a passed string variable
connection.Open();
SqlDataReader reader = command.ExecuteReader();

if (reader.HasRows)
{
    while (reader.Read())
    {
        // Here you have only the records with status = "N"
    }
}

Other advantages of this approach are explained in the comment above from Mr Joel Coehoorn.

Upvotes: 4

Related Questions