Four key parts of ADO.NET are DataReader, DataSet, DataAdapter, and DataTable. I’ll describe the distinctions between a DataReader, DataSet, DataAdapter, and DataTable in this blog post using C# code examples.

DataReader

DataReader is used to read data from databases, and its connection-oriented architecture is read-only while fetching data from databases. When compared to a dataset, a data reader will retrieve the data quite quickly. Typically, we’ll connect data to a datareader using an ExecuteReader object.

The following code must be written in order to link DataReader data to the GridView:

protected void BindGridview() {
    using(SqlConnection conn = new SqlConnection("Data Source=abc;Integrated Security=true;Initial Catalog=Test")) {
        con.Open();
        SqlCommand cmd = new SqlCommand("Select UserName, First Name,LastName,Location FROM Users", conn);
        SqlDataReader sdr = cmd.ExecuteReader();
        gvUserInfo.DataSource = sdr;
        gvUserInfo.DataBind();
        conn.Close();
    }
}

• Holds the connection open until you are finished (don’t forget to close it!).
• Can typically only be iterated over once
• Is not as useful for updating back to the database

DataSet

DataSets are a collection of DataTables and relations between tables and have a disconnected orient architecture, which means they may be used without the necessity for active connections. It is used to store numerous data tables. Table-based data selection, table-based view creation, and child rows over relations are all options. Additionally, DataSet offers you a variety of functions including loading and saving XML data.

protected void BindGridview() {
    SqlConnection conn = new SqlConnection("Data Source=abc;Integrated Security=true;Initial Catalog=Test");
    conn.Open();
    SqlCommand cmd = new SqlCommand("Select UserName, First Name,LastName,Location FROM Users", conn);
    SqlDataAdapter sda = new SqlDataAdapter(cmd);
    DataSet ds = new DataSet();
    sda.Fill(ds);
    gvUserInfo.DataSource = ds;
    gvUserInfo.DataBind();
}

DataAdapter

Between the DataSet and the database, the DataAdapter will serve as a bridge. The data is read from the database and bound to the dataset using this dataadapter object. A unconnected oriented architecture is Dataadapter. See how to use DataAdapter in the sample code below:

protected void BindGridview() {
    SqlConnection con = new SqlConnection("Data Source=abc;Integrated Security=true;Initial Catalog=Test");
    conn.Open();
    SqlCommand cmd = new SqlCommand("Select UserName, First Name,LastName,Location FROM Users", conn);
    SqlDataAdapter sda = new SqlDataAdapter(cmd);
    DataSet ds = new DataSet();
    sda.Fill(ds);
    gvUserInfo.DataSource = ds;
    gvUserInfo.DataBind();
}

• Lets you close the connection as soon it’s done loading data, and may even close it for you automatically
• All of the results are available in memory
• You can iterate over it as many times as you need, or even look up a specific record by index
• Has some built-in faculties for updating back to the database.

DataTable

A single database table is represented by a DataTable. It has columns and rows. Datasets and datatables are quite similar; a dataset is simply a collection of datatables.

protected void BindGridview()
{
    SqlConnection con = new SqlConnection("Data Source=abc;Integrated Security=true;Initial Catalog=Test");
    conn.Open();
    SqlCommand cmd = new SqlCommand("Select UserName, First Name,LastName,Location FROM Users", conn);
    SqlDataAdapter sda = new SqlDataAdapter(cmd);
    DataTable dt = new DataTable();
    sda.Fill(dt);
    gridview1.DataSource = dt;
    gvidview1.DataBind();
}