asked on

C# DataReader vs DataSet

Hi,

I am writing some code in C# that retrieves data from a microsoft access database. The question I have is when I should use a DataReader or DataSet? As I understand it DataReader is quicker and forward only where as a DataSet allows you more flexibility with your data.

When retriving the data I run one sql query once. It will typically return between 30,000 & 90,000 rows of data with between 4 to 10 fields (depends on the table called). Would this be to much in memory terms for a DataSet to work properly?

Thanks

ASKER CERTIFIED SOLUTION

Naman Goel

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

binaryevo

My suggestion would be to create an object with properties and then use a datareader, itterate through the data creating a new object each time and adding it to a generic list of that object... See below (note check the syntax, i did this from memory):

List<TestObject> myTestObjectList = new List<TestObject>();
while (reader.Read())
{
	TestObject myTestObject = new TestObject();
	myTestObject.Property1 = reader["WebUserID"].ToString();
	myTestObject.Property2 = reader["TestColumn2"].ToString();
	myTestObjectList.Add(myTestObject);
}

Open in new window

I am not a fan of datasets as they are designed to somewhat act like mini in memory databases therefore, by nature making them less efficient. With that being said, be careful not to bloat your objects too much aswell because they can also very easily become inefficient.

mcs26

ASKER

Thanks for the replies. But when it says,

"Use the DataReader in your application if you:

•Are processing a set of results too large to fit into memory."

How do you know or whats the rule of thumb when saying "to large to fit into memory"?

Cheers

Naman Goel

see if you are expecting 1 million records it is not advisable to store those in DataSet for further processing as that will be stored in memory for a while, on the other hand we can use DataReader will read one row at a time and the process it. That will save memory as well as processing time.