Support us .Net Basics C# SQL ASP.NET Aarvi MVC Slides C# Programs Subscribe Download

Part 15 - Disconnected data access in asp.net

Suggested Videos
Part 12 - Caching dataset in asp.net
Part 13 - What is SqlCommandBuilder
Part 14 - Sqlcommandbuilder update not working



ADO.NET offers two data access modes
1. Connection oriented data access
2. Disconnected data access

In this video, we will discuss disconnected data access. SqlDataAdapter and DataSet objects together provide disconnected data access.



A DataSet is an in-memory data store that can hold one or more tables. DataSets only hold data and do not interact with the underlying database table. The DataSet object has no knowledge of the underlying Data Source. It is the SqlDataAdapter object that retrieves data from the datasource.

This is how it works.

1. You create an instance of SqlDataAdapter by specifying a select command and a connection object
string connectionString = ConfigurationManager.ConnectionStrings["DBCS"].ConnectionString;
SqlConnection connection = new SqlConnection(connectionString);
string selectQuery = "Select * from tblStudents";
SqlDataAdapter dataAdapter = new SqlDataAdapter(selectQuery, connection);

2. When SqlDataAdapter.Fill() method is invoked, SqlDataAdapter opens the connection to the database, executes the select command, and the DataSet is populated with the data that is retrieved. The SqlDataAdapter automatically closes the connection.
DataSet dataSet = new DataSet();
dataAdapter.Fill(dataSet, "Students");

3. You now have data in the DataSet and there is no active connection to the database. At this point you can make any changes(insert, update, delete) to the data in the DataSet. Only the data in the DataSet is changed, the underlying database table data is not changed.

4. To update the underlying database table, invoke SqlDataAdapter.Update() method. Make sure there is an UPDATE, DELETE and INSERT command are associated with SqlDataAdapter object when Update() method is called, otherwise there would be a runtime exception.
dataAdapter.Update(DataSetObject, "Students");

ASPX Code:
<div style="font-family: Arial">
    <asp:Button ID="btnGetDataFromDB" runat="server" Text="Get Data from Database" 
        onclick="btnGetDataFromDB_Click" />
    <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" 
        DataKeyNames="ID" onrowediting="GridView1_RowEditing" 
        onrowcancelingedit="GridView1_RowCancelingEdit" 
        onrowdeleting="GridView1_RowDeleting" 
        onrowupdating="GridView1_RowUpdating">
        <Columns>
            <asp:CommandField ShowDeleteButton="True" ShowEditButton="True" />
            <asp:BoundField DataField="ID" HeaderText="ID" InsertVisible="False" 
                ReadOnly="True" SortExpression="ID" />
            <asp:BoundField DataField="Name" HeaderText="Name" SortExpression="Name" />
            <asp:BoundField DataField="Gender" HeaderText="Gender" 
                SortExpression="Gender" />
            <asp:BoundField DataField="TotalMarks" HeaderText="TotalMarks" 
                SortExpression="TotalMarks" />
        </Columns>
    </asp:GridView>
    <asp:Button ID="btnUpdateDatabaseTable" runat="server" 
        Text="Update Database Table" onclick="btnUpdateDatabaseTable_Click" />
    <asp:Label ID="lblStatus" runat="server"></asp:Label>
</div>

ASPX.CS Code:
public partial class WebForm1 : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
    }

    private void GetDataFromDB()
    {
        string connectionString = ConfigurationManager.ConnectionStrings["DBCS"].ConnectionString;
        SqlConnection connection = new SqlConnection(connectionString);
        string selectQuery = "Select * from tblStudents";
        SqlDataAdapter dataAdapter = new SqlDataAdapter(selectQuery, connection);

        DataSet dataSet = new DataSet();
        dataAdapter.Fill(dataSet, "Students");
        // Set ID column as the primary key
        dataSet.Tables["Students"].PrimaryKey =
            new DataColumn[] { dataSet.Tables["Students"].Columns["ID"] };
        // Store the dataset in Cache
        Cache.Insert("DATASET", dataSet, null, DateTime.Now.AddHours(24),
            System.Web.Caching.Cache.NoSlidingExpiration);

        GridView1.DataSource = dataSet;
        GridView1.DataBind();

        lblStatus.Text = "Data loded from Database";
    }

    private void GetDataFromCache()
    {
        if (Cache["DATASET"] != null)
        {
            GridView1.DataSource = (DataSet)Cache["DATASET"];
            GridView1.DataBind();
        }
    }

    protected void GridView1_RowEditing(object sender, GridViewEditEventArgs e)
    {
        // Set row in editing mode
        GridView1.EditIndex = e.NewEditIndex;
        GetDataFromCache();
    }

    protected void GridView1_RowCancelingEdit(object sender, GridViewCancelEditEventArgs e)
    {
        GridView1.EditIndex = -1;
        GetDataFromCache();
    }

    protected void GridView1_RowUpdating(object sender, GridViewUpdateEventArgs e)
    {
        // Retrieve dataset from cache
        DataSet dataSet = (DataSet)Cache["DATASET"];
        // Find datarow to edit using primay key
        DataRow dataRow = dataSet.Tables["Students"].Rows.Find(e.Keys["ID"]);
        // Update datarow values
        dataRow["Name"] = e.NewValues["Name"];
        dataRow["Gender"] = e.NewValues["Gender"];
        dataRow["TotalMarks"] = e.NewValues["TotalMarks"];
        // Overwrite the dataset in cache
        Cache.Insert("DATASET", dataSet, null, DateTime.Now.AddHours(24),
            System.Web.Caching.Cache.NoSlidingExpiration);
        // Remove the row from edit mode
        GridView1.EditIndex = -1;
        // Reload data to gridview from cache
        GetDataFromCache();
    }

    protected void GridView1_RowDeleting(object sender, GridViewDeleteEventArgs e)
    {
        DataSet dataSet = (DataSet)Cache["DATASET"];
        dataSet.Tables["Students"].Rows.Find(e.Keys["ID"]).Delete();
        Cache.Insert("DATASET", dataSet, null, DateTime.Now.AddHours(24),
            System.Web.Caching.Cache.NoSlidingExpiration);
        GetDataFromCache();
    }

    protected void btnGetDataFromDB_Click(object sender, EventArgs e)
    {
        GetDataFromDB();
    }

    protected void btnUpdateDatabaseTable_Click(object sender, EventArgs e)
    {
        if (Cache["DATASET"] != null)
        {
            string connectionString =
            ConfigurationManager.ConnectionStrings["DBCS"].ConnectionString;
            SqlConnection connection = new SqlConnection(connectionString);
            string selectQuery = "Select * from tblStudents";
            SqlDataAdapter dataAdapter = new SqlDataAdapter(selectQuery, connection);

            // Update command to update database table
            string strUpdateCommand = "Update tblStudents set Name = @Name, Gender = @Gender, TotalMarks = @TotalMarks where Id = @Id";
            // Create an instance of SqlCommand using the update command created above
            SqlCommand updateCommand = new SqlCommand(strUpdateCommand, connection);
            // Specify the parameters of the update command
            updateCommand.Parameters.Add("@Name"SqlDbType.NVarChar, 50, "Name");
            updateCommand.Parameters.Add("@Gender"SqlDbType.NVarChar, 20, "Gender");
            updateCommand.Parameters.Add("@TotalMarks"SqlDbType.Int, 0, "TotalMarks");
            updateCommand.Parameters.Add("@Id", SqlDbType.Int, 0, "Id");
            // Associate update command with SqlDataAdapter instance
            dataAdapter.UpdateCommand = updateCommand;

            // Delete command to delete data from database table
            string strDeleteCommand = "Delete from tblStudents where Id = @Id";
            // Create an instance of SqlCommand using the delete command created above
            SqlCommand deleteCommand = new SqlCommand(strDeleteCommand, connection);
            // Specify the parameters of the delete command
            deleteCommand.Parameters.Add("@Id", SqlDbType.Int, 0, "Id");
            // Associate delete command with SqlDataAdapter instance
            dataAdapter.DeleteCommand = deleteCommand;

            // Update the underlying database table
            dataAdapter.Update((DataSet)Cache["DATASET"], "Students");
            lblStatus.Text = "Database table updated";
        }
    }
}

Please make sure to include the following using declarations:
using System.Configuration;
using System.Data;
using System.Data.SqlClient;

11 comments:

  1. Hello Venkat,
    Thank you for providing these wonderful tutorials. They have helped me a lot. I was wondering if you had any plans to include some tutorials regarding subjects such as JavaScript, JQuery, and XML. These topics seem to be very essential to Web Application development, and the hiring managers always ask about them.

    If you are not planning on making video tutorials about these topics yourself, can you recommend a good source for such video tutorials?

    Thanks for creating this site, and for all your hard work. It is very much appreciated! You are an excellent instructor.

    ReplyDelete
  2. Hello Venkat Sir
    Good Tutorial indeed, but when i am updating in the dataset , it is throwing an error as object ref not set to the instance of the object, what could be the possible mistake i am doing.

    ReplyDelete
  3. e.keys , e.NewValue , e.OldValues all are showing empty . Please help

    ReplyDelete
  4. Hi Sir I need ur help ............




    I m trying to generate auto generated CRUD in my Asp.net code using Visual studio

    Problem:
    In lecture video tutorial these check boxes are enabled But they are disabled as i cannot tick these boxes
    Please tell my How can i get rid of this problem??????????????

    Regards......

    ReplyDelete
    Replies
    1. Visual Studio Problem
      you re-install your vs

      Delete
  5. Hello Venkat!

    Is it possible to do this with a strongly typed dataset? How would you do it? How would the code for Row Updating look like and Update Database etc?

    Thanks!

    ReplyDelete

  6. Dataset is the disconnected environment. suppose if you are binding records to gridview (disconnected environment) and you are making changes to the the grid but before updating the database if any other user modify the data, how will you avoid such problem?

    ReplyDelete
  7. help me with this.....An exception of type 'System.Data.SqlClient.SqlException' occurred in System.Data.dll but was not handled in user code

    ReplyDelete
  8. Dear Venkat, really your tutorials helped me a lot. kindly help me in inserting new row with identity. i am able to add new row but unable how to get identity for inserting. Moreover, what if i load a subset of data (e.g only male in this example) from underlying database table then want to insert new records. here also i am unable to generate identity.


    if (e.CommandName == "InsertRow")
    {
    string name = ((TextBox)GridView1.FooterRow.FindControl("NameInsertTextBox")).Text;
    string gender = ((DropDownList)GridView1.FooterRow.FindControl("GenderInsertDropDownList")).SelectedValue;
    int totalMarks =Int32.Parse(((TextBox)GridView1.FooterRow.FindControl("TotalMakrsInsertTextBox")).Text);

    DataSet dataSet = (DataSet)Cache["DATASET"];
    DataRow dataRow = dataSet.Tables["Students"].NewRow();
    // Update datarow values
    dataRow["ID"] = //i need identity here please help
    dataRow["Name"] = name;
    dataRow["Gender"] = gender;
    dataRow["TotalMarks"] = totalMarks;
    dataSet.Tables["Students"].Rows.Add(dataRow);
    // Overwrite the dataset in cache
    Cache.Insert("DATASET", dataSet, null, DateTime.Now.AddHours(24), System.Web.Caching.Cache.NoSlidingExpiration);
    // Remove the row from edit mode
    GridView1.EditIndex = -1;
    // Reload data to gridview from cache
    GetDataFromCache();

    }

    ReplyDelete
  9. Venket sir, How can i insert a new record using the above example also, if i give new record and give update, it shows an error. i could not insert a new record. pls do one video for insert data using the above example.

    ReplyDelete
  10. I have this error at the time of updating and deleting please give me solution

    Object reference not set to an instance of an object.
    Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

    Exception Details: System.NullReferenceException: Object reference not set to an instance of an object.

    Source Error:


    Line 81: {
    Line 82: DataSet ds = (DataSet)Cache["DATASET"];
    Line 83: ds.Tables["Student"].Rows.Find(e.Keys["ID"]).Delete();
    Line 84: // DataRow dr = ds.Tables["Student"].Rows.Find(e.Keys["ID"]);
    Line 85: // dr.Delete();


    ReplyDelete

It would be great if you can help share these free resources