In this article I will explain with an example, the difference between DataReader, DataSet, DataAdapter and DataTable in C# and VB.Net.
DataReader
DataReader as the name suggests reads data. DataReader is used for fetching records from the SQL Query or Stored Procedure i.e. SELECT Operation.
DataReader is the fastest technique to fetch records from database and it works only in Forward direction meaning a row read once cannot be read again.
DataReader is ReadOnly and it fetches one row at a time in memory and hence it has less load on memory.
The Read function of the DataReader reads one row at a time in memory and if a row is read then the function returns True else False.
DataReader requires an open connection in order to execute the SQL statement.
Example would be fetching Name City for all records in the Person Table using DataReader.
C#
string constring = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
using (SqlConnection con = new SqlConnection(constring))
{
using (SqlCommand cmd = new SqlCommand("SELECT Name, City FROM Persons", con))
{
cmd.CommandType = CommandType.Text;
con.Open();
SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
string name = dr["Name"].ToString();
string city = dr["City"].ToString();
Response.Write("Name: " + name);
Response.Write("City: " + city);
}
con.Close();
}
}
VB.Net
Dim constring As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
Using con As New SqlConnection(constring)
Using cmd As New SqlCommand("SELECT Name, City FROM Persons", con)
cmd.CommandType = CommandType.Text
con.Open()
Dim dr As SqlDataReader = cmd.ExecuteReader()
While dr.Read()
Dim name As String = dr("Name").ToString()
Dim city As String = dr("City").ToString()
Response.Write("Name: " & name)
Response.Write("City: " & city)
End While
con.Close()
End Using
End Using
DataAdapter
DataAdapter is used to execute SQL statements and is used to populate the results of SQL Query into a DataSet or DataTable.
DataAdapter gets all the rows of the executed SQL statement at once and populates into DataSet or DataTable in memory and hence DataAdapter is bit slower compared to DataReader.
Since the DataAdapter populates all rows in DataSet or DataTable it can be traversed in both Forward and Backward directions.
DataAdapter makes use of the Fill function to populate the rows of SQL statement into a DataSet or DataTable.
DataAdapter manages the connection internally and does not require to open or close connections explicitly and this feature is termed as Disconnected Architecture.
Example would be fetching Name City for all records in the Person Table using DataAdapter.
C#
string constring = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
using (SqlConnection con = new SqlConnection(constring))
{
using (SqlCommand cmd = new SqlCommand("SELECT Name, City FROM Persons", con))
{
cmd.CommandType = CommandType.Text;
using (SqlDataAdapter sda = new SqlDataAdapter(cmd))
{
DataSet ds = new DataSet();
sda.Fill(ds);
foreach (DataRow row in ds.Tables[0].Rows)
{
string name = row["Name"].ToString();
string city = row["City"].ToString();
Response.Write("Name: " + name);
Response.Write("City: " + city);
}
}
}
}
VB.Net
Dim constring As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
Using con As New SqlConnection(constring)
Using cmd As New SqlCommand("SELECT Name, City FROM Persons", con)
cmd.CommandType = CommandType.Text
Using sda As New SqlDataAdapter(cmd)
Dim ds As New DataSet()
sda.Fill(ds)
For Each row As DataRow In ds.Tables(0).Rows
Dim name As String = row("Name").ToString()
Dim city As String = row("City").ToString()
Response.Write("Name: " & name)
Response.Write("City: " & city)
Next
End Using
End Using
End Using
DataSet
DataSet is in simple terms set of Data i.e. set of DataTables or collection of DataTables i.e. it can hold one or multiple DataTables.
DataSet is mainly used to fetch and hold the records for one or more tables into memory.
A DataAdapter is used to populate DataSet from records returned from an SQL statement and also a DataSet can be created in memory and tables and data can be added to it.
DataSet can also be converted and saved as XML file.
Example would be fetching Name City for all records in the Person Table into a DataSet.
string constring = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
using (SqlConnection con = new SqlConnection(constring))
{
using (SqlCommand cmd = new SqlCommand("SELECT Name, City FROM Persons", con))
{
cmd.CommandType = CommandType.Text;
using (SqlDataAdapter sda = new SqlDataAdapter(cmd))
{
DataSet ds = new DataSet();
sda.Fill(ds);
foreach (DataRow row in ds.Tables[0].Rows)
{
string name = row["Name"].ToString();
string city = row["City"].ToString();
Response.Write("Name: " + name);
Response.Write("City: " + city);
}
}
}
}
VB.Net
Dim constring As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
Using con As New SqlConnection(constring)
Using cmd As New SqlCommand("SELECT Name, City FROM Persons", con)
cmd.CommandType = CommandType.Text
Using sda As New SqlDataAdapter(cmd)
Dim ds As New DataSet()
sda.Fill(ds)
For Each row As DataRow In ds.Tables(0).Rows
Dim name As String = row("Name").ToString()
Dim city As String = row("City").ToString()
Response.Write("Name: " & name)
Response.Write("City: " & city)
Next
End Using
End Using
End Using
DataTable
A DataTable can hold records of a single Table consisting of rows and columns. A DataTable can be reside within a DataSet.
DataTable is mainly used to fetch and hold the records of one single table into memory.
A DataAdapter is used to populate DataTable from records returned from an SQL statement and also a DataTable can be created in memory and data can be added to it.
Example would be fetching Name City for all records in the Person Table into a DataTable.
C#
string constring = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
using (SqlConnection con = new SqlConnection(constring))
{
using (SqlCommand cmd = new SqlCommand("SELECT Name, City FROM Persons", con))
{
cmd.CommandType = CommandType.Text;
using (SqlDataAdapter sda = new SqlDataAdapter(cmd))
{
DataTable dt = new DataTable();
sda.Fill(dt);
foreach (DataRow row in dt.Rows)
{
string name = row["Name"].ToString();
string city = row["City"].ToString();
Response.Write("Name: " + name);
Response.Write("City: " + city);
}
}
}
}
VB.Net
Dim constring As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
Using con As New SqlConnection(constring)
Using cmd As New SqlCommand("SELECT Name, City FROM Persons", con)
cmd.CommandType = CommandType.Text
Using sda As New SqlDataAdapter(cmd)
Dim dt As New DataTable()
sda.Fill(dt)
For Each row As DataRow In dt.Rows
Dim name As String = row("Name").ToString()
Dim city As String = row("City").ToString()
Response.Write("Name: " & name)
Response.Write("City: " & city)
Next
End Using
End Using
End Using
