Overview of ADO.NET:

Overview of ADO.NET:
Most applications need to interact in stored data of some form as well as store user data for further retrieval. This data can be in a normal file like spreadsheet, plain text,xml files or in dedicated application to store data i.e. RDMS of some sort. RDMS system also has vendor specific implementation of retrieving data programmatically as they are designed to work with all mainstream programming languages this is pain on programmers part to work with these varied data stores as for each one programmer need to master its own way of interacting with these data store.
What is ADO.NET: ADO.NET is object-oriented set of libraries that provides consistent access to local or remote data in data sources like RDMS (Oracle, MSSQL, MYSQL, SYBASE etc.), XML files, text files or spreadsheet.


The ADO.NET classes are found in System.Data.dll, and are integrated with the XML classes found in System.Xml.dll. When compiling code that uses the System.Data namespace, reference both System.Data.dll and System.Xml.dll.
ADO.NET is broken down into 3 namespaces in the .NET framework. Namespaces are used to organize components into groups based on organizational and logical reasons.


Evolution of ADO.NET
The first data access model, DAO (data access model) was created for local databases with the built-in Jet engine which had performance and functionality issues. Next came RDO (Remote Data Object) and ADO (Active Data Object) which were designed for Client Server architectures but, soon ADO took over RDO. ADO was a good architecture but as the language changes so is the technology. With ADO, all the data is contained in a recordset object which had problems when implemented on the network and penetrating firewalls. ADO was a connected data access, which means that when a connection to the database is established the connection remains open until the application is closed. Leaving the connection open for the lifetime of the application raises concerns about database security and network traffic. Also, as databases are becoming increasingly important and as they are serving more people, a connected data access model makes us think about its productivity. For example, an application with connected data access may do well when connected to two clients, the same may do poorly when connected to 10 and might be unusable when connected to 100 or more. Also, open database connections use system resources to a maximum extent making the system performance less effective.

Why ADO.NET?
To cope up with some of the problems mentioned above, ADO .NET came into existence. ADO .NET addresses the above mentioned problems by maintaining a disconnected database access model which means, when an application interacts with the database, the connection is opened to serve the request of the application and is closed as soon as the request is completed. Likewise, if a database is Updated, the connection is opened long enough to complete the Update operation and is closed. By keeping connections open for only a minimum period of time, ADO .NET conserves system resources and provides maximum security for databases and also has less impact on system performance. Also, ADO .NET when interacting with the database uses XML and converts all the data into XML format for database related operations making them more efficient.
In short Goals of ADO.NET Architecture(w.r.t. DAO,RDO,ADO ) are:
• XML integration
• Disconnected data model
• Scalability


ADO.NET is broken down into 3 namespaces in the .NET framework. Namespaces are used to organize components into groups based on organizational and logical reasons.



XML and ADO.NET
ADO.NET leverages the power of XML to provide disconnected access to data. ADO.NET was designed hand-in-hand with the XML classes in the .NET Framework — both are components of a single architecture.
ADO.NET and the XML classes in the .NET Framework converge in the DataSet object. The DataSet can be populated with data from an XML source, whether it is a file or an XML stream. The DataSet can be written as World Wide Web Consortium (W3C) compliant XML, including its schema as XML Schema definition language (XSD) schema, regardless of the source of the data in the DataSet. Because the native serialization format of the DataSet is XML, it is an excellent medium for moving data between tiers making the DataSet an optimal choice for remoting data and schema context to and from an XML Web service.
The Dataset can also be synchronized with an XmlDataDocument to provide relational and hierarchical access to data in real time

The ADO.NET Data Architecture
Data Access in ADO.NET relies on two components: DataSet and Data Provider.
DataSet
The dataset is a disconnected, in-memory representation of data. It can be considered as a local copy of the relevant portions of the database. The DataSet is persisted in memory and the data in it can be manipulated and updated independent of the database. When the use of this DataSet is finished, changes can be made back to the central database for updating. The data in DataSet can be loaded from any valid data source like Microsoft SQL server database, an Oracle database or from a Microsoft Access database.DataSets only hold data and do not interact with a Data Source
The DataSet contains a collection of one or more DataTable objects made up of rows and columns of data, as well as primary key, foreign key, constraint, and relation information about the data in the DataTable objects.
Data Provider
The Data Provider is responsible for providing and maintaining the connection to the database. A Data Provider is a set of related components that work together to provide data in an efficient and performance driven manner. The .NET Framework currently comes with two DataProviders: the SQL Data Provider which is designed only to work with Microsoft's SQL Server 7.0 or later and the OleDb DataProvider which allows us to connect to other types of databases like Access and Oracle.
In ADO an attempt was made to create as generic methods as possible, whereas with ADO.NET, the optimized data providers for specific database products are created e.g.Mssql server. Optimized ADO.NET provider just like for MSSQL server are available for other database like Oracle & Mysql etc. from there vendors or by third party providers.

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.



Each Data Provider consists of the following component classes:
a) Connection: The Connection object which provides a connection to the database.
Microsoft Visual Studio .NET provides two types of Connection classes: the SqlConnection object, which is designed specifically to connect to Microsoft SQL Server 7.0 or later, and the OleDbConnection object, which can provide connections to a wide range of database types like Microsoft Access and Oracle. The Connection object contains all of the information required to open a connection to the database.
b) Command: The Command object which is used to execute a command.
The Command object is represented by two corresponding classes: SqlCommand and OleDbCommand. Command objects are execute commands to a database across a data connection. The Command objects can be used to execute stored procedures on the database, SQL commands, or return complete tables directly. Command objects provide three methods that are used to execute commands on the database:
1)ExecuteNonQuery: Executes commands that have no return values such as INSERT,
UPDATE or DELETE
2) ExecuteScalar: Returns a single value from a database query

3) ExecuteReader: Returns a result set by way of a DataReader object

c) DataReader: The DataReader object which provides a forward-only, read only, connected recordset.
Unlike other components of the Data Provider, DataReader objects cannot be directly instantiated. Rather, the DataReader is returned as the result of the Command object's ExecuteReader method.
The SqlCommand.ExecuteReader method returns a SqlDataReader object, and the OleDbCommand.ExecuteReader method returns an OleDbDataReader object. The DataReader can provide rows of data directly to application logic when you do not need to keep the data cached in memory. Because only one row is in memory at a time, the DataReader provides the lowest overhead in terms of system performance but requires the exclusive use of an open Connection object for the lifetime of the DataReader.
d) DataAdapter: The DataAdapter object which populates a disconnected DataSet with data and performs update
DataAdapter is essentially the middleman facilitating all communication between the database and a DataSet. The DataAdapter is used either to fill a DataTable or DataSet with data from the database with it's Fill method. After the memory-resident data has been manipulated, the DataAdapter can commit the changes to the database by calling the Update method. The
DataAdapter provides four properties that represent database commands:
1) SelectCommand
2) InsertCommand
3) DeleteCommand
4) UpdateCommand
e) When the Update method is called, changes in the DataSet are copied back to the database and the appropriate InsertCommand, DeleteCommand, or UpdateCommand is executed.

The DataAdapter uses Command objects to execute SQL commands at the data source to both load the DataSet with data, and reconcile changes made to the data in the DataSet back to the data source.
The following diagram illustrates the components of ADO.NET architecture.

Choosing between DataReader or a DataSet
When deciding whether your application should use a DataReader or a DataSet, you should consider the type of functionality that your application requires.
Use a DataSet to do the following:
• Remote data between tiers or from an XML Web service.
• Interact with data dynamically such as binding to a Windows Forms control or combining and relating data from multiple sources.
• Cache data locally in your application.
• Provide a hierarchical XML view of relational data and use tools like an XSL Transformation or an XML Path Language (XPath) Query on your data.
• Perform extensive processing on data without requiring an open connection to the data source, which frees the connection to be used by other clients.
If you do not require the functionality provided by the DataSet, you can improve the performance of your application by using the DataReader to return your data in a forward-only read-only fashion. Although the DataAdapter uses the DataReader to fill the contents of a DataSet, by using the DataReader you can receive performance gains because you will save memory that would be consumed by the DataSet, as well as saving the processing required to create and fill the contents of the DataSet.