UNIT-III

Architecture of ADO.NET
Connected and Disconnected Database
Create Connection using ADO.NET Object Model
Connection Class
Command Class
DataAdapter Class
Dataset Class
Database Accessing on web applications:
Data Binding concept with web Creating
data grid
Binding standard web server controls
Display data on web form using Data bound controls.
Architecture of ADO.NET:
The full form of ADO.NET is ActiveX Data Object.Net. Basically it is container of all the
standard classes which are responsible for database connectivity of .net application to the
any kind of third party database software like Ms Sql Server, Ms Access, MySql, Oracle or
any other database software.
All classes belongs to ADO.NET are defined and arrange in “System.Data” namespace.
The architecture of ADO.NET is following.

From above diagram it is clear that ADO.NET contain following important

components.
1. Connection
2. Command
3. DataReader
4. DataAdapter
5. Dataset
1. Connection: This component of Ado.net is responsible to connect our
.net application with any data source like Ms sql server database.
2. Command: This component contain classes for executing any INSERT,
UPDATE, DELETE AND SELECT command over database software using
active connection.
3. DataReader: This component contains classes that capable to hold
reference of records that retrieved by Command class after executing
SELECT Query.
4. Data Adapter: This component is capable to perform the entire task like
executing INSERT, UPDATE, DELETE and SELECT command on give
Connection. The most important use of this component is executing
SELECT query for a number of records and fill up to Dataset components.
5. Dataset: This component is the main source of records for .net control like
GridView. It has capability to generate a number of tables to hold records
retrieved from DataAdapter or XML.

Connection String and Connection Class:

The first and essential part for data communication between .NET application
and any database software like Ms Sql Server is connection establishments
between them.
The connection will established using driver name, data source file name and
security options that enclosed in string formats called connection string. It
means right connection string is only responsible for establishing connection
with required database file.
In ASP.NET, connection string should be written in web.config file under root
directory of web site so that such connection string can be available to all code
behind of web forms.
Let database.mdf is a Ms sql server database file created using inbuilt tools
of Visual studio 2005. Then the Format of connection string is written in
web.config file as following.
<connectionStrings>
<add name="constr"
connectionString="Data Source= .\SQLEXPRESS;
AttachDbFilename=|DataDirectory|\Database.mdf; Integrated
Security=True ;
User Instance=true"/>
</connectionStrings>

Connection String can be copy and paste from server explorer🡪Select

database🡪property window🡪copy conection string.
In this web.config file connection string is enclosed in XML tag format.

Getting connection string on code behind:

Following namespace must be included in code behind of every page to get
connection string.

using System.Configuration;

After then we declare a string variable to store connection string that getting
from web.config file. It is written as.
string constr1= ConfigurationManager.
ConnectionStrings["constr"]. ConnectionString;
such connection string will enable connection with “Database.mdf” file that
created using MS-SQL Server database software.

Establishing Connection:
We need to connect our .NET application with required database file before
data communication. For this operation the connection must be opened and
connection will be open by Connection Class provided by ADO.NET. To
estblising connection with different database file, we need Connection Class.
Working with Connection Class:
Step 1: Create a connection string in web.config file.
Step 2: Include namespace: System.Data.SqlClient.
Step 3: Get Connection String in required Code Behind.
Step 4: Create Object of Connection Class and pass connection string to this
object.
Step 5: Open connection.
Step 6: Perform data access operation.
Step 7: Close active connection.
Above steps can be implemented as.
Namespace: If we want to connect a Database.mdf file that created under
MS-Sql Server Software then Code Behind must included following
namespace.
using System.Data.SqlClient;

Connection Class:
This namespace support following Connection Class to establishing
connection.
SqlConnection
Before establishing connection, we need to create an connection object and
passing connection string to the constructor of Connection class like this-
SqlConnection con= new SqlConnection(constr1);
here:
con = Connection object
constr1= a string variable that contain Connection String
Opening Connection: When Connection Object is created then by calling
Open() method of Connection Class, we can create an active connection.
Like-
con.Open();
When this method executes then connection will be establised with required
database file.
Closing Connection: When all data communication has been performed over
this active connection, then connection must be dis connected so that
connection object can be further used with same database file or different
database file. Like-
con.Close();

Command Class:
This is one of component of ADO.NET. With the help of this componenet we
can assign SQL command and execute on database software using active
connection.
SqlCommand
It is command class for MS-SQL Server Database.
Working process with Command Class:( for MS-SQL Server database)
Step1: Include ADO.NET namespace in code behind.
Ex: using System.Data.SqlClient ;
Step2: Open Database Connection.
Ex:
SqlConnection con=new SqlConnection(constr1);
con.Open();
Here constr1 is Connection String for required database connectivity.
Step3: Write Required SQL command like INSERT / UPDATE / DELETE
in string formate.
Ex:
To add new record
string sql= "INSERT INTO Book (BookName, Price)
VALUES('ASP.NET', 500)";
To update new record
string sql= "UPDATE Book SET BookName= 'C#.NET',
Price='400' WHERE BookID=1";
To delete any record
string sql= "DELETE FROM Book WHERE BookID=1";
Step4: Create Object of Command Class then Pass Sql Command and Active
Connection to the Constructor of command class.
Ex:
SqlCommand cmd = new SqlCommand(sql, con);
Here-
sql= SQL Command in string form.
con= Active Connection.
Step5: Execute INSERT / UPDATE / DELETE Command using
ExecuteNonQuery() method of Command class.
Ex:
cmd.ExecuteNonQuery();
When all above steps are complete then required sql command will be
executed on active connection sucessfully.

DataReader Class:
Data Reader class in one of ADO.NET’s components. The purpose of this
class is to hold the reference of records that retived after executing SELECT
command using ExecuteReader() method of Command Class.
SqlDataReader
It is a DataReader class for MS-SQL Server Database. Read() method of
DataReader class will return true value if records found otherwise false.
Using index value(0,1,..) of DataReader object, we can retieved column value
of records.
Working process with DataReader Class:( for MS-SQL Server database)
Step1: Include ADO.NET namespace in code behind.
Ex: using System.Data.SqlClient ;
Step2: Open Database Connection.
Ex:
SqlConnection con = new SqlConnection(constr1);
con.Open();
Here constr1 is Connection String for required database connectivity.
Step3: Write Required SELECT SQL command in string formate.
Ex:
string sql= "SELECT * FROM Book WHERE BookID=1";
Step4: Create Object of Command Class then Pass Sql Command and Active
Connection to the Constructor of command class.
Ex: SqlCommand cmd = new SqlCommand(sql, con);
Here-
sql= SQL Command in string form. con=
Active Connection.
Step5: Create Object reference of DataReader class.
Ex: SqlDataReader dr;
Step6: Execute SELECT sql Command using ExecuteReader() method of
Command class and assign reference of retrieved records to the DataReader
object reference.
Ex: dr=cmd.ExecuteReader();
Step7: Check availability of retirieved records in to DataReader using Read()
method. If found then retrives column's value into controls using idex value.
Ex:
if(dr.Read()==true)
{
TextBox1.Text=dr[0].ToString(); // First Column Value
TextBox2.Text=dr[1].ToString(); // Second Column Value
TextBox3.Text=dr[2].ToString(); // Third Column Value
}
When all above steps are complete then required SELECT sql command will
be executed on active connection sucessfully and records will be shown on
destination control.

Data Adapter and Datset Class:

DataAdapter:
DataAdapter class of ADO.NET can be be used to execute any SQL comaand
on given connection and retrieved records can be filled into given Dataset.
SqlDataAdapter
It is a DataAdapter class for MS-SQL Server.
Fill() method of DataAdapter class that can be used to papulate dataset.
Dataset:
Dataset is a class of ADO.NET which is used to store records that retrieved
from any source like MS-SQL server, or XML files. When Dataset is
papulated by records from any source then records of Dataset can be shown
on any Data bind control like GridView.
Working process with DataAdapter and Dataset Class:( for MS-SQL
Server database)
Step1: Include ADO.NET namespace in code behind.
Ex: using System.Data.SqlClient ;
Step2: Open Database Connection.
Ex:
SqlConnection con = new SqlConnection(constr1);
con.Open();
Here constr1 is Connection String for required database connectivity.
Step3: Write Required SELECT SQL command in string formate.
Ex:
string sql= "SELECT * FROM Book";
Step4: Create Object of Command Class then Pass Sql Command and Active
Connection to the Constructor of command class.
Ex: SqlCommand cmd = new SqlCommand(sql, con);
Here-
sql= SQL Command in string form. con=
Active Connection.
Step5: Create Object of DataAdapter class then pass object of Command
Class to its Constructur.
Ex: SqlDataAdapter da = new SqlDataAdapter (cmd);
Step6: Create Object of Dataset class.
Ex: Dataset ds = new Dataset();
Step7: Call Fill() method of DataAdapter class and pass object of Dataset to
fill retrived records.
Ex: da.Fill(ds);
Here-
da= DataReader ds=
Dataset
When all above steps are complete then required SELECT sql command will
be executed on active connection sucessfully and records will befillup into
Dataset.
Data Bound Control and Data Grid ( GridView Control):
Any control of ASP.NET that can be used to show records of any data source
like MS-SQL Server called Data Bound Control. To Bind records on Data
Bound control, DataSource property and DataBind() method is used of that
control.
Data Grid or GridView Control is one most papular and important Data bound
control. Using this control we can show records on table form. The Data
source of GridView control is Dataset.
Let GridView1 is one Databound control of GridView Control then to bind
Records of Dataset we have to apply DataSource property and DataBind()
method like.
GridView1.DataSource = ds;
GridView1.DataBind();
Here
ds= Dataset with Records.
Working process with DataBound Control ( DataGrid / GridView)
First add GridView Control on web page using following asp.net code.
<asp: GridView ID= “GridView1” runat= “server” />
Now apply following steps on page load event of web page.
Step1 to Step7 are same as DataAdpter and Dataset. Step8:
Set Data source of GridView control that is Dataset. Ex:
GridView1.DataSource=ds;
Step9: Bind GridView control so that data can be shown on control.
Ex: GridView1.DataBind();
When all above steps are completed then data of Dataset can be bindup to
the data bound control like GridView.