The full form of ADO is ActiveX Data Object.
ADO.NET is a very important feature of .NET Framework, which is used to work with data that is stored in structured data sources, such as databases and XML files. The following are some of the important features of ADO.NET:
Following are some major differences between both
DataReader and DataSet are the two fundamental objects in ADO.NET.
The following are the benefits of using ADO.NET in .NET 4.0 are as follows:
LINQ to SQL - Allows you to create queries for data stored in SQL server database in your .NET application. You can use the LINQ to SQL technology to translate a query into a SQL query and then use it to retrieve or manipulate data contained in tables of an SQL Server.
The following namespaces are required to enable the use of databases in ASP.NET pages:
The DataAdapter.Update() method calls any of the DML statements, such as the UPDATE, INSERT, or DELETEstatements, as the case may be to update, insert, or delete a row in a DataSet. TheDataSet.Acceptchanges() method reflects all the changes made to the row since the last time theAcceptChanges() method was called.
Object pooling is a concept of storing a pool (group) of objects in memory that can be reused later as needed. Whenever, a new object is required to create, an object from the pool can be allocated for this request; thereby, minimizing the object creation. A pool can also refer to a group of connections and threads. Pooling, therefore, helps in minimizing the use of system resources, improves system scalability, and performance.
The DataSource property and the DataMember property are used to bind a DataGridView control.
You must set the DataSource property and call the DataBind() method.
The System.Data.SqlClient namespace.
With respect to usage, there is no difference between OLEDB Provider and SqlClient. The difference lies in their performance. SqlClient is explicitly used to connect your application to SQL server directly, OLEDB Provider is generic for various databases, such as Oracle and Access including SQL Server.
Therefore, there will be an overhead which leads to performance degradation.
The properties of the GridView control that need to be specified to turn on sorting and paging are as follows:
Datasets follow the disconnected data architecture.
One of the major component of ADO.NET is the DataSet object, which always remains disconnected from the database and reduces the load on the database.
The DataReader object helps in retrieving the data from a database in a forward-only, read-only mode. The base class for all the DataReader objects is the DbDataReader class.
The DataReader object is returned as a result of calling the ExecuteReader() method of the Command object. The DataReader object enables faster retrieval of data from databases and enhances the performance of .NET applications by providing rapid data access speed. However, it is less preferred as compared to theDataAdapter object because the DataReader object needs an Open connection till it completes reading all the rows of the specified table.
An Open connection to read data from large tables consumes most of the system resources. When multiple client applications simultaneously access a database by using the DataReader object, the performance of data retrieval and other related processes is substantially reduced. In such a case, the database might refuse connections to other .NET applications until other clients free the resources.
The DataSet object provides the following two methods to track down the changes:
If you want to revert all changes since the DataSet object was loaded, use the RejectChanges() method.
The IsClosed property is used to check whether a DataReader is closed or opened. This property returns atrue value if a Data Reader is closed, otherwise a false value is returned.
The Fill() method is used to fill the dataset with data.
The Connection object is used to connect your application to a specific data source by providing the required authentication information in connection string. The connection object is used according to the type of the data source. For example, the OleDbConnection object is used with an OLE-DB provider and the SqlConnectionobject is used with an MS SQL Server.
The CommandBuilder class is used to automatically update a database according to the changes made in aDataSet.
This class automatically registers itself as an event listener to the RowUpdating event. Whenever data inside a row changes, the object of the CommandBuilder class automatically generates an SQL statement and uses theSelectCommand property to commit the changes made in DataSet.
OLEDB provider in .NET Framework has the OleDbCommandBuiider class; whereas, the SQL provider has theSqlCommandBuilder class.
AD0.NET consists of two fundamental components:
The data provider contains the Connection, Command, DataReader, and DataAdapter objects.
TheConnection object provides connectivity to the database. The Command object provides access to database commands to retrieve and manipulate data in a database. The DataReader object retrieves data from the database in the readonly and forward-only mode. The DataAdapter object uses Command objects to execute SQL commands. The DataAdapter object loads the DataSet object with data and also updates changes that you have made to the data in the DataSet object back to the database.
ADO.NET maintains a disconnected database access model, which means, the application never remains connected constantly to the data source. Any changes and operations done on the data are saved in a local copy (dataset) that acts as a data source. Whenever, the connection to the server is re-established, these changes are sent back to the server, in which these changes are saved in the actual database or data source.
The following are the usages of the Command object in AD0.NET:
The Command object in AD0.NET executes a command against the database and retrieves a DataReader orDataSet object.
The prerequisites for connection pooling are as follows:
Connection pooling refers to the task of grouping database connections in cache to make them reusable because opening new connections every time to a database is a time-consuming process. Therefore, connection pooling enables you to reuse already existing and active database connections, whenever required, and increasing the performance of your application.
You can enable or disable connection pooling in your application by setting the pooling property to either true or false in connection string. By default, it is enabled in an application.
The various methods provided by the DataSet object to generate XML are:
The Windows authentication technique is considered as a trusted authentication method because the username and password are checked with the Windows credentials stored in the Active Directory.
The SQL Server Authentication technique is not trusted as all the values are verified by SQL Server only.
The connection class is used to connect a .NET application with a database.
OleDbDataAdapter is used to get the data from an Access database.
The DataRelation object is used to add relationship between two DataTable objects.
.NET applications can use two different techniques to authenticate and connect with SQL Server. These techniques are as follows:
ADO.NET Entity Framework 4.0 is introduced in .NET Framework 4.0 and includes the following new features:
The Clone() method copies only the structure of a DataSet. The copied structure includes all the relation, constraint, and DataTable schemas used by the DataSet. The Clone() method does not copy the data, which is stored in the DataSet.
The Copy() method copies the structure as well as the data stored in the DataSet.
User-defined view of a table is contained in a DataView. A complete table or a small section of table depending on some criteria can be presented by an object of the DataView class. You can use this class to sort and find data within DataTable.
The DataView class has the following methods:
The parameters that control most of connection pooling behaviors are as follows:
schema as applied to the DataTable. The following are the methods provided by the DataRowCollection object:
The DataAdapter class retrieves data from the database, stores data in a dataset, and reflects the changes made in the dataset to the database. The DataAdapter class acts as an intermediary for all the communication between the database and the DataSet object. The DataAdapter Class is used to fill a DataTable or DataSetObject with data from the database using the Fill() method. The DataAdapter class applies the changes made in dataset to the database by calling the Update() method.
The DataAdapter class provides four properties that represent the database command:
SelectCommand, InsertCommand, DeleteCommand, and UpdateCommand.
Ado.net, classic ado, linq, dataset, datatable, oledb, sqlclient, sqlcommandbuilder, Datareader, dat