Ado

Microsoft has provided data access libraries, ADO It is the single library for accessing any type of data, i.e universal data access. ADO is designed based on OLEDB provider programming for Accessing any type of Data i.e Universal data acces. Initially ADO library is provided for web based application development i.e ASP

Drawbacks 1.ADO and OLEDB are based on COM(binary standard) implementation.so it is platform dependent. 2.It does not support complete disconnected architecture 3. .It does not support integrating with XML 4.It requires performance improvement.

Connected & disconnected architecture When the application is maintain data base table information by maintain connectivity with data base server then it is called connected architecture When the application is maintain data base table information without maintain connectivity with data base server then it is called disconnected architecture

ADO.NET ADO.NET is nothing but a library called as system.data.dll. ADO.NET comes with two parts, 1. Managed data provider(It provides connected architecture) 2. Dataset(It goes with data storage & it provides complete disconnected)

Managed Data Provider

Managed Data Provider for Oledb

Managed Data Provider for SqlServer

Managed Data Provider for Oracle

Managed Data Provider for ODBC

System.Data.Oledb Oledb connection Oledb Command Oledb Data Reader Oledb Data Adapter Oledb Command Builder

System.Data.SqlClie nt

System.Data.OracleClien t

Sql connection Sql Command

Oracle connection Oracle Command Oracle Data Reader Oracle Data Adapter

Sql Data Reader Sql Data Adapter

Oracle Command Builder

System.Data.ODBC ODBC connection ODBC Command ODBC Data Reader ODBC Data Adapter ODBC Command Builder

Sql Command Builder

. NET framework 1.0 comes with only two managed providers, i.e. OLEDB and SQL Server. . NET framework 1.1, comes with the entire four managed data provider. OLEDB MANAGED DATA PROVIDER

.NET Application OLEDB Managed Data Provider

OLEDB Provider for Oracle

Oracle

OLEDB Provider for SQLServer SQLServe r

The Application will be dependent on COM. It will make processing time, so that performance will come down. This is advisable, when there is no other option for connecting to data store.

Oledb Connection Syntax: Dim con as new oledbconnection(“provider =<provider name>; User Id=<User name>; Password=<Password>; Data Source=”) (Or) ----OLEDBConnection Conn = new OLEDBConnection (“provider =<provider name>; User Id=<User name>; Password=<Password>; Data Source=”) Database Oracle SqlServer JetEngine ODBC

Provider name msdaora.1 sqloledb.1 microsoft.jet.oledb.4.0 msdasql.1

In .NET ODBC option is disabled with managed provider for OLEDB. Con.open () this will establish the connection. Con.close () this will release the network resources. OLEDB COMMAND It will represent the sql statement. 1. Select 2. Insert 3. Delete 4. Update Syntax: Dim cmd as new oledbcommand(“select * from emp “,con) (or) oledbcommand cmd = new oledbcommand(“select * from emp”,con);

Methods: 1.Executescalar (): It will return only one value. E.g. 1) Select count (*) from EMP; 2) Select max (Sal) form EMP; 2.ExecuteNonQuery (): It will return number of records affected. E.g. delete from EMP where deptno=10; 3.ExecuteReader (): It returns data reader object (i.e. more than one value or record or rows) Select * from EMP; Data Reader data reader It consumes fewer amounts of resources. oledbdatareader dr = new oledbdatareader() dr=cmd.executereader ()

It will send select statement to the database server. Database sever will execute select statement and returns all the records to the client machine. The records will be stored within temporary memory storage. Data reader acts as a reference to the memory storage. Methods: 1.read (): - it will fetch one record into client application process. 10

Acc

hyd

2.GetInteger (0): - It returns first column value (i.e.10) Getstring (1): - It returns 2nd column value (i.e.Acc) GetString (2): - It returns 3rd column value (i.e.hyd) 3.close ():-It will release temporary memory storage.

Data Reader will provide only one record at a time into the client application process. This will improve the performance of the application. So it will come under connection-oriented architecture. TRANSACTION HANDLING A transaction can be defined as a unit of manipulations on the basis of all or none. Application 1.Insert into trans 2.Update Item

Commit Rollback (Update fails if item number Not found)

DATASET

TRANS ITEM

Dataset can be defined as in memory storage for the client application, which acts as a database to the client application. It is going to be purely disconnected architecture. DATA ADAPTER Data adapter acts as a mediator between dataset and the database server. This will perform two things, 1.Storing the database table information into dataset. 2.the changes made within dataset (i.e., insert, update & delete) will be updated to the database server. Data Adapter will contain four command objects to perform the above things.

. NET Managed data Provider

Data Adapter

Application

DATASET

XML Storag e

Select Command Insert Command Update Command Delete Command

Data Reader Command

Data Storag e

Connection Dataset is not a part of .net managed data provider. Data adapter is required only when u use dataset creation based on the database server. Dim ds as dataset Dim da as new SqlDataAdapter(“select * from emp”,con) Syntax: dataset ds = new dataset(); SqlDataAdapter da = new SqlDataAdapter (“select * from emp”, con) Da.fill (ds,”emp”) DEPT

EMP DA2 A

DA1

DATASET

(One To One relationship is maintained by each data adapter with the corresponding table, So each time it is required a new data adapter to perform the changes in a new table) When you configure data adapter manually it creates more complexity for the developer, this leads to wards more time consumption. To avoid this Microsoft has provided command builder class. Command builder class will generate insert command, update command and delete command to the data adapter. sqlcommandbuilder cb = new sqlcommandbuilder() cb=sqlcommandbuilder (da) It will execute constructor of SQLCommandBuilder and generate insert, delete and update commands.

Commandbuilderclass will not generate delete command and update command, if the backend table is not having primary key. Data Binding: When you establish relation between textboxes and data table columns, then it is called as data binding.