Unit - V
Data Access with ADO.NET
Understanding ADO.NET
The data access and manipulation protocol
used by c#.
It uses a disconnected data architecture.
In traditional client/server applications , you
get a connections to a database and keep it
open, while the application is running.
ADO.NET provides a set of components to
create distributed applications.
ADO.NET provides a consistent access to data
sources.
-Microsoft SQL Server, OLEDB and XML.
And can use to retrieve, manipulate and update data
present in these data sources.
The results are either processed directly or placed in
ADO.NET dataset object .
The ADO.NET classes are found in
System.Data.dll
and Integrated with the xml classes found in
System.Xml.dll
Features of ADO.NET are:
LINQ
Stands for Language-Integrated Query
includes Dataset disconnected programming
model and the existing SQL server database
scheme.
LINQ to Dataset:
Provides better querying over Dataset.
Easier and faster to query over data cached in a
Dataset object.
Enables to work more productively.
LINQ to SQL:
Provides run time infrastructure for managing
relational data as objects.
Can insert , update , and delete the information from
the table.
Data provider in ADO.NET
Set of related components that work together to provide
data in an efficient manner.
Used for connecting to database, executing commands
and retrieving results.
Objects of Data Provider
Connection-creates connection to the data source.
-Base class is Dbconnection class.
-Connection object has methods for opening closing
connection and beginning a transaction
Two type of connection classes:
sqlconnection- for Microsoft SQL Server
oleDbconnection - Microsoft access and oracle.
Command
-executes a command against data source and
retrieve a DataReader or Dataset.
- base class is Dbcommand class.
- sqlcommand and oleDbcommand
Three methods :1. ExecuteNonQuery()
2. ExecuteScalar()
3. ExecuteReader()
DataReader
Provides a forward-only and read-only connected result set.
Base class is DataReader class.
DataAdapter
Updates dataset with data from data source.
Acts as an intermediary for all communication between the
database and dataset.
Base class is DbDataAdapter class.
Fill() –fill the dataset.
Update()- changes to the database.
Uses four methods Selectcommand,
insertcommand ,deletecommand , updatecommand.
DataSet
DataSet can be considered as a local copy of the relevant
portions of the database.
The data in dataset can be manipulated and updated
independent of the database.
Components of DataSets:
DataTable
consists of data row and column and stores the data in table
row format.
Case sensitive
-employeedetails and Employeedetails are different.
NewRow() –is used to add a row to datatable.
DataView
Sorting, filtering , searching , editing and navigation.
It can be used to present a subset of data from the DataTable.
DataColumn
Consists of number of column comprises a DataTable.
Essential building block of DataTable.
DataType property of DataColumn determines the kind of
data that a column holds.
DataRow
Represent a row in the DataTable.
NewRow()- create a new DataRow
Add()- to add new DataRow .
Remove() –delete DataRow.
DataRelation
Allows to specify relation between various tables.
Used to relate Two datasets objects to each other
through Datacolumn objects.
Typed vs. Untyped DataSets
Typed DataSet
has Schema.
Derived from base class DataSet class.
It uses information such as tables, columns and properties
from DataSet designer ,which is stored in XML schema .
Assumes all the functionality of dataset class and uses
methods.
Untyped DataSets
Has no built-in schema.
Contains tables and columns as in typed dataset but is
accessed only as collections.
DataReader
Is Used to sequentially read data from a data
source.
Allows access information on a single row of
data.
Command object is required to retrieve data
from database using the DataReader.
Call Command.ExecuteReader to retrive the
rows from db.
Read() – is used to obtain row from the query.
Close()- call this method every time you finish
using DataReader object.
ADO.NET Entity Framework
ADO.NET allows you to focus on data
through an object model instead of a relational
model.
Allows you to write less data access code,
reduce maintenance, structure of data user
friendly.
Can use entity framework in your application
with using a data provider called EntityClient
and a new language called Entity Sql.
Includes the following components:
Entity Data Model(EDM)
-Defines the conceptual entities that can be read in a
serialized form using a DataReader.
Entity SQL
-Defines a common SQL based query language that is
extended to express queries in terms of EDM concepts
Entity Client
-provides a gateway for the queries of the entity-level
queries which is queried through a common Entity SQL
language
Object Services
- Allows you to interact with a conceptual layer through
a set of CLR classes.
- Provides services , such as state management,
Identity resolution and query building support.
LINQ to Entities
- Provides LINQ support for querying entities.
