Posted on 12/26/2020 5:25:23 AM by Admin

DataSet and DataAdpater in Ado.Net

DataSet and DataAdapter   Example
DataSet and DataAdapter

In order to understand Dataset and DataAdapter , first we need to understand the types of relation between .net application and Data source. Consider following scenario where
Step 1.You have to retrieve information of all employee from database.
Step 2. Display it in a Gridview.
Step3. Modify some of the information of some or all employee.
Step4. View the changes in a Grid. and when you are completely satisfied with the changes then
Step 5.Commit all the changes in the database in one go.

How do you think, you will achieve this requirement.

If you will use SqlDataReader to read data from database and ExecuteNonQuery() method of SqlCommand class to save changes to database then it becomes very tough to achieve the above requirement , because SqlDataReader and SqlCommand requires an open and active connection throughout the life time of operation , meaning any changes will not be saved in memory temporarily rather the changes will immediately commit to database as soon as ExecuteNonQuery will execute the command.

But in our case we want to commit changes to the database after being assure that the changes are as per the requirement and meanwhile we want to store the changes somewhere in Memory Temporarily.

That is why, the ADO.NET Framework supports two models of Data Access Architecture:-
    • Connection Oriented Data Access Architecture and
    • Disconnected Data Access Architecture.

In Connection Oriented Data Access Architecture the application makes a connection to the Data Source and then interact with it through SQL requests using the same connection. In this case the application stays connected to the database system even when it is not using any Database Operation.

On the other hand the disconnected approach makes no attempt to maintain a connection to the data source.

So we can say that a connected mode of operation in ADO.Net is one in which the connection to the underlying database is alive throughout the lifetime of the operation. whereas, a disconnected mode of operation is one in which ADO.Net retrieves data from the underlying database, stores the retrieved data temporarily in the memory, and then closes the connection to the database.

SqlDataReader and SqlCommand object are used for Connection oriented architecture but Dataset and SqlDataAdapter are used in disconnected data access architecture.

Ado.Net DataSet

A DataSet is an in-memory data store, that can store tables, just like a database. Not only this ,It can hold multiple tables at the same time. DataSets only hold data and do not interact with a Data Source. One of the key characteristics of the DataSet is that it has no knowledge of the underlying Data Source that might have been used to populate it.

By keeping connections open for only a minimum period of time, ADO .NET conserves system resources and provides maximum security for databases and also has less impact on system performance.

DataSet Class Signature


public class DataSet : System.ComponentModel.MarshalByValueComponent, System.ComponentModel.IListSource,  System.ComponentModel.ISupportInitializeNotification, System.Runtime.Serialization.ISerializable,  System.Xml.Serialization.IXmlSerializable  


DataSet Constructors

Constructor Description
DataSet() It is used to initialize a new instance of the DataSet class.
DataSet(String) It is used to initialize a new instance of a DataSet class with the given name.
DataSet(SerializationInfo, StreamingContext) It is used to initialize a new instance of a DataSet class that has the given serialization information and context.
DataSet(SerializationInfo, StreamingContext, Boolean) It is used to initialize a new instance of the DataSet class.

DataSet Properties

Properties Description
CaseSensitive It is used to check whether DataTable objects are case-sensitive or not.
DataSetName It is used to get or set name of the current DataSet.
DefaultViewManager It is used to get a custom view of the data contained in the DataSet to allow filtering and searching.
HasErrors It is used to check whether there are errors in any of the DataTable objects within this DataSet.
IsInitialized It is used to check whether the DataSet is initialized or not.
Locale It is used to get or set the locale information used to compare strings within the table.
Namespace It is used to get or set the namespace of the DataSet.
Site It is used to get or set an ISite for the DataSet.
Tables It is used to get the collection of tables contained in the DataSet.

DataSet Methods

The following table contains some commonly used methods of DataSet.

Method Description
BeginInit() It is used to begin the initialization of a DataSet that is used on a form.
Clear() It is used to clear the DataSet of any data by removing all rows in all tables.
Clone() It is used to copy the structure of the DataSet.
Copy() It is used to copy both the structure and data for this DataSet.
CreateDataReader(DataTable[]) It returns a DataTableReader with one result set per DataTable.
CreateDataReader() It returns a DataTableReader with one result set per DataTable.
EndInit() It ends the initialization of a DataSet that is used on a form.
GetXml() It returns the XML representation of the data stored in the DataSet.
GetXmlSchema() It returns the XML Schema for the XML representation of the data stored in the DataSet.
Load(IDataReader, LoadOption, DataTable[]) It is used to fill a DataSet with values from a data source using the supplied IDataReader.
Merge(DataSet) It is used to merge a specified DataSet and its schema into the current DataSet.
Merge(DataTable) It is used to merge a specified DataTable and its schema into the current DataSet.
ReadXml(XmlReader, XmlReadMode) It is used to read XML schema and data into the DataSet using the specified XmlReader and XmlReadMode.
Reset() It is used to clear all tables and removes all relations, foreign constraints, and tables from the DataSet.
WriteXml(XmlWriter, XmlWriteMode) It is used to write the current data and optionally the schema for the DataSet using the specified XmlWriter and XmlWriteMode.

Ado.Net DataAdapter

In Connection Oriented Data Access, when you read data from a database by using a DataReader object, an open connection must be maintained between your application and the Data Source. And we know, unlike the DataReader, the DataSet is not connected directly to a Data Source through a Connection object when you populate it. Then who opens the connection, execute the command , Fill the DataSet and Closes the Connection.

It is the DataAdapter that manages connections between Data Source and Dataset by fill the data from Data Source to the Dataset and giving a disconnected behavior to the Dataset. Thus, we can say, the DataAdapter acts as a bridge between the Connected and Disconnected Objects. We know every changes that we made on DataSet doesn't reflect in database , it is just stored in Memory and when we are sure we can commit all the changes to the Database by using DataAdapter's Update() method.

DataAdapter Class Signature


   public class DataAdapter : System.ComponentModel.Component, System.Data.IDataAdpter  

DataAdapter Constructors

Constructors Description
DataAdapter() It is used to initialize a new instance of a DataAdapter class.
DataAdapter(DataAdapter) It is used to initializes a new instance of a DataAdapter class from an existing object of the same type.

Methods

Method Description
CloneInternals() It is used to create a copy of this instance of DataAdapter.
Dispose(Boolean) It is used to release the unmanaged resources used by the DataAdapter.
Fill(DataSet) It is used to add rows in the DataSet to match those in the data source.
FillSchema(DataSet, SchemaType, String, IDataReader) It is used to add a DataTable to the specified DataSet.
GetFillParameters() It is used to get the parameters set by the user when executing an SQL SELECT statement.
ResetFillLoadOption() It is used to reset FillLoadOption to its default state.
ShouldSerializeAcceptChangesDuringFill() It determines whether the AcceptChangesDuringFill property should be persisted or not.
ShouldSerializeFillLoadOption() It determines whether the FillLoadOption property should be persisted or not.
ShouldSerializeTableMappings() It determines whether one or more DataTableMapping objects exist or not.
Update(DataSet) It is used to call the respective INSERT, UPDATE, or DELETE statements.

How to work with DataSet and DataAdapter

In our previous articles ,we have discussed about SqlCommand object. When we create an instance of SqlCommand object, we pass in the following 2 parameters to the constructor of the SqlCommand class.
    1. The command that we want to execute
    2. The connection on which we want to execute the command
Along the same lines, when creating an instance of the SqlDataAdapter, we specify
    1. The sql command that we want to execute
    2. The connection on which we want to execute the command

The example shown below
  1. Creates an instance of SqlDataAdapter, passing in the required parameters (SqlCommandText and the Connection object)
  2. Creates an instance of DataSet object.
  3. The Fill() method of the SqlDataAdapter class is then invoked. This method does most of the work. It opens the connection to the database, executes the SQL command, fills the dataset with the data, and closes the connection. Opening and closing connections is handled for us. The connection is kept open only as long as it is needed.
  4. The dataset object, is then set as the DataSource of the GridView1 control
  5. Finally the DataBind() method is called, which binds the data to the control.


   using (SqlConnection con = new SqlConnection("Data Source=SACHIN-PC\\SQLEXPRESS;Initial Catalog=Test;Integrated Security=True"))
   {
    // Create an instance of SqlDataAdapter. Spcify the command and the connection
    SqlDataAdapter dataAdapter = new SqlDataAdapter("select * from Employee", con);
    // Create an instance of DataSet, which is an in-memory datastore for storing tables
    DataSet ds = new DataSet();
    // Call the Fill() methods, which automatically opens the connection, executes the command 
    // and fills the dataset with data, and finally closes the connection.
    dataAdapter.Fill(ds);
    GridView1.DataSource = ds;
    GridView1.DataBind();
  }

Why is it called disconnected when we need connection to fill the data?

Many beginners ask this question to me that when we still need a SqlConnection, even if we are using SqlDataAdapter and DataSet then how does it give disconnected mechanism .

The answer is , we only need connection to fill the DataSet and then there is no need of connection , the DataAdapter automatically closes the connection and after that any change made to DataSet doesn't commit to database until we call Update() method of SqlDataAdapter, thus it gives us a disconnected model.

How to Execute Store procedure using SqlDataAdapter

Let's first create a Store procedure to fetch all employee from Employee's Table of Test database.


  Create procedure spGetEmployee 
  as
  Begin
  Select * from Employee
  End

If you want to execute stored procedure spGetEmployee, using the SqlDataAdapter, just specify the name of the procedure instead of the in-line SQL statement.


  using (SqlConnection con = new SqlConnection("Data Source=SACHIN-PC\\SQLEXPRESS;Initial Catalog=Test;Integrated Security=True"))
  {
    SqlDataAdapter da = new SqlDataAdapter("spGetEmployee ", con);
    da.SelectCommand.CommandType = CommandType.StoredProcedure;
    DataSet ds = new DataSet();
    dataAdapter.Fill(ds);
    GridView1.DataSource = ds;
    GridView1.DataBind();
  }

How to Execute stored procedure with parameters using SqlDataAdapter


   Create procedure spGetEmployeesByDepartment
   @DepartmentId  int
   as
  Begin
  Select * from Employee
  where DepartmentId = @DepartmentId  
  End

To execute stored procedure spGetEmployeesByDepartment, we need to associate parameter @EmployeeId to the SqlDataAdapeter object's SelectCommand as shown below.


   using (SqlConnection con = new SqlConnection("Data Source=SACHIN-PC\\SQLEXPRESS;Initial Catalog=Test;Integrated Security=True"))
    {
    // Create an instance of SqlDataAdapter, specifying the stored procedure
    // and the connection object to use
    SqlDataAdapter da = new SqlDataAdapter("spGetEmployeesByDepartment ", con);
    // Specify the command type is an SP
    da.SelectCommand.CommandType = CommandType.StoredProcedure;
    // Associate the parameter with the stored procedure
    da.SelectCommand.Parameters.AddWithValue("@DepartmentId ", 1);
    DataSet dataset = new DataSet();
    dataAdapter.Fill(dataset);
    GridView1.DataSource = dataset;
    GridView1.DataBind();
   }

Following Table have been used in the above example, Open SQL Server Management Studio and Run below script, it will create a database with the name Test and Two tables Employee and Department and populate them with some Test Data.


    CREATE TABLE [dbo].[Department] (
    [Id]   INT          IDENTITY (1, 1) NOT NULL,
    [Name] VARCHAR (50) NULL,
    CONSTRAINT [PK_Department] PRIMARY KEY CLUSTERED ([Id] ASC)
    );

   CREATE TABLE [dbo].[Employee] (
    [Id]           INT          IDENTITY (1, 1) NOT NULL,
    [Name]         VARCHAR (50) NULL,
    [Salary]       INT          NULL,
    [DepartmentId] INT          NULL,
    CONSTRAINT [PK_Employee] PRIMARY KEY CLUSTERED ([Id] ASC),
    CONSTRAINT [FK_Employee_Department] FOREIGN KEY ([DepartmentId]) REFERENCES [dbo].[Department] ([Id])
  );
  SET IDENTITY_INSERT [dbo].[Department] ON
  INSERT INTO [dbo].[Department] ([Id], [Name]) VALUES (1, N'HR')
  INSERT INTO [dbo].[Department] ([Id], [Name]) VALUES (2, N'Development')
  INSERT INTO [dbo].[Department] ([Id], [Name]) VALUES (3, N'Database')
  INSERT INTO [dbo].[Department] ([Id], [Name]) VALUES (4, N'Business')
  SET IDENTITY_INSERT [dbo].[Department] OFF
  SET IDENTITY_INSERT [dbo].[Employee] ON
  INSERT INTO [dbo].[Employee] ([Id], [Name], [Salary], [DepartmentId]) VALUES (1, N'sachin', 100000, 1)
  INSERT INTO [dbo].[Employee] ([Id], [Name], [Salary], [DepartmentId]) VALUES (2, N'arjun', 23000, 1)
  INSERT INTO [dbo].[Employee] ([Id], [Name], [Salary], [DepartmentId]) VALUES (3, N'vikash', 30000, 2)
  INSERT INTO [dbo].[Employee] ([Id], [Name], [Salary], [DepartmentId]) VALUES (4, N'pocha', 43000, 3)
  INSERT INTO [dbo].[Employee] ([Id], [Name], [Salary], [DepartmentId]) VALUES (5, N'Nivedita', 24000, 4)
  SET IDENTITY_INSERT [dbo].[Employee] OFF
 
How to change the Table Name inside DataSet

If a SQL Query returns two result set then each will be stored as a different Table in DataSet, by default the tables in the DataSet will have table names as Table, Table1, Table2 etc. So if you want to give the tables in the DataSet a meaningful name,we use the TableName property as shown below.


   dataset.Tables[0].TableName = "Employee";
   dataset.Tables[1].TableName = "Department";

These table names can then be used when binding to a GridView control, instead of using the integral indexer, which makes your code more readable, and maintainable.


   GridViewEmployee.DataSource = dataset.Tables["Employee"];
   GridViewEmployee.DataBind();
  GridViewDepartment.DataSource = dataset.Tables["Department"];
  GridViewDepartment.DataBind();

Let's come to the requirement , with which we have started the article , we have to retrieve all Employee table data and display it in a GridView and after modifying some of the details of some employee we have to save the changes in database in one go.

Now, we Know, how does the DataAdapter work and how does it Fill the DataSet, which is an In-Memory store, so it becomes very easy to achieve the requirement. Go through the figure shown below to understand the steps.

DataSet and DataAdapter   Example
DataSet example

Step 1.Retrieve Data from Database and Bind the Gridview.


   private DataSet GetEmployee()
        {
            DataSet ds = new DataSet();
            using (SqlConnection con = new SqlConnection(cs))
            {
                SqlDataAdapter da = new SqlDataAdapter("select * from Employee",con);
                da.Fill(ds);
            }
            Cache["ds"] = ds;
            return ds;
        }
   private void BindGrid()
        {
            if (Cache["ds"] != null)
            {
                GridView1.DataSource = (DataSet)Cache["ds"];
                GridView1.DataBind();
            }
            else
            {
                GridView1.DataSource = GetEmployee();
                GridView1.DataBind();
            }
            
        }

Step 2.Modify Details and Save Changes to DataSet.


     protected void GridView1_RowEditing(object sender, GridViewEditEventArgs e)
        {      
            GridView1.EditIndex = e.NewEditIndex;
            BindGrid();          
        }

        protected void GridView1_RowUpdating(object sender, GridViewUpdateEventArgs e)
        {
            DataSet ds = (DataSet)Cache["ds"];
            DataRow dr = ds.Tables[0].Rows[e.RowIndex];
            dr[1] = e.NewValues[0].ToString();
            dr[2] = e.NewValues[1].ToString();
            dr[3] = Convert.ToInt32(e.NewValues[2]);
            Cache["ds"] = ds;
            GridView1.EditIndex = -1;
            BindGrid();
        }

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

Step 3.On button update click ,commit all changes to database.


   protected void Button2_Click(object sender, EventArgs e)
        {
            using (SqlConnection con = new SqlConnection(cs))
            {
                SqlDataAdapter da = new SqlDataAdapter("select * from Employee", con);

                SqlCommandBuilder builder = new SqlCommandBuilder(da);  
                da.Update((DataSet)Cache["ds"]);
            }
        }

Note:- The DataAdapter's Update() method requires an update command to update changes to database, here we have used SqlCommandBuilder object which automatically build update command based on select command used in DataAdapter.

The complete source code looks like below.

The code behind (aspx.cs)

   public partial class DataSetAndDataAdapter : System.Web.UI.Page
    {
        string cs = "Data Source=SACHIN-PC\\SQLEXPRESS;Initial Catalog=Test;Integrated Security=True";
        protected void Page_Load(object sender, EventArgs e)
        {
            if(!IsPostBack)
         {
             BindGrid();
             }         
        }
        private DataSet GetEmployee()
        {
            DataSet ds = new DataSet();
            using (SqlConnection con = new SqlConnection(cs))
            {
                SqlDataAdapter da = new SqlDataAdapter("select * from Employee",con);
                da.Fill(ds);
            }
            Cache["ds"] = ds;
            return ds;
        }

        private void BindGrid()
        {
            if (Cache["ds"] != null)
            {
                GridView1.DataSource = (DataSet)Cache["ds"];
                GridView1.DataBind();
            }
            else
            {
                GridView1.DataSource = GetEmployee();
                GridView1.DataBind();
            }
            
        }
        protected void GridView1_RowEditing(object sender, GridViewEditEventArgs e)
        {      
            GridView1.EditIndex = e.NewEditIndex;
            BindGrid();         
        }

        protected void GridView1_RowUpdating(object sender, GridViewUpdateEventArgs e)
        {
            DataSet ds = (DataSet)Cache["ds"];
            DataRow dr = ds.Tables[0].Rows[e.RowIndex];
            dr[1] = e.NewValues[0].ToString();
            dr[2] = e.NewValues[1].ToString();
            dr[3] = Convert.ToInt32(e.NewValues[2]);
            Cache["ds"] = ds;
            GridView1.EditIndex = -1;
            BindGrid();
        }

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

        protected void Button2_Click(object sender, EventArgs e)
        {
            using (SqlConnection con = new SqlConnection(cs))
            {
                SqlDataAdapter da = new SqlDataAdapter("select * from Employee", con);

                SqlCommandBuilder builder = new SqlCommandBuilder(da);  
                da.Update((DataSet)Cache["ds"]);
            }
        }
    }

Aspx page

    <form id="form1" runat="server">
    <div>
        <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataKeyNames="Id" 
        OnRowCancelingEdit="GridView1_RowCancelingEdit" OnRowEditing="GridView1_RowEditing" OnRowUpdating="GridView1_RowUpdating">
            <Columns>
                <asp:CommandField 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="Salary" HeaderText="Salary" SortExpression="Salary" />
                <asp:BoundField DataField="DepartmentId" HeaderText="DepartmentId" SortExpression="DepartmentId" />
            </Columns>
        </asp:GridView>  
        <br />  
        <asp:Button ID="Button2" runat="server" Text="Updata Database" OnClick="Button2_Click" />
    </div>
    </form>

Now, Run the application and Modify some records, on Update button click , you will be successfully able to save changes to the database at one go.