Difference between DataSet and DataReader

Difference between DataSet and DataReader

March 17, 2011 07:41 by Aamir Hasan

0

Share

Some developers say that DataSet is better and some says DataReader is better. But, both are Microsoft developments but are depend upon the situation that where DataReader and DataSet are useful. DataReader is faster than DataSet, DataSet is complex where as DataReader is lightweight, let’s discussed the both with example.

DataSet

1. Dataset is disconnected architecture.
2. Data set cannot directly accessible to database.
3. DataAdapter is used to communicate between DataSet and database.
4. DataSet can hold more than one table.
5. Primary and foreign keys can be created in DataSet tables.
6. XML technology is used to work dataset.
7. DataSet is present inside System.Data namespace.
8. There are two type of DataSet
   1. Typed DataSet
   2. Untyped DataSet
9. DataSet will retrieve the data into memory and return the database connection to the connection pool.
10. DataSet changes can directly update to database
11. DataSet can be loaded from
    1. Microsoft SQL Server
    2. Oracle Database
    3. Microsoft Access Database.
12. When you have to delete, edit and update records then DataSet is good choice.

Example

C#

using (SqlConnection connection = new SqlConnection("CONNECTIONSTRING"))

{

connection.Open();

SqlCommand command = new SqlCommand("SELECT * FROM TABLE", connection);

SqlDataReader reader = command.ExecuteReader();

while (reader.Read())

{

Response.Write(String.Format("{0}", reader[0].ToString() + "
"));

}

}

VB.NET

Using connection As New SqlConnection("CONNECTIONSTRING")

connection.Open()

Dim command As New SqlCommand("SELECT * FROM TABLE", connection)

Dim reader As SqlDataReader = command.ExecuteReader()

While reader.Read()

Response.Write([String].Format("{0}", reader(0).ToString() + "
"))

End While

End Using

DataReader

1. DataReader is connected architecture.
2. DataReader is read only.
3. DataReader is faster than DataSet.
4. DataReader keeps the connection locked open until processing is complete.
5. DataReader is present inside System.Data.SqlClient namespace.
6. DataReader is useful when you have to fetch thousand of records from database.

Example

C#

using (SqlConnection conn = new SqlConnection("CONNECTIONSTRING"))

{

SqlDataAdapter DataAd = new SqlDataAdapter("SELECT * FROM TABLE", conn);

System.Data.DataSet ds = new System.Data.DataSet();

DataAd.Fill(ds);

foreach (System.Data.DataRow dr in ds.Tables[0].Rows)

{

Response.Write(String.Format("{0}", dr[0].ToString() + "
"));

}

}

VB.NET

Using conn As New SqlConnection("CONNECTIONSTRING")

Dim DataAd As New SqlDataAdapter("SELECT * FROM TABLE", conn)

Dim ds As New System.Data.DataSet()

DataAd.Fill(ds)

For Each dr As System.Data.DataRow In ds.Tables(0).Rows

Response.Write([String].Format("{0}", dr(0).ToString() & "
"))

Next

End Using