Hi, I am Sumit Gupta. I am a .Net Developer with about 4 years of experience. I have built various applications for both Desktop & Web besides designing Wrapper classes for managing Multi-threading. I would like to share the concept of ADO.NET with you all.
ADO.NET Overview
ADO.NET provides consistent access to data sources such as SQL Server and XML, and to data sources exposed through OLE DB and ODBC. Data-sharing consumer applications can use ADO.NET to connect to these data sources and retrieve, handle, and update the data that they contain.
Connected Architecture of ADO.NET
The architecture such as ADO.net, in which connection must be opened to access the data retrieved from database, is called as connected architecture. Connected architecture is built on the classes’ connection, command, data reader and transaction.
Connection: The purpose of connection is to just establish a communication channel to the database, connection itself would not transfer any data automatically.
Command:After establishing a connection to a data source, you can execute commands and return results from the data source using a DbCommand object. You can create a command using one of the command constructors for the .NET Framework data provider you are working with. Constructors can take optional arguments.
Data Reader: Data Reader is used to store the data retrieved by command object and make it available for .NET application. Data in Data Reader is read only and within the Data Reader you can navigate only in forward direction and it also only one record at a time.
To access one by one record from the Data Reader, call Read() method of the Data Reader whose return type is bool. When the next record was successfully read, the Read() method will return true otherwise false.
Transaction: Transactions are groups of database commands that execute as a package. Using a transaction gives your application the ability to abort (roll back) all changes executed from within the transaction if any errors occur during any part of the transaction process.
Disconnected Architecture of ADO.NET
Data Adapter: Data Adapter is used to transfer the data between database and dataset. It has commands like select, insert, update and delete. Select command is used to retrieve data from database and insert, update and delete commands are used to send changes to the data in dataset to database. It needs a connection to transfer the data.
Command Builder: By default, data adapter contains only the select command and it doesn’t contain insert, update and delete commands. To create insert, update and delete commands for the data adapter, command builder is used. It is used only to create these commands for the data adapter and has no other purpose.
DataSet: Dataset is used to store the data retrieved from database by data adapter and make it available for .net application.
To fill data into dataset fill() method of data adapter is used and has the following syntax> Da.Fill(Ds,”TableName”);
When fill method was called, data adapter had opened a connection to database, had executed select command, had stored the data retrieved by select command in to dataset and had also immediately closed the connection.
As connection to database was closed, any changes to the data in dataset wasn’t directly sent to the database and changes were made only in the dataset. To send changes made to data in dataset to the database, Update() method of the data adapter is used that has the following syntax> Da.Update(Ds,”Tablename”);
When Update method is called, data adapter will again open the connection to database, executes insert, update and delete commands to send changes in dataset to database and immediately closes the connection. As connection is opened only when it is required, the connection will be automatically closed when it was not required, this architecture is called disconnected architecture.
DataView: DataView is a view of table available in DataSet. It is used to find a record, sort the records and filter the records. By using DataView, you can also perform insert, update and delete as in case of a DataSet.A dataset can contain data in multiple tables.
So this is how ADO.NET provides consistent access to data sources such as SQL Server and XML, and to data sources exposed through OLE DB and ODBC.
Thanks for reading. Please share your ideas on our talk on ado.net here.
