Instead of binding the SQL command with the gridview, store the results of the SQL query that match your criteria in a dataset. Then bind the dataset with the gridview. I think this is what Abhishek was suggesting as well. It would look something like:
// Create a dataset that represents the data you are retrieving from the database
DataSet myDataSet = new DataSet();
DataTable myDataTable = myDataTable.Tables.Add();
myDataTable.Columns.Add("ID", typeof(int));
myDataTable.Columns.Add("Foo", typeof(string));
myDataTable.Columns.Add("Bar", typeof(string));
// Execute your sql reader as normal
connection.Open();
SqlDataReader dr = cmd.ExecuteReader();
while(dr.Read())
{
// instead of binding the reader, add the records you want to the dataset
if (dr["myCriteria"].ToString() == "this")
{
myDataTable.Rows.Add(
(int)dr["ID"],
dr["Foo"].ToString(),
dr["Bar"].ToString()
);
}
}
dr.Close();
connection.Close();
// Now bind the dataset you created to your GridView
myGridView.DataSource = myDataSet;
myGridView.BindData();
This could be done more elegantly by using myDataTable.Load(dr) then removing the rows that do not meet your criteria via a LINQ statement. But it should give you an idea of what you are trying to accomplish with a DataSet and get you started... - Dave
