Merge results from queries from two separate databases in a datagrid

[Dudge669]

What is the best way to do this? I have access to a readonly sql
database with information about people - first, last, address & social
security number, etc and I want to display this information in a vb.net
datagrid. That part I have down and I use a dataadapter to populate my
dataset, etc etc. What I'm getting hung up on is the fact that I need
to also merge into this grid information from a separate database on a
different server that has "person ids" associated with social security
numbers.

My grid will only have at most between 300-500 records in it with
addresses and ssn's, etc of people of recent interest and I need to
query this other database with 'person ids' and ssns which has around
70,000 records. I have a very slow solution right now that loops
through the grid after first populating from database A, looking up
each ssn in table B and populating the missing fields in the columns.
This process takes between 1 to 2 minutes to do this lookup. I tried
adding an index on the ssn column in table B but that didn't help much.
Database A is offsite and I have readonly access to it.

I looked into using the .Merge function after first retrieving all
70,000 records from database B into a dataset and setting primary keys
on SSN but it didn't work. I read information about rowstate and how it
has to be set a certain way and to try setting the dataadapter's
AcceptChangesDuringFill property = False so that the rows have a state
of 'added' to get the merge to work but it's not working and I really
can't figure out why.

Another option I'm thinking of doing is after retrieving the 300-500
records from database A, creating a temporary table in database B on my
local sql server here and then just doing something like:

SELECT tablea.first, tablea.last, tablea.address, tablea.ssn,
tableB.personid where tabela.ssn=tableb.ssn

But I would like to avoid the temporary table creation/fill/destroy
method as this may soon be run quite a few times a day concurrently by
many people. I have been working with VB for quite a few years and SQL
off and on for a few years. I'm no expert in sql but I know there must
be some way to do this easier.

What is my best approach to this problem? Many thanks!

 

[Cor Ligthert [MVP]]

Dudge,

If I understand your problem well than is an idea for your solution
One datagrid with the 300-500 adresses readed withouth a Where clause
Another datagrid with the other data from the other database, that you fill
using a Where from the ID at currencymanager position change of the First
datagrid.

http://msdn.microsoft.com/library/d...ndingmanagerbaseclasspositionchangedtopic.asp

Don't forget to use on both
ds.tables(0).defaultview.addnew = false
and more of those defaultview (dataview) properties

I hope this helps,

Cor

 

[Dudge669]

So this would basically do a query of database B using a where ID= with
information from the first datagrid as the first datagrid is being
populated? That is still doing 300-500 queries to the second database
though isn't it? I guess I don't see how that's any different than the
loop approach I have set up.

What I'm thinking I may do is read in the information from server A,
create a temporary table on server B and then do a cross-table query
from server B with the information I need. I'll check on how that goes
sepeed wise later today. Anyone else have any suggestions? I'm really
stumped here. Thanks!

 

[Cor Ligthert [MVP]]

So this would basically do a query of database B using a where ID= with

information from the first datagrid as the first datagrid is being
populated? That is still doing 300-500 queries to the second database
though isn't it? I guess I don't see how that's any different than the
loop approach I have set up.

It will in my opinion only be read all, if the user select all the rows one
by one.

Cor

 

[Dudge669]

Ok I see what you're saying and I think you misunderstrood my original
question. Thank you for the suggestions but they won't work for what
I'm trying to do. I want to populate the entire table from the
beginning - all 300-500 rows for the user to be asble to use. I need
all lookups done as soon as the user has control of the program, not as
they click on each record.

 

[Cor Ligthert [MVP]]

Dudge,

Ok I see what you're saying and I think you misunderstrood my original
question. Thank you for the suggestions but they won't work for what
I'm trying to do. I want to populate the entire table from the
beginning - all 300-500 rows for the user to be asble to use. I need
all lookups done as soon as the user has control of the program, not as
they click on each record.

Have you a reason for that, I know one situation where this can be the only
way however you did not talk about that.

Cor