ADO.NET

				Overview of ADO.NET ADO.NET 
provides an extensive set of .NET classes. 
• ADO.NET can be used in any of the languages that can run in VS.NET . 
• Using (XML), ADO.NET can ensure the efficient transfer of data to any application on any platform. 
• ADO.NET does not retain lengthy database locks or active connections that monopolize limited resources. 
• ADO.NET can be used in any consumer application that uses data sources such as MS SQL Server, OLE DB and XML. 
The ADO.NET Object Model is made up of 
• Data View
• Data Set
• Data Provider
• Data View 
speaks to the DataSet and is a special class designed for UI objects to bind to and can provide customized views of the DataSet. A DataView is only used in conjunction with a DataSet and never with a DataReader. 
DataSet 
• The DataSet is the core component of the disconnected architecture of ADO.NET that caches data locally on the client .
• The DataSet is explicitly designed for data access independent of any data source. 
• DataSet tracks changes that are made to the data it holds before updating the source data.
• DataSet are also fully XML-featured (GetXML, WriteXML).
• The data within a DataSet is manipulated via the set of standard APIs. 
• The DataSet is similar to a Recordset with CursorLocation = adUseClient, CursorType = adOpenStatic, and LockType = adLockOptimistic. 
The Key objects of DataProvider are
• Connection
• Command 
• DataAdaptor 
• DataReader 
. NET provides two distinct Data Providers and only one is used at a time.
- SQL Server .NET Data Provider
- OLE DB .NET Provider 
SqlClient Provider
• Provider uses a special protocol called TDS (Tabular Data Stream) to communicate directly with SQL Server. 
• It is a lightweight, highly optimized provider.
• I mproved efficiency, smaller memory footprint, and improved speed over the OLE DB provider.
• This Provider is represented in the .NET namespace as System.Data.SqlClient.
Ex: SqlConnection myConn = new SqlConnection 
("Data Source=localhost;Integrated Security=SSPI;"+"Initial Catalog=northwind"); 
myConn.Open(); 
OleDB Provider 
Using .NET's COM interoperability.
O ne important difference between OLE DB and SqlClient is that the OLE DB .NET Provider supports both manual and automatic transactions.
Automatically enlists in a transaction and obtains transaction details from Windows 2000 Component Services. 
This Data Provider is represented in the .NET namespace as System.Data.OleDb. 
Ex: OleDbConnection myConn = new OleDbConnection 
("Provider=SQLOLEDB;Data Source=localhost;" + "Integrated Security=SSPI;Initial Catalog=northwind"); 
myConn.Open(); 
• The Connection object represents a database connection of a .NET data provider. 
And it implements IDbConnection interface.
• A Connection is created and opened before Commands can be executed.
• It is strongly recommended that .NET data providers support the ConnectionString property using the familiar name-value pair system defined by OLE DB. Providers should use the same names to refer to connection properties.
• Connection objects are often expensive to obtain 
. • The implementation of IDbConnection should include, at a minimum, two constructors: a default constructor, as well as one that takes a connection string. 
• The Command object is responsible for formulating a request and passing it on to the data source. It is responsible for returning results as a DataReader, a scalar value or as parameters. 
• To implement a command object, create a class that implements IDbCommand .
• Implement the ExecuteReader method to return a resultset (or multiple resultsets) as a DataReader , the ExecuteScalar method to return a result as a scalar value, and the ExecuteNonQuery method to process a command at the data source that does not return a resultset but may return parameters. 
• The implementation of the DataAdapter will require minimal coding, as the System.Data.Common.DbDataAdapter class provides most of the implementation.
• The DbDataAdapter class leverages IDbConnection , IDbCommand , IDataReader etc.,.
• Implement the IDbDataAdapter interface which allows users of .NET data provider to reference the objects directly, instead of through interfaces such as IDbCommand . • 
• The implementation of the DataReader must provide two basic capabilities: forward-only access over one or more of the resultsets obtained by executing a Command , and access to the column values within each row.
• Extremely fast, Low Overhead way of retrieving information from a data stream.
• DataReader at any instant of time maintains a single row of data in memory.
• Better performance with this can be achieved with forward only, read-only data access. 
Advantages of ADO.NET 
• Better Performance (Extremely faster in committing database operations.)
• Optimized SQL Server (SQL Server Data Provider is highly optimized for interaction with SQL Server which uses SQL Server's own Tabular Data Stream.) 
• XML Support (Everything you do in ADO.NET at some point will boil out to XML.)
• Disconnected Operation Model (Data Set operates in an disconnected fashion hence easy to change the supported data source.)
• Rich Object Model (ADO.NET architecture is built on hierarchy of class inheritance and interface implementation.) 
Disadvantages of ADO.NET 
• Managed Only Access (ADO.NET can be used only from the managed code.) 
• Only three managed providers.
• Learning Curve (ADO.NET should be thought of more as the data access class library for use with the .NET framework. Programmers need to learn that even though some syntax may appear the same, there is actually a considerable amount of difference in the internal workings of many classes.) 
Contact 24/7 for all Doubts 
Srinivas Babu Thota Chandra Shekar 
Microsoft Certified Technology Specialist
Sr Software Engineer
Hyderabad
Enail : chandrashekarthota@gmail.com
Mobile: 91 99488 99388