ADO.NET
ADO.NET is a part of. NET platform developed by Microsoft. It is a collection of classes that provide access to relational databases.
ADO.NET is the successor to the ActiveX Data Objects ( ADO), but has nothing to do with the ActiveX technology. In fact, it has been expanded to include numerous functions, so that one can speak of a new development.
- 4.1 Querying Data 4.1.1 Lazy, Eager Loading and Explicit
Tasks
The object of the classes (which are located in the namespace System.Data) is the database connection and data storage in memory. To this end, there are classes that connect to a database (Microsoft SQL Server, Oracle, etc. ) make (so-called Connection classes ), classes that represent tables in memory, and make it possible to work with them (so called Data Tables) and classes that for all databases are in memory (called datasets ).
Other classes define the connection to a database. For the connection to the physical database exist so-called data provider. The databases can be mapped to XML format, which is why there are classes for direct access to XML in the System.Xml namespace.
Architecture of ADO.NET
The main concern of ADO.NET is to completely separate the data collection of the provision and display of data. To achieve this goal, ADO.NET splits into the three main components DataSet, data provider and the classes of data binding. However, the latter do not represent an integral part of ADO.NET, instead they are used to connect the control to a DataSet.
Data provider
The data provider is the interface to a database. It must have technical information about the database, i.e., it must know the database. For different databases exist individual data provider. In the. NET Framework data provider MS SQL Server and OLE DB are included by default. Also, for many open source databases such as MySQL, exist. NET data provider.
. The four core components of the NET Data Provider are:
- Connection: Establishes a connection that is used to communicate with a data source. Since the. NET 2.0 it is possible to load extensive metadata to the database at the link.
- Command: Executes statements, stored procedures, and action queries. These include SELECT, UPDATE, or DELETE commands.
- DataAdapter: The DataAdapter fills a DataSet with data and compensates for updates from the data source. It acts as a bridge between the data source and a DataSet object.
- DataReader: It is a forward data set reader that allows only read access to the data. Navigation through the data is not possible here, since these are processed sequentially.
DataSet
A DataSet represents the memory-resident relational database in the actual application. This is a dump of the database itself. A DataSet is always used when data is needed several times and changed by the application. In this case, the data on the DataAdapter in the DataSet are stored where they are available to the application for further use.
The major classes of the DataSet:
- 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 dar. It is a data storage with records and columns.
- DataRow: The DataRow class represents a specific record in a DataTable. A DataRow instance is always bound to a DataTable object.
- DataView: Used for filtering ( for example, WHERE ) and Sorting (eg ORDER BY ) of data in a DataTable. About a DataView can be created a special view to the data of a table.
Limitations
In ADO.NET 1.x could at certain times per connection only one database command to be active, for example, a DataReader has read access to the database. Attempts by a parallel access were not possible and led to error messages. This architecture can be described as single active result sets (SARS).
ADO.NET 2.0 does support Multiple Active Result Sets (MARS), which is the multiple use of a compound. MARS is available for SQL Server 2005 and 2008 and there has been deactivated in the standard.
ADO.NET Entity Framework
Using ( MyDbEntities context = new MyDbEntities ()) { / / Create two entities Person person1 = new Person () { FirstName = " William " LastName = "Adama " DateOfBirth = DateTime.Now }; Person person2 = new Person () { FirstName = " Laura " LastName = " Roslin " DateOfBirth = DateTime.Now }; / / Create an address entity Address address = new Address ( ) { Street = " 70 Market Street " City = "Philadelphia", State = "PA " Zip = " 12345" }; / / First variant context.Persons.Add ( person1 ); / / Second variant / / Link with the context and mark as added. context.Entry ( person2 ) State = EntityState.Added. ; / / Third variation / / The Entity is hung on an already observed from the context entity person1.Address.Add (address); / / Save changes to the context in the database context.SaveChanges (); } Query data
Query all data from a data set:
Using ( MyDbEntities context = new MyDbEntities ())
{
IEnumerator
Using ( MyDbEntities context = new MyDbEntities ()) { var persons = context.Persons; / / Database query is executed and returned as a list var = allPersons persons.ToList (); / / No more database queries by using the list foreach ( Person person in allPersons ) {/ * ... * /} foreach ( Person person in allPersons ) {/ * ... * /} } Searches a specific object in the database:
Person person = context.Persons.SingleOrDefault (p => p.PersonId == PersonID ); Or with LINQ:
Person person = (from p in context.Persons where p.PersonId == PersonID select p) SingleOrDefault ().; LINQ Selectors method result Single () Returns the one element which provides the request. Throws an exception if there is no or multiple results are returned. SingleOrDefault () Returns the one element which provides the request. Returns null if no results are delivered. Throws an exception if multiple results are returned. First () Returns the first element, if the query returns one or more results. Throws an exception if no results are returned. FirstOrDefault () Returns the first element, if the query returns one or more results. Returns null if no results are returned. In Db objects is also the Find ( ) method. This searches the first object in memory and makes a database query if the object is not found in memory:
Person person = context.Persons.Find ( PersonID ); Lazy, Eager Loading and Explicit
The Entity Framework uses the normal case, lazy loading, are then loaded with the data from the database when the data are requested:
/ / Lazy loading / / Only the people be queried and loaded var persons = context.Peoples; If more data must be loaded with, the eager loading is used:
/ / Eager loading / / Addresses are already loaded in the inquiry of persons var persons = context.Peoples.Include ( " Addresses "); / / LINQ to Entities example of eager loading var persons = from p in context.Peoples.Include ( " Addresses " ) where p.FirstName == fname select p; or type-safe from EF5:
/ / LINQ to Entities example of eager loading var persons = from p in context.Peoples.Include (p => p.Adresses ) where p.FirstName == fname select p; The explicit loading (explicit loading ) of the data is similar to the lazy loading, but allows the loading of navigation properties ( navigation properties ).
/ / Explicit loading var persons = context.Peoples; / / How lazy loading; Addresses are not loaded as foreach ( var person in persons) { person.Adresses.Load (); / / Explicit loading; navigation properties for addresses are loaded foreach (var address in person.Addresses ) { / / ... } } update Entities
Delete entities
Delete any entity in the Entity Framework 4:
Using ( MyDbEntities context = new MyDbEntities ()) { / / Query of an entity from the database Person person = ( for p in context.Persons where p.Id == id select p) SingleOrDefault ().; if ( person! = null) { context.Persons.DeleteObject (person); context.SaveChanges (); } } Delete any entity in the Entity Framework 5:
Using ( MyDbEntities context = new MyDbEntities ()) { / / Query of an entity from the database Person person = ( for p in context.Persons where p.Id == id select p) SingleOrDefault ().; if ( person! = null) { context.Entry (person) State = EntityState.Deleted. ; / / Mark the entity for deletion context.SaveChanges (); / / Delete Entity in the database } } Precompiled queries
Database queries are compiled by the Entity Framework in the database interface matching requests. However, this process takes time, so compiled queries - if they are needed again - not discarded but stored in an object and should be reused later.
To save time 4 when first using a query with the Entity Framework, queries can be precompiled.
Static Func < MyDbEntities, int, ObjectQuery
Transactions
Using ( MyDbEntities context = new MyDbEntities ()) { using ( TransactionScope scope = new TransactionScope ()) { / / The TransactionScope examines the latest on the Context / / Stack trace and automatically linked with this try { / / Editing entities context.SaveChanges (); / / Save the changes to the database scope.Complete (); / / Transaction is completed } catch ( InvalidOperationException e) { / / Transaction failed } } / / Scope.Dispose () } / / Context.Dispose () Code First Migrations
Code First Migrations is a set of PowerShell scripts which facilitate the database migration.
- Enable migration created a migration script
- Add- Migration Create a script for the production of the current database structure
- Update Database Bring the database structure to a particular condition. By default, the latest migration script will be used.