Tuesday, May 7, 2013

Connected and Disconnected Mode in ADO.NET

Introduction

This article describes you how to retrieve tables data from Sql Database in your ASP.NET applications with both "Connected" and "Disconnected" Mode and this article also clarify you which one is better in desire situation. Firstly going with What is Connected and Disconnected Mode, and After it I will write a code which help you to retrieve tables data from the Sql database in both mode.

Connected Mode

  1. Connected mode is connection oriented.
  2. In Connection mode, we read data from a database by using a DataReader object.
  3. Connected mode methods provide faster performance.
  4. Connected mode can hold the data of single table.
  5. Connected mode is read only, we can't update the data.
Disconnected mode
  1. Disconnected mode is disconnected connection oriented.
  2. In Disconnection mode, we read data from a database by using a DataSet object.
  3. Disconnection mode get low in speed and performance.
  4. Disconnection mode can hold multiple tables of data.
  5. we can perform all option as like update, insert, delete etc.


Why we use Disconnected mode

In Disconnected mode, we are used DataSet for retrieving data from database. so we are not need to maintaining the connection also.  we can be performed all the operations with the data. It "wont cause traffic problem" while working with database.

Example

In this example, i will explain how to work with connected and disconnected mode in ADO.NET.

Program Code

Connection_Mode.aspx


<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Connection_Mode.aspx.cs" Inherits="Connection_Mode" %>
 
<!DOCTYPE html>
 
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
</head>
<body>
    <form id="form1" runat="server">
   <h3 style="font-weight: bold; color: #0000FF;">Connected and Disconnected Mode in ASP.NET</h3>
        <div>
            <asp:Button ID="Connected" runat="server" Text="Connected Mode" Font-Bold="True" OnClick="Button1_Click1" />
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
            <asp:Button ID="Disconneted" runat="server" Text="Disconnected Mode" Font-Bold="True" OnClick="Disconneted_Click" />
        </div>
        <div>
            <br />
            <asp:GridView ID="gdview" runat="server" HeaderStyle-BackColor="#669999" HeaderStyle-Font-Bold="true" HeaderStyle-ForeColor="White" >
            </asp:GridView>
        </div>
        <p>
            &nbsp;</p>
    </form>
</body>
</html>

Connection_Mode.aspx.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
 
public partial class Connection_Mode : System.Web.UI.Page
{
    SqlConnection con;
    SqlCommand cmd;
    DataTable dt;       // Use for Connected mode
    SqlDataReader dr;
 
    SqlDataAdapter da;  // Use for Disconnected Mode
    DataSet ds;

    protected void Page_Load(object sender, EventArgs e)
    {
    }
    protected void Button1_Click1(object sender, EventArgs e)
    {
 
        // Connected Mode
        con = new SqlConnection(ConfigurationManager.ConnectionStrings["constr"].ConnectionString);
        con.Open();
        cmd = new SqlCommand("select * from LoginDetail", con);
        dr = cmd.ExecuteReader();
        dt = new DataTable();
        dt.Load(dr);
        gdview.DataSource = dt;
        gdview.DataBind();
        con.Close();
    }
 
    protected void Disconneted_Click(object sender, EventArgs e)
    {
 
        // Disconneted Mode
        using (con = new SqlConnection(ConfigurationManager.ConnectionStrings["constr"].ConnectionString))
        {
            using (da = new SqlDataAdapter("select * from EmpSalary_Info", con))
            {
                ds = new DataSet();
                da.Fill(ds);
                gdview.DataSource = ds.Tables[0];
                gdview.DataBind();
            }
        }
    }
}

Output 1

 




Output 2

Click on Connected button

Output 3

Click on Disconnected button