ADO.NET is designed to help developers work efficiently with multi-tier databases, across intranet or Internet scenarios.
The ADO.NET object model consists of two key components as follows:
-
Connected model (.NET Data Provider - a set of components including the Connection, Command, DataReader, and DataAdapter objects)
-
Disconnected model (DataSet).
The Connection object is the first component of ADO.NET. The connection object opens a connection to your data source.
All of the configurable aspects of a database connection are represented in the Connection object, which includes ConnectionString and ConnectionTimeout.
Connection object helps in accessing and manipulating a database. Database transactions are also dependent upon the Connection object.
In ADO.NET the type of the Connection is depended on what Database system you are working with. The following are the commonly used connections in the ADO.NET
-
SqlConnection
-
OleDbConnection
-
OdbcConnection
Command
The Command object is used to perform an action on the data source. Command object can execute stored procedures and T-SQL commands.
You can execute SQL queries to return data in a DataSet or a DataReader object. Command object performs the standard Select, Insert, Delete, and Update T-SQL operations.
DataReader
The DataReader is built as a way to retrieve and examine the rows returned in response to your query as quickly as possible.
No DataSet is created; in fact, no more than one row of information from the data source is in memory at a time. This makes the DataReader quite efficient at returning large amounts of data.
The data returned by a DataReader is always read-only. This class was built to be a lightweight forward-only, read-only, way to run through data quickly (this was called a firehose cursor in ADO).
However, if you need to manipulate schema or use some advanced display features such as automatic paging, you must use a DataAdapter and DataSet.
DataReader object works in a connected model.
DataAdapter
The DataAdapter takes the results of a database query from a Command object and pushes them into a DataSet using the DataAdapter.Fill() method. Additionally the DataAdapter.Update() method will negotiate any changes to a DataSet back to the original data source.
DataAdapter object works in a connected model. DataAdapter performs the five following steps:
-
Create/open the connection
-
Fetch the data as per command specified
-
Generate XML file of data
-
Fill data into DataSet.
-
Close connection.
Command Builder
It is used to save changes made in an in-memory cache of data on the backend. The work of Command Builder is to generate Command as per changes in DataRows.
Command Builder generates command on basis of row state. There is a five-row state:
-
Unchanged
-
Added
-
Deleted
-
Modified
-
Detached
Command Builder works on add, delete, and modified row state only.
Detached is used when an object is not created from row state.
Transaction
The Transaction object is used to execute the backend transaction. Transactions are used to ensure that multiple changes to database rows occur as a single unit of work.
The Connection class has a BeginTransaction method that can be used to create a Transaction.
A definite best practice is to ensure that Transactions are placed in Using statements for rapid cleanup if they are not committed. Otherwise, the objects (and any internal locks that may be needed) will remain active until the GC gets around to cleaning it up.
Parameters
Parameter object is used to solve the SQL Injection attack problem while dealing with the user input parameters.
Parameter object allows passing parameters into a Command object the Parameter class allows you to quickly put parameters into a query without string concatenation.
Note: See my other article on ADO.NET Objects Part II.
Conclusion
Hope the article would have helped you in understanding ADO.NET objects.
Your feedback and constructive contributions are welcome. Please feel free to contact me for feedback or comments you may have about this article.