Now here in this tutorial, I’ll explain the main difference between dataset vs datareader as well as show you how to use dataset or datareader with an example in asp.net using c# and vb.net.
In my previous tutorials, I’d explained the difference between dataset and datatable, difference between String and StringBuilder, difference between executereader executenonquery and executescalar, OOP concepts with examples, difference between dataset datareader dataadapter and dataview and other similar tutorials on difference here.
Let’s start with the difference between DataSet vs DataReader.
Difference: DataSet vs DataReader
Following are the list of possible differences between DataSet vs DataReader:
| DataSet | DataReader |
|---|---|
| It has read/write access, we can update records | It has read-only access, we can’t update records |
| It supports both forward and backward scanning of data | It supports forward-only scanning of data |
| It supports multiple data tables from different databases | It only supports a single table of one database |
| It is connection-less oriented, that means whenever we bind data from database it connects indirectly to the database and then disconnected | It is connection oriented, that means whenever we bind data from database it must require a connection and after that disconnected from the connection |
| You have to be careful about how much data you pull into a DataSet, because this is an in-memory representation | It is scalable to any number of records, at least in terms of memory pressure, since it only loads one record at a time |
| You can Fill a DataSet using the Fill() method of a DataAdapter | One typical way to get a DataReader is by using the ExecuteReader() method of a DbCommand |
| Greater overhead to enable additional features | Light-weight object with very little overhead |
| Slower access to data | Faster access to data |
The biggest disadvantage of DataSet is speed because it is a high resource consuming process with one or more related database tables are in-memory. It carries considerable overhead because of the related tables, constraints, and relationships among the database tables.
Using DataSet in Asp.net with Example
Example [C#]:
public static string strConn =
@”Data Source=datasource;Integrated Security=true;Initial Catalog=yourDB”;
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
BindGridviewFileData();
}
}
//bind subject details to gridview
private void BindGridviewFileData()
{
try
{
using (SqlConnection sqlConn = new SqlConnection(strConn))
{
using (SqlCommand sqlCmd = new SqlCommand())
{
sqlCmd.CommandText = “SELECT * FROM SubjectDetails”;
sqlCmd.Connection = sqlConn;
sqlConn.Open();
SqlDataAdapter objDataAdapter = new SqlDataAdapter(sqlCmd);
DataSet objDataSet = new DataSet();
objDataAdapter.Fill(objDataSet);
gvSubjectDetails.DataSource = objDataSet;
gvSubjectDetails.DataBind();
sqlConn.Close();
}
}
}
catch { }
}
Example [Vb.net]:
Public Shared strConn As String =
“Data Source=datasource;Integrated Security=true;Initial Catalog=yourDB”
Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs) Handles Me.Load
If Not IsPostBack Then
BindGridviewFileData()
End If
End Sub
‘bind subject details to gridview
Private Sub BindGridviewFileData()
Try
Using sqlConn As New SqlConnection(strConn)
Using sqlCmd As New SqlCommand()
sqlCmd.CommandText = “SELECT * FROM SubjectDetails”
sqlCmd.Connection = sqlConn
sqlConn.Open()
Dim objDataAdapter As New SqlDataAdapter(sqlCmd)
Dim objDataSet As New DataSet()
objDataAdapter.Fill(objDataSet)
gvSubjectDetails.DataSource = objDataSet
gvSubjectDetails.DataBind()
sqlConn.Close()
End Using
End Using
Catch
End Try
End Sub
Using DataReader in Asp.net with Example
Example [C#]:
public static string strConn =
@”Data Source=datasource;Integrated Security=true;Initial Catalog=yourDB”;
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
BindGridviewFileData();
}
}
//bind subject details to gridview
private void BindGridviewFileData()
{
try
{
using (SqlConnection sqlConn = new SqlConnection(strConn))
{
using (SqlCommand sqlCmd = new SqlCommand())
{
sqlCmd.CommandText = “SELECT * FROM SubjectDetails”;
sqlCmd.Connection = sqlConn;
sqlConn.Open();
SqlDataReader objDataReader = sqlCmd.ExecuteReader();
gvSubjectDetails.DataSource = objDataReader;
gvSubjectDetails.DataBind();
sqlConn.Close();
}
}
}
catch { }
}
Example [Vb.net]:
Public Shared strConn As String =
“Data Source=datasource;Integrated Security=true;Initial Catalog=yourDB”
Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs) Handles Me.Load
If Not IsPostBack Then
BindGridviewFileData()
End If
End Sub
‘bind subject details to gridview
Private Sub BindGridviewFileData()
Try
Using sqlConn As New SqlConnection(strConn)
Using sqlCmd As New SqlCommand()
sqlCmd.CommandText = “SELECT * FROM SubjectDetails”
sqlCmd.Connection = sqlConn
sqlConn.Open()
Dim objDataReader As SqlDataReader = sqlCmd.ExecuteReader()
gvSubjectDetails.DataSource = objDataReader
gvSubjectDetails.DataBind()
sqlConn.Close()
End Using
End Using
Catch
End Try
End Sub
