Geeks With Blogs
AzamSharp Some day I will know everything. I hope that day never comes.

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

 

Posted on Tuesday, October 25, 2005 12:18 PM | Back to top


Comments on this post: Searching in the DataSet to save a Database trip

# re: Searching in the DataSet to save a Database trip
Requesting Gravatar...
A dataview would give you this same functionality much more efficiently. Use the filter method.

Left by Jason Coyne on Oct 25, 2005 5:03 PM

# re: Searching in the DataSet to save a Database trip
Requesting Gravatar...
Hi,

The problem I am facing was to write the T-SQL Query that will separate the search criteria. For some reason Access Database is not recognizing the T-SQL format.

Left by Azamsharp on Oct 25, 2005 6:19 PM

# re: Searching in the DataSet to save a Database trip
Requesting Gravatar...
One trick is to use the Query builder in Access and then switch to SQL view to see the correct Access-style syntax. It's been a long while since I've done this, but as I recall you execute that syntax as a 'pass through' query so it's sent to the Jet engine for execution. Most of my work in this manner ended with DAO & ODBC when I moved to RDO against SQL Server and Oracle. But this should give you a start in the right direction.
Left by John Watson on Oct 26, 2005 5:51 AM

# re: Searching in the DataSet to save a Database trip
Requesting Gravatar...
Thanks for the advice. I will check it out!

Left by AzamSharp on Oct 26, 2005 6:02 AM

# re: Searching in the DataSet to save a Database trip
Requesting Gravatar...
Hi, to save you time, here's the reply to your question on using LIKE:

I can never remember because they use non standard syntax so I opened Northwind, created a new query, filtered on City LIKE 'L%' and failed :) Digging through the help revealed it's City LIKE "L*" instead. After running the query to view the results, I switched to SQL view and here's what I got:

SELECT Customers.CustomerID, Customers.CompanyName, Customers.ContactName, Customers.Address, Customers.City, Customers.Region, Customers.PostalCode, Customers.Country
FROM Customers
WHERE (((Customers.City) Like "L*"));

The differences are the asterisk instead of the percent and must end with semi-colon. If I recall, JET is particular about that.
Left by John Watson on Oct 26, 2005 7:21 AM

# re: Searching in the DataSet to save a Database trip
Requesting Gravatar...
this method is not an efficient one, what would happen if your original recordset has 1 mln of records?
Left by ASP.NET on Dec 02, 2010 1:40 AM

Your comment:
 (will show your gravatar)


Copyright © Mohammad Azam | Powered by: GeeksWithBlogs.net