ADO.Net

ADO.NET Overview
Most applications handle data, whether in the form of a database, text file, spreadsheet, whatever — the majority of modern day programs need access to an information store.
Now when Visual Basic 3.0 came along with its new and groovy data access capabilities, people were amazed. Suddenly a mass of Access desktop applications sprung up and the world was at peace.
Today, people are taking advantage of the latest data access technologies to improve scalability and boost interoperability. But it all needs speeding up - faster, faster!
And there's a chance that the tools we use today just won't be able to keep up with this demand. That's why with the .NET move, Microsoft have unveiled ADO.NET — its "evolutionary" step in data access technologies that promises the world and infinitely more.
But what exactly does it deliver?
ADO.NET brings along with it four, much-publicised advantages:
Interoperability - All data in ADO.NET is transported in XML format, meaning it's simply a structured text document that can be read by anyone on any platform.
Scalability - The client/server model is out. ADO.NET promotes the use of disconnected datasets, with automatic connection pooling bundled as part of the package.
Productivity - You can't just pick it up and run, but ADO.NET can certainly improve your overall development time. For example, "Typed DataSets" help you work quicker and produce more bug-free code.
Performance - Because ADO.NET is mainly about disconnected datasets, the database server is no longer a bottleneck and hence applications should incur a performance boost
In ADO.NET, functionality is split into two key class groups — content components and managed-provider components.
The content components essentially hold actual data and include the DataSet, DataTable, DataView, DataRow, DataColumn and DataRelation classes.
We also have the managed-provider components, which actually talk to the database to assist in data retrievals and updates. Such objects include the connection, command and data reader.
Also, the managed-provider components are split into two key groups — one designed for regular data sources, with another finely tuned specifically for SQL Server.
ADO.NET bundles with a bunch of content components. The most important are:
DataSet — This is a lot like the old Recordset object, except that it can hold multiple "tables" of data. You can also setup internal data constraints and relationships.
DataView — The DataView is similar to a regular database view. You can essentially use this object to filter tables inside the DataSet object.
Currently, there are two key sets of managed provider components — one designed for general data access (in System.Data.OleDb) and one fine-tuned for SQL Server (in System.Data.SqlClient).
Both of these comply with the standard data implementations defined in the System.Data.Common namespace.
So, what are the key managed-provider components?
Connection — OleDbConnection + SQLConnection — Like classic ADO, this object implements properties such as the connection string and state. We also have the typical .Open and .Close, plus .BeginTransaction returning an object to control a database transaction. Note that you no longer have a .Execute method on the Connection object.
Command — OleDbCommand + SqlCommand — This is the pipeline to the backend data. You can use the command to either .ExecuteNonQuery, which will action an SQL statement (such as a DELETE command) upon the data — or .ExecuteReader, which links straight in to the Data Reader object.
Data Reader — OleDbDataReader + SqlDataReader — This object essentially takes a stream of data from the Command object and allows you to read it. It's like a forward-only Recordset and is very efficient. However this uses a server-side cursor, so you should avoid it too much as it naturally requires an open connection.
Data Adapter — OleDbDataAdapter + SqlDataAdapter — The Data Adapter object provides an all-in-one stop for your data. It essentially serves as a middle man, going through your connection to retrieve data, then passing that into a DataSet. You can then pass the DataSet back to the Data Adapter, which will go and update the database. The SQL statements for each command are specified in the InsertCommand, UpdateCommand, InsertCommand and DeleteCommand properties.
Sample code using each of these objects will be given later
As more and more companies move toward XML as the standard for cross-platform communication, Microsoft is making an active effort to use this structure to power many of its new technologies.
One application of this is in ADO.NET — where literally all data is intrinsically stored in such a format.
And you can easily tap into this store by getting your information into a DataSet — then accessing the.ReadXml, .WriteXml and .GetXml functions.
In addition, you can parse XML programmatically using the System.Xml.XmlDocument object.