SQL query on Datatables [VB 2005]

[Marchetto]

Hi all.

I thought that it was a FAQ.... but I can't find answer so i post in
this NG.


I have a dataset with two datatables (dt1 and dt2) in my application.

I'd like to know if there is a way to execute a SQL query that join
both datatables like this :

SELECT dt1.field1
FROM dt1 INNER JOIN dt2 ON dt1.field1 = dt2.field1

The ideal result is a third datatable.

I would like to make this only with code, without any wizard because I
would like to learn : )

Sorry for my English....

Thank you in advance !
MarK

[Ken Tucker [MVP]]

Re: SQL query on Datatables [VB 2005]

Hi,

You can create a data releation between 2 tables but you can not run
a query on in memory datatable with this version of vb. Microsoft is
working on the LINQ project which will allow you to do this. LINQ is a ctp
for features to be available in the next version of visual studio.

http://msdn2.microsoft.com/en-us/library/dbwcse3d.aspx

http://msdn.microsoft.com/vbasic/Future/default.aspx

Ken
------------
"Marchetto" <marcovbdotnet@ libero.it> wrote in message
news:1144661284 .278554.81030@t 31g2000cwb.goog legroups.com...[color=blue]
> Hi all.
>
> I thought that it was a FAQ.... but I can't find answer so i post in
> this NG.
>
>
> I have a dataset with two datatables (dt1 and dt2) in my application.
>
> I'd like to know if there is a way to execute a SQL query that join
> both datatables like this :
>
> SELECT dt1.field1
> FROM dt1 INNER JOIN dt2 ON dt1.field1 = dt2.field1
>
> The ideal result is a third datatable.
>
> I would like to make this only with code, without any wizard because I
> would like to learn : )
>
> Sorry for my English....
>
> Thank you in advance !
> MarK
>[/color]

[Marchetto]

Re: SQL query on Datatables [VB 2005]

Hi Ken, first of all I would to say a big THANK YOU for your answer.

It isn't possible to run a query on datatables.... that's a bad news :
)

The problem is that I import data from 2 .CSV files into 2 datatables.

Is there no way to join this data without save all records in a
database ?

What is the much correct solution ? I don't care about performance... I
would like to use clean and good code.

Thank you again !

[Cor Ligthert [MVP]]

Re: SQL query on Datatables [VB 2005]

Marchetto,

It can be that this is what you mean.

It is on our website.

http://www.vb-tips.com/default.aspx?ID=5fd5a8cf-54dc-4946-a193-8a9529b2b38b

Cor

"Marchetto" <marcovbdotnet@ libero.it> schreef in bericht
news:1144667526 .585223.201550@ t31g2000cwb.goo glegroups.com.. .[color=blue]
> Hi Ken, first of all I would to say a big THANK YOU for your answer.
>
> It isn't possible to run a query on datatables.... that's a bad news :
> )
>
> The problem is that I import data from 2 .CSV files into 2 datatables.
>
> Is there no way to join this data without save all records in a
> database ?
>
> What is the much correct solution ? I don't care about performance... I
> would like to use clean and good code.
>
> Thank you again !
>[/color]

[Marchetto]

Re: SQL query on Datatables [VB 2005]

Cor, thank you for your answer.

It's very interesting !

But probably I need a database :-(

The query in my first post was only a simple example. In my application
probably I'll need to run a bit more complex query, with sorting and
filtering functions.

I hoped it was possible to work with datatables just like "phisical"
tables stored in a database.
In my mind the perfect solution is one where I can use directly SQL
statement on two or more datatables.

I'm a novice and, in every case, I appreciate your help very much.

A big THANK YOU.

[Marchetto]

Re: SQL query on Datatables [VB 2005]

It seemed to me a good solution to fill a datatable for every .csv file
I have.... but at this point I ask to me which is the right way.....

1) To use my .csv files as datasource and run query directly on these.

2) To export all data from .csv files to a database (SQL Express,
Access....)

3) To export all data from .csv files to an XML file and use it as a
database.

The bigger file has about 10.000 rows.... I don't like very much the
idea to install SQL Express on every client..... my application
generates only a structured report... and should be quite "light".

Please give me a suggestion on what way to start to walk into....

Thank you MVPs !

[Cor Ligthert [MVP]]

Re: SQL query on Datatables [VB 2005]

Marchetto,
[color=blue]
>
> 1) To use my .csv files as datasource and run query directly on these.
>[/color]
Mostly the worsest from the bad ones
[color=blue]
> 2) To export all data from .csv files to a database (SQL Express,
> Access....)
>[/color]
Yes but than don't use your csv anymore. And try to avoid Access especially
the ammount of records as you wrote.
[color=blue]
> 3) To export all data from .csv files to an XML file and use it as a
> database.
>[/color]
This is in between not bad, but be aware that you should always read and
write complete XML files as with CSV. In the same way do you have to protect
that if the computer goes down you are not overwiting in that time your
inputfile. You are as well fast at your limit with what you want to do.
[color=blue]
> The bigger file has about 10.000 rows.... I don't like very much the
> idea to install SQL Express on every client..... my application
> generates only a structured report... and should be quite "light".
>[/color]
Just downloading and install.
[color=blue]
> Please give me a suggestion on what way to start to walk into....
>[/color]
That is probably impossibile for that we don't know your application enough,
which is normally not to describe in a newsgroup, so don't even try it in my
idea.

I hope this helps however something.

Cor

[Marchetto]

Re: SQL query on Datatables [VB 2005]

Cor, I really appreciate your help.

A tech department in my company uses a software of third parts that
doesn't provide any reporting system....so, with some command line
utility, I can export every data I need in comma separated values
files.

At this point I have to develop a simple (for you, not completely for
me :P ) application that shows these datas in a datagrid and provide a
structured report.

Up to now my biggest problem is to have a "menageable " data source.

I have to leave the idea to install SQL Express.... it's better to
export all to XML. No problem if a client crashes...becau se every time
my application starts I have to re-obtain datas from the source....the
database of the third parts software.

I hope I explained clearly enough...... my Italian is better than my
English : )

Another time.... THANK YOU.