DataRelations, DataTables, DataSets... what's the equivalent of a SQL JOIN statement?

[Anthony Williams]

Hi all,

I'm hoping that you guys will be able to help me out here. A friend of mine
has suggested that I look into DataRelations, but to be completely honest,
I'm just starting to use ADO.NET properly, and with the lack of useful
tutorials or walkthroughs on the 'net, and a current short-term lack of
money to allow me to buy a good book on the subject, I'm finding it
difficult getting what you guys would probably call an easy solution.

Anyway, I'm currently creating a timesheet application, the timesheet
component of which is likely to be used in various other applications on our
intranet - service diaries, meeting management etc. - so I'm hoping to get
this right, as I'll be using it again and again.

The basic idea of the application is that a day is split into 240 6-minute
(1/10 hour) slots. In this initial version of the app, I only need to show
whether or not a person was working or not during each 6 minute slot. Using
SQL, I could have created a stored procedure which would create a temporary
table, populate the temporary table with datetime values ranging from
DD-MM-YYYY 00:00:00 to DD+1-MM-YYYY 00:00:00 in 6 minute increments, and
then join the temporary table on the Progress table, which stores the
progress of the jobs that people are doing in the current version of the
timesheet app.

Example output of the Progress table is as follows:

------------------------------
[JobNum] [TimeStart] [TimeEnd] [Person]
0308-006 2003-09-02 09:00:00.000 2003-09-02 11:42:00.000 AW
0308-013 2003-08-27 14:36:00.000 2003-08-27 18:00:00.000 JW
0308-033 2003-08-28 08:00:00.000 2003-08-28 12:00:00.000 AW
0308-034 2003-09-01 17:24:00.000 2003-09-01 18:00:00.000 AW
0308-030 2003-09-03 14:00:00.000 2003-09-03 17:06:00.000 JW
0308-005 2003-09-04 08:24:00.000 2003-09-04 09:18:00.000 JW
0309-012 2003-09-04 11:54:00.000 2003-09-04 16:36:00.000 AK
0308-033 2003-09-05 13:12:00.000 2003-09-05 18:06:00.000 JW
------------------------------

The temporary table would then be joined, something like:

SELECT #FreeBusy.FBTime, COUNT(TimeMgrProgress.JobNum) As Busy
FROM #FreeBusy LEFT OUTER JOIN TimeMgrProgress
ON TimeMgrProgress.TimeStart <= FreeBusy.FBTime
AND TimeMgrProgress.TimeEnd >= FreeBusy.FBTime
GROUP BY FreeBusy.FBTime

If the "Busy" value returned is greater than 0, the person was busy during
that period, otherwise, they were not.

So, here comes my request for doing this the .NET way, using ADO.NET
DataTables. I have manually created a DataTable called "FreeBusy" in my
DataSet, and populated it with time values ranging from midnight today to
midnight tomorrow, in 6 minute increments - the ADO.NET equivalent of my
temporary table #FreeBusy:

------------------------------
[FBTime]
09-SEP-2003 00:00
09-SEP-2003 00:06
09-SEP-2003 00:12
09-SEP-2003 00:18
09-SEP-2003 00:24
....
09-SEP-2003 23:36
09-SEP-2003 23:42
09-SEP-2003 23:48
09-SEP-2003 23:54
10-SEP-2003 00:00
------------------------------

The question is, where do I go from here? Is it quicker to use the ADO.NET
methods, or will it be just as quick to use the stored procedure I've
already planned? As mentioned, I have very little ADO.NET experience, but
would really like to get under the hood and find out some more.

Many thanks to anyone who takes the time to reply.

Cheers,
Anthony.

 

[Ravikanth[MVP]]

Hi

Use stored procedures only.

Ravikanth[MVP]

-----Original Message-----
Hi all,

I'm hoping that you guys will be able to help me out here. A friend of mine
has suggested that I look into DataRelations, but to be completely honest,
I'm just starting to use ADO.NET properly, and with the lack of useful
tutorials or walkthroughs on the 'net, and a current short-term lack of
money to allow me to buy a good book on the subject, I'm finding it
difficult getting what you guys would probably call an easy solution.

Anyway, I'm currently creating a timesheet application, the timesheet
component of which is likely to be used in various other applications on our
intranet - service diaries, meeting management etc. - so I'm hoping to get
this right, as I'll be using it again and again.

The basic idea of the application is that a day is split into 240 6-minute
(1/10 hour) slots. In this initial version of the app, I only need to show
whether or not a person was working or not during each 6 minute slot. Using
SQL, I could have created a stored procedure which would create a temporary
table, populate the temporary table with datetime values ranging from
DD-MM-YYYY 00:00:00 to DD+1-MM-YYYY 00:00:00 in 6 minute increments, and
then join the temporary table on the Progress table, which stores the
progress of the jobs that people are doing in the current version of the
timesheet app.

Example output of the Progress table is as follows:

[TimeEnd] [Person]

0308-006 2003-09-02 09:00:00.000 2003-09-02 11:42:00.000 AW
0308-013 2003-08-27 14:36:00.000 2003-08-27 18:00:00.000 JW
0308-033 2003-08-28 08:00:00.000 2003-08-28 12:00:00.000 AW
0308-034 2003-09-01 17:24:00.000 2003-09-01 18:00:00.000 AW
0308-030 2003-09-03 14:00:00.000 2003-09-03 17:06:00.000 JW
0308-005 2003-09-04 08:24:00.000 2003-09-04 09:18:00.000 JW
0309-012 2003-09-04 11:54:00.000 2003-09-04 16:36:00.000 AK
0308-033 2003-09-05 13:12:00.000 2003-09-05 18:06:00.000 JW
------------------------------

The temporary table would then be joined, something like:

SELECT #FreeBusy.FBTime, COUNT

(TimeMgrProgress.JobNum) As Busy

FROM #FreeBusy LEFT OUTER JOIN TimeMgrProgress
ON TimeMgrProgress.TimeStart <= FreeBusy.FBTime
AND TimeMgrProgress.TimeEnd >= FreeBusy.FBTime
GROUP BY FreeBusy.FBTime

If the "Busy" value returned is greater than 0, the person was busy during
that period, otherwise, they were not.

So, here comes my request for doing this the .NET way, using ADO.NET
DataTables. I have manually created a DataTable called "FreeBusy" in my
DataSet, and populated it with time values ranging from midnight today to
midnight tomorrow, in 6 minute increments - the ADO.NET equivalent of my
temporary table #FreeBusy:

------------------------------
[FBTime]
09-SEP-2003 00:00
09-SEP-2003 00:06
09-SEP-2003 00:12
09-SEP-2003 00:18
09-SEP-2003 00:24
....
09-SEP-2003 23:36
09-SEP-2003 23:42
09-SEP-2003 23:48
09-SEP-2003 23:54
10-SEP-2003 00:00
------------------------------

The question is, where do I go from here? Is it quicker to use the ADO.NET
methods, or will it be just as quick to use the stored procedure I've
already planned? As mentioned, I have very little ADO.NET experience, but
would really like to get under the hood and find out some more.

Many thanks to anyone who takes the time to reply.

Cheers,
Anthony.


.

 

[William Ryan]

Anthony:

Think of it this way.. you are free to create a schema locally that may
exactly match the DB, or it may look nothing like it. You can create the
equivalent of your temp table just by writing a query and assigning a
DataTable to it. Typically, you don't want to do the join server side but
ignore that for now. Check out this MSDN link, It does a pretty good job of
explaining how Relations work. I'm at work now, but I'll write some more
later on when I have a few mintues.

Cheers,

Bill

Hi all,

I'm hoping that you guys will be able to help me out here. A friend of mine
has suggested that I look into DataRelations, but to be completely honest,
I'm just starting to use ADO.NET properly, and with the lack of useful
tutorials or walkthroughs on the 'net, and a current short-term lack of
money to allow me to buy a good book on the subject, I'm finding it
difficult getting what you guys would probably call an easy solution.

Anyway, I'm currently creating a timesheet application, the timesheet
component of which is likely to be used in various other applications on our
intranet - service diaries, meeting management etc. - so I'm hoping to get
this right, as I'll be using it again and again.

The basic idea of the application is that a day is split into 240 6-minute
(1/10 hour) slots. In this initial version of the app, I only need to show
whether or not a person was working or not during each 6 minute slot. Using
SQL, I could have created a stored procedure which would create a temporary
table, populate the temporary table with datetime values ranging from
DD-MM-YYYY 00:00:00 to DD+1-MM-YYYY 00:00:00 in 6 minute increments, and
then join the temporary table on the Progress table, which stores the
progress of the jobs that people are doing in the current version of the
timesheet app.

Example output of the Progress table is as follows:

------------------------------
[JobNum] [TimeStart] [TimeEnd] [Person]
0308-006 2003-09-02 09:00:00.000 2003-09-02 11:42:00.000 AW
0308-013 2003-08-27 14:36:00.000 2003-08-27 18:00:00.000 JW
0308-033 2003-08-28 08:00:00.000 2003-08-28 12:00:00.000 AW
0308-034 2003-09-01 17:24:00.000 2003-09-01 18:00:00.000 AW
0308-030 2003-09-03 14:00:00.000 2003-09-03 17:06:00.000 JW
0308-005 2003-09-04 08:24:00.000 2003-09-04 09:18:00.000 JW
0309-012 2003-09-04 11:54:00.000 2003-09-04 16:36:00.000 AK
0308-033 2003-09-05 13:12:00.000 2003-09-05 18:06:00.000 JW
------------------------------

The temporary table would then be joined, something like:

SELECT #FreeBusy.FBTime, COUNT(TimeMgrProgress.JobNum) As Busy
FROM #FreeBusy LEFT OUTER JOIN TimeMgrProgress
ON TimeMgrProgress.TimeStart <= FreeBusy.FBTime
AND TimeMgrProgress.TimeEnd >= FreeBusy.FBTime
GROUP BY FreeBusy.FBTime

If the "Busy" value returned is greater than 0, the person was busy during
that period, otherwise, they were not.

So, here comes my request for doing this the .NET way, using ADO.NET
DataTables. I have manually created a DataTable called "FreeBusy" in my
DataSet, and populated it with time values ranging from midnight today to
midnight tomorrow, in 6 minute increments - the ADO.NET equivalent of my
temporary table #FreeBusy:

------------------------------
[FBTime]
09-SEP-2003 00:00
09-SEP-2003 00:06
09-SEP-2003 00:12
09-SEP-2003 00:18
09-SEP-2003 00:24
...
09-SEP-2003 23:36
09-SEP-2003 23:42
09-SEP-2003 23:48
09-SEP-2003 23:54
10-SEP-2003 00:00
------------------------------

The question is, where do I go from here? Is it quicker to use the ADO.NET
methods, or will it be just as quick to use the stored procedure I've
already planned? As mentioned, I have very little ADO.NET experience, but
would really like to get under the hood and find out some more.

Many thanks to anyone who takes the time to reply.

Cheers,
Anthony.

 

[Anthony Williams]

Bill,

Sorry to sound like someone who hasn't got a clue (well... not -strictly-
true...) but I've not really gotten anywhere with the ADO.NET relations
problem, and I've still not been able to find anything that gives me a
helpful nudge (or kick in the back) in the right direction.

I've got a stored procedure ready to roll, but would like to evaluate the
..NET way with ADO.NET before I make my final decision on the method to use
in this respect.

Many thanks for your time,
Anth

Anthony:

Think of it this way.. you are free to create a schema locally that may
exactly match the DB, or it may look nothing like it. You can create the
equivalent of your temp table just by writing a query and assigning a
DataTable to it. Typically, you don't want to do the join server side but
ignore that for now. Check out this MSDN link, It does a pretty good job of
explaining how Relations work. I'm at work now, but I'll write some more
later on when I have a few mintues.

Cheers,

Bill

Hi all,

I'm hoping that you guys will be able to help me out here. A friend of mine
has suggested that I look into DataRelations, but to be completely honest,
I'm just starting to use ADO.NET properly, and with the lack of useful
tutorials or walkthroughs on the 'net, and a current short-term lack of
money to allow me to buy a good book on the subject, I'm finding it
difficult getting what you guys would probably call an easy solution.

Anyway, I'm currently creating a timesheet application, the timesheet
component of which is likely to be used in various other applications on our
intranet - service diaries, meeting management etc. - so I'm hoping to get
this right, as I'll be using it again and again.

The basic idea of the application is that a day is split into 240 6-minute
(1/10 hour) slots. In this initial version of the app, I only need to show
whether or not a person was working or not during each 6 minute slot. Using
SQL, I could have created a stored procedure which would create a temporary
table, populate the temporary table with datetime values ranging from
DD-MM-YYYY 00:00:00 to DD+1-MM-YYYY 00:00:00 in 6 minute increments, and
then join the temporary table on the Progress table, which stores the
progress of the jobs that people are doing in the current version of the
timesheet app.

Example output of the Progress table is as follows:

------------------------------
[JobNum] [TimeStart] [TimeEnd] [Person]
0308-006 2003-09-02 09:00:00.000 2003-09-02 11:42:00.000 AW
0308-013 2003-08-27 14:36:00.000 2003-08-27 18:00:00.000 JW
0308-033 2003-08-28 08:00:00.000 2003-08-28 12:00:00.000 AW
0308-034 2003-09-01 17:24:00.000 2003-09-01 18:00:00.000 AW
0308-030 2003-09-03 14:00:00.000 2003-09-03 17:06:00.000 JW
0308-005 2003-09-04 08:24:00.000 2003-09-04 09:18:00.000 JW
0309-012 2003-09-04 11:54:00.000 2003-09-04 16:36:00.000 AK
0308-033 2003-09-05 13:12:00.000 2003-09-05 18:06:00.000 JW
------------------------------

The temporary table would then be joined, something like:

SELECT #FreeBusy.FBTime, COUNT(TimeMgrProgress.JobNum) As Busy
FROM #FreeBusy LEFT OUTER JOIN TimeMgrProgress
ON TimeMgrProgress.TimeStart <= FreeBusy.FBTime
AND TimeMgrProgress.TimeEnd >= FreeBusy.FBTime
GROUP BY FreeBusy.FBTime

If the "Busy" value returned is greater than 0, the person was busy during
that period, otherwise, they were not.

So, here comes my request for doing this the .NET way, using ADO.NET
DataTables. I have manually created a DataTable called "FreeBusy" in my
DataSet, and populated it with time values ranging from midnight today to
midnight tomorrow, in 6 minute increments - the ADO.NET equivalent of my
temporary table #FreeBusy:

------------------------------
[FBTime]
09-SEP-2003 00:00
09-SEP-2003 00:06
09-SEP-2003 00:12
09-SEP-2003 00:18
09-SEP-2003 00:24
...
09-SEP-2003 23:36
09-SEP-2003 23:42
09-SEP-2003 23:48
09-SEP-2003 23:54
10-SEP-2003 00:00
------------------------------

The question is, where do I go from here? Is it quicker to use the ADO.NET
methods, or will it be just as quick to use the stored procedure I've
already planned? As mentioned, I have very little ADO.NET experience, but
would really like to get under the hood and find out some more.

Many thanks to anyone who takes the time to reply.

Cheers,
Anthony.

 

[MeDhanush]

Anthony,
u r right..,ADO.NET's DataTables/DataSets are not grown..,for example
if you wants to do DISTINCT on a column in data table, you have to
write your own routinte, and similarly for GROUP BY.
sticking to sp is recommeneded

Kishore

Bill,

Sorry to sound like someone who hasn't got a clue (well... not -strictly-
true...) but I've not really gotten anywhere with the ADO.NET relations
problem, and I've still not been able to find anything that gives me a
helpful nudge (or kick in the back) in the right direction.

I've got a stored procedure ready to roll, but would like to evaluate the
.NET way with ADO.NET before I make my final decision on the method to use
in this respect.

Many thanks for your time,
Anth

Anthony:

Think of it this way.. you are free to create a schema locally that may
exactly match the DB, or it may look nothing like it. You can create the
equivalent of your temp table just by writing a query and assigning a
DataTable to it. Typically, you don't want to do the join server side but
ignore that for now. Check out this MSDN link, It does a pretty good job of
explaining how Relations work. I'm at work now, but I'll write some more
later on when I have a few mintues.

Cheers,

Bill

Hi all,

I'm hoping that you guys will be able to help me out here. A friend of mine
has suggested that I look into DataRelations, but to be completely honest,
I'm just starting to use ADO.NET properly, and with the lack of useful
tutorials or walkthroughs on the 'net, and a current short-term lack of
money to allow me to buy a good book on the subject, I'm finding it
difficult getting what you guys would probably call an easy solution.

Anyway, I'm currently creating a timesheet application, the timesheet
component of which is likely to be used in various other applications on our
intranet - service diaries, meeting management etc. - so I'm hoping to get
this right, as I'll be using it again and again.

The basic idea of the application is that a day is split into 240 6-minute
(1/10 hour) slots. In this initial version of the app, I only need to show
whether or not a person was working or not during each 6 minute slot. Using
SQL, I could have created a stored procedure which would create a temporary
table, populate the temporary table with datetime values ranging from
DD-MM-YYYY 00:00:00 to DD+1-MM-YYYY 00:00:00 in 6 minute increments, and
then join the temporary table on the Progress table, which stores the
progress of the jobs that people are doing in the current version of the
timesheet app.

Example output of the Progress table is as follows:

------------------------------
[JobNum] [TimeStart] [TimeEnd] [Person]
0308-006 2003-09-02 09:00:00.000 2003-09-02 11:42:00.000 AW
0308-013 2003-08-27 14:36:00.000 2003-08-27 18:00:00.000 JW
0308-033 2003-08-28 08:00:00.000 2003-08-28 12:00:00.000 AW
0308-034 2003-09-01 17:24:00.000 2003-09-01 18:00:00.000 AW
0308-030 2003-09-03 14:00:00.000 2003-09-03 17:06:00.000 JW
0308-005 2003-09-04 08:24:00.000 2003-09-04 09:18:00.000 JW
0309-012 2003-09-04 11:54:00.000 2003-09-04 16:36:00.000 AK
0308-033 2003-09-05 13:12:00.000 2003-09-05 18:06:00.000 JW
------------------------------

The temporary table would then be joined, something like:

SELECT #FreeBusy.FBTime, COUNT(TimeMgrProgress.JobNum) As Busy
FROM #FreeBusy LEFT OUTER JOIN TimeMgrProgress
ON TimeMgrProgress.TimeStart <= FreeBusy.FBTime
AND TimeMgrProgress.TimeEnd >= FreeBusy.FBTime
GROUP BY FreeBusy.FBTime

If the "Busy" value returned is greater than 0, the person was busy during
that period, otherwise, they were not.

So, here comes my request for doing this the .NET way, using ADO.NET
DataTables. I have manually created a DataTable called "FreeBusy" in my
DataSet, and populated it with time values ranging from midnight today to
midnight tomorrow, in 6 minute increments - the ADO.NET equivalent of my
temporary table #FreeBusy:

------------------------------
[FBTime]
09-SEP-2003 00:00
09-SEP-2003 00:06
09-SEP-2003 00:12
09-SEP-2003 00:18
09-SEP-2003 00:24
...
09-SEP-2003 23:36
09-SEP-2003 23:42
09-SEP-2003 23:48
09-SEP-2003 23:54
10-SEP-2003 00:00
------------------------------

The question is, where do I go from here? Is it quicker to use the ADO.NET
methods, or will it be just as quick to use the stored procedure I've
already planned? As mentioned, I have very little ADO.NET experience, but
would really like to get under the hood and find out some more.

Many thanks to anyone who takes the time to reply.

Cheers,
Anthony.