Filter in a Dataset in asp.net C#

Question

i want to filter the gridview on textchange in search textbox.... if i press some character like "S" the gridview should filled up with record starting with "S".

Public class DALDepartment
{
public Dataset DepartmentSearch(string Connectionstring, string conditon)
{
 SqlConnection connection = new SqlConnection(connectionstring);
            SqlCommand command = new SqlCommand("select departmentname,departmentcode from Department" + condition, connection);                  
            SqlDataAdapter adapter = new SqlDataAdapter(command);
            DataSet ds = new DataSet();
            try
            {
                connection.Open();            //Opening Connection                
                adapter.Fill(ds, "Department");      //Filling values to Dataset from Adapter
                connection.Close();          //closing connection
            }
            catch (Exception e)
            {
                ErrorCollection.AddErrors("XMS0000", e.Data + e.Message);
                return null;
            }
            return ds;
          }   
       }

//Dep.Aspx.cs in the asp page
DataSet ds = new DataSet();
        string condition = "where departmentname LIKE '%" + Textbox1.Text + "%'" ;
        ds=DepartmentSearch(Connectionstring,condition);
        GridView1.DataSource = ds.Tables["Department"];
        GridView1.DataBind();

Thw above code works fine. now i need to sort this in the dataset. which i got stored inside the dataset, when the pageload. For Exampe:

 //The Data's are found and loaded in a dataset

 SqlCommand command = new SqlCommand("select departmentname,departmentcode from
 Department" , connection);
            SqlDataAdapter adapter = new SqlDataAdapter(command);
            DataSet ds = new DataSet();
                connection.Open();         
                adapter.Fill(ds, "Department");
                connection.Close();          


//In dataset

 DataSet ds = new DataSet();
 ds = DepartmentSearch(Connectionstring);

Now i don't know how to Filter the values inside the dataset.

I want to thank you friends in advance for your answers and suggestion.

Answer 1

You can use Select function in the case of DataTable ie

dtNames.Select("name like 'a%'");

So you will be sorts the name .Remember that name must be a column of your datatable.So for next time you can search only by changing the alphabet no need for executing the query.But this method will retrieve only data which is available only when the first query executes.If any changes were occurred to database then it will reflect only after executing the query.

Answer 2

if you are trying to select and sort some rows from your database, you need to change your select command like so:

SqlCommand command = new SqlCommand("select departmentname, departmentcode from Department " + condition + "order by departmentname", connection); 

what you want is to sort it just in your dataset. I have to say, that's impossible in any other ways.
you can do what you want with a BindingSource.

BindingSource bs = new BindingSource();
bs.DataSource = ds;
bs.Sort = "departmentname asc";
bs.Filter = "departmentname like 'depa%'";