[ADO.NET] Architecture

◎Below Image from MSDN


▣ADO.NET Components
◎.NET Framework Data Provider
    ▷Designed for data manipulation and fast, forward-only, read-only access to data
    ▷ADO.NET supports multiple data providers
    ▷Data Connection Object : connectivity to a data source
        ▶Transaction

    ▷Data Command Object  : enables access to database commands to return data, modify data, run stored procedures, and send or retrieve parameter information
        ▶Parameters
    ▷Data Reader Object  : provides a high-performance stream of data from the data source
    ▷Data Adapter Object : provides the bridge between the DataSet object and the data source, the DataAdapter uses Command objects to execute SQL commands at the data source
        ▶Select Command
        ▶Insert Command
        ▶Update Command
        ▶Delete Command
◎DataSet
   ▷Designed for data access independent of any data source
   ▷Can be used with multiple and differing data sources

   ▷Represents a local copy of any number of related data tables

   ▷Contains one or more DataTable
   ▷DataTable objects made up of rows and columns of data,primary key, foreign key, constraint and relation information

▣ADO.NET Connectin Objects
◎ Any DB
◎ XML

◎ADO.NET <-> XML 
●DataSet be filled with data from XML file or XML stream

◎Namespace

Microsoft.SqlServer.Server	CLR and SQL Server 2005 and later integration services
System.Data	defines the core ADO.NET types e.g., DataSet and DataTable
System.Data.Common	contains types shared between all ADO.NET data providers including the common abstract base classes.
System.Data.Sql	allow you to discover Microsoft SQL Server instances installed on the current local network
System.Data.SqlTypes	native data types used by Microsoft SQL Server

▷System.Data.dll

▷System.Data.SqlClient.dll
▷System.Data.OracleClient.dll
▷System.Data.Entity.dll 

◎ADO.NET Data Provider 

Type of Object	Base Class	Relevant Interfaces	Meaning in Life
Connection	DbConnection	IDbConnection	Provides the ability to connect to and disconnect from the data store. Connection objects also provide access to a related transaction object
Command	DbCommand	IDbCommand	Represents a SQL query or a stored procedure. Command objects also provide access to the provider’s data reader object.
DataReader	DbDataReader	IDataReader,IDataRecord	Provides forward-only, read-only access to data using a server-side cursor.
DataAdapter	DbDataAdapter	IDataAdapter,IDbDataAdapter	Transfers DataSets between the caller and the data store. Data adapters contain a connection and a set of four internal command objects used to select, insert, update, and delete information from the data store.
Parameter	DbParameter	IDataParameter,IDbDataParameter	Represents a named parameter within, a parameterized query
Transaction	DbTransaction	IDbTransaction	Encapsulates a database transaction

※Although some core namespace is different, the base class is same.
DbConnection
<-SqlConnection
<-OracleConnection
<-OdbcConnection
<-MySqlConnection

 ※Microsoft ADO.NET Data Providers 

Data Provider	Namespace	Assembly
OLE DB	System.Data.OleDb	System.Data.dll
Microsoft SQL Server	System.Data.SqlClient	System.Data.dll
Microsoft SQL Server Mobile	System.Data.SqlServerCe	System.Data.SqlServerCe.dll
ODBC	System.Data.Odbc	System.Data.dll

 
◎System.Data Namespace 
▷ IDbConnection Interface 
 

public interface IDbConnection : IDisposable 

{ 

  string ConnectionString { get; set; } 

  int ConnectionTimeout { get; } 

  string Database { get; } 

  ConnectionState State { get; } 

 

  IDbTransaction BeginTransaction(); 

  IDbTransaction BeginTransaction(IsolationLevel il); 

  void ChangeDatabase(string databaseName); 

  void Close(); 

  IDbCommand CreateCommand(); 

  void Open(); 

} 

◎IDbTransaction Interface 

public interface IDbTransaction : IDisposable 

{ 

  IDbConnection Connection { get; } 

  IsolationLevel IsolationLevel { get; } 

 

  void Commit(); 

  void Rollback(); 

} 

◎IDbCommand Interface

public interface IDbCommand : IDisposable 

{ 

  string CommandText { get; set; } 

  int CommandTimeout { get; set; } 

  CommandType CommandType { get; set; } 

  IDbConnection Connection { get; set; } 

  IDataParameterCollection Parameters { get; } 

  IDbTransaction Transaction { get; set; } 

  UpdateRowSource UpdatedRowSource { get; set; } 

 

  void Cancel(); 

  IDbDataParameter CreateParameter(); 

  int ExecuteNonQuery(); 

  IDataReader ExecuteReader(); 

  IDataReader ExecuteReader(CommandBehavior behavior); 

  object ExecuteScalar(); 

  void Prepare(); 

} 

◎ IDbDataParameter

public interface IDbDataParameter : IDataParameter 

{ 

  byte Precision { get; set; } 

  byte Scale { get; set; } 

  int Size { get; set; } 

} 

◎IDataParameter Interfaces

public interface IDbDataParameter : IDataParameter 

{ 

  byte Precision { get; set; } 

  byte Scale { get; set; } 

  int Size { get; set; } 

} 

public interface IDataParameter 

{ 

  DbType DbType { get; set; } 

  ParameterDirection Direction { get; set; } 

  bool IsNullable { get; } 

  string ParameterName { get; set; } 

  string SourceColumn { get; set; } 

  DataRowVersion SourceVersion { get; set; } 

  object Value { get; set; } 

} 

◎IDbDataAdapter

public interface IDbDataAdapter : IDataAdapter 

{ 

  IDbCommand DeleteCommand { get; set; } 

  IDbCommand InsertCommand { get; set; } 

  IDbCommand SelectCommand { get; set; } 

  IDbCommand UpdateCommand { get; set; } 

} 

◎IDataAdapter

public interface IDataAdapter 

{ 

  MissingMappingAction MissingMappingAction { get; set; } 

  MissingSchemaAction MissingSchemaAction { get; set; } 

  ITableMappingCollection TableMappings { get; } 

 

  int Fill(DataSet dataSet); 

  DataTable[] FillSchema(DataSet dataSet, SchemaType schemaType); 

  IDataParameter[] GetFillParameters(); 

  int Update(DataSet dataSet); 

} 

◎IDataReader
can iterate over the result set in a forward-only, read-only manner

 

public interface IDataReader : IDisposable, IDataRecord 

{ 

  int Depth { get; } 

  bool IsClosed { get; } 

  int RecordsAffected { get; } 

 

  void Close(); 

  DataTable GetSchemaTable(); 

  bool NextResult(); 

  bool Read(); 

} 

◎IDataRecord
can iterate over the result set in a forward-only, read-only manner

public interface IDataRecord 

{ 

  int FieldCount { get; } 

  object this[ string name ] { get; }

  object this[ int i ] { get; } 

  bool GetBoolean(int i); 

  byte GetByte(int i); 

  char GetChar(int i); 

  DateTime GetDateTime(int i); 

  decimal GetDecimal(int i); 

  float GetFloat(int i); 

  short GetInt16(int i); 

  int GetInt32(int i); 

  long GetInt64(int i); 

... 

  bool IsDBNull(int i); <---check specific field is null

}