All About Adodotnet

Connected and disconnected architecture in ADO.Net with Example

Disconnected Architecture in ADO.NET

http://www.programcall.com/3/adonet/disconnected-architecture-in-adonet.aspx

he 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.





Connection : Connection object is used to establish a connection to database and connectionit self will not transfer any data.

DataAdapter : DataAdapter is used to transfer the data between database and dataset. It has commands like select, insert, update and delete. Select command is used to retrieve data from database and insert, update and delete commands are used to send changes to the data in dataset to database. It needs a connection to transfer the data.

CommandBuilder : by default dataadapter contains only the select command and it doesn’tcontain insert, update and delete commands. To create insert, update and delete commands for the dataadapter, commandbuilder is used. It is used only to create these commands for the dataadapter and has no other purpose.

DataSet : Dataset is used to store the data retrieved from database by dataadapter and make it available for .net application.

To fill data in to dataset fill() method of dataadapter is used and has the following syntax.

Da.Fill(Ds,”TableName”); 

When fill method was called, dataadapter will open a connection to database, executes select command, stores the data retrieved by select command in to dataset and immediately closes the connection. 

As connection to database was closed, any changes to the data in dataset will not be directly sent to the database and will be made only in the dataset. To send changes made to data in dataset to the database, Update() method of the dataadapter is used that has the following syntax.

Da.Update(Ds,”Tablename”); 

When Update method was called, dataadapter will again open the connection to database, executes insert, update and delete commands to send changes in dataset to database and immediately closes the connection. As connection is opened only when it is required and will be automatically closed when it was not required, this architecture is called disconnected architecture. 

A dataset can contain data in multiple tables. 

DataView : DataView is a view of table available in DataSet. It is used to find a record, sort the records and filter the records. By using dataview, you can also perform insert, update and delete as in case of a DataSet.

Connected Architecture of ADO.NET

The architecture of ADO.net, in which connection must be opened to access the data retrieved from database is called as connected architecture. Connected architecture was built on the classes connection, command, datareader and transaction. 

Connection : in connected architecture also the purpose of connection is to just establish aconnection to database and it self will not transfer any data. 

 DataReader : DataReader is used to store the data retrieved by command object and make it available for .net application. Data in DataReader is read only and within the DataReader you can navigate only in forward direction and it also only one record at a time. 

To access one by one record from the DataReader, call Read() method of the DataReader whose return type is bool. When the next record was successfully read, the Read() method will return true and otherwise returns false.

ADO.NET Dataset vs DataReader

DataSet and DataReader are called fundamental objects of ADO.net as they are used to store data and make it available for .net application and they have the following differences.

Differences Between DataSet and DataReader

DataSet	DataReader
It  is  disconnected  object  and  can  provide	It is connected object and can not provide
access  to  data  even  when  connection  to	access to data when connection to database
database was closed.	was closed.
It can store data from multiple tables	It can store data from only one table.
It allows insert, update and delete on data	It  is  read  only  and  it  doesn’t  allow  insert,
	update and delete on data.
It  allows  navigation  between  record  either	It  allows  only  forward  navigation  that  also
forward or backward.	only to immediate next record.
It can contain multiple records.	It can contain only one record at a time.
All  the  data  of  a  dataset  will  be  on  client	All  the  data  of  a  DataReader  will  be  on
system.	server and one record at a time is retrieved
	and stored in datareader when you call the
	Read() method of datareader.

Command Object in ADO.NET

Command : Command is used to execute almost any SQL command from within the .net application. The SQL command like insert, update, delete, select, create, alter, drop can be executed with command object and you can also call stored procedures with the command object. Command object has the following important properties.

• Connection : used to specify the connection to be used by the command object.
• CommandType : Used to specify the type of SQL command you want to execute. To assign a value to this property, use the enumeration CommandType that has the members Text, StoredProcedure and TableDirect. Text is the default and is set when you want to execute ant SQL command with command object. StoredProcedure is set when you want to call a stored procedure or function andTableDirect is set when you want to retrieve data from the table directly by specifying the table name without writing a select statement.
• CommandText : Used to specify the SQL statement you want to execute.
• Transaction : Used to associate a transaction object to the command object so that the changes made to the database with command object can be committed or rollback.

Command object has the following important methods.

• ExecuteNonQuery() : Used to execute an SQL statement that doesn’t return any value like insert, update and delete. Return type of this method is int and it returns the no. of rows effected by the given statement.
• ExecuteScalar() : Used to execute an SQL statement and return a single value. When the select statement executed by executescalar() method returns a row and multiple rows, then the method will return the value of first column of first row returned by the query. Return type of this method is object.

• ExecuteReader() : Used to execute a select a statement and return the rows returned by the select statement as a DataReader. Return type of this method is DataReader.

What are the usages of the Command object in ADO.NET?

The following are the usages of the Command object in AD0.NET: The Command object in AD0.NET executes a command against the database and retrieves a DataReader or DataSet object. * It also executes the INSERT, UPDATE, or DELETE command against the database. * All the command objects are derived from the DbCommand class. * The command object is represented by two classes: SqlCommand and OleDbCommand. * The Command object provides three methods to execute commands on the database: o The ExecuteNonQuery() method executes the commands and does not return any value. o The ExecuteScalar() method returns a single value from a database query. o The ExecuteReader() method returns a result set by using the DataReader object.

http://helpingdotnet.blogspot.in/2011/10/connected-and-disconnected-architecture.html

Connected Architecture of ADO.NET

The architecture of ADO.net, in which connection must be opened to access the data retrieved from database is called as connected architecture. Connected architecture was built on the classes connection, command, datareader and transaction.

Connected architecture is when you constantly make trips to the database for any CRUD (Create, Read, Update and Delete) operation you wish to do. This creates more traffic to the database but is normally much faster as you should be doing smaller transactions.

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

Difference between Connected and disconnected architecture

Connected	Disconnected
It is connection oriented.	It is dis_connection oriented.
Datareader	DataSet
Connected methods gives faster performance	Disconnected get low in speed and performance.
connected can hold the data of single table	disconnected can hold multiple tables of data
connected you need to use a read only forward only data reader	disconnected you cannot
Data Reader can't persist the data	Data Set can persist the data
It is Read only, we can't update the data.	We can update data

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 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>

Code Behind

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 Connected_Click(object sender, EventArgs e)

{

using (SqlConnection objConn = new SqlConnection(StrConnection))

{

SqlCommand objCmd = new SqlCommand(StrSQL, objConn);

objCmd.CommandType = CommandType.Text;

objConn.Open();

SqlDataReader objDr = objCmd.ExecuteReader();

GridView1.DataSource = objDr;

GridView1.DataBind();

objConn.Close();

}

}

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();

}

}

Download

Download code here..