In Technologies
4
239

Every application which deals with database needs to be connected with it and maybe your next question is What, How and in which way?

So we work in two different ways with database connectivity and which deal with dynamic data needs to connect with database and there are many ways to get connected with a database. So in this blog, I am going describe you “How To Work On Connected Disconnected Environment“. I am a .NET developer so I’ll explain the ways of doing this in .NET.

As the number of users accessing application increases to access same data simultaneously which may cause network congestion and this will lead the application to crash because of data transfer between the client application and the data source.

For this kind of situation, .Net introduce ADO in which there are both connected and disconnected environment and both types of connection is a difference just on the basis of connection made to the database. Here’s a diagram of both environment to give guys an overview.

Connected And Disconnected Environment

Overview

The ADO.NET environment categorized into connected and disconnected environments. A connected environment in ADO needs a continuously open connection for transferring data and information between the application and the database until transfer completed or “connection.close()”.

Meanwhile, in disconnected ADO environment transfer data or information and do update database table without a continuously open connection on the network, its only get in touch with a database when needed and also do the opening and closing the connection by itself or automatically.

Connected Environment

In this environment, our code is always in connected phase continuously with a database. Some of the class is fully responsible for connection and action performed on data sources like Connection, Command, DataReader and Transaction.

Whenever a continuous trip to the database on a purpose CRUD (Create, Read, Update and Delete) operation you want to do, it generally creates more traffic to the database but is normally much faster as you should be doing smaller transactions. They include Data Reader/ Command/ Connection Transaction/ Parameter Collection/ Parameter classes.

Few Queries that are used to get data from the database under connected environment:

1. ExecuteReader (To get a set of data from a table)

using (SqlConnection connection = new SqlConnection(connectionString))
{
    connection.Open();
    SqlCommand command = new SqlCommand("Select * from TableName", connection);
    SqlDataReader reader = command.ExecuteReader();
    while (reader.Read())
    {
      Console.WriteLine(String.Format("{0}", reader[0]));
    }
}

2. ExecuteScalar (To get the single value from a database, below query we are getting the autogenerated ID)

string sql ="INSERT INTO dbo.TableName (Name) VALUES (@Name);"+ "SELECT CAST(scope_identity() AS int)";
using (SqlConnection conn = new SqlConnection(connString))
{
   SqlCommand cmd = new SqlCommand(sql, conn);
   cmd.Parameters.Add("@Name", SqlDbType.VarChar);
   cmd.Parameters["@name"].Value = newName;
   try
   {
     conn.Open();
     ID = (Int32)cmd.ExecuteScalar();
   }
   catch (Exception ex)
   {
     Console.WriteLine(ex.Message);
   }
}

3. ExecuteNonQuery

using (SqlConnection connection = new SqlConnection(connectionString))
{
    queryString = "Select * from Employee where Deparment='engineer'";
    SqlCommand command = new SqlCommand(queryString, connection);
    command.Connection.Open();
    command.ExecuteNonQuery();
}

Disconnected Environment

In a disconnected environment, we can retrieve data from the data source and manipulate as per need and later reconnect with the data source to update the existing data it’s not a continuously connected with the database.

Some of the classes of ADO disconnected environment is the key or having the main feature which helps us to keep performing an operation on data in client side and update that data in the database whenever it’s needed.

Types of Objects in ADO.NET Disconnected architecture

The ADO.NET Disconnected architecture considers primarily the following types of objects:

DataSet

A DataSet is on memory store data and it can hold many numbers of tables and also didn’t interact with data source directly.

DataSet ds = new DataSet();

Initialization of the DataSet doesn’t require parameters as the constructor of DataSet don’t have parameters.

SqlDataAdapter

It’s the main part of disconnected environment because it manages the connection with the database only when it’s required and close the connection when a task is performed or when it’s not required. SqlDataAdapter also fills the dataset after fetching data from data source.

The SqlDataAdapter holds the SQL commands and connection object to read and write data.

SqlDataAdapter da = new SqlDataAdapter("select CustomerID, CompanyName from Customers", conn);
da.Fill(ds, "Customers");

The code above creates a new SqlDataAdapter, da and also a SQL statement specifies what data will be read/write into a DataSet. The connection object, conn is already been instantiated, but not opened. It is the SqlDataAdapter’s responsibility to open and close the connection during Fill and Update method calls.

SqlConnection

A connection string which we generally initialized with con is passed in parameter of SqlDataAdapter da in a disconnected environment and SqlCommandBuilder in a connected environment that is passed in parameter of a data provider which connect with the database by the help of it, and a connection string contains server-name, database-name, user-name, password etc.

SqlConnection conn = new SqlConnection("Server=(local);DataBase=Northwind;Integrated Security=SSPI");

The code above creates a new SqlConnection conn contains the local as a server name which localhost with windows authentication and database name is Northwind.

SqlCommandBuilder

A CommandBuilder helps to generate an update, delete and insert commands on a single table for a SqlDataAdapter. Similar to other objects, each data provider has a command builder class.

SqlCommandBuilder cmd = new SqlCommandBuilder(da);

Implementation of Disconnected Environment

using System;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.Windows.Forms;

class DisconnectedDataform : Form
{
   private SqlConnection conn;
   private SqlDataAdapter daCustomers;

   private DataSet dsCustomers;
   private DataGrid dgCustomers;

   private const string tableName = "Customers";

   // initialize form with DataGrid and Button
   public DisconnectedDataform()
   {
     // fill dataset
     Initdata();

     // set up datagrid
     dgCustomers = new DataGrid();
     dgCustomers.Location = new Point(5, 5);
     dgCustomers.Size = new Size(this.ClientRectangle.Size.Width - 10, this.ClientRectangle.Height - 50);
     dgCustomers.DataSource = dsCustomers;
     dgCustomers.DataMember = tableName;

     // create update button
     Button btnUpdate = new Button();
     btnUpdate.Text = "Update";
     btnUpdate.Location = new Point(this.ClientRectangle.Width/2 - btnUpdate.Width/2, this.ClientRectangle.Height - (btnUpdate.Height + 10));
     btnUpdate.Click += new EventHandler(btnUpdateClicked);

     // make sure controls appear on form
     Controls.AddRange(new Control[] { dgCustomers, btnUpdate });
   }

   // set up ADO.NET objects
   public void Initdata()
   {
     // instantiate the connection
     conn = new SqlConnection("Server=(local);DataBase=Northwind;Integrated Security=SSPI");
 
     // 1. instantiate a new DataSet
     dsCustomers = new DataSet();

     // 2. init SqlDataAdapter with select command and connection
     daCustomers = new SqlDataAdapter("select CustomerID, CompanyName from Customers", conn);

     // 3. fill in insert, update, and delete commands
     SqlCommandBuilder cmdBldr = new SqlCommandBuilder(daCustomers);
 
     // 4. fill the dataset
     daCustomers.Fill(dsCustomers, tableName);
   }

     // Update button was clicked
   public void btnUpdateClicked(object sender, EventArgs e)
   {
     // write changes back to DataBase
     daCustomers.Update(dsCustomers, tableName);
   }

     // start the Windows form
   static void Main()
   {
      Application.Run(new DisconnectedDataForm());
   }
}

It is a wide topic so we can not understand it within a blog. It consists of different approaches that help to achieve the desired task, so we will discuss them later.

I hope this blog will help to get some basic idea of working in .NET both connected and disconnected environment to develop web applications. Feel free to comment your queries in the comments section below.

RECOMMENDED POSTS

Start typing and press Enter to search