Transcript ADO.NET - iba-s1

ADO.NET
ADO.NET
• ADO.NET deals with accessing and
manipulating databases.it comprises of many
namespaces and classes to do so.
• ADO.NET provides access to datasources such
as Microsoft SQL server,OLE DB and XML.
• Application written in .NET compliant
languages connect to these datasources to
access manipulate or update the data
ADO(activeX data objects)
• ADO relied on a connection based mode.in the
connection approach ,the client had to be
connected with the server(data source) and
remain connected till the whole procedure or
transaction was copleted.in such approach a lot
of bandwidth was required.
• If 100 clients were to use a server,each would
need a connection.
• Time ,resources and bandwidth became a major
constraint on such architecture.
• To solve this problem latest version of ADO
used record sets, All the content from the
datasource was coppied from data source to
record set..This allowed the client to get
disconnected from the server,work on the
record set and copy the changes back to data
source again.
• It required COM marshalling to transmit
disconnected data,it support only COM
supported data types and it required type
conversion.
ADO
• Microsoft's ActiveX Data Objects (ADO) is a set of
Component Object Model (COM) objects for accessing data
sources. it provides a middleware layer between
programming languages and OLE DB (a means of accessing
data stores, whether they be databases or otherwise, in a
uniform manner). ADO allows a developer to write
programs that access data without knowing how the
database is implemented. He must be aware of the
database for connection only. No knowledge of SQL is
required to access a database when using ADO, although
one can use ADO to directly execute SQL commands. The
disadvantage of the latter is that it introduces a
dependency upon the type of database used.
Basic Usage
1.
2.
3.
4.
5.
6.
7.
8.
Create a connection object to connect to the database.
Create a recordset object in order to receive data in.
Open the connection
Populate the recordset by opening it and passing the
desired table name or SQL statement as a parameter to
open function.
Do all the desired searching/processing on the fetched
data.
Commit the changes you made to the data (if any) by
using Update or UpdateBatch methods.
Close the recordset
Close the connection
ADO.NET
• ADO.NET (ActiveX Data Object for .NET) is a set of
computer software components that programmers can
use to access data and data services. It is a part of the
base class library that is included with the Microsoft
.NET Framework. It is commonly used by programmers
to access and modify data stored in relational database
systems, though it can also access data in nonrelational sources. ADO.NET is sometimes considered
an evolution of ActiveX Data Objects (ADO) technology,
but was changed so extensively that it can be
considered an entirely new product..
ADO.NET
• ADO.NET supports both disconected and
connected approach.In majority of situations
disconected approach is used ,in this approach
transmission of data is in XML format.XML
format places no restriction on the datatypes
and require no type conversion.
WCF Data services
• WCF Data Services (formerly ADO.NET Data
Services, codename "Astoria") is a platform
for what Microsoft calls Data Services. It is
actually a combination of the runtime and a
web service through which the services are
exposed.
ADO.NET COMPONENTS
• DATA SET
• .NET DATA Providers
Is used for connecting to the database,executing
commands and retreiving results.we can either
access database directly or use the disconected
approach.For the disconected approach we use the
DATASET class.
.NET Data Providers
• A Data provider consists of following basic
objects
a) A connection object
b) A command object
c) A DataReader object
d) A dataAdapter object
Connection object
• The connection object is used to connect to the
data source.Data source can be any database
file.The connection object contains the
information like the provider name,server
name, data source name,user name,password
and so on/
Command object
• A command object is used to connect the
connection object to a DataReader or
DataAdapter object.The command object
allows us to execute an SQL statement or
stored procedures in a data source
DataReader
• The DataReader is used to read the data in a
fast and efficient manner from the database..
It is generally used to extract one or a few
records or a specific field value or to execute
simple SQL statements.
DataAdapter object
A DataAdapter is used to fill data from the
database into the Dataset object.The
dataAdater is used in disconnected approach.
.NET Data providers
• OLE DB .NET PROVIDER (MICROSOFT ACCESS)
• SQL SERVER .NET PROVIDER
Create a database
• Create a database in access and name it
“bank”.
• Create a table and name it “account”
• It should be like the next slide.
• And fill it with appropriate data.
Now fill the data like this
Question of today ?
• Write a program to access
these values and print them
using ADO.NET Objects ?
1. Create an empty web site named it “bank”
2. Add a web page account.aspx in solution
explorer.
DataSets
• ADO.NET imlements the disconected
approach using the Dataset class.In the
disconnected approach connection is
established ,the data from the database is
copied to the cache and then connection is
closed.Next we manipulate the data in the
cache and then connect back to restore the
data in the database.This approach saves time
and resources.
Dataset class
• The dataset class is designed for dataaccess
independent of the .NET Data Providers.it
means that it does not matter whether we are
using the SQL .net OR the OLE DB .NET
provider .we can use the same class for both
the providers.The DataSet class provides
methods and properties to access the
tables,rows and columns of the data source.
Datset (continued)
• A dataset object can hold more than one table
from the datasource.it can also hold the
relationship that exist between the table in
the dataset.
Binding the GridView
• We must bind the grid view control to some
data source.the control then automatically
displays the records of the specified table
Quiz(dat access layer)
Q1
C eate a project and connect it to the databasd (Northwind).
and create a DAL layer.(1) Issuing SELECT, INSERT, UPDATE, and DELETE commands, and
so on – should be located in the DAL. The presentation layer (your web page)should not
contain any references to such data access code, but should instead make calls into the DAL
for any and all data requests. Data Access Layers typically contain methods for accessing
the underlying database data. The Northwind database, for example, has Products and
Categories tables that record the products for sale and the categories to which they belong.
In our DAL You will have methods like:
2)GetCategories(), which will return information about all of the categories
3) GetProducts(), which will return information about all of the products
4) GetProductsByCategoryID(categoryID), which will return all products that belong to a
specified category
5) GetProductByProductID(productID), which will return information about a particular product
Q2)what is the difference between loosely typed objects and strongly typed objects.why this
approach (DAL)is prefferable over using DatsSource controls directly.
MAIL IT([email protected])