Get relation between 2 databases.

[waleed.makarem]

Dear All ,

The question is simple , but may be It is very simple for me that I can not see it .

I have two dataset , each with with table . each datasets is connected to a separate dataadapter to get data from sql server database file (two different tables SQLtableOrders & SQLtableCustomerA))

datasetOrders.Tables("Orders") >>> the first column "orderID" is a primary unique key. Its structure is details below :

OrderID----OrderName----OrderCost--------Date
1----------A---------------54------------11/11/2010
2----------asdf------------45------------12/11/2010
3----------hrty------------89------------13/11/2010
4----------werff-----------36------------11/11/2010
5----------fgn------------154------------15/11/2010

datasetCustomers.Tablet("CustomerA") >>> contains only one column "orderID" which is a primary unique key.

OrdersID
1
3
4
datasetCustomers.Tablet("CustomerB") >>> contains only one column "orderID" which is a primary unique key.

OrdersID
3
4
5

The Qestion is :
When I click CustomerA >>> I need to display orders (assigned to CustomerA) into datagridview

OrderID----OrderName----OrderCost--------Date
1----------A---------------54------------11/11/2010
3----------hrty------------89------------13/11/2010
5----------fgn------------154------------15/11/2010

When I click CustomerB >>> I need to display orders (assigned to CustomerB) into datagridview
OrderID----OrderName----OrderCost--------Date
3----------hrty------------89------------13/11/2010
4----------werff-----------36------------11/11/2010
5----------fgn------------154------------15/11/2010

Please note that I had already made this by getting the data by SQL Statement
"Select SQLtableOrders.* , SQLtableCustomerA.orderID FROM SQLtableOrders , SQLtableCustomerA WHERE SQLtableOrders.orderID = SQLtableCustomerA.orderID"
It is really working fine , but the problem that it takes time to get data from the SQL server each time I disply orders. I already have the datasetOrders.Tables("Orders") data on the client PC , I need only to make the client PC extract which orders are assinged to a ceratin customers.
I can do it by For... Next , but I guess it will take time , specially if "orders" data contains many data .

Thanks,

[Pgmer]

you can fetch all the records in form load in background thread.
in click just filter out the records and show to the user, dont fecth from DB all the time.

[Ranx]

Can you not just add a datarelation to your dataset between your customer table and your orders table and navigate it that way?

[waleed.makarem]

Dear Pgmer ,
That already what I have ,at form Load event , I download the entire customers data from server to a datatable on the client software , then when I want to display orders for customerA , I just downooad the customerA orderID only , which are small amount of data that contain only the orders ID , then I need then to display order details using the orders datatable that is already found present into the client machine memory. But how to display order details ( data , cost , name , .. etc) if i have orderID only . ? This is the question .

By the way , One approach I used was to use the "Select XXXXXX Where " sentence by get relation between the two SQL tables , but this will get data from server each time i need to see a certain customers data . So I need to make the processing on the client machine ( as I already have the orders details table on the client "

I hope the case is now clear for you .

[waleed.makarem]

Dear Ranx ,

Can you please give me a small code for it . I already searched the net and found datarelation , but how to use . I made many trials but I failed .

Also , Does datarelation work on different tables from different datasets or it must be used for tables in one dataset .

I appreciate if you supply me with a code .

Thanks,

[waleed.makarem]

Another question : Which is faster , connected or disconnected mode

[Ranx]

You would need both tables to be in the same dataset.This is a pretty good article on datarelations

I think you should probably have a look at your "customer" dataset. Surely there is a one to many relationship based on the CustomerID, not the OrderID?

e.g.

Customer Table
CustomerID PK
CustomerName
etc

Order Table
OrderID PK
CustomerID FK (link to customer)
Order Details etc

[waleed.makarem]

Thanks . But you mean that all datatables must be in the same dataset.
how to handle this if I have many dataset for each table . do you have a solution for this.

Thanks,
Waleed

[Ranx]

Without seeing your data schema, I would only be guessing at the logical deployment of your data, so I don't really have enough information to provide you with a solution