DataTable Help Required

[Baillie11]

Hi all I am creating an app that utilises and MS Access back end and queries it quite a bit at various times.

What I would like to do is bring in an entire table and then query the row(s) i want as I need them.

This is what I have so far:

Dim con As New OleDb.OleDbConnection
        con.ConnectionString = "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source = C:\Documents and Settings\Administrator\My Documents\Visual Studio 2005\Projects\TenderSearch\eTenderBox\eTenderBox\Databases\eTenderBox.mdb"
        con.Open()
        Dim ds As New DataSet
        Dim da As OleDb.OleDbDataAdapter
        Dim sql As String
        inc = -1
        'Here we search the SUPPLIER TABLE for an email address that matches that privided in the supplier login text box.
        sql = "SELECT * FROM tblSuppliers"
        da = New OleDb.OleDbDataAdapter(sql, con)

        'Here we place all data returned from our table into a dataset call SUPPLIERINFO
        'All comparisons and changes are actually made to this data set and then saved to the table when finished.
        da.Fill(ds, "SupplierInfo")
        con.Close()

        Dim supplierlogin As String
        Dim supplierpassword As String
        Dim suppliername As String
        Dim maxrows As Integer

        maxrows = ds.Tables("SupplierInfo").Rows.Count

        supplierlogin = ds.Tables("SupplierInfo").Rows(0).Item("Email")
        supplierpassword = ds.Tables("SupplierInfo").Rows(0).Item("fldPW")
        suppliername = ds.Tables("SupplierInfo").Rows(0).Item("Firstname")
        sSupplierID = ds.Tables("Supplierinfo").Rows(0).Item("SupplierID")

Rather than having to run SQL queries all the time, I was hoping to load everything into a datatable and simply query that as needed.

Can anyone assist how to set up the DataTable and then query it.

OR any suggestions for a better alternative would be gladly accepted.

Thank you

[sknake]

You don't want to do that. Keep querying the access database ;)

[TomW]

For the most part I absolutely agree with Sknake above. However in certain situations, such as working with the same group of records over & over that Im not updating only filtering for a result, I do find it useful to hold the date in memory rather then run a new query every minute.

With that said, with data that you are updating which it seems you are updating one record at a time here, it will only take milliseconds to query and return only the record(s) you want to work with. This outweighs the cost of keeping an entire table in memory.

Searching for Data in the DataSet
When querying a DataSet for rows that match particular criteria, you can increase the performance of your searches by taking advantage of index-based lookups. When you assign a PrimaryKey value to a DataTable, an index is created. When you create a DataView for a DataTable, an index is also created. Here are a few tips for taking advantage of index-based lookups.

• If the query is against the columns that make up the PrimaryKey of the DataTable, use DataTable.Rows.Find instead of DataTable.Select.

• For queries involving non-primary key columns, you can improve performance for multiple queries of the data using a DataView. When you apply a sort order to a DataView, an index is built that is used when searching. The DataView exposes the Find and FindRows methods to query the data in the underlying DataTable.

• If you do not require a sorted view of a table, you can still take advantage of index-based lookups by creating a DataView for the DataTable. Note that this is only an advantage if you are performing multiple queries on the data. If you are only performing a single query, the processing required to create the index reduces the performance gained by using the index.