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:-
• Connected architecture or connection Oriented Data Access Architecture and
• Disconnected architecture or disconnected Data Access Architecture.
In Connection Oriented Architecture the application establishes a connection to the Data Source and then interact with it through SQL requests using that connection. The problem is the application is always connected to the database, even when it is not using any Database Operation.
On the other hand, with the disconnected approach we don't need 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
You can think of a DataSet as an in-memory data store, that stores tables, just like a database does. A DataSet can store multiple tables at the same time. DataSets only hold data but do not interact with the data source , it has no idea about the data source , it is the SqlDataAdapter that does all the connection related work.
With DataSet the connections are opened only for a certain amount of time as long as the DataSet needs it, then the DataAdapter closes the connection and in this way it provides security to database and along the same line reduces the impact on server
DataSet Class Signature
public class DataSet : System.ComponentModel.MarshalByValueComponent, System.ComponentModel.IListSource, System.ComponentModel.ISupportInitializeNotification, System.Runtime.Serialization.ISerializable, System.Xml.Serialization.IXmlSerializable
Ado.Net DataAdapter
In Connection Oriented Data Access we use DataReader that requires an open and active connection to the Data Source but unlike the DataReader, the DataSet is not connected directly to a Data Source through a Connection object when we populate it. Then who opens the connection, execute the command , Fill the DataSet and Closes the Connection.
It is the DataAdapter that manages the connection between Data Source and Dataset and fill the data from Data Source to the Dataset. In short the SqlDataAdapter is the one that gives 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.
How to work with DataSet and DataAdapter
In our previous articles ,we have discussed about SqlCommand object. We know to create an instance of SqlCommand object, we need to pass in 2 parameters to the constructor of the SqlCommand class.
1. First, the command (T-Sql) that we want to execute
2. The SqlConnection object that specifies on which data source we want to execute the command
Along the same lines, an instance of the SqlDataAdapter, requires
1. The sql command or T-Sql that we want to execute
2. The connection object that specifies the connection on which we want to execute the command.
In The example shown below
1. First, we are Creating an instance of SqlDataAdapter,where we are passing in the required parameters like SqlCommandText and the Connection object
2. Then, we are creating an instance of the DataSet object.
3. Finally, the Fill() method of the SqlDataAdapter class is being invoked. The Fill() method does most of the work like opening the connection to the database, executing the SQL command, filling the dataset with the data, and then closing the connection. Opening and closing of connections are handled for us. The DataAdapter keeps the connection open only as long as it is needed.
4. The dataset object, is then assigned as DataSource of the GridView1 control
5. Finally we are calling DataBind() method that binds the data to the GridView control.
using (SqlConnection con = new SqlConnection("Data Source=SACHIN-PC\\SQLEXPRESS;Initial Catalog=Test;Integrated Security=True"))
{
SqlDataAdapter da= new SqlDataAdapter("select * from Employee", con);
DataSet ds = new DataSet();
da.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"))
{
SqlDataAdapter da = new SqlDataAdapter("spGetEmployeesByDepartment ", con);
da.SelectCommand.CommandType = CommandType.StoredProcedure;
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.
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.