Filtering Dataset Records - Need to replicate SQL Inner Join query

[nells1]

Hi All

I am creating a search builder for my VB.Net database application. The search builder currently produces a SQL statement using Inner Joins like:

SELECT Customers.*
FROM Customers
INNER JOIN Jobs
ON Customers.CustomerID = Jobs.CustomerID
WHERE Customers.CustomerName Like 'A%' AND Jobs.JobNameID = 1

This would be fine and I could send this to SQL Server and get back the required results. However I'd like to do this completly disconneced from SQL just using the dataset the application runs on which is an exact copy of the database on the server (relations and foreign keys included).

Is there a way to do this??

or do I have to filter each "inner joined" datatable/dataview first and then get the parent rows for each row left? There has to be a better way... I hope )

Any ideas, help would be very much appreciated.

Thank you
Nells

 

[RiverGuy]

Use DataRelations, and GetChildRows. Look them up in VS.Net help.

 

[chrissie1]

arent you trying to something that sql-server is meant to do?? you want a client that does all the work while the server does nothing??

Christiaan Baes
Belgium

What a wonderfull world - Louis armstrong

 

[RiverGuy]

If he or she was doing a parent/child grid type of form, the the preferred method IMO is to do it within the DataSets. It seems as though it is heirchical, so that makes sense.

 

[chrissie1]

not if you have thousands and thousands of records. then you would be trying to reduce the number of records you wanna send over the cable and save some bandwidth IMHO.

Christiaan Baes
Belgium

What a wonderfull world - Louis armstrong

 

[RiverGuy]

I think it is a tradeoff. If there are few columns...and any record can be worked on...then why not have all records available? Why ask the database to re-query and send over the results each time you change records.

At the minimum, there would need to be all parent records brought over with the filter. What if each parent has at the most a few jobs? Is it better to go ahead and bring over all jobs for all parent records in the filter....or do re-query each time the current customer record is changed?

 

[chrissie1]

yep like you said its a trade off

Christiaan Baes
Belgium

What a wonderfull world - Louis armstrong

 

[nells1]

Hi RiverGuy and Christiaan

Thanks for the insight, its seems there is no easy answer.

Yes I am trying to do something that Sql Server should be doing. This is because I'm making this application for a small company that can't afford the full blown Sql Server, so is using MSDE instead.. I understand that this has a maximum of 5 useable connections at any one time so I am trying to limit the number of times people connect. There could be between 5 & 10 LAN users.

When a user starts the application the in-memory dataset is created and filled from the database.. ALL records are sent. There will not be thousands and thousands of records, but I am still worried that this could be too much information for the network or the client to hold in memory? I don't know yet as I don't have their full dataset (it has to be converted from a DOS system).

In my heart of hearts I'm sure this isn't the right way of doing things.. but my problem comes in that I have got all the information I need on the client machine... it seems such a waste to go back to the server just to run a query and I was wondering if the dataset had a similar functionality..

I've been reading the documentation and couldn't find anything except the GetParentRows and GetChildRows... But there may be multiple tables involved in this search....

ie
Find Customers with Jobs Like 'These'
and with Sites like 'These'
and with Repairs like 'These'

... and I'm not too sure how efficent the GetParentRows and GetChildRows would be.

I'm sorry if I sound confused.. but thats because I am.. this is my first non MSAccess MultiUser application and there are so many things to consider.

Should I set up a webserver that maintains one connection to the database and the LAN users could connect to that.

Should I make it so that all the users are completly disconnected from the database so they can work as remote users when away from the office as well....

And back to my original question, what do you think of XPath as a Dataset query tool?

Sorry for the extended questions, but if you could offer advice on anything I would be extremly interested in what you have to say.

Many thanks
Nells

 

[chrissie1]

dont worry to much about the 5 user limit of msde (not with a maximum of 10 users). I cant imagine that those 10 users would be querying the database at the same time.

For the webserver thingie, I dont really know how complex the database is going to be or how complex the client application needs to be but me I rather prefer windows forms over the thin client aproach (IMHO).

I never worked with xpath and because my database has milions of records I am not planning to in the near future.

I wish you a lot of luck with this first vb.net project.

Christiaan Baes
Belgium

What a wonderfull world - Louis armstrong

 

[nells1]

Thanks for your time on this Christiaan.. I'm pleased to see you weren't horrified by the set up of the project )

And thank you for the Luck as well I'm sure I'm going to need every bit of it )

I think I'll just run the query back on the server and if it starts to jam up I'll look for another solution.

Cheers!!
Nells