Thursday 27 June 2013

ADO.NET Connected & Disconnected Architecture

ADO.NET Connected & Disconnected Architecture



ADO.NET is a middleware technology used to connect front end and back end of .NET applications.
ASP.NET is used to create front end of applications using a code behind language like C#.NET.
Most of the applications require database as back end to store application information.
To connect front end and back end ADO.NET  is used.

It contains two types of architectures.
1. Connected architecture.
2. Disconnected architecture.
Two namespaces required to work with ADO.NET. They are
System.Data.SqlClient
System.Data
SqlConnection class is used to create connection object containing server, database and authentication details in connection string.
SqlCommand class is used to create command object which stores sql query which can be executed.

ADO.NET Connected architecture

In ADO.NET Connected architecture connection to the database has to be in opened state to access the database.
In Connected architecture connection string is used to connect to the database.
Connection should be opened and closed.
Commands are executed using methods like
MethodCommandsReturn type
ExecuteNonQueryInsert,Update,Delete,...int
ExecuteReaderSelectSqlDataReader
ExecuteScalarAggregate commandsObject

ExecuteNonQuery method is used to execute commands and return number of rows effected.
ExecuteReader method is used to execute select command that retreives data and stores it in SqlDataReader which is capable of reading data row by row. SqlDataReader is read only and it reads in sequential order only.
Read( ) method of SqlDataReader is used to read data row by row and it returns bool value.
ExecuteScalar method is used to execute sql command that returns one value as its return type is object.
If ExecuteScalar is used to execute select query that retreives table data. It can return only first row first column cell value.

ADO.NET Disconnected architecture

In ADO.NET Disconnected architecute data is retreived from database and stored in dataset.
Data in dataset can be accessed even when the connection to the database is closed.
SqlDataAdapter is used to open the connection ,execute the command, store data in dataset and close the connection.
DataSet is capable of storing more than one table.
DataSet is used to store data on client side.

da.fill(ds);
The above line of code is used to execute the command by opening the connection to store data in dataset and closing the connection.

Example to insert values into database table on button click in ASP.NET page

First create a database name adotask.
Create a table named tbl_register with two columns username -varchar(50), emailid - varchar(50)

Open visual studio
Create empty website
Create a webpage
Add two textboxes and two buttons.
Under first button click event write ADO.NET Connected architecture code and under second button write ADO.NET disconnected architecture code to insert the values in textboxes into database table.

ado.netConnectedDisconnectedArchitecture

ASP.NET Source code

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default2.aspx.cs" Inherits="Default2" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
   
        username<asp:TextBox ID="TextBox1" runat="server"></asp:TextBox>
        <br />
        emailid&nbsp;&nbsp;&nbsp;
        <asp:TextBox ID="TextBox2" runat="server"></asp:TextBox>
        <br />
        <asp:Button ID="Button1" runat="server" onclick="Button1_Click"
            Text="Register" />
        <asp:Button ID="Button2" runat="server" onclick="Button2_Click"
            Text="Register 2" />
   
    </div>
    </form>
</body>
</html>

CSharp.NET Code

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

using System.Data.SqlClient;
using System.Data;

public partial class Default2 : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {

    }
    protected void Button1_Click(object sender, EventArgs e)
    {
        //connected architecture
     
        //connection string - to which database you have to connect
        SqlConnection con = new SqlConnection("Data Source=SUDHIRCHEKURI;Initial Catalog=adotask;Integrated Security=True");
        //command - sql query to execute
        SqlCommand cmd = new SqlCommand("insert into tbl_register values('"+TextBox1.Text +"','"+TextBox2.Text+"')", con);
        //open connection
        con.Open();
        //execute command
        int i = cmd.ExecuteNonQuery(); //to execute insert command
        if (i > 0)
        {
            Response.Write("<script>alert('Successfully registered');</script>");
        }
        //close connection
        con.Close();
    }
    protected void Button2_Click(object sender, EventArgs e)
    {
        //disconnected architecture

        //connection string - to which database you have to connect
        SqlConnection con = new SqlConnection("Data Source=SUDHIRCHEKURI;Initial Catalog=adotask;Integrated Security=True");
        //command - sql query to execute
        SqlCommand cmd = new SqlCommand("insert into tbl_register values('" + TextBox1.Text + "','" + TextBox2.Text + "')", con);
        //dataset
        DataSet ds = new DataSet();
        //sqldata adapter to open connection- execute cmd - store data in dataset - close connection  
        SqlDataAdapter da=new SqlDataAdapter (cmd);
        da.Fill(ds);
        Response.Write("successfully inserted");
    }
}

No comments:

Post a Comment