Tricky ADO.NET question: joining data from database with a DataTable

[Uri Dor]

Hi everyone,

We're writing a windows forms application which produces various
reports. Some of the reports include complex financial calculations of
several types. Each calculation type is performed in a separate
'engine', which returns a DataTable.
For example, we'll have the following engines: (simplified for the sake
of discussion)
holdings: given a protfolio ID, gives a list of the stocks in it and
amounts.
stockinfo: given a set of stocks, fetches information about them (name,
stock exchange, etc.)

The process should be:
- receive a portfolio ID
- activate the holdings engine to get the holdings
- activate the stockinfo engine to get the info

The stockinfo engine needs to perform a SELECT which is filtered by the
output of the holdings engine, otherwise the query result will be way
too big.
The obvious thing would be to perform some kind of SQL JOIN between the
holdings engine's output and the database table stockinfo uses, but how
can I join a DataTable in memory with a database table?

I'd appreciate it if anyone has insight on this or ideas on how to
perform this.

Thx
Uri

 

[Manoj G [VB MVP]]

I dont know if I got the requirement right, but I suppose, the best way to
handle the situation is to use a view in the database and fetching
information against the view itself by creating the necessary where clause
information as required. But this might not really be applicable if your
stockinfo and holdings are from disparate data sources, like web services
for instance. In such cases you can implement a Join helper described by
this KB article:
http://support.microsoft.com/default.aspx?scid=kb;EN-US;325688

HTH,
Manoj G
[VB MVP]

 

[Uri Dor]

The thing is that the calculations which create the 'view' you meant
(e.g. the table of holdings) can't be created using SQL, due to complex
calculations or database size optimizations. This means it has to be
created either in C# or in T-SQL. But one of my project's requirements
is to be able to run with the Jet engine on an Access database, without
SQL Server, so T-SQL is out.

I dont know if I got the requirement right, but I suppose, the best way to
handle the situation is to use a view in the database and fetching
information against the view itself by creating the necessary where clause
information as required. But this might not really be applicable if your
stockinfo and holdings are from disparate data sources, like web services
for instance. In such cases you can implement a Join helper described by
this KB article:
http://support.microsoft.com/default.aspx?scid=kb;EN-US;325688

HTH,
Manoj G
[VB MVP]

Hi everyone,

We're writing a windows forms application which produces various
reports. Some of the reports include complex financial calculations of
several types. Each calculation type is performed in a separate
'engine', which returns a DataTable.
For example, we'll have the following engines: (simplified for the sake
of discussion)
holdings: given a protfolio ID, gives a list of the stocks in it and
amounts.
stockinfo: given a set of stocks, fetches information about them (name,
stock exchange, etc.)

The process should be:
- receive a portfolio ID
- activate the holdings engine to get the holdings
- activate the stockinfo engine to get the info

The stockinfo engine needs to perform a SELECT which is filtered by the
output of the holdings engine, otherwise the query result will be way
too big.
The obvious thing would be to perform some kind of SQL JOIN between the
holdings engine's output and the database table stockinfo uses, but how
can I join a DataTable in memory with a database table?

I'd appreciate it if anyone has insight on this or ideas on how to
perform this.

Thx
Uri

 

[Peter Huang]

Hi Uri,

Based on my knowledge, we can not do the join operation between a DataTable
and database.
As for your senario, I think you may try to create a temporary table in the
access database and then put the DataTable return in the calculation into
the temporary table, so that we can do the join operation now.

If you still have any concern on this issue, please feel free to post here.

Best regards,

Peter Huang
Microsoft Online Partner Support

Get Secure! - www.microsoft.com/security
This posting is provided "AS IS" with no warranties, and confers no rights.

 

[Jason]

use a constraint - ignore the other answers

 

[Jason]

further info: Constraints is a property on the datatable - you'll pick up
your answer there using intellisense - also - your post should really be in
ado.net group

The thing is that the calculations which create the 'view' you meant
(e.g. the table of holdings) can't be created using SQL, due to complex
calculations or database size optimizations. This means it has to be
created either in C# or in T-SQL. But one of my project's requirements
is to be able to run with the Jet engine on an Access database, without
SQL Server, so T-SQL is out.

I dont know if I got the requirement right, but I suppose, the best way to
handle the situation is to use a view in the database and fetching
information against the view itself by creating the necessary where clause
information as required. But this might not really be applicable if your
stockinfo and holdings are from disparate data sources, like web services
for instance. In such cases you can implement a Join helper described by
this KB article:
http://support.microsoft.com/default.aspx?scid=kb;EN-US;325688

HTH,
Manoj G
[VB MVP]

Hi everyone,

We're writing a windows forms application which produces various
reports. Some of the reports include complex financial calculations of
several types. Each calculation type is performed in a separate
'engine', which returns a DataTable.
For example, we'll have the following engines: (simplified for the sake
of discussion)
holdings: given a protfolio ID, gives a list of the stocks in it and
amounts.
stockinfo: given a set of stocks, fetches information about them (name,
stock exchange, etc.)

The process should be:
- receive a portfolio ID
- activate the holdings engine to get the holdings
- activate the stockinfo engine to get the info

The stockinfo engine needs to perform a SELECT which is filtered by the
output of the holdings engine, otherwise the query result will be way
too big.
The obvious thing would be to perform some kind of SQL JOIN between the
holdings engine's output and the database table stockinfo uses, but how
can I join a DataTable in memory with a database table?

I'd appreciate it if anyone has insight on this or ideas on how to
perform this.

Thx
Uri

 

[Jason]

and i forgot to add - you are looking at constraint in conjunction with
datarelation

further info: Constraints is a property on the datatable - you'll pick up
your answer there using intellisense - also - your post should really be in
ado.net group

The thing is that the calculations which create the 'view' you meant
(e.g. the table of holdings) can't be created using SQL, due to complex
calculations or database size optimizations. This means it has to be
created either in C# or in T-SQL. But one of my project's requirements
is to be able to run with the Jet engine on an Access database, without
SQL Server, so T-SQL is out.

way

to

handle the situation is to use a view in the database and fetching
information against the view itself by creating the necessary where clause
information as required. But this might not really be applicable if your
stockinfo and holdings are from disparate data sources, like web services
for instance. In such cases you can implement a Join helper described by
this KB article:
http://support.microsoft.com/default.aspx?scid=kb;EN-US;325688

HTH,
Manoj G
[VB MVP]


Hi everyone,

We're writing a windows forms application which produces various
reports. Some of the reports include complex financial calculations of
several types. Each calculation type is performed in a separate
'engine', which returns a DataTable.
For example, we'll have the following engines: (simplified for the sake
of discussion)
holdings: given a protfolio ID, gives a list of the stocks in it and
amounts.
stockinfo: given a set of stocks, fetches information about them (name,
stock exchange, etc.)

The process should be:
- receive a portfolio ID
- activate the holdings engine to get the holdings
- activate the stockinfo engine to get the info

The stockinfo engine needs to perform a SELECT which is filtered by the
output of the holdings engine, otherwise the query result will be way
too big.
The obvious thing would be to perform some kind of SQL JOIN between the
holdings engine's output and the database table stockinfo uses, but how
can I join a DataTable in memory with a database table?

I'd appreciate it if anyone has insight on this or ideas on how to
perform this.

Thx
Uri

 

[Uri Dor]

So far, except from mentioning this isn't the right NG (I agree), I
haven't understood any of your replies or their relevance to my question.
thanks anyway

 

[Peter Huang]

Hi Uri,

From your description, now you wants to do a join operation with a
datatable and the tables in the database, am I right?
Because the ASO.NET did not support the feature, I think now you may try to
put the datatable back to a table in the database, so that we can do the
join operation, because all the table now is in the database, we can use a
SQL statement to do the job.

If you still have any concern, please feel free to let me know.

Best regards,

Peter Huang
Microsoft Online Partner Support

Get Secure! - www.microsoft.com/security
This posting is provided "AS IS" with no warranties, and confers no rights.

 

[Uri Dor]

Thanks, Peter,
That was one idea we thought of here.

 

[Jason]

i wouldn't have thought it so hard to underand given i mentioned a couple of
words which you then plug into vs studio help

you get a table back from engine 1
you get a table back from engine 2 - but you need to relate the results from
table 1 to table 2

so join the datatables - which is why i mentioned constraints and
datarelations

now: you are talking of joining your datatable on the client to the table on
the server. well that might be something you can do in yukon perhaps (that
is me extrapolating) - but here and right now - you need to do your join on
the client.

 

[Nick Malik]

You can extend the functionality of the stockinfo engine in the following
way:
The holdings engine gets a list of stocks (and a great deal of other
information, I'm sure). From there, create an object that contains only the
information needed for a successful join. For the sake of argument, let's
say it's a list of stock symbols. So you pass that LIST to your extended
stockinfo engine.

The extended engine will run on the same server as the stockinfo engine
currently runs, using similar mechanisms for listening and responding. It
will accept the extended request, and issue individual requests for a single
stock from the stockinfo question, all the while building up a single
response object. Once the list is done, you return the response object.
(Note: if these objects are large, use async operations, publish and
subscribe patterns, and a tool like Biztalk to collect your data).

Note: if the stockinfo engine uses SQL Server, there's no reason you
couldn't do most of this is a stored proc using temporary tables to hold the
result set until it was completely built.

I don't know enough about your operation to know if this suggestion is
feasable. However, joining on the end client sounds too inefficient.

--- Nick

 

[Uri Dor]

the problem is that if I "get a table back from engine 2" without using
engine 1's results as a filter, the table returned is too big.
quoting from my original post, which you should have read:
"The stockinfo engine needs to perform a SELECT which is filtered by the
output of the holdings engine, *** otherwise the query result will be
way too big. ***"

 

[Uri Dor]

Thanks, Nick,
Basically that's a record-by-record way of doing the work. Sounds like a
good idea.

 

[Jason]

i did read it. it is poor design. i am trying to help you. rewrite it - it
is crap. what more can i say. either pass your parameters through some
other way - or leave a table behind on the server in your crap application -
or heed my advice.

either way - you guys in the financial industry are supposed to earn heeps
to know things. clearly that is not the case.

 

[Jason]

well put - a nicer way of helping the undeserving. you do realise he gets
paid well to copy your instrcutions.

 

[Jason]

oh - and would you like me to name your variables for you too?

 

[scorpion53061]

Uri,

Joins are highly overrated. I would strongly suggest a copy of David
Sceppa's ADO.NET book for you.

http://www.computer-mentors.co.uk/c...d=0735614237&search_type=AsinSearch&locale=us

But anyway, I would do as Jason suggested. Take a deep breath and I know
this sounds corny in this day and age but take pencil and paper and plot
out the course of your code prior to actually typing it in. I guess what
I mean is get the concept you want then devise the code. Otherwise you
find yourself going in all sorts of directions. Believe me in the long
run you will be glad you did. Coding on the fly as you go can get you
into some real quagmires.

I don't think Jason means to show you disrespect. He is just frustrated
you are not "getting it."

P.S. I am pretty good with apps such as these and if you would like
assistance, please contact me off list at
(e-mail address removed) (remove nospamhere). Write down on
paper, what your goals are and how you would like to see them
accomplished and then email me.



________________________________

From: Jason [mailto:[email protected]]
Sent: Tuesday, July 06, 2004 6:53 AM
To: microsoft.public.dotnet.framework
Subject: Re: Tricky ADO.NET question: joining data from database with a
DataTable



i did read it. it is poor design. i am trying to help you. rewrite it -
it
is crap. what more can i say. either pass your parameters through some
other way - or leave a table behind on the server in your crap
application -
or heed my advice.

either way - you guys in the financial industry are supposed to earn
heeps
to know things. clearly that is not the case.

the problem is that if I "get a table back from engine 2" without using
engine 1's results as a filter, the table returned is too big.
quoting from my original post, which you should have read:
"The stockinfo engine needs to perform a SELECT which is filtered by the
output of the holdings engine, *** otherwise the query result will be
way too big. ***"

Jason wrote:

couple of

from

table on

 

[Jason]

thank you scorpion - i hope there isn't a sting in the tail

you are right - in what you say - but i'll be a little clearer. i prefer to
help people by giving them pointers to the answers - not actually doing the
work for them - or rewriting the docs. there are many people who post
messages wherein they want the community to scope out their problem and give
them an answer to business related issues - not technology related. clearly
uri is having himself as much a business problem here - i think he knows
that he has got himself in a position where his technical solution doesn't
fit his business problem, but i am also sure he knows what the immediate
"quick fit" solution is - shared data.

his problem is that he knows technology, knows his business, but somehow
didn't make the two glue. we can all postulate solutions - but i think uri
has the skills to do this on his own. there is no magic wand for curing
what was done wrong in the first place and i am sure he knows that. but
rather than fight me on it uri - get to it - you now have a number of
techinical solutions that will get your business working again quickly.
lets be fair, you probably don't have the time or the money to rewrite - or
at least - the bank does - but you want to keep your job.

Uri,

Joins are highly overrated. I would strongly suggest a copy of David
Sceppa's ADO.NET book for you.

http://www.computer-mentors.co.uk/c...d=0735614237&search_type=AsinSearch&locale=us

But anyway, I would do as Jason suggested. Take a deep breath and I know
this sounds corny in this day and age but take pencil and paper and plot
out the course of your code prior to actually typing it in. I guess what
I mean is get the concept you want then devise the code. Otherwise you
find yourself going in all sorts of directions. Believe me in the long
run you will be glad you did. Coding on the fly as you go can get you
into some real quagmires.

I don't think Jason means to show you disrespect. He is just frustrated
you are not "getting it."

P.S. I am pretty good with apps such as these and if you would like
assistance, please contact me off list at
(e-mail address removed) (remove nospamhere). Write down on
paper, what your goals are and how you would like to see them
accomplished and then email me.



________________________________

From: Jason [mailto:[email protected]]
Sent: Tuesday, July 06, 2004 6:53 AM
To: microsoft.public.dotnet.framework
Subject: Re: Tricky ADO.NET question: joining data from database with a
DataTable



i did read it. it is poor design. i am trying to help you. rewrite it -
it
is crap. what more can i say. either pass your parameters through some
other way - or leave a table behind on the server in your crap
application -
or heed my advice.

either way - you guys in the financial industry are supposed to earn
heeps
to know things. clearly that is not the case.

the problem is that if I "get a table back from engine 2" without using
engine 1's results as a filter, the table returned is too big.
quoting from my original post, which you should have read:
"The stockinfo engine needs to perform a SELECT which is filtered by the
output of the holdings engine, *** otherwise the query result will be
way too big. ***"

Jason wrote:

couple of
results

from

table on

(that

join

on

question.

sake

(name,

the

the

how

 

[scorpion53061]

No its all good.

There are times when I will come to the group for concept help (how
would you approach this kind of thing) but the problem here is that no
forethought into how the goals would be accomplished and an overall
strategy of how to approach the application was set.

You are right though. For what he was asking he needs to pay you an
hourly fee to walk you through it because the topics are so broad.

________________________________

From: Jason [mailto:[email protected]]
Sent: Wednesday, July 07, 2004 9:56 AM
To: microsoft.public.dotnet.framework
Subject: Re: Tricky ADO.NET question: joining data from database with a
DataTable



thank you scorpion - i hope there isn't a sting in the tail

you are right - in what you say - but i'll be a little clearer. i
prefer to
help people by giving them pointers to the answers - not actually doing
the
work for them - or rewriting the docs. there are many people who post
messages wherein they want the community to scope out their problem and
give
them an answer to business related issues - not technology related.
clearly
uri is having himself as much a business problem here - i think he
knows
that he has got himself in a position where his technical solution
doesn't
fit his business problem, but i am also sure he knows what the immediate
"quick fit" solution is - shared data.

his problem is that he knows technology, knows his business, but somehow
didn't make the two glue. we can all postulate solutions - but i think
uri
has the skills to do this on his own. there is no magic wand for curing
what was done wrong in the first place and i am sure he knows that. but
rather than fight me on it uri - get to it - you now have a number of
techinical solutions that will get your business working again quickly.
lets be fair, you probably don't have the time or the money to rewrite -
or
at least - the bank does - but you want to keep your job.

Uri,

Joins are highly overrated. I would strongly suggest a copy of David
Sceppa's ADO.NET book for you.


http://www.computer-mentors.co.uk/c...d=0735614237&search_type=AsinSearch&locale=us


But anyway, I would do as Jason suggested. Take a deep breath and I know
this sounds corny in this day and age but take pencil and paper and plot
out the course of your code prior to actually typing it in. I guess what
I mean is get the concept you want then devise the code. Otherwise you
find yourself going in all sorts of directions. Believe me in the long
run you will be glad you did. Coding on the fly as you go can get you
into some real quagmires.

I don't think Jason means to show you disrespect. He is just frustrated
you are not "getting it."

P.S. I am pretty good with apps such as these and if you would like
assistance, please contact me off list at
(e-mail address removed) (remove nospamhere). Write down on
paper, what your goals are and how you would like to see them
accomplished and then email me.



________________________________

From: Jason [mailto:[email protected]]
Sent: Tuesday, July 06, 2004 6:53 AM
To: microsoft.public.dotnet.framework
Subject: Re: Tricky ADO.NET question: joining data from database with a
DataTable



i did read it. it is poor design. i am trying to help you. rewrite it -
it
is crap. what more can i say. either pass your parameters through some
other way - or leave a table behind on the server in your crap
application -
or heed my advice.

either way - you guys in the financial industry are supposed to earn
heeps
to know things. clearly that is not the case.

the problem is that if I "get a table back from engine 2" without using
engine 1's results as a filter, the table returned is too big.
quoting from my original post, which you should have read:
"The stockinfo engine needs to perform a SELECT which is filtered by the
output of the holdings engine, *** otherwise the query result will be
way too big. ***"

Jason wrote:

couple of
results

from

table on

(that

join

on

question.

of

sake

(name,

the

way

the

how