ADO.NET Providers:

A data provider is nothing but a set of ADO.NET classes that can be used to access a specific database and execute commands against it and retrieve the data. Mainly, data provider acts like a bridge between your application and data source.

When you try to choose a provider, you should first try to find a native .NET provider that is customized for your data source.

ADO.NET Architecture:

As we have discussed previously the data provider includes the following components or classes

Connection: Object to establish connection with a data source(SQL, OLEDB, Oracle etc.)

Command: Object is responsible for SQL commands and stored procedures

DataReader: Object is responsible for providing fast, read-only, forward-only access to the data retrieved by executing a query.

Data Adapter: Object to fill a DataSet with the data retrieved from data source.  It uses to apply the changes after modifying the data within a dataset. And it play key role in the disconnected data architecture.

ADO.NET Classes:

DataProvider:

Connection	Provides Connectivity to data source
Command	Runs queries and executes other database commands
DataReader	Providing-High Performance read-access data
DataAdapter	Loads data into DataSets and applies changes

Data Table	Contains rows and columns of data from a query
Command	Stores relationship between Data tables

DataSet:

Connection Object:

Its allows you to establish a connection with a data source. The connection class implements the interface known as IDbInterface, which consists of all the core connection properties and methods. Mainly these connection settings are included with the server where database is located, the database you want to use and database authentication credentials.

Connection object mainly supports

Properties: ConnectionString, ConnectionTimeout

Methods: open (), close()

Syntax:

//Windows Authentication

String ConnectionString = "server=localhost; database=AdventureWorks; Integrated Security=True";

            SqlConnection conn = new SqlConnection(ConnectionString);

            conn.Open();

//If you provide username and password, SQL Authentication

String ConnectionString = "server=localhost; database=AdventureWorks; UserID=ramakrishna; Password=sysadmin";

NOTE: It is recommended to place connection string in web.config file in your application to avoid security threats.

Command Object:

The command class allows you to execute any type of SQL statement against a database. The command object is mainly used to perform data manipulation tasks, such as retrieving and updating the records in a table. It implements, IDbCommand interface defines a few core properties & methods to execute a command over an open connection to the database.

Command Object:

Properties supported:

·        CommandType

·        CommandText

·        Connection

·        Parameters

Methods supported:

·        ExecuteNonQuery()

·        ExecuteReader()

·        ExecuteScalar()

Once created, the command object must be configured by setting the Connection, CommandType, and CommandText properties.

DataReader Object:

A DataReader class allows you to read the data returned by a SELECT command one record at a time, in forward-only, read-only stream.

Results are returned as the query executes, and are stored in the network buffer on the client. DataReader increases application performance.

Always call the Close() method when you have finished using the DataReader object.


DataAdapter:

Data adapters are an integral part of ADO.NET managed providers, which are the set of objects used to communicate between a datasource and a dataset.

Data adapters retrieve data from database into DataSet and update the changes made within the dataset back to the database.

DataAdapter provides three key methods:

·        Fill()

o   Method adds a DataTable to a DataSet by executing the query in the SelectCommand.  If your query returns multiple result sets, this method will add multiple DataTable objects to the DataSet.

·        FillSchema()

o   Method adds a DataTable to a DataSet by executing the query in the SelectCommand and retrieving schema information only.

·        Update()

o   Method examines all the changes in a single DataTable and applies this batch of changes to the actual data source by executing the appropriate commands.