ADO .NET namespaces. Review. ADO .NET Interfaces

Search other resources:

1. The System.Data namespace. General information

The System.Data namespace is the base namespace that combines other namespaces that use ADO .NET technology to access databases. It is not possible to create an application that uses ADO .NET without using this namespace.

This namespace declares the common classes (types) used by all ADO .NET providers, regardless of the data source they use. The list of System.Data tools includes:

types (classes) representing various database primitives, which include tables, columns, rows, constraints, etc.;
common interfaces implemented by data provider objects;
exceptions that are specific to databases (NoNullAllowedException, RowNotInTableException, MissingPrimaryKeyException).

The following list represents the main types (components) of the System.Data namespace:

class Constraint – implements constraints that can be imposed on one or more objects of the DataColumn type;
DataColumn class – represents one column in the database table;
DataRelation class – represents a relationship between tables of the DataTable type;
DataRow class – one row (record) in an object of the DataTable type;
DataSet class – represents a data cache in memory, consisting of an arbitrary number of interrelated DataTable objects;
DataTable class – a data block in the form of a table;
class DataTableReader – implements access to the data of the DataTable table in read-only mode and in the forward direction (from beginning to end);
DataView class – provides a custom data-bound view (databindable customized view) for performing sorting, filtering, searching, editing, and navigating operations. This class does not store data, but only contains a relationship with the data of the DataTable object. Operations that make changes to the DataView will affect the data in the DataTable. Conversely, changes to the data in the DataTable object will affect all DataView objects that display that data;
IDataAdapter interface – defines the basic behavior of the data adapter object (DataAdapter);
IDataParameter interface – defines the basic behavior of the parameter object (DbParameter);
IDataReader interface – defines the behavior of the data reader object (DbDataReader);
IDbCommand interface – defines the behavior of an object that implements in the SQL language (DbCommand);
IDbConnection interface – defines the behavior of the object that connects to the data storage (DbConnection);
IDbDataAdapter interface – extends IDataAdapter to obtain additional features of the data adapter object (DbDataAdapter);
IDbTransaction interface – defines the behavior of objects that implement a transaction.

⇑

2. Interfaces ADO .NET

2.1. Interface IDbConnection. Connection object

The IDbConnection interface contains a set of elements that are used:

to set up a connection to a specific data provider (DbConnection, SqlConnection, OledbConnection, OdbcConnection);
to get the object that performs the transaction. Such an object can be one of the following types: DbTransaction, SqlTransaction, OledbTransaction, OdbcTransaction.

An approximate implementation of the interface is as follows:

public interface IDbConnection : IDisposable
{
  string ConnectionString { get; set; }
  int ConnectionTimeout { get; }
  string Database { get; }
  ConnectionState State { get; }
  IDbTransaction BeginTransaction();
  void ChangeDatabase(string databaseName);
  void Close();
  IDbCommand CreateCommand();
  void Open();
}

The components of the interface have the following purposes:

ConnectionString – database connection string;
ConnectionTimeot—time in seconds during which the object of type DbConnection (or a derived type) tries to establish a connection with the data source;
Database – the database name;
State—describes the current state of the connection to the data source;
BeginTransaction() – starts executing a transaction in the database;
ChangeDatabase() – changes the database for an open connection with the current data provider (SqlConnection, OledbConnection, etc.);
Close() – closes the connection to the database that was previously opened by the Open() method. The combination of these methods is implemented in database connection mode (the so-called connected layer);
CreateCommand() – creates a command in SQL language for the current connection;
Open() – opens a connection to access the database in connected mode (connected layer).

⇑

2.2. Interface IDbTransaction. Working with transactions

The IDbTransaction interface defines components that allow you to programmatically interact with the transaction session and the corresponding data store. A transaction is created using the BeginTransaction() method, which is described in the IDbConnection interface.

The IDbTransaction interface has the following declaration:

public interface IDbTransaction : IDisposable
{
  IDbConnection Connection { get; }
  IsolationLevel IsolationLevel { get; }
  void Commit();
  void Rollback();
}

Interface elements have the following purpose:

Connection interface – reference to the current connection to the data storage;
enumeration IsolationLevel – defines a transaction that blocks behavior for the connection;
Commit() – a method that commits a transaction in the database;
Rollback() – method that rolls back a transaction.

⇑

2.3. Interface IDbCommand. Set the SQL-command

The IDbCommand interface is implemented by classes whose objects allow you to programmatically work with SQL statements, stored procedures, and parameterized queries. Also, objects of these classes support access to the provider’s data reader type using the ExecuteReader() method.

The IDbCommand interface specification is as follows:

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();
  object ExecuteScalar();
  void Prepare();
}

The following is the purpose of the elements of the IDbCommand interface:

CommandText property – text of the command in Transact-SQL (T-SQL), the name of the table or stored procedure to be executed on the data source;
property CommandTimeout – time interval to wait before completing the attempt to execute a command and generate the error;
enumeration CommandType – defines how the command line CommandText will be interpreted (SQL text, stored procedure, etc.);
property Connection – an object of the DbConnection type that defines the connection for which this DbCommand object is executed;
property Parameters – collection of parameters (if any) received by the command in the SQL language;
property Transaction – the object of the transaction performed by the current object of the DbCommand type;
property UpdatedRowSource – determines how the result of the SQL command is applied to an object of the DataRow type when the UPDATE command is executed in the DbDataAdapter object;
method Cancel() – attempts to cancel the execution of the command formed in the DbCommand type object;
method CreateParameter() – creates a new instance of the DbParameter type (SqlParameter, OledbParameter, OdbcParameter);
method ExecuteNonQuery() – executes a Transact-SQL command for a given connection. Method returns the number of rows affected;
method ExecuteReader() – sends the text described in DbCommand.CommandText to the DbCommand.Connection object and builds a collection (set) of records of the DbDataReader type. When constructing the DbDataReader collection, the value of the CommandBehavior enumeration is taken into account;
method ExecuteScalar() – executes the query and returns the first column of the first row in the result set of the query. In this case, additional columns or rows are ignored;
method Prepare() – creates a prepared version of the command on the SQL Server instance.

⇑

2.4. Interfaces IDbDataAdapter, IDataAdapter. Data adapter between dataset and database

Data adapters are designed to perform read/write operations between DataSet object and a particular data store (database). Data adapters are described in the IDbDataAdapter and IDataAdapter interfaces. The IDbDataAdapter interface describes commands for reading, inserting, editing, and deleting data from the table based on SQL statements. The declaration of interface is as follows

public interface IDbDataAdapter : IDataAdapter
{
  IDbCommand DeleteCommand { get; set; }
  IDbCommand InsertCommand { get; set; }
  IDbCommand SelectCommand { get; set; }
  IDbCommand UpdateCommand { get; set; }
}

In this interface, elements have the following purposes:

property DeleteCommand property – describes the DELETE command in SQL language;
property InsertCommand – encapsulates the INSERT SQL command;
property SelectCommand – encapsulates the command for selecting data from the storage (SELECT);
property UpdateCommand – describes the UPDATE SQL-command.

The IDbDataAdapter behavior defines the IDataAdapter interface. This interface declares the tools that implement the main function of the data adapter: providing data exchange between DataSet objects and the data store (database). This interface also allows you to map column names from the database to more meaningful display names.

Interface declaration is as follows

public interface IDataAdapter
{
  MissingMappingAction MissingMappingAction { get; set; }
  MissingSchemaAction MissingSchemaAction { get; set; }
  ITableMappingCollection TableMappings { get; }
  int Fill(System.Data.DataSet dataSet);
  DataTable FillSchema(DataSet dataSet, SchemaType schemaType);
  IDataParameter[] GetFillParameters();
  int Update(DataSet dataSet);
}

The components of the IDataAdapter interface have the following purpose:

property MissingMappingAction – used for table names or column names. The property specifies whether to pass for filtering or error detection for non-displayed source tables or columns along with their source names;
property MissingSchemaAction – indicates whether missing source tables, columns and their relationships are added (ignored) to the dataset schema or cause an error;
property TableMappings – indicates how the source table is mapped onto the table in a dataset;
method Fill() – based on the name of the data set, adds or updates rows in the data set so that they match the rows in the data set. When making changes, the name of the dataset is used. It also creates a DataTable object named Table;
method FillSchema() – adds a DataTable object named Table to a specific DataSet object with simultaneous configuration of the schema in accordance with the specified schema object of the SchemaType type;
method GetFillParameters() – returns a set of user parameters when SELECT SQL-query is executed;
the Update() method – for a given DataSet, calls one of the INSERT, UPDATE, or DELETE operations for each inserted, modified, and deleted row in the table named Table of the DataTable object.

⇑

2.5. Interfaces IDataReader and IDataRecord. Reading rows from the database table

The IDataReader interface declares common functions used to read data from the store. If a program declares an object that is compatible with the IDataReader type, then the data set can be viewed using that object. Browsing occurs in only one (forward) direction. Data modification is prohibited.

The declaration of the IDataReader interface is as follows:

public interface IDataReader : IDisposable, IDataRecord
{
  int Depth { get; }
  bool IsClosed { get; }
  int RecordAffected { get; }
  void Close();
  DataTable GetSchemaTable();
  bool NextResult();
  bool Read();
}

The properties and methods of the IDataReader interface have the following purposes:

property Depth – returns a value indicating the nesting depth for the current row;
property IsClosed – returns a value indicating whether the data reader object is closed;
property RecordAffected – returns the number of rows affected by insertion, deletion, and modification methods as a result of SQL-query execution;
method Close() – closes the IDataReader object;
method GetSchemaTable() – returns an object of the DataTable type that describes the metadata of the column of the IDataReader object;
method NextResult() – moves to the next result in case of reading a result based on a batch SQL-command;
the Read() method – moves to the next record. If there is no record, then the method returns false.

The IDataRecord interface is the base interface for the IDataReader interface. This interface implements elements that allow you to extract strongly typed values (int, float, short, etc.) from the data stream. The IDataRecord interface declares many methods of the GetXXX() type. The abbreviated fragment of the interface declaration is as follows:

public interface IDataRecord
{
  int FieldCount;
  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);

  ...

  // Інші елементи
  // ...
}

The content of some elements of the IDataRecord interface is explained below:

property FieldCount – returns the number of columns in the current row;
GetBoolean() – returns the value of a certain column as a boolean;
GetByte() – returns the value of a certain column as a byte;
GetChar() – returns the value of a certain column as a char;
GetDateTime() – returns the value of a specific column as a DateTime;
GetDecimal() – returns the value of a certain column as a Decimal;
GetFloat() – returns the value of a certain column as a float;
GetInt16(), GetInt32(), GetInt64() – returns the value of a certain column as short, int, long.

⇑

2.6. Interfaces IDbDataParameter and IDataParameter. Parameter in the request

The IDbCommand interface has a Parameters property that returns a strongly typed collection. This collection implements the IDataParameterCollection interface. This interface gives access to a set of classes compatible with the IDbDataParameter interface (parameter objects). The IDbDataParameter interface extends the IDataParameter interface.

The IDbDataParameter interface declaration looks like this

public interface IDbDataParameter : IDataParameter
{
  byte Precision { get; set; }
  byte Scale { get; set; }
  int Size { get; set; }
}

The elements of the IDbDataParameter interface have the following purpose:

Precision – determines the accuracy of numerical parameters;
Scale – defines the scale of numerical parameters;
Size – parameter size.

The IDataParameter interface has the following declaration

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; }
}

The components of the IDataParameter interface have the following purpose:

enumeration DbType – defines the data type of a field, property, or object of the Parameter type of the .NET provider;
property Direction – a value that determines whether the parameter is only input (input-only), only output (output-only), bidirectional (bidirectional), or a return parameter from a stored procedure;
properry IsNullable – returns a value that determines whether the parameter accepts a NULL value;
property ParameterName – the name of an object of the IDataParameter type;
property SourceColumn – the name of the column that is the source of display on the DataSet object. This source is used to load or return an IDataParameter.Value;
property SourceVersion – reads or sets an object of type DataRowVersion in order to use it when loading the IDataParameter.Value object;
property Value – intended for reading or setting the parameter value.

BestProg

C#. ADO .NET. Namespaces. Review. ADO .NET Interfaces