I was working on a project which used Access Database. There was one search feature where the user can search for different customers in the database. I tried to write a simple query but for some reason Access denied all my queries. Then I asked myself why not search the DataSet instead of going to the Database. (Please note this is not a good idea since for this you will have to retrieve all the rows in the DataSet from the database and also it will not provide you the flexibility which is offered by some of the enterprise level databases).

Anyway, here is code that goes to the DataSet and searches for a particular name and put that name into a new DataTable object and then returns the DataTable which only contains the search result.

 private DataTable Search(string name)
    {
        SqlConnection myConnection = new SqlConnection(ConnectionString);
        SqlDataAdapter ad = new SqlDataAdapter("SELECT * FROM Categories", myConnection);
        DataSet ds = new DataSet();
        ad.Fill(ds, "Categories"); 

        // Make a dataTable object 
        DataTable dt = new DataTable();
        ArrayList primKeyList = new ArrayList(); 

        // Clone the old datable to the new one 
        dt = ds.Tables[0].Clone(); 

        // Now search the DataSet 
        foreach (DataRow row in ds.Tables[0].Rows)
        {
            string rowValue = row["CategoryName"] as String;

            if (rowValue.StartsWith(name))
            {
                // Need to add this row to the DataTable object 
                dt.ImportRow(row);                             
            }            
        }

        return dt; 

    }

 

powered by IMHO