Dataset or datareader

[^MisterJingo^]

Hi all,

I have a form with 4 dropdownlist controls which I populate with data
from DB tables. I have a class with a method which constructs a
dataset, putting each DB table into a dataset table. I then return the
DS and bind the tables to the relevant controls.
I've been reading that DataReaders are much more efficient than
DS's for getting data from the database.
So would it better to have 4 methods, each returning a dataReader, or
one method populating a dataset and returning that?

Any opinions would be appreciated.

 

[jhcorey]

I think most of the examples you'll find will use datasets.
datareaders require some care in that you have to explicitly close them
to prevent leaving an open connection (or at least it used to be this
way). In any event I think any efficiency gain will probably be
trivial compared to other factors that might affect the performance of
your page.

Jim

 

[sloan]

The "middle ground" approach would be to use a IDataReader to populate some
kind of collection.
In 1.1, its an object (like Employee), and a collection (EmployeeCollection
: CollectionBase)

In 2.0, its an object (like Employee) and a new List<Employee> ( a generic )



I have a complete downloadable example at:

http://spaces.msn.com/sholliday/ 5/24/2006 entry

 

[Kevin Spencer]

It all depends on what you want to do with the data. From your description,
you are not going to change any of the data in the database that is being
used to populate the DropDownList Controls. If that is the case, you would
certainly gain some performance by initializing them using a DataReader
instead of a DataSet. It could be as simple as running a query or Stored
Procedure against your database, and using the DataReader to populate an
Array or Collection, which you can then use to bind to the DropDownList
Control.

--
HTH,

Kevin Spencer
Microsoft MVP
Professional Development Numbskull

Nyuck nyuck nyuck

 

[Mark Rae]

Any opinions would be appreciated.

You'll probably get several opinions on this one - it has been debated since
v1.0 pre-release.

If the following criteria are all met, I use a DataReader:

a) I need to use the data once and once only

b) I do not need to change the data in this operation

c) I do not need to know how many records will be returned

d) I'm not using the data to bind to a DataGrid / GridView with paging
and/or sorting enabled

For everything else, I use a DataSet:

 

[^MisterJingo^]

You'll probably get several opinions on this one - it has been debated since
v1.0 pre-release.

If the following criteria are all met, I use a DataReader:

a) I need to use the data once and once only

b) I do not need to change the data in this operation

c) I do not need to know how many records will be returned

d) I'm not using the data to bind to a DataGrid / GridView with paging
and/or sorting enabled

For everything else, I use a DataSet:

Thanks for the replies all. I seem to have a problem when using a
datareader in this context. I have the following function:

public MySqlDataReader populateHoursList()
{
MySqlConnection mySqlConn = new
MySqlConnection(ConfigurationManager.ConnectionStrings["MySQL"].ConnectionString);
MySqlCommand myCommand = new MySqlCommand("SELECT * FROM hours",
mySqlConn);
MySqlDataReader myDataReader;
try
{
mySqlConn.Open();
myDataReader =
myCommand.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
}
catch (Exception ex)
{
HttpContext.Current.Response.Write(ex.ToString());
}


return myDataReader;
}

And then in the aspx file:
Journal myJournal = new Journal();


ddlHours.DataSource = myJournal.populateHoursList();
ddlHours.DataTextField = "text";
ddlHours.DataValueField = "idhours";
ddlHours.DataBind();


This woks fine. But when I check the SQL server, every request and
refresh causes a new connection to be created. So If I reload the page
5 times there will be 5 connections. If i use a dataset to do the
above, only one connection is ever used no mater how many times I
refresh. So the above code seems not to be closing the datareader. How
do I close the reader after it is bound?

Thanks.

 

[^MisterJingo^]

The "middle ground" approach would be to use a IDataReader to populate some
kind of collection.
In 1.1, its an object (like Employee), and a collection (EmployeeCollection
: CollectionBase)

In 2.0, its an object (like Employee) and a new List<Employee> ( a generic )



I have a complete downloadable example at:

http://spaces.msn.com/sholliday/ 5/24/2006 entry

Hi Sloan,

I've implemented something like this. I have a class representing the
page and store the ddl info in List<> objects in this class.
On page load the an instance of the class is created and the List<>
objects are populated using datareaders. The lists are bound to the
ddl, accessed using properties.
Is there anything with this model? I was wondering about any potential
performance worries when storing list<> objects full of data.

Any help would be appreciated.

 

[Mark Rae]

How do I close the reader after it is bound?

MySqlDataReader myDataReader = null;
try
{
myDataReader = myJournal.populateHoursList();
ddlHours.DataSource = myDataReader;
ddlHours.DataTextField = "text";
ddlHours.DataValueField = "idhours";
ddlHours.DataBind();
}
catch (Exception ex)
{
HttpContext.Current.Response.Write(ex.ToString());
}
finally
{
if(!myDataReader.IsClosed)
{
myDataReader.Close();
}
myDataReader = null;
}

 

[^MisterJingo^]

MySqlDataReader myDataReader = null;
try
{
myDataReader = myJournal.populateHoursList();
ddlHours.DataSource = myDataReader;
ddlHours.DataTextField = "text";
ddlHours.DataValueField = "idhours";
ddlHours.DataBind();
}
catch (Exception ex)
{
HttpContext.Current.Response.Write(ex.ToString());
}
finally
{
if(!myDataReader.IsClosed)
{
myDataReader.Close();
}
myDataReader = null;
}

Hi Mark,

When I try this the connection is still remaining open. I think the
problem stems from opening the connection in the called function, but
then the connection object is out of scope to close the connection.
I could simply put all this code in the code-behind file. But I'd
really like another layer to seperate such things from the aspx pages.
I have read that its a abd idea to return datareaders from functions.
is this true?

Thanks.

 

[Mark Rae]

When I try this the connection is still remaining open. I think the
problem stems from opening the connection in the called function, but
then the connection object is out of scope to close the connection.

I guess it could be that, depending on how you call it etc...

I have read that its a abd idea to return datareaders from functions.
is this true?

Not at all, so long as it's done correctly. Take a look at the Microsoft DAL
for a "best practice" example...
http://aspnet.4guysfromrolla.com/articles/070203-1.aspx

 

[sloan]

If you're doing a good tiered approach, then...
You presentation layer would know NOTHING about what a IDataReader is.

You business layer will call the Data layer.
The datalayer will return an IDataReader, and the biz layer will ~use the
IDataReader, and then get rid of it.
the business layer will return objects, and collections to the presentation
layer.

...

I think you need a combination of 2 of my blogs:

http://spaces.msn.com/sholliday/ 6/5/2006 10/24/2005
entries.


I say this because you mention you want to have some layers/tiers.

It is overkill for simple projects, but most times the stuff developers work
on doesn't remain simple.

 

[Guest]

Why return an IDataReader to the BLL from the DAL?
You're also passing its live database connection!
Properly done, the DAL returns arrays of BO to the BLL, imho.