DataReader vs DataSet

  • Thread starter Thread starter A.M
  • Start date Start date
A

A.M

Hi,

For data retriving only operations I always try to use DataReader as much as
possible rather than DataSet because I think DataSet is bulky and takes
alots of resources.
Am i on right track ?

Thanks,
Ali
 
Hi,

If you don't need to store the data in datatable then datareader is
certainly faster.
However, take note that dataadapter.fill method (one that fills datatables)
uses datareaders.
 
datasets are actually pretty lightweight and you will see little performance
difference. datasets are safer then datareaders as the adapter handles the
connection correctly even during errors.

also avoid returning datareaders as you are just asking for a resource leak.
a datareader should only be local to a method, and should always be in using
(c#) or try/catch block

-- bruce (sqlwork.com)
 
I have a utility function like the following code.
Do you think the application uses this function will have link ?
Thanks
Ali



public static System.Data.SqlClient.SqlDataReader ADOExecuteReader(string
strSql, string strConnection)
{
if( ConfigurationSettings.AppSettings["LogSqls"] == "Yes")
{
Append2TextLog("ADOExecuteReader: " + strSql);
}
System.Data.SqlClient.SqlDataReader sqlReader = null;
System.Data.SqlClient.SqlConnection myConnection = new
System.Data.SqlClient.SqlConnection(strConnection);
System.Data.SqlClient.SqlCommand myCommand = new
System.Data.SqlClient.SqlCommand();
try
{
myConnection.Open();
myCommand.Connection = myConnection;
myCommand.CommandText = strSql;
sqlReader =
myCommand.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
}
catch (System.Exception e1 )
{
ErrorHandler(e1);
}
finally
{
//myConnection.Close() ' CommandBehavior.CloseConnection will do that
}
return sqlReader;
}
 
Hi Ali,

Thank you for using MSDN Newsgroup! My name is Kevin, and I will be
assisting you on this issue.

First of all, I would like to confirm my understanding of your issue. From
your description, I understand that you need to know between DataSet and
DataReader, which is better on a data retriving only operation. If there's
anything I misunderstood, please feel free to let me know.

I think this depends on the environment you're working one. Generally
speaking, the DataReader is used in a connected circs while DataSet is used
in disconnected circs. It means that when working on a DataReader, the
Connection to the data source should always be opened. DataReader will
retrieve data from the source once a record with DataReader.Read() method.

However, DataSet is a dump of data in the local memory on client machine.
It puts all the rows in to the DataSet with DataAdapter.Fill() method.
Because an active connection is not required when manipulting on a DataSet,
we can also persist data with DataSet.WriteXml(). DataSet is much more
powerful than DataReader. It can also link parent and child table with
DataRelation objects. Thus makes the DataSet able to maintain hierachical
data.

Also, just as Miha mentioned in his post, the DataAdapter actually calls
DataReader to retrieve data. So calling DataAdapter.Fill() might be much
slower than the ExecuteReader(), because it is "dumping" data to the client
side. However, after the data is get to the client, it will be faster to
manipulate on local data than get data from source each time with
DataReader.Read().

Here's an article about introduction to ADO+(ADO .NET): Data Access
Services for the Microsoft .NET Framework

http://msdn.microsoft.com/msdnmag/issues/1100/adoplus/default.aspx

For information about DataSet class and SqlDataReader class, pleaser refer
to:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/
frlrfSystemDataDataSetClassTopic.asp

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/
frlrfsystemdatasqlclientsqldatareaderclasstopic.asp

For more information, please refer to the following links:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vbcon/html/
vbconfundamentaldataconceptsinvisualstudionet.asp

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/htm
l/cpconoverviewofadonet.asp

I've checked your code in your last post. This is a good function which
returns a SqlDataReader object. Do you have any concerns on it? If so,
please feel free to let me know, and I would like to provide more detailed
information on that.

Does this answer your question? 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."
 
Thanks Kevin for help.

My only concern over the function i mentioned is resource like. Do you see
any resource leak possibility even if programmer user closes and nulls the
returned DataReader ?

Thanks again,
Ali
 
Hi Ali,

There will not be any resource leak if you use the DataReader object in the
correct way. As DataReader is a managed type, all the instances of it are
created as a memory block on Managed Heap. When no reference is pointed to
that block, or the reference is out of scope, that object as considered as
unreferenced by CLR. So it might be collected when the CLR thinks that
there's not enough memory available on managed heap. All the unmanaged
resources referenced by this object will be released by the managed type's
Finalize method. This is called Garbage Collection (GC).

GC is one of the cool features of Microsoft .NET technology. The garbage
collections are performed silently and automatically. You needn't force it
to do so, CLR will decide when to collect garbages.

For more information about Garbage Collection and Automatic Memeory
Management, please refer to Jeffrey Richter's Garbage Collection: Automatic
Memory Management in the Microsoft .NET Framework. Here's a link of this
technical article:

http://msdn.microsoft.com/msdnmag/issues/1100/GCI/default.aspx
(Part I)

http://msdn.microsoft.com/msdnmag/issues/1200/gci2/default.aspx
(Part II)

Jeffrey's Applied .NET Framework Programming is also a good book to learn
this.

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."
 
Ali,

I think you have a _potential_ problem. By returning the data reader outside
the immediate function, you lose control over it. It is hard to know that it
is disposed of correctly, even if the code using it encounters an exception.
If the connection is not closed, it remains open and wastes resources. That
isn't strictly a memory leak, but its a potential source of problems.
Because of that I don't pass data readers out of the method that created
them. You can put hte data into another data store, such as an array using
GetValues and return it very efficiently.

--
Kathleen Dollard
Microsoft MVP
Author "Code Generation in Microsoft .NET"
 
Hi Ali,

As Kathleen says, There won't be a memory leak, but a potential resource
problem. If you lost the reference to the DataReader, the connection will
not be closed until the application ends. Apologize that I didn't fully
catch your meaning in my last post.

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."
 
Yup, while it's possible to pass DataReaders to a calling routine, but if you do you MUST make sure that:
1.. The DataReader is constructed with CommandBehavior.CloseConnection. This lets the receiving code close the DataReader AND close the Connection (which it can't see) at the same time.
2.. Make SURE the receiving routine closes the DataReader when it reaches EOF (Read returns false). This is problematic as there are situations where closing the connection upsets other logic that depends on the Connection remaining open.
So we have a quandary. Don't pass the DataSet to other routines (as Kathleen suggests) or ensure (somehow) that other developers that consume your DataReader behave themselves. I prefer the former solution.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
MVP, hRD
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
 
William,

I am also confused when to use a datareader versus a dataset. In my application I have a bunch of combo boxes that I bind to a datatable at runtime. Currently, I designed by data access layer to return datatables using filling a dataset and returning dataset.tables[0]; I designed this way because I did not want pass the datareader down to my UI which would keep the connection open longer. After I read this thread, I am rethinking my approach and maybe I should use the datareader object instead of a dataset and then loop through the datareader and fill a datatable. What are your thoughts?

Thanks
Yup, while it's possible to pass DataReaders to a calling routine, but if you do you MUST make sure that:
1.. The DataReader is constructed with CommandBehavior.CloseConnection. This lets the receiving code close the DataReader AND close the Connection (which it can't see) at the same time.
2.. Make SURE the receiving routine closes the DataReader when it reaches EOF (Read returns false). This is problematic as there are situations where closing the connection upsets other logic that depends on the Connection remaining open.
So we have a quandary. Don't pass the DataSet to other routines (as Kathleen suggests) or ensure (somehow) that other developers that consume your DataReader behave themselves. I prefer the former solution.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
MVP, hRD
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
 
If you need to create a DataTable, always use a DataAdapter to fill it. No matter how good your code is (and you'll have to write and support it), it won't beat the Fill method in performance and ease of use. I work with many companies that don't let their developers use the DataReader as it's simply too expensive to code and support--and it does not yield that much in improved performance.

My approach is to submit several SELECT statements at once to populate drop-down lists. A single call to Fill handles this for me as it builds several DataTables that can be bound to the drop-down lists. Since these dropdown lists aren't updatable, I don't worry about filling in anything except the SelectCommand. It's easy and takes only one round-trip and no waiting to complete rowset population--and the I never leak connections this way.
--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
MVP, hRD
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________

<m> wrote in message William,

I am also confused when to use a datareader versus a dataset. In my application I have a bunch of combo boxes that I bind to a datatable at runtime. Currently, I designed by data access layer to return datatables using filling a dataset and returning dataset.tables[0]; I designed this way because I did not want pass the datareader down to my UI which would keep the connection open longer. After I read this thread, I am rethinking my approach and maybe I should use the datareader object instead of a dataset and then loop through the datareader and fill a datatable. What are your thoughts?

Thanks
Yup, while it's possible to pass DataReaders to a calling routine, but if you do you MUST make sure that:
1.. The DataReader is constructed with CommandBehavior.CloseConnection. This lets the receiving code close the DataReader AND close the Connection (which it can't see) at the same time.
2.. Make SURE the receiving routine closes the DataReader when it reaches EOF (Read returns false). This is problematic as there are situations where closing the connection upsets other logic that depends on the Connection remaining open.
So we have a quandary. Don't pass the DataSet to other routines (as Kathleen suggests) or ensure (somehow) that other developers that consume your DataReader behave themselves. I prefer the former solution.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
MVP, hRD
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
 
One problem with A.M.'s code is that it's unrealistic to put the burden of
closing the SqlDataReader instance the method returns on the calling
functions. it results in that the opening and closing the reader sit far
part in two different functions and most likely two different classes that
are not aware of each other. As someone on this thread pointed out, you
should keep SqlDataReader instance local, that is, you should avoid return
it outside of the function.

From a tiered design perspectively, this is not good either. SqlDataReader
should be contained inside the data access layer and the caller should not
have knowledge about it. Of course the validity of this argument is
dependent on the actual design.

Bob
 
Back
Top