Connected and Disconnected architecture in Ado.net

The architecture followed by Ado.net for the connectivity with the database is categorized into two modes:-

• Connected Architecture
• Disconnected Architecture

Types of Architecture

Ado.net is both connection-oriented as well as disconnection oriented. Depending upon the functionality of an application, we can make it connection-oriented or disconnection oriented. We can even use both the modes together in a single application.

1. Connected Architecture

• As the name suggests, connected architecture refers to the fact that the connection is established for the full time between the database and application. For e.g. we make a program in C# that is connected with the database for the full time, so that will be connected architecture.
• Connected architecture is forward only and read-only. This means the connected mode will work only in one particular direction i.e. forward and that too for read-only purpose. Application issues query then read back results and process them.
• For connected architecture, we mainly use the object of the DataReader class.
• DataReader is used to retrieve the data from the database and it also ensures that the connection is maintained for the complete interval of time.
• In connected architecture, the application is directly linked with the Database.

   DataReader in Connected architecture

• DataReader class is used to read the data from the database. It works in forward only and reads the only mode and requires the connection for the complete time. That is why we use it in connected architecture.
• The forward only feature makes it an efficient way to read data. Thus we can say, DataReader is connection-oriented and requires an active connection while reading the data.

In order to make an object of the DataReader class, we never use the new keyword instead we call the ExecuteReader() of the command object. For e.g.

SqlCommand cmd= new SqlCommand(“Select * from Table”);

SqlDatareader rdr=cmd.ExecuteReader(cmd);

  Here cmd.ExecuteReader() executes the command and creates the instance of DataReader class and loads the instance with data. Therefore, we do not use the ‘new’ keyword.

 2.  Disconnected Architecture

• Disconnected architecture refers to the mode of architecture in Ado.net where the connectivity between the database and application is not maintained for the full time. Connectivity within this mode is established only to read the data from the database and finally to update the data within the database.
• This means during the processing of the application, we need data so that data is fetched from the database and kept in temporary tables. After that whenever data is required, it is fetched from the temporary tables. And finally, when the operations were completed, the connection was established to update the data within the database from the temporary tables.
• In this mode, application issues query then retrieves and store results for processing. For this purpose, we use objects of SqlDataAdapter and DataSet classes.
• In disconnected architecture, a Dataset is used for retrieving data from the database. This way there is no need to establish a connection for the full time because DataSet acts as temporary storage. All the operations can be performed on the data using the Dataset and finally modified at the database.

DataAdapter in Disconnected architecture

• DataAdapter class acts as an interface between application and database. It provides the data to the Dataset which helps the user to perform the operations and finally the modifications are done in the Dataset which is passed to the DataAdapter which updates the database. DataAdapter takes the decision for the establishment and termination of the connection.
• DataAdapter is required for connectivity with the database. DataAdapter established a connection with the database and fetches the data from the database and fill it into the Dataset. And finally, when the task is completed it takes the data from the DataSet and updates it into the database by again establishing the connection.

• It can be said that DataAdapter acts as a mediator between the application and database which allows the interaction in disconnected architecture.

For example:-

 public DataTable GetTable(string query)
          {

            SqlDataAdapter adapter = new SqlDataAdapter(query, ConnectionString);
            DataTable Empl = new DataTable();
            adapter.Fill(Empl);
            return Empl;

          }

In the above lines of code, the object of the SqlDataAdapter is responsible for establishing the connection. It takes query and ConnectionString as a parameter. The query is issued on the database to fetch the data. ConnectionString allows connectivity with the database. The fill() of the SqlDataAdapter class adds the Table.