Querying the data in a DataSet in C#

[Dan Koster]

I have three weeks of history for all user accounts stored in a DataSet (dsAccountsHistory). I want to be able to narrow that data down to one specific user, at runtime, without having to make another round-trip to the dadabase. I would love to be able to do it with an SQL command like "select * from dsAccountsHistory where dsAccountsHistory.custId=targetId" but a DataAdapter only lets you connect to a database, not a DataSet. Any suggestions

If you respond with code samples, C# is my perfered language but VB.Net is ok too

Thanks
Dan

[Rob Windsor [MVP]]

Use a DataView which lets you sort, search and filter the contents of a
DataTable.

http://msdn.microsoft.com/library/de...classtopic.asp

--
Rob Windsor [MVP-VB]
G6 Consulting
Toronto, Canada

"Dan Koster" <Da*@LiveComputers.com> wrote in message
news:8F**********************************@microsof t.com...

I have three weeks of history for all user accounts stored in a DataSet (dsAccountsHistory). I want to be able to narrow that data down to one
specific user, at runtime, without having to make another round-trip to the
dadabase. I would love to be able to do it with an SQL command like "select
* from dsAccountsHistory where dsAccountsHistory.custId=targetId" but a
DataAdapter only lets you connect to a database, not a DataSet. Any
suggestions?
If you respond with code samples, C# is my perfered language but VB.Net is ok too.
Thanks,
Dan

[Cor]

Hi Dan,

In my opinion is this a bad design isue to read useless bunches of data from
a SQLServer and spoiling with that the performance from that and the
connections while you only needs the data of one user.

Just my thought,

Cor

[Rob Windsor [MVP]]

Use a DataView which lets you sort, search and filter the contents of a
DataTable.

http://msdn.microsoft.com/library/de...classtopic.asp

--
Rob Windsor [MVP-VB]
G6 Consulting
Toronto, Canada

"Dan Koster" <Da*@LiveComputers.com> wrote in message
news:8F**********************************@microsof t.com...

I have three weeks of history for all user accounts stored in a DataSet (dsAccountsHistory). I want to be able to narrow that data down to one
specific user, at runtime, without having to make another round-trip to the
dadabase. I would love to be able to do it with an SQL command like "select
* from dsAccountsHistory where dsAccountsHistory.custId=targetId" but a
DataAdapter only lets you connect to a database, not a DataSet. Any
suggestions?
If you respond with code samples, C# is my perfered language but VB.Net is ok too.
Thanks,
Dan

[Jon Skeet [C# MVP]]

Cor <no*@non.com> wrote:

In my opinion is this a bad design isue to read useless bunches of data from
a SQLServer and spoiling with that the performance from that and the
connections while you only needs the data of one user.

That assumes that he only *ever* needs the information for the one
user. The way I read the question, the OP already has the information
for all users for other parts of his app, and doesn't want to have to
go back to the database to fetch data he's already got, just for the
purposes of filtering.

I don't think that's bad design at all.

--
Jon Skeet - <sk***@pobox.com>
http://www.pobox.com/~skeet
If replying to the group, please do not mail me too

[Cor]

Hi Dan,

In my opinion is this a bad design isue to read useless bunches of data from
a SQLServer and spoiling with that the performance from that and the
connections while you only needs the data of one user.

Just my thought,

Cor

[Cor]

Hi Jon,

You are free to think that, in my opinion it is using a SQL database as a
papertape.

The only situation I can think of this situation as not bad desing are pda
solutions with a disconnected dataset on it.

Cor

[Jon Skeet [C# MVP]]

Cor <no*@non.com> wrote:

In my opinion is this a bad design isue to read useless bunches of data from
a SQLServer and spoiling with that the performance from that and the
connections while you only needs the data of one user.

That assumes that he only *ever* needs the information for the one
user. The way I read the question, the OP already has the information
for all users for other parts of his app, and doesn't want to have to
go back to the database to fetch data he's already got, just for the
purposes of filtering.

I don't think that's bad design at all.

--
Jon Skeet - <sk***@pobox.com>
http://www.pobox.com/~skeet
If replying to the group, please do not mail me too

[Jon Skeet [C# MVP]]

Cor <no*@non.com> wrote:

You are free to think that, in my opinion it is using a SQL database as a
papertape.

The only situation I can think of this situation as not bad desing are pda
solutions with a disconnected dataset on it.

So if you already had all the data on the desktop, you would still go
back to the database just for filtering purposes? Do you see *any*
value in DataView.RowFilter?

--
Jon Skeet - <sk***@pobox.com>
http://www.pobox.com/~skeet
If replying to the group, please do not mail me too

[Cor]

Hi Jon,

You are free to think that, in my opinion it is using a SQL database as a
papertape.

The only situation I can think of this situation as not bad desing are pda
solutions with a disconnected dataset on it.

Cor

[Jon Skeet [C# MVP]]

Cor <no*@non.com> wrote:

You are free to think that, in my opinion it is using a SQL database as a
papertape.

The only situation I can think of this situation as not bad desing are pda
solutions with a disconnected dataset on it.

So if you already had all the data on the desktop, you would still go
back to the database just for filtering purposes? Do you see *any*
value in DataView.RowFilter?

--
Jon Skeet - <sk***@pobox.com>
http://www.pobox.com/~skeet
If replying to the group, please do not mail me too

[Cor]

Hi Jon,

So if you already had all the data on the desktop, you would still go
back to the database just for filtering purposes? Do you see *any*
value in DataView.RowFilter?

You could have seen in messages from me that the dataview is one of my
favorites.

I could have written the same message as Rob, however when it is to prevent
a "roundtrip" to a SQL server than it is in my opinion not the right use.

This is the start of the message from the OP, "I have three weeks of history
for all user accounts stored in a DataSet"

I hope that you agree with me after reading that line of text?

Cor

[Jon Skeet [C# MVP]]

Cor <no*@non.com> wrote:

So if you already had all the data on the desktop, you would still go
back to the database just for filtering purposes? Do you see *any*
value in DataView.RowFilter?
You could have seen in messages from me that the dataview is one of my
favorites.

I could have written the same message as Rob, however when it is to prevent
a "roundtrip" to a SQL server than it is in my opinion not the right use.

Why? If the roundtrip is entirely unnecessary because you already have
the data for other reasons, why not avoid it and just use the data you
have?
This is the start of the message from the OP, "I have three weeks of history
for all user accounts stored in a DataSet"

I hope that you agree with me after reading that line of text?

No - because it seems entirely reasonable for other parts of the
application to have three weeks of history for all user accounts.

Imagine it's a log viewer - and you can either view all entries, or
view by a particular user. To me, the absolutely obvious way of doing
that is to use a RowFilter when you want to view only log entries for a
particular user, rather than doing a whole extra query each time.

Where is the *benefit* in doing the extra query? As far as I can see it
only saves a small amount of CPU time on the client while it does the
filtering.

--
Jon Skeet - <sk***@pobox.com>
http://www.pobox.com/~skeet
If replying to the group, please do not mail me too

[Cor]

Hi Jon,

So if you already had all the data on the desktop, you would still go
back to the database just for filtering purposes? Do you see *any*
value in DataView.RowFilter?

You could have seen in messages from me that the dataview is one of my
favorites.

I could have written the same message as Rob, however when it is to prevent
a "roundtrip" to a SQL server than it is in my opinion not the right use.

This is the start of the message from the OP, "I have three weeks of history
for all user accounts stored in a DataSet"

I hope that you agree with me after reading that line of text?

Cor

[Cor]

Hi Jon,

Where is the *benefit* in doing the extra query? As far as I can see it
only saves a small amount of CPU time on the client while it does the
filtering.

Do you know something about Internet or other methods of networking.

In dataprocessing it always important to have your performance issues on the
smallest part of the pipe, when there are users and a database involved you
can assume that there are netwerkconnections.

Hugh amount of data will give you a lot of performance problems, one of them
is in a normal ethernet network a collission, however there are hundreds
which you have to avoid.

On the internet a hugh dataset is even worse when you are not sure that all
clients have something as adsl.

To retrieve only the data you needed as much as possible one by one helps
you to avoid those problems (and than I am not even talking about
concurrency because I think that is not important in this situation, however
normally it can be).

Cor

[Jon Skeet [C# MVP]]

Cor <no*@non.com> wrote:

Where is the *benefit* in doing the extra query? As far as I can see it
only saves a small amount of CPU time on the client while it does the
filtering.

Do you know something about Internet or other methods of networking.

In dataprocessing it always important to have your performance issues on the
smallest part of the pipe, when there are users and a database involved you
can assume that there are netwerkconnections.

Hugh amount of data will give you a lot of performance problems, one of them
is in a normal ethernet network a collission, however there are hundreds
which you have to avoid.

On the internet a hugh dataset is even worse when you are not sure that all
clients have something as adsl.

To retrieve only the data you needed as much as possible one by one helps
you to avoid those problems (and than I am not even talking about
concurrency because I think that is not important in this situation, however
normally it can be).

But you're *assuming* that he *doesn't* need all the data elsewhere. He
said he's already got the data in his data set - is it so inconceivable
to you that he might need it in an unfiltered form *as well as* in a
filtered form? Look at the example I gave (which you snipped).

You seem to be making an awful lot of assumptions in order to come up
with the statement that it's a badly designed system.

--
Jon Skeet - <sk***@pobox.com>
http://www.pobox.com/~skeet
If replying to the group, please do not mail me too

[Jon Skeet [C# MVP]]

Cor <no*@non.com> wrote:

So if you already had all the data on the desktop, you would still go
back to the database just for filtering purposes? Do you see *any*
value in DataView.RowFilter?
You could have seen in messages from me that the dataview is one of my
favorites.

I could have written the same message as Rob, however when it is to prevent
a "roundtrip" to a SQL server than it is in my opinion not the right use.

Why? If the roundtrip is entirely unnecessary because you already have
the data for other reasons, why not avoid it and just use the data you
have?
This is the start of the message from the OP, "I have three weeks of history
for all user accounts stored in a DataSet"

I hope that you agree with me after reading that line of text?

No - because it seems entirely reasonable for other parts of the
application to have three weeks of history for all user accounts.

Imagine it's a log viewer - and you can either view all entries, or
view by a particular user. To me, the absolutely obvious way of doing
that is to use a RowFilter when you want to view only log entries for a
particular user, rather than doing a whole extra query each time.

Where is the *benefit* in doing the extra query? As far as I can see it
only saves a small amount of CPU time on the client while it does the
filtering.

--
Jon Skeet - <sk***@pobox.com>
http://www.pobox.com/~skeet
If replying to the group, please do not mail me too

[Cor]

Hi Jon,

You seem to be making an awful lot of assumptions in order to come up
with the statement that it's a badly designed system.

When you knows something more from this subject it is not assumpions, it is
80% knowing with the 20% change that there can be a situation in this that
you are wrong.

Cor

[Jon Skeet [C# MVP]]

Cor <no*@non.com> wrote:

You seem to be making an awful lot of assumptions in order to come up
with the statement that it's a badly designed system.

When you knows something more from this subject it is not assumpions, it is
80% knowing with the 20% change that there can be a situation in this that
you are wrong.

Well, I'm afraid I think we'll have to agree to disagree. I'm unwilling
to make any assumptions that the OP has designed his system badly just
from the one quote of "I have three weeks of history for all user
accounts stored in a DataSet".

--
Jon Skeet - <sk***@pobox.com>
http://www.pobox.com/~skeet
If replying to the group, please do not mail me too

[Cor]

Hi Jon,

Where is the *benefit* in doing the extra query? As far as I can see it
only saves a small amount of CPU time on the client while it does the
filtering.

Do you know something about Internet or other methods of networking.

In dataprocessing it always important to have your performance issues on the
smallest part of the pipe, when there are users and a database involved you
can assume that there are netwerkconnections.

Hugh amount of data will give you a lot of performance problems, one of them
is in a normal ethernet network a collission, however there are hundreds
which you have to avoid.

On the internet a hugh dataset is even worse when you are not sure that all
clients have something as adsl.

To retrieve only the data you needed as much as possible one by one helps
you to avoid those problems (and than I am not even talking about
concurrency because I think that is not important in this situation, however
normally it can be).

Cor

[Jon Skeet [C# MVP]]

Cor <no*@non.com> wrote:

Where is the *benefit* in doing the extra query? As far as I can see it
only saves a small amount of CPU time on the client while it does the
filtering.

Do you know something about Internet or other methods of networking.

In dataprocessing it always important to have your performance issues on the
smallest part of the pipe, when there are users and a database involved you
can assume that there are netwerkconnections.

Hugh amount of data will give you a lot of performance problems, one of them
is in a normal ethernet network a collission, however there are hundreds
which you have to avoid.

On the internet a hugh dataset is even worse when you are not sure that all
clients have something as adsl.

To retrieve only the data you needed as much as possible one by one helps
you to avoid those problems (and than I am not even talking about
concurrency because I think that is not important in this situation, however
normally it can be).

But you're *assuming* that he *doesn't* need all the data elsewhere. He
said he's already got the data in his data set - is it so inconceivable
to you that he might need it in an unfiltered form *as well as* in a
filtered form? Look at the example I gave (which you snipped).

You seem to be making an awful lot of assumptions in order to come up
with the statement that it's a badly designed system.

--
Jon Skeet - <sk***@pobox.com>
http://www.pobox.com/~skeet
If replying to the group, please do not mail me too

[Cor]

> Well, I'm afraid I think we'll have to agree to disagree. I'm unwilling

to make any assumptions that the OP has designed his system badly just
from the one quote of "I have three weeks of history for all user
accounts stored in a DataSet".

OK but you where in my area now, I will give you next time again a change in
your area.

:-)

Cor

[Cor]

Hi Jon,

You seem to be making an awful lot of assumptions in order to come up
with the statement that it's a badly designed system.

When you knows something more from this subject it is not assumpions, it is
80% knowing with the 20% change that there can be a situation in this that
you are wrong.

Cor

[Jon Skeet [C# MVP]]

Cor <no*@non.com> wrote:

Well, I'm afraid I think we'll have to agree to disagree. I'm unwilling
to make any assumptions that the OP has designed his system badly just
from the one quote of "I have three weeks of history for all user
accounts stored in a DataSet".

OK but you where in my area now, I will give you next time again a change in
your area.

Eh? Sorry, I didn't understand that sentence at all...

--
Jon Skeet - <sk***@pobox.com>
http://www.pobox.com/~skeet
If replying to the group, please do not mail me too

[Jon Skeet [C# MVP]]

Cor <no*@non.com> wrote:

You seem to be making an awful lot of assumptions in order to come up
with the statement that it's a badly designed system.

When you knows something more from this subject it is not assumpions, it is
80% knowing with the 20% change that there can be a situation in this that
you are wrong.

Well, I'm afraid I think we'll have to agree to disagree. I'm unwilling
to make any assumptions that the OP has designed his system badly just
from the one quote of "I have three weeks of history for all user
accounts stored in a DataSet".

--
Jon Skeet - <sk***@pobox.com>
http://www.pobox.com/~skeet
If replying to the group, please do not mail me too

[Cor]

>

Eh? Sorry, I didn't understand that sentence at all...

Let's keep it that way

(was no wrong intention in it)

Cor

[Cor]

> Well, I'm afraid I think we'll have to agree to disagree. I'm unwilling

to make any assumptions that the OP has designed his system badly just
from the one quote of "I have three weeks of history for all user
accounts stored in a DataSet".

OK but you where in my area now, I will give you next time again a change in
your area.

:-)

Cor

[Jon Skeet [C# MVP]]

Cor <no*@non.com> wrote:

Well, I'm afraid I think we'll have to agree to disagree. I'm unwilling
to make any assumptions that the OP has designed his system badly just
from the one quote of "I have three weeks of history for all user
accounts stored in a DataSet".

OK but you where in my area now, I will give you next time again a change in
your area.

Eh? Sorry, I didn't understand that sentence at all...

--
Jon Skeet - <sk***@pobox.com>
http://www.pobox.com/~skeet
If replying to the group, please do not mail me too

[AA]

What is the size of dataset with three weeks of data
Whats the utilization of the network
What is the physical capacity of the network and machines involved
How do you know he didn't get the three weeks of data incrementally and do dataset merges

I could go on but the fact is I agree with John, you don't know his design or enough information to make a judgement call on his design. The only FACT that you know is that he has a dataset in memory.

[Cor]

>

Eh? Sorry, I didn't understand that sentence at all...

Let's keep it that way

(was no wrong intention in it)

Cor

[Cor]

> I could go on but the fact is I agree with John, you don't know his design
or enough information to make a judgement call on his design. The only FACT
that you know is that he has a dataset in memory.

I have read in his message the FACTS that he has a database to which he does
not want to go for a roundtrip, and that he has users for which he does
logging so there is in someway a network,

In am not saying what you would know, so please do that also not about me.

Cor

[Cor]

> Look you said his design is bad. He has not told you his design. You can't
even answer how he got the three weeks of data? How can you critic his
design?

Again you are telling what I can't answer you are guessing a lot AA.

[AA]

What is the size of dataset with three weeks of data
Whats the utilization of the network
What is the physical capacity of the network and machines involved
How do you know he didn't get the three weeks of data incrementally and do dataset merges

I could go on but the fact is I agree with John, you don't know his design or enough information to make a judgement call on his design. The only FACT that you know is that he has a dataset in memory.

[Cor]

> I could go on but the fact is I agree with John, you don't know his design
or enough information to make a judgement call on his design. The only FACT
that you know is that he has a dataset in memory.

I have read in his message the FACTS that he has a database to which he does
not want to go for a roundtrip, and that he has users for which he does
logging so there is in someway a network,

In am not saying what you would know, so please do that also not about me.

Cor

[Cor]

> Look you said his design is bad. He has not told you his design. You can't
even answer how he got the three weeks of data? How can you critic his
design?

Again you are telling what I can't answer you are guessing a lot AA.