What Is The Best Way To Get A RecordSet ?

  • Thread starter Thread starter William Ryan eMVP
  • Start date Start date
W

William Ryan eMVP

Tiraman:

Depending on the functionality you need, a datareader may be more costly
over the session. It's certainly faster in a head to head comparison, but
if you need functionaility like Paging and sorting on a datagrid, you'll
probably be better off with a DataTable b/c youl'll have to keep going back
to the db for your data if you use a Reader.

I would highly discourage ever taking an approach that cuts off alternatives
wihthout waying the costs and benefits. There may be other reasons for
avoiding datasets, but simply b/c you think that datareaders are more
efficient isn't one of them.

It really depends on your web site needs. If you have lots of grids and
lists and comboboxes that share the same data and/or need sorting,
datatables are the way to go for most of that. Use cmd.ExecuteScalar for
instance to get back individual values. Use Stored procedures if possible.
Use Output Paramaters to get values back instaed of whole record sets.

You can use DataTable.Rows.Count to get the number of rows returned by a
datatable. You can use an integer to get the records affected by an
ExecuteNonQuery.

For a datareader, if you are using the 1.1 framework, you can use the
..HasRows property to determine if you have rows or not, but you'll have to
go through the reader to get the number of rows in there.

If you have any questions though, please feel free to ask.

Cheers,

Bill
 
i m using the SqlDataReader In order to get a recordset from the SQL .
i don't want to work with data set since my web site have lots of traffic
Click to expand...
..

How about taking the DataReader and populating a DataTable from it. Then
you could cache the DataTable or use OutputCaching. This will generally
result in much better performance than COM InterOp to an ADO.Recordset.

A DataTable exposes Rows and Rows have a Count property to determine how
many rows (records) there are.
 
Hi ,

i m using the SqlDataReader In order to get a recordset from the SQL .

i don't want to work with data set since my web site have lots of traffic .

what is the best way to get recordset ?

how can i get the number of records ?


Best Regards ,

Tiraman :-)
 
Thanks for Bill and Scott's quick responses!

Hi Tiraman,

First of all, I would like to confirm my understanding of your issue. From
your description, I understand that you have two question. The first one is
what is the best way to get a DataReader and the second is how to get the
number of records. If there is any misunderstanding, please feel free to
let me know.

I agree with Bill on the first question. Whether to use a SqlDataReader or
a DataTable depends on the logic of your website. If you need to get the
record count of a resultset, besides walking through the DataReader, you
can also use a SELECT COUNT(*) FROM Table1 statement and return the value
with SqlCommand.ExecuteScalar method.

For more information about SqlCommand.ExecuteScalar, please check the
following link:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/
frlrfSystemDataSqlClientSqlCommandClassExecuteScalarTopic.asp

Here is another KB article for your reference:
http://support.microsoft.com/?id=308352

HTH. If anything is unclear, please feel free to reply to the post.

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."
 
Hi ,

First i would like to 10x to all of you for the response .

Right now i m in the middle of design (moving to .NET) which i need to
choose what to use in each module

so it is important that i will understand the costs & benefits by using
dataset , datareader and so on ...

i understand the way that datareader work in front of the sql server but i
still not sure when to use a Dataset,

so let me ask you few questions ,

1) when i m creating a Dataset where it will be create (in the client memory
or in the server memory ) ?

2) for how long it (dataset) will stay in the memory ?

3) lets say that i have a page that create a dataset and the page is used by
a lots of people ,
does each request for that page will create a new dataset ?

or they will all use the same dataset ?

4) when and how it will synchronized with the SQL Server ?

5) which type of query (select , insert , delete and update) will work
better with dataset or with datareader ?

6) lets say that i have n servers that working in front of some load
balancer and i need to have the same data in all of the servers .
one user is working in front of server number 1 and the other in front
of server number 2 .
now if they are both working in front of the same page which create a
Dataset and they both change some data
how this will be affected ?

Once again , 10x to all of you and i m sure that more questions will be ;-)

Bye
 
1) when i m creating a Dataset where it will be create (in the client
memory
or in the server memory ) ?
Click to expand...

Server memory.
2) for how long it (dataset) will stay in the memory ?
Click to expand...

Only for the duration of the page's processing. It can be retained longer
(between page calls) by storing it in the application cache. Also, the page
output itself can be cached (output caching) for high volume pages where the
data doesn't change from second to second.
3) lets say that i have a page that create a dataset and the page is used by
a lots of people ,
does each request for that page will create a new dataset ?
or they will all use the same dataset ?
Click to expand...

By default, yes. But as described above, there are many ways to enhance the
performance and change the default behavior. If the data doesn't change
from second to second, you could cache the output produced by the first page
call for a period of seconds and on subsequent calls for that page (within
the seond range specified), the page won't re-process at all, it will just
spit out the cached results from earlier. This technique can drastically
reduce the load on the web server.

Also, you could store the DataSet in the Applicaiton Cache and check for its
existance before the page begins to process another DataSet. If it already
exists, then use the one that is there, if not, create a new one.
4) when and how it will synchronized with the SQL Server ?
Click to expand...

You can create a DataSet via a DataAdapter. The DataAdapter is what makes a
local copy of the data for you and the DataAdapter is what reconciles any
changes to the local copy with the original when you call the Update method
of the DataAdapter.
5) which type of query (select , insert , delete and update) will work
better with dataset or with datareader ?
Click to expand...

A DataReader is a CONNECTED, READ-ONLY, FORWARD-ONLY data container. You
can't modify the data in a DataReader at all so that only leavs Select.
6) lets say that i have n servers that working in front of some load
balancer and i need to have the same data in all of the servers .
one user is working in front of server number 1 and the other in front
of server number 2 .
now if they are both working in front of the same page which create a
Dataset and they both change some data
how this will be affected ?
Click to expand...

But you would only have one database, so if one web server has a DataAdapter
that fires its Update method, then the original database will be updated and
the other server will know about it because it is reading from the one
database as well. As for data concurrency issues, which arise, a little
care in how you configure your Update statements will handle that.
 
Thanks Scott !

Tiraman

Scott M. said:
Server memory.


Only for the duration of the page's processing. It can be retained longer
(between page calls) by storing it in the application cache. Also, the page
output itself can be cached (output caching) for high volume pages where the
data doesn't change from second to second.
used

By default, yes. But as described above, there are many ways to enhance the
performance and change the default behavior. If the data doesn't change
from second to second, you could cache the output produced by the first page
call for a period of seconds and on subsequent calls for that page (within
the seond range specified), the page won't re-process at all, it will just
spit out the cached results from earlier. This technique can drastically
reduce the load on the web server.

Also, you could store the DataSet in the Applicaiton Cache and check for its
existance before the page begins to process another DataSet. If it already
exists, then use the one that is there, if not, create a new one.


You can create a DataSet via a DataAdapter. The DataAdapter is what makes a
local copy of the data for you and the DataAdapter is what reconciles any
changes to the local copy with the original when you call the Update method
of the DataAdapter.


A DataReader is a CONNECTED, READ-ONLY, FORWARD-ONLY data container. You
can't modify the data in a DataReader at all so that only leavs Select.


But you would only have one database, so if one web server has a DataAdapter
that fires its Update method, then the original database will be updated and
the other server will know about it because it is reading from the one
database as well. As for data concurrency issues, which arise, a little
care in how you configure your Update statements will handle that.
Click to expand...
 
Hi Tiraman.

It was nice to hear that you have had the problem resolved. Thanks for
sharing your experience with all the people here. If you have any
questions, please feel free to post them in the community.

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."
 
Back
Top