Database query using dataset datatable or array

Posted on 2008-01-26
Medium Priority
Last Modified: 2008-01-27

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

Question by:MichMat
  • 5
  • 2
LVL 12

Expert Comment

ID: 20752844
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.
LVL 12

Expert Comment

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

Author Comment

ID: 20752963
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.

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

LVL 12

Expert Comment

ID: 20753188
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

LVL 12

Expert Comment

ID: 20753190
.. some of your code.

Author Comment

ID: 20753317
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.

LVL 12

Accepted Solution

vb_jonas earned 2000 total points
ID: 20757041
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.

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article, I’ll look at how you can use a backup to start a secondary instance for MongoDB.
Creating a Cordova application which allow user to save to/load from his Dropbox account the application database.
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
Suggested Courses

601 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question