ADO.NET
ADO.NET is part of the .NET platform developed by Microsoft . It is a collection of classes that guarantee access to relational databases .
ADO.NET is considered the successor to ActiveX Data Objects (ADO), but has nothing to do with ActiveX technology. In fact, numerous functions have been added so that one can speak of a new development.
tasks
The task of the classes (located in the System.Data namespace ) is the database connection and data storage in the main memory. For this purpose, there are classes that establish a connection to a database ( Microsoft SQL Server , Oracle etc.) (so-called connection classes), classes that represent tables in the main memory and make it possible to work with them (so-called DataTables ) and classes that stand for entire databases in the main memory (so-called data sets ).
Other classes regulate the connection to a database. So-called data providers exist for the connection to the physical database . The databases can be mapped in XML format, which is why there are classes for direct access to XML in the System.Xml namespace .
ADO.NET architecture
The main concern of ADO.NET is to completely separate the data acquisition from the provision and display of the data. To achieve this goal, ADO.NET is divided into the three main components DataSet , data provider and the data binding classes . The latter, however, are not an integral part of ADO.NET; instead, they serve to connect the control elements to a data set.
Data provider
The data provider is the interface to a database. He must have technical information about the database, i. i.e. he needs to know the database. Individual data providers exist for different databases. The data providers Microsoft SQL Server and OLE DB are included as standard in the .NET Framework . Also for many open source databases, such as There are .NET data providers such as MySQL .
The four core components of the .NET data provider are:
- Connection : Establishes a connection that is used for communication with a data source. Since .NET 2.0 it is possible to load extensive metadata to the database when connecting .
- Command : Executes statements, stored procedures, and action queries. These include SELECT , UPDATE or DELETE commands.
- DataAdapter : The DataAdapter fills a DataSet with data and matches updates with the data source. It acts as a bridge between the data source and a DataSet object.
- DataReader : This is a forward-looking data record reader that only allows read access to the data. It is not possible to navigate through the data records because they are processed sequentially.
DataSet
A DataSet represents the memory-resident, relational database in the actual application. It is a memory dump of the actual database. A DataSet is always used when data is required more than once and is changed by the application. In this case, the data are stored in the DataSet via the DataAdapter , where they are available to the application for further use.
The most important classes of the DataSet are:
- DataSet : This class represents a logical schema . The database schema manages relationships between the tables contained in the class and ensures that the database is relational.
- DataTable : The DataTable object represents a database table. It is a data store with data records and columns.
- DataRow : The DataRow class represents a concrete data record in a DataTable. A DataRow instance is always bound to a DataTable object.
- DataView : Used to filter (e.g. WHERE ) and sort (e.g. ORDER BY ) data in a DataTable. A data view can be used to create a special view of the data in a table.
restrictions
In ADO.NET 1.x, only one database command could be active per connection at certain times, for example a DataReader read access to the database. Attempts at parallel access were not possible and led to error messages. This architecture can be referred to as Single Active Result Sets (SARS).
In contrast, ADO.NET 2.0 supports Multiple Active Result Sets (MARS), i.e. the multiple use of a connection. MARS is available for Microsoft SQL Server 2005 and 2008 and is deactivated there by default.
example
- Configuration of the app.config or web.config
<configuration>
<!-- Connection String für Verbindung zur Datenbank -->
<connectionStrings>
<add name="MyConnectionString"
providerName="System.Data.SqlClient"
connectionString="Data Source=(localdb)\v11; InitialCatalog=MyDatabaseName" />
</connectionStrings>
<!-- Datenbank-Provider Factory -->
<!-- (meist in machine.config konfiguriert) -->
<system.data>
<DbProviderFactories>
<add name="SqlClient Data Provider"
invariant="System.Data.SqlClient"
description=".NET Framework Data Provider for SQL Server"
type="System.Data.SqlClient.SqlClientFactory, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" />
</DbProviderFactories>
</system.data>
</configuration>
- Establishing a database connection
var settings = ConfigurationManager.ConnectionStrings["MyConnectionString"];
var factory = DbProviderFactories.GetFactory(settings.ProviderName);
using (var connection = factory.CreateConnection())
{
if (connection == null) throw new Exception("Could not create a connection.");
connection.ConnectionString = settings.ConnectionString;
// use DbParameters to prevent SQL Injections
var age = factory.CreateParameter();
if (age == null) throw new Exception("Could not create parameter.");
age.ParameterName = "@age";
age.Value = 25;
// get a DbCommand with factory method
var command = connection.CreateCommand();
command.CommandText = "SELECT * FROM dbo.People WHERE Age > @age";
command.CommandType = CommandType.Text;
command.Parameters.Add(age);
// open connection to database
await connection.OpenAsync();
// Variant 1: read query results
var dataReader = await command.ExecuteReaderAsync();
while (await dataReader.ReadAsync())
{
Console.WriteLine("Id: '{0}', FirstName: '{1}', MiddleName: '{2}', LastName: '{3}'",
dataReader["id"], dataReader["firstname"], dataReader["middlename"], dataReader["lastname"]);
}
dataReader.Close();
// Variant 2: use a data adapter to fill a data table
using (var dataAdapter = factory.CreateDataAdapter())
{
if (dataAdapter == null) throw new Exception("Could not create a data adapter.");
dataAdapter.SelectCommand = command;
var dataTable = new DataTable();
dataAdapter.FillSchema(dataTable, SchemaType.Source); // get table schema
dataAdapter.Fill(dataTable); // get data
using (var dataTableReader = new DataTableReader(dataTable))
{
while (await dataTableReader.ReadAsync())
{
Console.WriteLine("Id: '{0}', FirstName: '{1}', MiddleName: '{2}', LastName: '{3}'",
dataTableReader["id"], dataTableReader["firstname"], dataTableReader["middlename"], dataTableReader["lastname"]);
}
}
}
}
See also
literature
- Andrew Troelsen: Pro C # 5.0 and the .Net 4.5 Framework . Springer, 2012, ISBN 978-1-4302-4233-8 , pp. 1560 (English).