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 Usage1. Create a connection object to connect to the database.2. Create a recordset object in order to receive data in.3. Open the connection4. Populate the recordset by opening it and passing the desired

table name or SQL statement as a parameter to open function.

5. Do all the desired searching/processing on the fetched data.6. Commit the changes you made to the data (if any) by using

Update or UpdateBatch methods.7. Close the recordset8. 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 non-relational 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 ProvidersIs 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 objectb) A command objectc) A DataReader objectd) 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 objectA 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 categories3) GetProducts(), which will return information about all of the products4) GetProductsByCategoryID(categoryID), which will return all products that belong to a

specified category5) 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(alnasrkhan@gmail.com)