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.
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.
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.
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
In Connected architecture connection string is used to connect to the database.
Connection should be opened and closed.
Commands are executed using methods like
| Method | Commands | Return type |
|---|---|---|
| ExecuteNonQuery | Insert,Update,Delete,... | int |
| ExecuteReader | Select | SqlDataReader |
| ExecuteScalar | Aggregate commands | Object |
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.
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);
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.
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.
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
<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>
<!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
<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");
}
}
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");
}
}
