ADO.NET Overview

I have done quite a bit of hands-on development with ADO.NET, but I eventually switched to NHibernate for my DAL.

I also did quite a bit of LINQ-to-SQL (migrated from NHibernate to LINQ-to-SQL), so I am familiar with LINQ syntax.

When I first looked at Entity Framework during DevConnections 2008, I was a bit turned off by its complexity. It was in beta at that point, and I never went back and revisited it. I guess I'll be doing so now, as it is a decent portion (11% according to the Skills Measured) on the Exam 70-561: ADO.NET Application Development.

Starting with a basic review of ADO.NET (just wanted to jot down the basics that I recall off the top of my head, I'll fill in more of the detailed nuances from MSDN later):

Can work in a connected or disconnected data architecture.

Disconnected data architecture:

• Disconnected architecture makes use of DataSet, DataTable, DataColumn, DataRow to hold a copy of the data in memory.
• These classes belong to the System.Data namespace and are Database vendor agnostic.
  
• One DataSet contains one-to-many DataTables.
• One DataTable contains one-to-many DataColumns, which represent the structure of the table.
• The DataTable contains zero-to-many DataRows, which represent the content of the table.

Bridging DataSet to DB implementation (Populating DataSets):

• DataSets are populated via DataAdapters: calling DataAdapter.Fill(DataSet). The DataTables can also be filled by calling DataAdapter.Fill(DataTable). DataAdapter.Fill is overloaded.
  
• DataAdapters are Database vendor specific.
  
• DataAdapters use Commands, which are also Database vendor specific. There are properties on DataAdapter: SelectCommand, UpdateCommand, InsertCommand, DeleteCommand.
• Commands contain a reference to a Connection, which represents the actual physical connection to the Database instance. Connections are Database vendor specific.

Here is a list of the classes (DataAdapter, Command, Connection) and the corresponding version by Database vendor:

DataAdapter

• System.Data.SqlClient.SqlDataAdapter
• System.Data.OracleClient.OracleDataAdapter
• System.Data.OleDb.OleDbDataAdapter
• System.Data.Odbc.OdbcDataAdapter

Command

• System.Data.SqlClient.SqlCommand
• System.Data.OracleClient.OracleCommand
• System.Data.OleDb.OleDbCommand
• System.Data.Odbc.OdbcCommand

Connection

• System.Data.SqlClient.SqlConnection
• System.Data.OracleClient.OracleConnection
• System.Data.OleDb.OleDbConnection
• System.Data.Odbc.OdbcConnection