I would like to know if anyone knows how to sort a Dataset table.  I have a
Dataset object that is made up of multiple DataTables from different data
sources.   In some cases, I take two tables and create one, so my client
only knows of the one table.  Works well.  However, I need to sort one of
the 'combined' tables by a date field.  I know I can do this with a
Dataview, however this is not an option.  My table MUST be sorted prior to
sending it over to the web client.

Any ideas on how to do this?

Read about DataView objects  that's why they exist.

----- Original Message -----
From: "greg robinson" <grobinson@d...>
To: "ADO.NET" <ado_dotnet@p...>
Sent: Monday, April 01, 2002 7:04 PM
Subject: [ado_dotnet] Sorting a Dataset Table


> I would like to know if anyone knows how to sort a Dataset table.  I have
a
> Dataset object that is made up of multiple DataTables from different data
> sources.   In some cases, I take two tables and create one, so my client
> only knows of the one table.  Works well.  However, I need to sort one of
> the 'combined' tables by a date field.  I know I can do this with a
> Dataview, however this is not an option.  My table MUST be sorted prior to
> sending it over to the web client.
>
> Any ideas on how to do this?

> Read about DataView objects  that's why they exist.

----- Original Message -----
From: "greg robinson" <grobinson@d...>
To: "ADO.NET" <ado_dotnet@p...>
Sent: Monday, April 01, 2002 7:04 PM
Subject: [ado_dotnet] Sorting a Dataset Table


> I would like to know if anyone knows how to sort a Dataset table.  I have
a
> Dataset object that is made up of multiple DataTables from different data
> sources.   In some cases, I take two tables and create one, so my client
> only knows of the one table.  Works well.  However, I need to sort one of
> the 'combined' tables by a date field.  I know I can do this with a
> Dataview, however this is not an option.  My table MUST be sorted prior 
to
> sending it over to the web client.
>
> Any ideas on how to do this?

Please re-read my post; I have researched the dataview.  I need to 
permanently change the sorting of my table within my Dataset BEFORE I 
return the Dataset to my client.  I cannot work with a dataview from my 
client.  Can the Dataview's sort be captured in the Dataset table?  If 
yes, please explain how. 





> Read about DataView objects  that's why they exist.

----- Original Message -----
From: "greg robinson" <grobinson@d...>
To: "ADO.NET" <ado_dotnet@p...>
Sent: Monday, April 01, 2002 7:04 PM
Subject: [ado_dotnet] Sorting a Dataset Table


> I would like to know if anyone knows how to sort a Dataset table.  I have
a
> Dataset object that is made up of multiple DataTables from different data
> sources.   In some cases, I take two tables and create one, so my client
> only knows of the one table.  Works well.  However, I need to sort one of
> the 'combined' tables by a date field.  I know I can do this with a
> Dataview, however this is not an option.  My table MUST be sorted prior 
to
> sending it over to the web client.
>
> Any ideas on how to do this?

When you fill the dataset can you not sort the data using SQL?

ORDER BY or SORT, something like that

select  ..
from ..
ORDER BY date_field

Im a noob to this so im probably wrong, or theres a better way

James



----- Original Message -----
From: "greg robinson" <grobinson@d...>
To: "ADO.NET" <ado_dotnet@p...>
Sent: Tuesday, April 02, 2002 1:49 PM
Subject: [ado_dotnet] Re: Sorting a Dataset Table


> Please re-read my post; I have researched the dataview.  I need to
> permanently change the sorting of my table within my Dataset BEFORE I
> return the Dataset to my client.  I cannot work with a dataview from my
> client.  Can the Dataview's sort be captured in the Dataset table?  If
> yes, please explain how.
>
>
>
>
>
> > Read about DataView objects  that's why they exist.
>
> ----- Original Message -----
> From: "greg robinson" <grobinson@d...>
> To: "ADO.NET" <ado_dotnet@p...>
> Sent: Monday, April 01, 2002 7:04 PM
> Subject: [ado_dotnet] Sorting a Dataset Table
>
>
> > I would like to know if anyone knows how to sort a Dataset table.  I
have
> a
> > Dataset object that is made up of multiple DataTables from different
data
> > sources.   In some cases, I take two tables and create one, so my client
> > only knows of the one table.  Works well.  However, I need to sort one
of
> > the 'combined' tables by a date field.  I know I can do this with a
> > Dataview, however this is not an option.  My table MUST be sorted prior
> to
> > sending it over to the web client.
> >
> > Any ideas on how to do this?
>
>

James, thanks for the input. We are actually already doing this.  Problem 
is we are pulling data from 2 different data sources (AS400 and SQL 
Server).  This data is sorted by Date, but we have to merge this data 
together.  We take the two tables and build one that represents all the 
data (using one of the 2 tables, then we remove the one we no longer 
need).  Now, with the one table in our Dataset, we need to sort a Date 
column 'again', and now we do not have the benefit of using SQL.  Tricky.  
This is what I cannot figure out how to do.  


> When you fill the dataset can you not sort the data using SQL?

ORDER BY or SORT, something like that

select  ..
from ..
ORDER BY date_field

Im a noob to this so im probably wrong, or theres a better way

James



----- Original Message -----
From: "greg robinson" <grobinson@d...>
To: "ADO.NET" <ado_dotnet@p...>
Sent: Tuesday, April 02, 2002 1:49 PM
Subject: [ado_dotnet] Re: Sorting a Dataset Table


> Please re-read my post; I have researched the dataview.  I need to
> permanently change the sorting of my table within my Dataset BEFORE I
> return the Dataset to my client.  I cannot work with a dataview from my
> client.  Can the Dataview's sort be captured in the Dataset table?  If
> yes, please explain how.
>
>
>
>
>
> > Read about DataView objects  that's why they exist.
>
> ----- Original Message -----
> From: "greg robinson" <grobinson@d...>
> To: "ADO.NET" <ado_dotnet@p...>
> Sent: Monday, April 01, 2002 7:04 PM
> Subject: [ado_dotnet] Sorting a Dataset Table
>
>
> > I would like to know if anyone knows how to sort a Dataset table.  I
have
> a
> > Dataset object that is made up of multiple DataTables from different
data
> > sources.   In some cases, I take two tables and create one, so my 
client
> > only knows of the one table.  Works well.  However, I need to sort one
of
> > the 'combined' tables by a date field.  I know I can do this with a
> > Dataview, however this is not an option.  My table MUST be sorted prior
> to
> > sending it over to the web client.
> >
> > Any ideas on how to do this?
>
>

Consider this.  I've always looked upon sorting as a display issue.  In
that light it's the job of the user interface to sort the data, not the
dataset.  so if you use a grid to display the dataset, you don't have to
be concerned with the order of the dataset, just sort the grid.

Steve Brownell, MCP
AllMeds, Inc.

-----Original Message-----
From: greg robinson [mailto:grobinson@d...]
Sent: Tuesday, April 02, 2002 12:12 PM
To: ADO.NET
Subject: [ado_dotnet] Re: Sorting a Dataset Table


James, thanks for the input. We are actually already doing this.
Problem
is we are pulling data from 2 different data sources (AS400 and SQL
Server).  This data is sorted by Date, but we have to merge this data
together.  We take the two tables and build one that represents all the
data (using one of the 2 tables, then we remove the one we no longer
need).  Now, with the one table in our Dataset, we need to sort a Date
column 'again', and now we do not have the benefit of using SQL.
Tricky. 
This is what I cannot figure out how to do. 


> When you fill the dataset can you not sort the data using SQL?

ORDER BY or SORT, something like that

select  ..
from ..
ORDER BY date_field

Im a noob to this so im probably wrong, or theres a better way

James



----- Original Message -----
From: "greg robinson" <grobinson@d...>
To: "ADO.NET" <ado_dotnet@p...>
Sent: Tuesday, April 02, 2002 1:49 PM
Subject: [ado_dotnet] Re: Sorting a Dataset Table


> Please re-read my post; I have researched the dataview.  I need to
> permanently change the sorting of my table within my Dataset BEFORE I
> return the Dataset to my client.  I cannot work with a dataview from
> my client.  Can the Dataview's sort be captured in the Dataset table?

> If yes, please explain how.
>
>
>
>
>
> > Read about DataView objects  that's why they exist.
>
> ----- Original Message -----
> From: "greg robinson" <grobinson@d...>
> To: "ADO.NET" <ado_dotnet@p...>
> Sent: Monday, April 01, 2002 7:04 PM
> Subject: [ado_dotnet] Sorting a Dataset Table
>
>
> > I would like to know if anyone knows how to sort a Dataset table.  I
have
> a
> > Dataset object that is made up of multiple DataTables from different
data
> > sources.   In some cases, I take two tables and create one, so my
client
> > only knows of the one table.  Works well.  However, I need to sort
> > one
of
> > the 'combined' tables by a date field.  I know I can do this with a
> > Dataview, however this is not an option.  My table MUST be sorted
> > prior
> to
> > sending it over to the web client.
> >
> > Any ideas on how to do this?
> > ---
> > Change your mail options at http://p2p.wrox.com/manager.asp or to
> > unsubscribe send a blank email to
>
> ---
> Change your mail options at http://p2p.wrox.com/manager.asp or to
> unsubscribe send a blank email to
>

I figured this out.  You can use the Select method of a Datatable.  The 
second param on the 3 param overload accepts a sort expression.   The 
return is an array of data rows.  Simply loop through the array and 
rebuild your table with the sorted rows.  Works perfect!  

James, thanks for the input. We are actually already doing this.  Problem 
i> s we are pulling data from 2 different data sources (AS400 and SQL 
S> erver).  This data is sorted by Date, but we have to merge this data 
t> ogether.  We take the two tables and build one that represents all the 
d> ata (using one of the 2 tables, then we remove the one we no longer 
n> eed).  Now, with the one table in our Dataset, we need to sort a Date 
c> olumn 'again', and now we do not have the benefit of using SQL.  
Tricky.  
T> his is what I cannot figure out how to do.  

> 
>>  When you fill the dataset can you not sort the data using SQL?

> ORDER BY or SORT, something like that

> select  ..
f> rom ..
O> RDER BY date_field

> Im a noob to this so im probably wrong, or theres a better way

> James

> 

> ----- Original Message -----
F> rom: "greg robinson" <grobinson@d...>
T> o: "ADO.NET" <ado_dotnet@p...>
S> ent: Tuesday, April 02, 2002 1:49 PM
S> ubject: [ado_dotnet] Re: Sorting a Dataset Table

> 
>>  Please re-read my post; I have researched the dataview.  I need to
>>  permanently change the sorting of my table within my Dataset BEFORE I
>>  return the Dataset to my client.  I cannot work with a dataview from my
>>  client.  Can the Dataview's sort be captured in the Dataset table?  If
>>  yes, please explain how.
>> 
>> 
>> 
>> 
>> 
>>  > Read about DataView objects  that's why they exist.
>> 
>>  ----- Original Message -----
>>  From: "greg robinson" <grobinson@d...>
>>  To: "ADO.NET" <ado_dotnet@p...>
>>  Sent: Monday, April 01, 2002 7:04 PM
>>  Subject: [ado_dotnet] Sorting a Dataset Table
>> 
>> 
>>  > I would like to know if anyone knows how to sort a Dataset table.  I
h> ave
>>  a
>>  > Dataset object that is made up of multiple DataTables from different
d> ata
>>  > sources.   In some cases, I take two tables and create one, so my 
c> lient
>>  > only knows of the one table.  Works well.  However, I need to sort 
one
o> f
>>  > the 'combined' tables by a date field.  I know I can do this with a
>>  > Dataview, however this is not an option.  My table MUST be sorted 
prior
>>  to
>>  > sending it over to the web client.
>>  >
>>  > Any ideas on how to do this?
>> 
%> %email.unsub%%.
>> 

> 

ADO.Net datasets can hold multiple tables.  These tables can come from any
source that can fill the dataset object with data.  You can specify
relationships (like inner or outer joins) among the tables in a single
recordset. You can also sort and filter the each table separately.  The
easiest way to rearrange the data is to use a dataview object which has
methods to sort and filter any one table which resides in the dataset
(datasets work like an in memory database).

So your chore is to load one table from your AS400 into your dataset, then
to load a second table into the same dataset from SQL Server.  If you allow
the DataAdapters to use a closed connection, the DataAdapter will even open
and then close the connection for you each time a table is loaded.  Then you
can define two different dataviews (one for each table in the dataset).
Sort and filter each dataview as needed so you can perform the merge you
need.  The dataset remains unaltered by all this unless you perform an
insert, delete or update on the data (through the dataview objects).  The
dataset which houses the data content of each record in each table (unlike
the dataview which is a filetered and sorted collection of pointers to the
records in the dataset), also maintains state information about each record
(New, Updated, Deleted) which allows the dataset methods to correctly update
the tables back in the original database when you are ready to do that.

There are at least 2 newly published texts which are focused on ADO.NET.
One is: Database Programming With Visual Basic.NET by Carsten Thomsen
published by Apress. Another has a title something like Essential ADO.NET by
Bob Beauchemin to be published May 31, 2002 by Addison Wesley Professional
press.

There are also a variety of more general texts which show example of how to
handle datasets with single tables which show to various degrees how to
handle the multiple table datasets.




----- Original Message -----
From: "greg robinson" <grobinson@d...>
To: "ADO.NET" <ado_dotnet@p...>
Sent: Wednesday, April 03, 2002 6:03 PM
Subject: [ado_dotnet] Re: Sorting a Dataset Table


> I figured this out.  You can use the Select method of a Datatable.  The
> second param on the 3 param overload accepts a sort expression.   The
> return is an array of data rows.  Simply loop through the array and
> rebuild your table with the sorted rows.  Works perfect!
>
> James, thanks for the input. We are actually already doing this.  Problem
> i> s we are pulling data from 2 different data sources (AS400 and SQL
> S> erver).  This data is sorted by Date, but we have to merge this data
> t> ogether.  We take the two tables and build one that represents all the
> d> ata (using one of the 2 tables, then we remove the one we no longer
> n> eed).  Now, with the one table in our Dataset, we need to sort a Date
> c> olumn 'again', and now we do not have the benefit of using SQL.
> Tricky.
> T> his is what I cannot figure out how to do.
>
> >
> >>  When you fill the dataset can you not sort the data using SQL?
>
> > ORDER BY or SORT, something like that
>
> > select  ..
> f> rom ..
> O> RDER BY date_field
>
> > Im a noob to this so im probably wrong, or theres a better way
>
> > James
>
> >
>
> > ----- Original Message -----
> F> rom: "greg robinson" <grobinson@d...>
> T> o: "ADO.NET" <ado_dotnet@p...>
> S> ent: Tuesday, April 02, 2002 1:49 PM
> S> ubject: [ado_dotnet] Re: Sorting a Dataset Table
>
> >
> >>  Please re-read my post; I have researched the dataview.  I need to
> >>  permanently change the sorting of my table within my Dataset BEFORE I
> >>  return the Dataset to my client.  I cannot work with a dataview from
my
> >>  client.  Can the Dataview's sort be captured in the Dataset table?  If
> >>  yes, please explain how.
> >>
> >>
> >>
> >>
> >>
> >>  > Read about DataView objects  that's why they exist.
> >>
> >>  ----- Original Message -----
> >>  From: "greg robinson" <grobinson@d...>
> >>  To: "ADO.NET" <ado_dotnet@p...>
> >>  Sent: Monday, April 01, 2002 7:04 PM
> >>  Subject: [ado_dotnet] Sorting a Dataset Table
> >>
> >>
> >>  > I would like to know if anyone knows how to sort a Dataset table.  I
> h> ave
> >>  a
> >>  > Dataset object that is made up of multiple DataTables from different
> d> ata
> >>  > sources.   In some cases, I take two tables and create one, so my
> c> lient
> >>  > only knows of the one table.  Works well.  However, I need to sort
> one
> o> f
> >>  > the 'combined' tables by a date field.  I know I can do this with a
> >>  > Dataview, however this is not an option.  My table MUST be sorted
> prior
> >>  to
> >>  > sending it over to the web client.
> >>  >
> >>  > Any ideas on how to do this?
> >>
> %> %email.unsub%%.
> >>
>
> >

> ADO.Net datasets can hold multiple tables.  These tables can come from 
any
source that can fill the dataset object with data.  You can specify
relationships (like inner or outer joins) among the tables in a single
recordset. You can also sort and filter the each table separately.  The
easiest way to rearrange the data is to use a dataview object which has
methods to sort and filter any one table which resides in the dataset
(datasets work like an in memory database).

So your chore is to load one table from your AS400 into your dataset, then
to load a second table into the same dataset from SQL Server.  If you allow
the DataAdapters to use a closed connection, the DataAdapter will even open
and then close the connection for you each time a table is loaded.  Then 
you
can define two different dataviews (one for each table in the dataset).
Sort and filter each dataview as needed so you can perform the merge you
need.  The dataset remains unaltered by all this unless you perform an
insert, delete or update on the data (through the dataview objects).  The
dataset which houses the data content of each record in each table (unlike
the dataview which is a filetered and sorted collection of pointers to the
records in the dataset), also maintains state information about each record
(New, Updated, Deleted) which allows the dataset methods to correctly 
update
the tables back in the original database when you are ready to do that.

There are at least 2 newly published texts which are focused on ADO.NET.
One is: Database Programming With Visual Basic.NET by Carsten Thomsen
published by Apress. Another has a title something like Essential ADO.NET 
by
Bob Beauchemin to be published May 31, 2002 by Addison Wesley Professional
press.

There are also a variety of more general texts which show example of how to
handle datasets with single tables which show to various degrees how to
handle the multiple table datasets.




----- Original Message -----
From: "greg robinson" <grobinson@d...>
To: "ADO.NET" <ado_dotnet@p...>
Sent: Wednesday, April 03, 2002 6:03 PM
Subject: [ado_dotnet] Re: Sorting a Dataset Table


> I figured this out.  You can use the Select method of a Datatable.  The
> second param on the 3 param overload accepts a sort expression.   The
> return is an array of data rows.  Simply loop through the array and
> rebuild your table with the sorted rows.  Works perfect!
>
> James, thanks for the input. We are actually already doing this.  Problem
> i> s we are pulling data from 2 different data sources (AS400 and SQL
> S> erver).  This data is sorted by Date, but we have to merge this data
> t> ogether.  We take the two tables and build one that represents all the
> d> ata (using one of the 2 tables, then we remove the one we no longer
> n> eed).  Now, with the one table in our Dataset, we need to sort a Date
> c> olumn 'again', and now we do not have the benefit of using SQL.
> Tricky.
> T> his is what I cannot figure out how to do.
>
> >
> >>  When you fill the dataset can you not sort the data using SQL?
>
> > ORDER BY or SORT, something like that
>
> > select  ..
> f> rom ..
> O> RDER BY date_field
>
> > Im a noob to this so im probably wrong, or theres a better way
>
> > James
>
> >
>
> > ----- Original Message -----
> F> rom: "greg robinson" <grobinson@d...>
> T> o: "ADO.NET" <ado_dotnet@p...>
> S> ent: Tuesday, April 02, 2002 1:49 PM
> S> ubject: [ado_dotnet] Re: Sorting a Dataset Table
>
> >
> >>  Please re-read my post; I have researched the dataview.  I need to
> >>  permanently change the sorting of my table within my Dataset BEFORE I
> >>  return the Dataset to my client.  I cannot work with a dataview from
my
> >>  client.  Can the Dataview's sort be captured in the Dataset table?  
If
> >>  yes, please explain how.
> >>
> >>
> >>
> >>
> >>
> >>  > Read about DataView objects  that's why they exist.
> >>
> >>  ----- Original Message -----
> >>  From: "greg robinson" <grobinson@d...>
> >>  To: "ADO.NET" <ado_dotnet@p...>
> >>  Sent: Monday, April 01, 2002 7:04 PM
> >>  Subject: [ado_dotnet] Sorting a Dataset Table
> >>
> >>
> >>  > I would like to know if anyone knows how to sort a Dataset table.  
I
> h> ave
> >>  a
> >>  > Dataset object that is made up of multiple DataTables from 
different
> d> ata
> >>  > sources.   In some cases, I take two tables and create one, so my
> c> lient
> >>  > only knows of the one table.  Works well.  However, I need to sort
> one
> o> f
> >>  > the 'combined' tables by a date field.  I know I can do this with a
> >>  > Dataview, however this is not an option.  My table MUST be sorted
> prior
> >>  to
> >>  > sending it over to the web client.
> >>  >
> >>  > Any ideas on how to do this?
> >>
> %> %email.unsub%%.
> >>
>
> >

Richard, thanks for the great comments.  One thing I still do not 
understand:  I have read about the Dataview object and how I can sort and 
filter a dataset table with this object.  My question is how to a update 
the dataset table with the datview's sorted table...make sense?  
Everythign I read states to sort with the datview and then bind to the 
datview.  I cannot do this in my application.  I send the entire dataset, 
with approx 14 tables in it, back to an aspx page.  That page binds to 
each table as needed.  I cannot send a dataview back to my aspx page. 

I am also not sure if you are suggesting another way of retrieving and 
merging the data.  I retrieve a datatable from the as400; I retrieve a 
dataset table from SQL Server (stored in one DataSet object).  The data is 
similiar enough so I can take the SQL Server table, loop through the rows 
collection and add the rows to the as400 table.  If course I have to use 
two different dataadapters (SQLClient and OleDb).  Are you suggesting 
another, more effiecient way to do this? 

Once again, thank you for taking the trime to respond!

Greg,

I am following this thread with interest and looking forward to
Richard's reply.  Thanks Richard, for your well written input.  I wanted
to ask, Greg, would you consider replying at the top of the thread
instead of the bottom.  I almost deleted this entry when there was no
new text at the top but was lucky enough to scroll down to see your
questions.  This is good dialog.  Thanks guys.

Kyle

-----Original Message-----
From: greg robinson [mailto:grobinson@d...]
Sent: Thursday, April 04, 2002 8:53 AM
To: ADO.NET
Subject: [ado_dotnet] Re: Sorting a Dataset Table

> ADO.Net datasets can hold multiple tables.  These tables can come from

any
source that can fill the dataset object with data.  You can specify
relationships (like inner or outer joins) among the tables in a single
recordset. You can also sort and filter the each table separately.  The
easiest way to rearrange the data is to use a dataview object which has
methods to sort and filter any one table which resides in the dataset
(datasets work like an in memory database).

So your chore is to load one table from your AS400 into your dataset,
then
to load a second table into the same dataset from SQL Server.  If you
allow
the DataAdapters to use a closed connection, the DataAdapter will even
open
and then close the connection for you each time a table is loaded.  Then

you
can define two different dataviews (one for each table in the dataset).
Sort and filter each dataview as needed so you can perform the merge you
need.  The dataset remains unaltered by all this unless you perform an
insert, delete or update on the data (through the dataview objects).
The
dataset which houses the data content of each record in each table
(unlike
the dataview which is a filetered and sorted collection of pointers to
the
records in the dataset), also maintains state information about each
record
(New, Updated, Deleted) which allows the dataset methods to correctly
update
the tables back in the original database when you are ready to do that.

There are at least 2 newly published texts which are focused on ADO.NET.
One is: Database Programming With Visual Basic.NET by Carsten Thomsen
published by Apress. Another has a title something like Essential
ADO.NET
by
Bob Beauchemin to be published May 31, 2002 by Addison Wesley
Professional
press.

There are also a variety of more general texts which show example of how
to
handle datasets with single tables which show to various degrees how to
handle the multiple table datasets.




----- Original Message -----
From: "greg robinson" <grobinson@d...>
To: "ADO.NET" <ado_dotnet@p...>
Sent: Wednesday, April 03, 2002 6:03 PM
Subject: [ado_dotnet] Re: Sorting a Dataset Table


> I figured this out.  You can use the Select method of a Datatable.
The
> second param on the 3 param overload accepts a sort expression.   The
> return is an array of data rows.  Simply loop through the array and
> rebuild your table with the sorted rows.  Works perfect!
>
> James, thanks for the input. We are actually already doing this.
Problem
> i> s we are pulling data from 2 different data sources (AS400 and SQL
> S> erver).  This data is sorted by Date, but we have to merge this
data
> t> ogether.  We take the two tables and build one that represents all
the
> d> ata (using one of the 2 tables, then we remove the one we no longer
> n> eed).  Now, with the one table in our Dataset, we need to sort a
Date
> c> olumn 'again', and now we do not have the benefit of using SQL.
> Tricky.
> T> his is what I cannot figure out how to do.
>
> >
> >>  When you fill the dataset can you not sort the data using SQL?
>
> > ORDER BY or SORT, something like that
>
> > select  ..
> f> rom ..
> O> RDER BY date_field
>
> > Im a noob to this so im probably wrong, or theres a better way
>
> > James
>
> >
>
> > ----- Original Message -----
> F> rom: "greg robinson" <grobinson@d...>
> T> o: "ADO.NET" <ado_dotnet@p...>
> S> ent: Tuesday, April 02, 2002 1:49 PM
> S> ubject: [ado_dotnet] Re: Sorting a Dataset Table
>
> >
> >>  Please re-read my post; I have researched the dataview.  I need to
> >>  permanently change the sorting of my table within my Dataset
BEFORE I
> >>  return the Dataset to my client.  I cannot work with a dataview
from
my
> >>  client.  Can the Dataview's sort be captured in the Dataset table?

If
> >>  yes, please explain how.
> >>
> >>
> >>
> >>
> >>
> >>  > Read about DataView objects  that's why they exist.
> >>
> >>  ----- Original Message -----
> >>  From: "greg robinson" <grobinson@d...>
> >>  To: "ADO.NET" <ado_dotnet@p...>
> >>  Sent: Monday, April 01, 2002 7:04 PM
> >>  Subject: [ado_dotnet] Sorting a Dataset Table
> >>
> >>
> >>  > I would like to know if anyone knows how to sort a Dataset
table. 
I
> h> ave
> >>  a
> >>  > Dataset object that is made up of multiple DataTables from
different
> d> ata
> >>  > sources.   In some cases, I take two tables and create one, so
my
> c> lient
> >>  > only knows of the one table.  Works well.  However, I need to
sort
> one
> o> f
> >>  > the 'combined' tables by a date field.  I know I can do this
with a
> >>  > Dataview, however this is not an option.  My table MUST be
sorted
> prior
> >>  to
> >>  > sending it over to the web client.
> >>  >
> >>  > Any ideas on how to do this?
> >>
> %> %email.unsub%%.
> >>
>
> >

Richard, thanks for the great comments.  One thing I still do not
understand:  I have read about the Dataview object and how I can sort
and
filter a dataset table with this object.  My question is how to a update

the dataset table with the datview's sorted table...make sense? 
Everythign I read states to sort with the datview and then bind to the
datview.  I cannot do this in my application.  I send the entire
dataset,
with approx 14 tables in it, back to an aspx page.  That page binds to
each table as needed.  I cannot send a dataview back to my aspx page.

I am also not sure if you are suggesting another way of retrieving and
merging the data.  I retrieve a datatable from the as400; I retrieve a
dataset table from SQL Server (stored in one DataSet object).  The data
is
similiar enough so I can take the SQL Server table, loop through the
rows
collection and add the rows to the as400 table.  If course I have to use

two different dataadapters (SQLClient and OleDb).  Are you suggesting
another, more effiecient way to do this?

Once again, thank you for taking the trime to respond!

Use a DataView, iterate thru the rows and add them to a new table with the
same schema as the first.
___

Benjamin Morin
Development
ProLaw Software

Subject: Re: Sorting a Dataset Table
From: "greg robinson" <grobinson@d...>
Date: Tue, 2 Apr 2002 13:49:02
X-Message-Number: 5

Please re-read my post; I have researched the dataview.  I need to 
permanently change the sorting of my table within my Dataset BEFORE I 
return the Dataset to my client.  I cannot work with a dataview from my 
client.  Can the Dataview's sort be captured in the Dataset table?  If 
yes, please explain how. 


> Read about DataView objects  that's why they exist.

----- Original Message -----
From: "greg robinson" <grobinson@d...>
To: "ADO.NET" <ado_dotnet@p...>
Sent: Monday, April 01, 2002 7:04 PM
Subject: [ado_dotnet] Sorting a Dataset Table

> I would like to know if anyone knows how to sort a Dataset table.  I 
> have
a
> Dataset object that is made up of multiple DataTables from different data
> sources.   In some cases, I take two tables and create one, so my client
> only knows of the one table.  Works well.  However, I need to sort one 
> of the 'combined' tables by a date field.  I know I can do this with a 
> Dataview, however this is not an option.  My table MUST be sorted 
> prior
to
> sending it over to the web client.
>
> Any ideas on how to do this?

Is this a more efficient way of doing this as this is pretty much what I 
am doing now, except without the DataView.  I am using the Select method 
on the Datatable and looping through the returned array of rows, adding 
them to a temp table, removing the old table and adding the temp table.  

> Use a DataView, iterate thru the rows and add them to a new table with 
the
same schema as the first.
___

Benjamin Morin
Development
ProLaw Software

Subject: Re: Sorting a Dataset Table
From: "greg robinson" <grobinson@d...>
Date: Tue, 2 Apr 2002 13:49:02
X-Message-Number: 5

Please re-read my post; I have researched the dataview.  I need to 
permanently change the sorting of my table within my Dataset BEFORE I 
return the Dataset to my client.  I cannot work with a dataview from my 
client.  Can the Dataview's sort be captured in the Dataset table?  If 
yes, please explain how. 


> Read about DataView objects  that's why they exist.

----- Original Message -----
From: "greg robinson" <grobinson@d...>
To: "ADO.NET" <ado_dotnet@p...>
Sent: Monday, April 01, 2002 7:04 PM
Subject: [ado_dotnet] Sorting a Dataset Table

> I would like to know if anyone knows how to sort a Dataset table.  I 
> have
a
> Dataset object that is made up of multiple DataTables from different data
> sources.   In some cases, I take two tables and create one, so my client
> only knows of the one table.  Works well.  However, I need to sort one 
> of the 'combined' tables by a date field.  I know I can do this with a 
> Dataview, however this is not an option.  My table MUST be sorted 
> prior
to
> sending it over to the web client.
>
> Any ideas on how to do this?

Please see latest respnse at end of text.

----- Original Message -----
From: "greg robinson" <grobinson@d...>
To: "ADO.NET" <ado_dotnet@p...>
Sent: Thursday, April 04, 2002 5:52 AM
Subject: [ado_dotnet] Re: Sorting a Dataset Table


> > ADO.Net datasets can hold multiple tables.  These tables can come from
> any
> source that can fill the dataset object with data.  You can specify
> relationships (like inner or outer joins) among the tables in a single
> recordset. You can also sort and filter the each table separately.  The
> easiest way to rearrange the data is to use a dataview object which has
> methods to sort and filter any one table which resides in the dataset
> (datasets work like an in memory database).
>
> So your chore is to load one table from your AS400 into your dataset, then
> to load a second table into the same dataset from SQL Server.  If you
allow
> the DataAdapters to use a closed connection, the DataAdapter will even
open
> and then close the connection for you each time a table is loaded.  Then
> you
> can define two different dataviews (one for each table in the dataset).
> Sort and filter each dataview as needed so you can perform the merge you
> need.  The dataset remains unaltered by all this unless you perform an
> insert, delete or update on the data (through the dataview objects).  The
> dataset which houses the data content of each record in each table (unlike
> the dataview which is a filetered and sorted collection of pointers to the
> records in the dataset), also maintains state information about each
record
> (New, Updated, Deleted) which allows the dataset methods to correctly
> update
> the tables back in the original database when you are ready to do that.
>
> There are at least 2 newly published texts which are focused on ADO.NET.
> One is: Database Programming With Visual Basic.NET by Carsten Thomsen
> published by Apress. Another has a title something like Essential ADO.NET
> by
> Bob Beauchemin to be published May 31, 2002 by Addison Wesley Professional
> press.
>
> There are also a variety of more general texts which show example of how
to
> handle datasets with single tables which show to various degrees how to
> handle the multiple table datasets.
>
>
>
>
> ----- Original Message -----
> From: "greg robinson" <grobinson@d...>
> To: "ADO.NET" <ado_dotnet@p...>
> Sent: Wednesday, April 03, 2002 6:03 PM
> Subject: [ado_dotnet] Re: Sorting a Dataset Table
>
>
> > I figured this out.  You can use the Select method of a Datatable.  The
> > second param on the 3 param overload accepts a sort expression.   The
> > return is an array of data rows.  Simply loop through the array and
> > rebuild your table with the sorted rows.  Works perfect!
> >
> > James, thanks for the input. We are actually already doing this.
Problem
> > i> s we are pulling data from 2 different data sources (AS400 and SQL
> > S> erver).  This data is sorted by Date, but we have to merge this data
> > t> ogether.  We take the two tables and build one that represents all
the
> > d> ata (using one of the 2 tables, then we remove the one we no longer
> > n> eed).  Now, with the one table in our Dataset, we need to sort a Date
> > c> olumn 'again', and now we do not have the benefit of using SQL.
> > Tricky.
> > T> his is what I cannot figure out how to do.
> >
> > >
> > >>  When you fill the dataset can you not sort the data using SQL?
> >
> > > ORDER BY or SORT, something like that
> >
> > > select  ..
> > f> rom ..
> > O> RDER BY date_field
> >
> > > Im a noob to this so im probably wrong, or theres a better way
> >
> > > James
> >
> > >
> >
> > > ----- Original Message -----
> > F> rom: "greg robinson" <grobinson@d...>
> > T> o: "ADO.NET" <ado_dotnet@p...>
> > S> ent: Tuesday, April 02, 2002 1:49 PM
> > S> ubject: [ado_dotnet] Re: Sorting a Dataset Table
> >
> > >
> > >>  Please re-read my post; I have researched the dataview.  I need to
> > >>  permanently change the sorting of my table within my Dataset BEFORE
I
> > >>  return the Dataset to my client.  I cannot work with a dataview from
> my
> > >>  client.  Can the Dataview's sort be captured in the Dataset table?
> If
> > >>  yes, please explain how.
> > >>
> > >>
> > >>
> > >>
> > >>
> > >>  > Read about DataView objects  that's why they exist.
> > >>
> > >>  ----- Original Message -----
> > >>  From: "greg robinson" <grobinson@d...>
> > >>  To: "ADO.NET" <ado_dotnet@p...>
> > >>  Sent: Monday, April 01, 2002 7:04 PM
> > >>  Subject: [ado_dotnet] Sorting a Dataset Table
> > >>
> > >>
> > >>  > I would like to know if anyone knows how to sort a Dataset table.
> I
> > h> ave
> > >>  a
> > >>  > Dataset object that is made up of multiple DataTables from
> different
> > d> ata
> > >>  > sources.   In some cases, I take two tables and create one, so my
> > c> lient
> > >>  > only knows of the one table.  Works well.  However, I need to sort
> > one
> > o> f
> > >>  > the 'combined' tables by a date field.  I know I can do this with
a
> > >>  > Dataview, however this is not an option.  My table MUST be sorted
> > prior
> > >>  to
> > >>  > sending it over to the web client.
> > >>  >
> > >>  > Any ideas on how to do this?
> > >>
> > %> %email.unsub%%.
> > >>
> >
> > >
>
> Richard, thanks for the great comments.  One thing I still do not
> understand:  I have read about the Dataview object and how I can sort and
> filter a dataset table with this object.  My question is how to a update
> the dataset table with the datview's sorted table...make sense?
> Everythign I read states to sort with the datview and then bind to the
> datview.  I cannot do this in my application.  I send the entire dataset,
> with approx 14 tables in it, back to an aspx page.  That page binds to
> each table as needed.  I cannot send a dataview back to my aspx page.
>
> I am also not sure if you are suggesting another way of retrieving and
> merging the data.  I retrieve a datatable from the as400; I retrieve a
> dataset table from SQL Server (stored in one DataSet object).  The data is
> similiar enough so I can take the SQL Server table, loop through the rows
> collection and add the rows to the as400 table.  If course I have to use
> two different dataadapters (SQLClient and OleDb).  Are you suggesting
> another, more effiecient way to do this?
>
> Once again, thank you for taking the trime to respond!

Because the dataview is nothing more than a collection of pointers to the
data itself, when you think you are updating the dataview, you are infact
updating the dataset.  You can add rows to the dataview which contains the
AS400 table (as you described) and then update the AS400 database using data
in the dataset and the functinality in the AS400 specific data adapter.  The
data adapter will need appropriate SQL statments to perform these updates.
You can supply them or use can have the SQLBuilder try to compose them for
you (but if so, you better check they are appropriate for the AS400
database...). Then invoke the Update method.

There are other variations of this.  For example, if all the action is
simply to insert new records in the AS400 database, you might want to
compose update SQL script as you scan through the data looking for missing
records in the AS400 database, then (if the AS400 allows multiple SQL
statement script) submit the action query t the AS400 when you done
scanning.

There is also a tick you can use to find missing rows.  Add an extra field
to the AS400 select statement and assign it a constant value (say 'A').
Define a relation to link the two tables and in the definition allow left
joins where the SQL table is one that can have records not in the AS400
table.  Set a filter  on the a dataview object so that the value of this
extra field coming from the AS400 table is null (I have not tried this exact
senario, you may need to concatenate a null string to the field and test
against a null string). You can do something similar using any other field
in the AS400 Table provided that field is always defined as something
different than the default value for the field.  This filter should filter
out all the records in common, allowing you to focus one just the records
you need to add into the AS400 database.  The resulting scan loop logic is a
lot simplier than stepping through both tables.....

I appreciate your interest.  AND, I thank you for pointing me to the end of
text -- I almost tossed out Greg's response....

----- Original Message -----
From: "Kyle" <Kyle@T...>
To: "ADO.NET" <ado_dotnet@p...>
Sent: Thursday, April 04, 2002 7:23 AM
Subject: [ado_dotnet] Re: Sorting a Dataset Table


Greg,

I am following this thread with interest and looking forward to
Richard's reply.  Thanks Richard, for your well written input.  I wanted
to ask, Greg, would you consider replying at the top of the thread
instead of the bottom.  I almost deleted this entry when there was no
new text at the top but was lucky enough to scroll down to see your
questions.  This is good dialog.  Thanks guys.

Kyle

-----Original Message-----
From: greg robinson [mailto:grobinson@d...]
Sent: Thursday, April 04, 2002 8:53 AM
To: ADO.NET
Subject: [ado_dotnet] Re: Sorting a Dataset Table

> ADO.Net datasets can hold multiple tables.  These tables can come from

any
source that can fill the dataset object with data.  You can specify
relationships (like inner or outer joins) among the tables in a single
recordset. You can also sort and filter the each table separately.  The
easiest way to rearrange the data is to use a dataview object which has
methods to sort and filter any one table which resides in the dataset
(datasets work like an in memory database).

So your chore is to load one table from your AS400 into your dataset,
then
to load a second table into the same dataset from SQL Server.  If you
allow
the DataAdapters to use a closed connection, the DataAdapter will even
open
and then close the connection for you each time a table is loaded.  Then

you
can define two different dataviews (one for each table in the dataset).
Sort and filter each dataview as needed so you can perform the merge you
need.  The dataset remains unaltered by all this unless you perform an
insert, delete or update on the data (through the dataview objects).
The
dataset which houses the data content of each record in each table
(unlike
the dataview which is a filetered and sorted collection of pointers to
the
records in the dataset), also maintains state information about each
record
(New, Updated, Deleted) which allows the dataset methods to correctly
update
the tables back in the original database when you are ready to do that.

There are at least 2 newly published texts which are focused on ADO.NET.
One is: Database Programming With Visual Basic.NET by Carsten Thomsen
published by Apress. Another has a title something like Essential
ADO.NET
by
Bob Beauchemin to be published May 31, 2002 by Addison Wesley
Professional
press.

There are also a variety of more general texts which show example of how
to
handle datasets with single tables which show to various degrees how to
handle the multiple table datasets.




----- Original Message -----
From: "greg robinson" <grobinson@d...>
To: "ADO.NET" <ado_dotnet@p...>
Sent: Wednesday, April 03, 2002 6:03 PM
Subject: [ado_dotnet] Re: Sorting a Dataset Table


> I figured this out.  You can use the Select method of a Datatable.
The
> second param on the 3 param overload accepts a sort expression.   The
> return is an array of data rows.  Simply loop through the array and
> rebuild your table with the sorted rows.  Works perfect!
>
> James, thanks for the input. We are actually already doing this.
Problem
> i> s we are pulling data from 2 different data sources (AS400 and SQL
> S> erver).  This data is sorted by Date, but we have to merge this
data
> t> ogether.  We take the two tables and build one that represents all
the
> d> ata (using one of the 2 tables, then we remove the one we no longer
> n> eed).  Now, with the one table in our Dataset, we need to sort a
Date
> c> olumn 'again', and now we do not have the benefit of using SQL.
> Tricky.
> T> his is what I cannot figure out how to do.
>
> >
> >>  When you fill the dataset can you not sort the data using SQL?
>
> > ORDER BY or SORT, something like that
>
> > select  ..
> f> rom ..
> O> RDER BY date_field
>
> > Im a noob to this so im probably wrong, or theres a better way
>
> > James
>
> >
>
> > ----- Original Message -----
> F> rom: "greg robinson" <grobinson@d...>
> T> o: "ADO.NET" <ado_dotnet@p...>
> S> ent: Tuesday, April 02, 2002 1:49 PM
> S> ubject: [ado_dotnet] Re: Sorting a Dataset Table
>
> >
> >>  Please re-read my post; I have researched the dataview.  I need to
> >>  permanently change the sorting of my table within my Dataset
BEFORE I
> >>  return the Dataset to my client.  I cannot work with a dataview
from
my
> >>  client.  Can the Dataview's sort be captured in the Dataset table?

If
> >>  yes, please explain how.
> >>
> >>
> >>
> >>
> >>
> >>  > Read about DataView objects  that's why they exist.
> >>
> >>  ----- Original Message -----
> >>  From: "greg robinson" <grobinson@d...>
> >>  To: "ADO.NET" <ado_dotnet@p...>
> >>  Sent: Monday, April 01, 2002 7:04 PM
> >>  Subject: [ado_dotnet] Sorting a Dataset Table
> >>
> >>
> >>  > I would like to know if anyone knows how to sort a Dataset
table.
I
> h> ave
> >>  a
> >>  > Dataset object that is made up of multiple DataTables from
different
> d> ata
> >>  > sources.   In some cases, I take two tables and create one, so
my
> c> lient
> >>  > only knows of the one table.  Works well.  However, I need to
sort
> one
> o> f
> >>  > the 'combined' tables by a date field.  I know I can do this
with a
> >>  > Dataview, however this is not an option.  My table MUST be
sorted
> prior
> >>  to
> >>  > sending it over to the web client.
> >>  >
> >>  > Any ideas on how to do this?
> >>
> %> %email.unsub%%.
> >>
>
> >

Richard, thanks for the great comments.  One thing I still do not
understand:  I have read about the Dataview object and how I can sort
and
filter a dataset table with this object.  My question is how to a update

the dataset table with the datview's sorted table...make sense?
Everythign I read states to sort with the datview and then bind to the
datview.  I cannot do this in my application.  I send the entire
dataset,
with approx 14 tables in it, back to an aspx page.  That page binds to
each table as needed.  I cannot send a dataview back to my aspx page.

I am also not sure if you are suggesting another way of retrieving and
merging the data.  I retrieve a datatable from the as400; I retrieve a
dataset table from SQL Server (stored in one DataSet object).  The data
is
similiar enough so I can take the SQL Server table, loop through the
rows
collection and add the rows to the as400 table.  If course I have to use

two different dataadapters (SQLClient and OleDb).  Are you suggesting
another, more effiecient way to do this?

Once again, thank you for taking the trime to respond!

Greg
when you 'combine' your two tables, sort this 'combined' set then then 
write it to the table that you wish to deliver

Regards

Greg Frazer

> I would like to know if anyone knows how to sort a Dataset table.  I 
have a
D> ataset object that is made up of multiple DataTables from different data
s> ources.   In some cases, I take two tables and create one, so my client
o> nly knows of the one table.  Works well.  However, I need to sort one of
t> he 'combined' tables by a date field.  I know I can do this with a
D> ataview, however this is not an option.  My table MUST be sorted prior 
tos> ending it over to the web client.

> Any ideas on how to do this?