We help IT Professionals succeed at work.

Database query using dataset datatable or array

MichMat asked

A question regarding an Database query, using VB.net.
I would like to query a database to get the names and telephone numbers from the database using as a reference either an array or a datatable using sql.
like this
Select Names, Telnumbers from My database if ( the name im looking for  is in a datatable or array ) (I can create either)
at the moment Im using a loop to search for them but this is so slow I would like to get all the info out at once.

Is this possible? Or is it only possible if the names are in a nother database? And if so how can I for instance access the said data from a Access database when the names are coming from a dbf?

I have done this with 2 dbf's in the same folder quite sucessfully cutting down the search time using loops from 30 minutes to about 60 seconds and would like to do so again in this different scenario. What would be the best way to go?

Thank you

Watch Question

If I understand you right it's a common relation, that performs best on two tables in the same db.

SELECT Table1.Names,Table2.TeleNumbers FROM Table1 INNER JOIN Table2 ON Table1.Names=Table2.Names

That would also be possible to do with a linked table from an Access database to your sql database.

To use a Accesstable from within SQL, you can use OpenRowSet:
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
   'c:\docs\mydatabase.mdb';'admin';'', TableName)


Not sure that I have explained my self right.

Idealy I would like to query a database using the names that I already have in a datatable.
There might be for instance 700 names that I have extracted into a datatable from a dbf free file.
Now I want to match the names to a master database containing 800,000 names and create a NEW dataset datatable that contains details from the master database about a person if the name in the master database matches that of the datatable name. All at the same time.

I am new to all of this and basicaly know only very simple sql statments.


Is the "master database" in access or sql server? And are you using a dbf as a file, or in a sql server attached db?

You could use a dataadapter to fill data to a new datatable, using the SQL statement above. But the little tricky bit might be to link the access datatable into the dbf.

For a better explanation / working code, please post

.. some of your code.


That is the trouble,
I havent got anything as yet. Currently the dbf is the only thing that is not changable, the rest I need to make up. There is no sql database rather sql statments. The master database can be anything I want im using excel file at the moment because its what I know. I access the dbf using vfpoledb and return a dataset with all the names and details. Then I loop throug the dataset and 'find' the names in the master excel file. As you can imagine it takes a long time.  I was hoping to use that dataset to construct a query fro the  master database because I have to use it for other purposes so Im trying not to duplicate.

Basicaly Im trying to find a faster method.

So you have a dataset.datatable with approx  700 names and details from a Visual Fox pro database. And use a datafile with 800000 names, and you would like to extract a table with those names who exists in both the 700 (+details) and the 800000, right?

When you did it with 2 dbf's, did you still work with a loop? The fastest method is running sql instructions at the same db. If you are comfortable with FoxPro then run your query there. Otherwise - run it from within Access. Since I'm used to Access here's my idea there:

Open up an Access db, and create a table with the 800000 names (you could do that, no?), call it for examle "MasterTable"
Create a linked table to your dbf - in Access tables create a new linked table, select the Fox Pro provider and your dbf, and the table within it. "DetailsTable"
Create a new table that should contain the results, "ResultsTable" - create the columns you need.

Create a new insert query something like this:

INSERT INTO ResultsTable (Name, Details)
SELECT MasterTable.Names,DetailsTable.TeleNumbers FROM MasterTable INNER JOIN DetailsTable ON MasterTable.Names=DetailsTable.Names

That query will insert all names and details into resultstable, where the names exists in both mastertable & detailstable.

Explore More ContentExplore courses, solutions, and other research materials related to this topic.