Step-by-Step Guide: Differentiating DataReader and DataSet

DataReader

Basically in C# DataReader is used to retrieve data from database. It is connected architecture. The data is exists as long as the connection with database exists. It can’t persist the data.  It fetches the record from the database. It releases the fetch record after query executes and don’t wait for entire query execution. This is why very fast as compare to Dataset.

DataReader is forward only. It retrieves one row at a time. At one row at a time is stored data in memory in datareader. It increases the performance of application. When you are using datareader you can not move back and can’t fetch random record. DataReader fetches data from single table.

DataReader is read only so insert, update or delete transactions could not be performed. DataReader is the best choice if you show the data from database which requires no manipulation. 

Example of DataReader

using (SqlConnection sqlConnection = new SqlConnection("Data Source=WIN-PC; Integrated Security=true; Initial Catalog=Demo; User Id=sa; Password=xxxxxx"))
            {
                try
                {
                    sqlConnection.Open();
                    SqlCommand sqlCommand = new SqlCommand("Select * from Employee", sqlConnection);
                    SqlDataReader sqlDataReader = null;
                    sqlDataReader = sqlCommand.ExecuteReader();
                    if (sqlDataReader.HasRows)
                    {
                        grvEmployee.DataSource = sqlDataReader;
                        grvEmployee.DataBind();
                    }
                }
                catch (SqlException ex)
                {
                    throw ex;
                }                
            }

Dataset

Dataset is disconnected oriented architecture. It means there is no need of active database connection during when you are working with datasets. Dataset is collection of DataTables. Dataset object contain multiple rowsets from the same data source as well as from the relationship between them. Dataset can save data as xml and also load data from xml DataSource.

Dataset is best choice where you want manipulation of data. Dataset automatically maintains the connection like open connection or close connection.

Example of Dataset

using (SqlConnection sqlConnection = new SqlConnection("Data Source=WIN-PC; Integrated Security=true; Initial Catalog=Demo; User Id=sa; Password=xxxxx"))
            {
                try
                {
                    sqlConnection.Open();
                    SqlCommand sqlCommand = new SqlCommand("Select * from Employee", sqlConnection);
                    SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(sqlCommand);
                    DataSet ds = new DataSet();
                    sqlDataAdapter.Fill(ds);
                    if(ds.Tables[0].Rows.Count>0)
                    {
                        grvEmployee.DataSource = ds;
                        grvEmployee.DataBind();
                    }
                }
                catch (SqlException ex)
                {
                    throw ex;
                }
            }

Conclusion:

Today We learned what is difference between DataReader and Dataset in Ado.Net. Why we should use it and when.

I hope this post will help you. Please put your feedback using comment which  helps me to improve myself for next post. If you have any doubts please ask your doubts or query in the comment section and if you like this post, please share it with your friends. Thanks