Disconnected Architecture in ADO.NET
The architecture of ADO.net in which data retrieved from database can be accessed even when connection to database was closed is called as disconnected architecture. Disconnected architecture of ADO.net was built on classes connection, dataadapter, commandbuilder and dataset and dataview.
Disconnected architecture is a method of retrieving a record set from the database and storing it giving you the ability to do many CRUD (Create, Read, Update and Delete) operations on the data in memory, then it can be re-synchronized with the database when reconnecting. A method of using disconnected architecture is using a Dataset.
DataReader is Connected Architecture since it keeps the connection open until all rows are fetched one by one
DataSet is DisConnected Architecture since all the records are brought at once and there is no need to keep the connection alive
Example :
Example
Create Database “Student”
CREATE TABLE [dbo].[Student]
(
[ID] [int] PRIMARY KEY IDENTITY(1,1) NOT NULL,
[Name] [varchar](255) NULL,
[Age] [int] NULL,
[Address] [varchar](255) NULL
)
INSERT INTO Student([Name],[Age],[Address])VALUES('NAME 1','22','PUNE')
INSERT INTO Student([Name],[Age],[Address])VALUES('NAME 2','25','MUMBAI')
INSERT INTO Student([Name],[Age],[Address])VALUES('NAME 3','23','PUNE')
INSERT INTO Student([Name],[Age],[Address])VALUES('NAME 4','21','DELHI')
INSERT INTO Student([Name],[Age],[Address])VALUES('NAME 5','22','PUNE')
HTML
<html xmlns="http://www.w3.org/1999/xhtml">
<head id="Head1" runat="server">
<title>Untitled Pagetitle>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:GridView ID="GridView1" runat="server" BackColor="White" BorderColor="#CC9966"
BorderStyle="None" BorderWidth="1px" CellPadding="4">
<FooterStyle BackColor="#FFFFCC" ForeColor="#330099" />
<RowStyle BackColor="White" ForeColor="#330099" />
<PagerStyle BackColor="#FFFFCC" ForeColor="#330099" HorizontalAlign="Center" />
<SelectedRowStyle BackColor="#FFCC66" Font-Bold="True" ForeColor="#663399" />
<HeaderStyle BackColor="#990000" Font-Bold="True" ForeColor="#FFFFCC" />
</asp:GridView>
<br />
<asp:Button ID="Connected" runat="server" OnClick="Connected_Click" Text="Connected" />
<asp:Button ID="Disconnected" runat="server" EnableTheming="False" OnClick="Disconnected_Click"
Text="Disconnected" />
</div>
</form>
</body>
</html>
.cs
String StrSQL = "", StrConnection = "";
protected void Page_Load(object sender, EventArgs e)
{
StrSQL = "SELECT * FROM Student";
StrConnection = "Data Source=ServerName;Initial Catalog=Database;User ID=Username;Password=password";
}
protected void Disconnected_Click(object sender, EventArgs e)
{
SqlDataAdapter objDa = new SqlDataAdapter();
DataSet objDs = new DataSet();
using (SqlConnection objConn = new SqlConnection(StrConnection))
{
SqlCommand objCmd = new SqlCommand(StrSQL, objConn);
objCmd.CommandType = CommandType.Text;
objDa.SelectCommand = objCmd;
objDa.Fill(objDs, "Student");
GridView1.DataSource = objDs.Tables[0];
GridView1.DataBind();
}
}