468,765 Members | 812 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,765 developers. It's quick & easy.

Querying the data in a DataSet in C#

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
Jul 21 '05 #1
34 18057
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

Jul 21 '05 #2
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
Jul 21 '05 #3
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

Jul 21 '05 #4
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
Jul 21 '05 #5
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
Jul 21 '05 #6
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
Jul 21 '05 #7
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
Jul 21 '05 #8
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
Jul 21 '05 #9
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
Jul 21 '05 #10
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
Jul 21 '05 #11
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

Jul 21 '05 #12
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
Jul 21 '05 #13
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

Jul 21 '05 #14
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

Jul 21 '05 #15
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
Jul 21 '05 #16
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
Jul 21 '05 #17
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
Jul 21 '05 #18
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
Jul 21 '05 #19
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

Jul 21 '05 #20
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
Jul 21 '05 #21
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
Jul 21 '05 #22
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
Jul 21 '05 #23
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
Jul 21 '05 #24
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
Jul 21 '05 #25
Cor
>
Eh? Sorry, I didn't understand that sentence at all...

Let's keep it that way

(was no wrong intention in it)

Cor
Jul 21 '05 #26
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
Jul 21 '05 #27
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
Jul 21 '05 #28
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.
Jul 21 '05 #29
Cor
>
Eh? Sorry, I didn't understand that sentence at all...

Let's keep it that way

(was no wrong intention in it)

Cor
Jul 21 '05 #30
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

Jul 21 '05 #31
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.


Jul 21 '05 #32
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.
Jul 21 '05 #33
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

Jul 21 '05 #34
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.


Jul 21 '05 #35

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

17 posts views Thread by Dan Koster | last post: by
3 posts views Thread by C. Hughes | last post: by
2 posts views Thread by Abhishek Srivastava | last post: by
3 posts views Thread by MDB | last post: by
1 post views Thread by Michel Moreno | last post: by
6 posts views Thread by Fred | last post: by
1 post views Thread by Ryan Ramsey | last post: by
5 posts views Thread by sql_er | last post: by
2 posts views Thread by Steven Cheng [MSFT] | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by Marin | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.