WINDOWS
PROGRAMMING USING C#
Lecture 4: ADO.NET Architecture
ADO.NET
ADO.NET is the data access technology, which
allows you to access data residing in various data
source.
ADO.NET is a part of the .NET Framework.
ADO.NET technology allows you to connect to
database systems such as Microsoft SQL Server,
Oracle, Microsoft Access.
Data Access Architecture
ADO.NET have two components:
Data provider are used for providing and
maintaining connection to the database.
Datasets are extracted and maintained in the
form of table
Data Provider
Data provider are used for providing and
maintaining connection to the database.
The .NET Framework provides different data
providers which are listed as follows:
.NET Framework data provider for SQL
Server
.NET Framework data provider for OLE DB
.NET Framework data provider for ODBC
.NET Framework data provider for Oracle
System.Data Namespace
The System.Data namespace consists of classes,
interfaces, and enumerations that represent the
ADO.NET architecture.
These data providers are located in their
namespaces, which are listed as follows:
System.Data.SqlClient
System.Data.OracleClient
System.Data.OleDb
System.Data.Odbc
Data Access Components
ADO.Net provides two components to access and
manipulate data:
.NET Framework data providers
The DataSet
Data Access Components
ADO.NET consists of objects that allows you to
establish connections and work with the database. The
objects associated with ADO.NET technology are:
Command object
Connection object
DataAdpater
DataReader
DataSet
DataProvider
DataSet
DataSet are used to display and update data. It can
be used for retrieving data from multiple sources.
Connection Object
The Connection Object allows you to create a
connection between your application and the
database.
The connection object stores information that is
required to establish the connection.
In ADO.NET you can establish a connection to
SQL Server and OLE DB data source
SqlConnextion Class
OleDbConnection Class
SqlConnection Class
SqlConnection Class is used to connect to an SQL
Server
Name Description
TheConnectionString
table lists the Thiscommonly
property retrievesused properties,
the string, which is
used to open a SQL Server database
methods,
State
and events of the SqlConnection Class
This property retrieves the current state of
the connection
Close This method closes the connection to the
database
CreateCommand This method creates and returns a
SqlCommand object, which is associated
with SqlConnection
Open This method opens a connection to the
database
StateChange This event occurs when the state of the
connection is changed
SqlConnection Class
The Syntax:
SqlConnection <ConnectionNAme> = new
SqlConnection (<connectionString>);
Where:
<connectionString> : Contains the database
connection string.
Command object
The command object enables you to execute a
specific command against a data source.
For executing commands on SQL Server the
System.Data.SqlClient.SqlComand . It can be used
to execute SQL statements such as UPDATE,
DELETE, INSERT, or SELECT.
Command object
The table lists the commonly used properties and
methods of the SqlCommand class
Name Description
ComandText This property retrieves the commands to be executed at the
data source
Connection This property retrieves the SqlConnection
CommandTimeout This method retrieves the wait time before ending an
attempt to run a command and generating an error
ExcuteNonQuery this method executes SQL statements against the
connection across the data source and retrieves the number
of rows affected.
Command object
The Syntax:
SqlCommand CommandName = new SqlCommand();
Example:
SqlCommand sqlComName= new SqlCommand();
SqlComName.Connection = sqlComName;
sqlComName.CommadText = “CREATE TABLE TbleNAme
(CloumnName varchar(50))”;
sqlComName.ExcuteNonQuery();
DataAdapter object
The DataAdapter object acts as a bridge between a
dataset and a data source. This object updates the
database to match it with the data in dataset.
It uses the Fill() method to fill the dataset with the
data from data source.
The update() method is used to insert, update, or
delete data from the data source as specified in the
dataset.
DataAdapter object Cont
The code demonstrates how to use the
SqlDataAdapter:
SqlDataAdapter sqldaSchool = new
SqlDataAdapter(“SELECT * FROM Stdudent”,
sqlconSchool);
DataSet dsetSchoolInfo = new DataSet();
sqldaSchool.Fill(dsetSchoolInfo);
sqldaSchool.Update(dserSchoolInfo);
DataReader object
The DataReader object reads a stream of data from
the database it reads one row at a time and moves
forward one record at a time.
DataReader object Cont
The code demonstrates how to create an object of
the sqlDataReader class:
SqlDataReader sqldatareader = new SqlDataReader
();
sqldatareader.ExcuteReader();
