DataSet & DataTable vs DataReader

DataReader is a forward-only stream of rows and connected object with database. Also changes made to a result set by another process or thread while data is being read may be visible . It is extremely faster than DataTable. But you need to close the DataReader properly otherwise it will lead to timeout error when connecting with database.

DataTable is a disconnected object and cached in the computer memory when filled from database. You can move bacjward and forward only between rows. Also it allows you to add/modify rows.This is helpful when you want to check some condition or add some dummry rows before binding a asp.net control such as dropdownlist. Easy to manage in code.

You can also look into collections to populate dropdownlist.

Take a look at the following links.

Contrasting the ADO.NET DataReader and DataSet
DataSets vs. Collections


 

Hi Ramesh S,

Thank you for the quick response...

I have a question about the DataSet and DataTable, and that is I have read just as you have mentioned that data is stored in memory when using either a DataSet, DataTable or both but I have always wondered what happens if I use a DataTable for example and I return some data from my DB which is now disconnected from my Data Source, what happens if I pull the data now and im using it within memory and another user changes that data. How can I make sure that I'm always using the correct data since it's disconnected??

It depends upon your requirement. If your requirement is to get the latest data then you must use DataReader to bind the data. This is called Real time scenario. For e.g Data Reader is used in application like retrieving records from stock market, where the data is changing every second.
The second option in your case is to bind the data in each refresh of the page.

If you use datatable/dataset and bind the dropdownlist, it will not be reflected if other users changes data. Also even if you are using datareader, once the dropdownlist is bound and datareader is closed, it will not be reflected the changes made by other user. The user user change is refelected in datareader when it loads to data to dropdownlist which only happens in rare scenario or may be it happens in a connected environment such as windows forms applications. Please note that asp.net is web application environement which is disconnected.

Ok, so if I only need to retrieve and display the data once to a User control such as a DropDownList then a DataReader is best for that? If I need to filter/sort data then a DataTable is best if I only have one DB table that I will be using? Now I have been seeing examples that display "Dispose" rather than just "close", is there a better one of the two to use?

The datatable can be used to retrieve data from multiple tables using JOIN condition in SELECT statements. Once the datatable is populated, it is stores a snapshot oif the data in memory and disconnected from database. It has built-in features to sort/fitler and move backward/forward  between rows of the data.

Dispose is meant to clean up unmanaged resources before the object is  garbage collected.  The Dispose method is availabe for Connection, Reader and DataTable objects. Which one are you talking about.

If you use Close the with above ADO.NET objects such as connection, it will close connection to the database and release it from memory by carbage collector in .NET.

I was wondering in regards to the connection, datareader, and datatable as you mentioned above. Is there a difference between using the "Close" and or "Dispose" when closing each of these?

The basic difference between Close() and Dispose() is, when a Close() method is called, any managed resource can be temporarily closed and can be opened once again. It means that, with the same object the resource can be reopened or used. Where as Dispose() method permanently removes any resource (unmanaged code) from memory for cleanup and the resource no longer exists for any further processing.
For Example:
public void ConnectionStatus() {  
    if(connection.State == ConnectionState.Open) {    
        connection.Close();    
        Console.WriteLine("Connection closed..");  
    }
    //connection.Dispose();      
    if(connection.State == ConnectionState.Closed){
        connection.Open();    
        Console.WriteLine("Connection again opened..");
    }  
}
In the above example if you uncomment the "connection.Dispose()" method and execute, you will get an exception as, "The ConnectionString property has not been initialized.".This is the difference between Close() and Dispose().
Moreover, you do not need to dispose connection, datareader, and datatable because they are managed resources. CLR automatically take care of garbage collection for managed resources.

Ok, thank you for the explanation above. Is there a benefit to using dispose over close?