Introduction to ADO.net :

Sep 30, 2011

The ADO.net is object oriented set of libraries which allows us to interact with different types of Databases and different types of Data sources.ADO.NET provides a relatively common way to interact with data sources, but comes in different sets of libraries for each way you can interact with a data source or a database.
Below are some of the libraries which will help you to interact with the specific datasource or the database.

Provider Name API prefix Data Source Description
ODBC Data Provider Odbc Data Sources with an ODBC interface. Normally older data bases.
OleDb Data Provider OleDb Data Sources that expose an OleDb interface, i.e. Access or Excel.
Oracle Data Provider Oracle For Oracle Databases.
SQL Data Provider Sql For interacting with Microsoft SQL Server.
Borland Data Provider Bdp Generic access to many databases such as Interbase, SQL Server, IBM DB2, and Oracle.

ADO.net Objects :

Connection object :

To interact with databse you need to establish connection with the database. The connection object takes connectionstring string as a parameter which has Datasource, Name of database (Initial Catalog),User name, Password and other parameters can be passed. The connection object is used by command object to know which databse to connect and interact for excution of queries. Read more....

Command object :

To interact with database you need to define the action which will get exceuted that action can be defined by command object which can be a query or a stored procedure.Command object takes connection object so as to know which database to use for excuting query or stored procedure. Afer establishing connection we can use datareader or dataAdapter to get the data. Read more....

Datareader object :

When you require data for reading purpose only that time you can use datareader. Datareader object allows you to retrieve result of SELECT statement from command object. Datareader reads data in forward-only manner so it can only read data in one direction. Read more....

Dataset object :

Dataset is basically in-memory representation of data. The Dataset basically can contains multiple datatables which contain normal rows and column like normal database table. You can even define relation between two tables. Datatset object is used to stor data in memory and dataset basiclly operates in disconnected architecture. Unlike datareader dataset can be use to carry out insert, update or delete of your data. Read more....

DataAdapter object :

When you primarily want to read the data and may want to update it or you want to cache data to minimize the database call you can use dataadapter. DataAdapter works in disconnected mode. It has reference to connection object so it automatically opens the connection fetches the data and closes the connection. We explicitly don't need to open and close connection. Also it can update data in a batch when we are persisting chages. Read more....