Better practice: Dataset.Filter or Requery Database?

  • Thread starter Thread starter arby
  • Start date Start date
A

arby

Hi There.

I having searched the groups/web for awhile looking for some insight
but can't seem to get a solid answer.

I have a DataSet that is loaded once my application starts through a
stored procedure call (basically "Select * FROM..."). Anyways, later
on in the application I want to get a subset of this dataset and was
wondering what the best practice is for achieving this goal.

I figure I have two options, at least:

1) Use the .Select method of the DataSet to filter my cached DataSet
and just return this filtered version.

2) Requery the database, calling a specialized store procedure
(basically "Select * FROM ... WHERE ...").

My gut feel is that 2) is preferred simply because that's what SQL is
built to do. However, I'm relatively new to .NET and since I'd imagine
this is a common goal I was thinking it might be optimized to also
perform such actions, and since it's locallized and in memory maybe
it's better?

Thanks in advance,
Jason
 
Somebody like to use Dataview.RowFilter to get a subset of dataset. They
mentioned DataSet is designed to be connectionless.
 
Arby:

Unless your data is totally volatile and changes very quickly, use a
RowFilter. Remember that an ADO.NET disconnected object like
DataSet/DataTable/DataView all make local copies of the table that the query
is based on (restricted by the fields in your query). You can simply set a
DataView's .RowFilter property to restrict only the data you want which is
simple and takes very little code ....
http://www.knowdotnet.com/articles/dataviewspart2.html

On the other hand, making a trip back to the db requires at least one
additional command object, connection object (and if you are using
disconnected objects like the DataSet/DataTable/DataView) and DataAdapter
object. So what you do if you go back to the database is effectively say "I
have all the data I need right here locally, but it's not in the position(s)
I want it to be in. I can either manipulate it locally to meet my needs, or
I can throw it all out, create a few new objects, put additional stress on
my database, machine and network (the latter only being the case in a
networked environment) just so i can have the same data in a different
format. (Now, if the data changes all of the time and each time you change
your filtering you absolutely need to most recent data and it's worth the
performance hit then requerying is probably your better option - or
employing SQL Server Notification services, but that's a totally different
issue)..

Throw in the fact that your database may not have available connections or a
local computer may be having network problems, and the odds on favorite is
manipulating it locally.

Think about it... If your data isn't totally volatile or if it is but you
are only working with a small subset and it doesn't need up to the second
changes... If you use local methodology. You can pull out your network
cables, take down the server, do your thing locally and no one would ever
know. Then you could get everythign back up a few hours later, and all of
the updates would be fine. On the other hand, if you requery the database
each time, you'd cause your app to crash or behave irregularly, just b/c the
database was temporarily unavailable or someone accidentally kicked a
network cable hard enough to make it come loose.

IMHO, take advantage of all of the wonders ADO.NET provides and deal with
your data locally. And Trust me, your DBA will love you for it, so will
your users.

Bill
 
Thanks guys!

I especially liked your article William, it was quite helpful. Your
specific points to this post make sense too and since I have
relatively static data I will just go forward with the localized
model.

Thanks again,
Jason
 
Back
Top