A DataReader provides a forward-only and read-only access to data, while the DataSet object can carry more than one table and at the same time hold the relationships between the tables. Also note that a DataReader is used in a connected architecture whereas a Dataset is used in a disconnected architecture.
Use the AcceptChanges method which commits all the changes made to the dataset since last time Acceptchanges was performed.
A dataset is an in memory database kind of object that can hold database information in a disconnected environment.
A DataReader works in a connected environment, whereas DataSet works in a disconnected environment.
A DataReader object represents a forward only, read only access to data from a source. It implements IDataReader & IDataRecord interfaces. For example, The SQLDataReader class can read rows from tables in a SQL Server data source. It is returned by the ExecuteReader method of the SQLCommand class, typically as a result of a SQL Select statement. The DataReader class’ HasRows property can be called to determine whether the DataReader retrieved any rows from the source. This can be used before using the Read method to check whether any data has been retrieved.
Dim objCmd as New SqlCommand(“Select * from t_Employees”, objCon)
Dim objReader as SqlDataReader
objReader = objCom.ExecuteReader(CommandBehavior.CloseConnection)
If objReader.HasRows = True then
Do While objReader.Read()
ListBox1.Items.Add(objReader.GetString(0) & vbTab & objReader.GetInt16(1))
(NOTE: XmlReader object is used for Forward only Read only access of XML).
A DataSet represents an in-memory cache of data consisting of any number of inter-related DataTable objects. A DataTable object represents a tabular block of in-memory data. Further, a DataRow represents a single row of a DataTable object. A Dataset is like a mini-database engine, but its data is stored in the memory. To query the data in a DataSet, we can use a DataView object.
Dim objCon as SqlConnection = New SqlConnection(“server=(local);database=NameOfYourDb;user id=sa; password=;)
Dim da as New SqlDataAdapter
Dim ds as DataSet = New DataSet
da.SelectCommand.Connection = objCon ‘The Data Adapter manages on its own, opening & closing of connection object
da.SelectCommand.CommandText = “Select * from t_SomeTable”
Suppose you want to bind the data in this dataset to a gridview
Gridview1.DataSource = ds
Gridview1.DataMember = “YourTableName”
Data binding is a way used to connect values from a collection of data (e.g. DataSet) to the controls on a web form. The values from the dataset are automatically displayed in the controls without having to write separate code to display them.
Datareader is like a forward only recordset. It fetches one
row at a time so very less Network Cost compare to DataSet(Fetches all the rows at a time). DataReader is readonly so we cannot do any transaction on them. DataReader will be the best choice where we need to show the data to the user which requires no transaction ie reports. Due to DataReader is forward only we cannot fetch the data randomly. .NET Dataproviders optimizes the datareaders to handle the huge amount of data.
DataSet is always a bulky object that requires lot of memory space compare to DataReader. We can say the dataset as a small database coz it stores the schema and data in the application memory area. DataSet fetches all data from the datasource at a time to its memory area. So we can traverse through the object to get required data like qureying database.
The dataset maintains the relationships among the datatables inside
it. We can manipulate the realational data as XML using dataset.We can do transactions (insert/update/delete) on them and finally the modifications can be updated to the actual database. This provides impressive flexibility to the application but with the cost of memory space. DataSet maintains the original data and the modified data seperately which requires more memory space. If the amount of data in the dataset is huge
then it will reduce the applications performance dramatically.