ADO.NET provides consistent access to data sources such as SQL Server and XML, and to data sources exposed through OLE DB and ODBC. Data-sharing consumer applications can use ADO.NET to connect to these data sources and retrieve, handle, and update the data that they contain.
ADO.NET separates data access from data manipulation into discrete components that can be used separately or in tandem. ADO.NET includes .NET Framework data providers for connecting to a database, executing commands, and retrieving results. Those results are either processed directly, placed in an ADO.NET DataSet object in order to be exposed to the user in an ad hoc manner, combined with data from multiple sources, or passed between tiers. The DataSet object can also be used independently of a .NET Framework data provider to manage data local to the application or sourced from XML.
The ADO.NET classes are found in System.Data.dll, and are integrated with the XML classes found in System.Xml.dll. For sample code that connects to a database, retrieves data from it, and then displays that data in a console window.
ADO.NET provides functionality to developers who write managed code similar to the functionality provided to native component object model (COM) developers by ActiveX Data Objects (ADO). We recommend that you use ADO.NET, not
Privacy Statement: The System.Data.dll, System.Data.Design.dll, System.Data.OracleClient.dll, System.Data.SqlXml.dll, System.Data.Linq.dll, System.Data.SqlServerCe.dll, and System.Data.DataSetExtensions.dll assemblies do not distinguish between a user's private data and non-private data. These assemblies do not collect, store, or transport any user's private data. However, third-party applications might collect, store, or transport a user's private data using these assemblies.
1、ADO.NET Architecture
Data processing has traditionally relied primarily on a connection-based, two-tier model. As data processing increasingly uses multi-tier architectures, programmers are switching to a disconnected approach to provide better scalability for their applications.
ADO.NET Components
The two main components of ADO.NET 3.0 for accessing and manipulating data are the .NET Framework data providers and the DataSet.
.NET Framework Data Providers
The .NET Framework Data Providers are components that have been explicitly designed for data manipulation and fast, forward-only, read-only access to data. The Connection object provides connectivity to a data source. The Command object enables access to database commands to return data, modify data, run stored procedures, and send or retrieve parameter information. The DataReader provides a high-performance stream of data from the data source. Finally, the DataAdapter provides the bridge between the DataSet object and the data source. The DataAdapter uses Command objects to execute SQL commands at the data source to both load the DataSet with data and reconcile changes that were made to the data in the DataSet back to the data source.
The DataSet
The ADO.NET DataSet is explicitly designed for data access independent of any data source. As a result, it can be used with multiple and differing data sources, used with XML data, or used to manage data local to the application. The DataSet contains a collection of one or more DataTable objects consisting of rows and columns of data, and also primary key, foreign key, constraint, and relation information about the data in the DataTable objects. The following diagram illustrates the relationship between a .NET Framework data provider and a DataSet.
ADO.NET architecture
Choosing a DataReader or a DataSet
When you decide whether your application should use a DataReader (see Retrieving Data Using a DataReader (ADO.NET)) or a DataSet (see DataSets, DataTables, and DataViews (ADO.NET)), consider the type of functionality that your application requires. Use a DataSet to do the following:
- Cache data locally in your application so that you can manipulate it. If you only need to read the results of a query, the DataReader is the better choice.
- Remote data between tiers or from an XML Web service.
- Interact with data dynamically such as binding to a Windows Forms control or combining and relating data from multiple sources.
- Perform extensive processing on data without requiring an open connection to the data source, which frees the connection to be used by other clients.
If you do not require the functionality provided by the DataSet, you can improve the performance of your application by using the DataReader to return your data in a forward-only, read-only manner. Although the DataAdapter uses the DataReader to fill the contents of a DataSet (see Populating a DataSet from a DataAdapter (ADO.NET)), by using the DataReader, you can boost performance because you will save memory that would be consumed by the DataSet, and avoid the processing that is required to create and fill the contents of the DataSet.
LINQ to DataSet
LINQ to DataSet provides query capabilities and compile-time type checking over data cached in a DataSet object. It allows you to write queries in one of the .NET Framework development language, such as C# or Visual Basic.
LINQ to SQL
LINQ to SQL supports queries against an object model that is mapped to the data structures of a relational database without using an intermediate conceptual model. Each table is represented by a separate class, tightly coupling the object model to the relational database schema. LINQ to SQL translates language-integrated queries in the object model into Transact-SQL and sends them to the database for execution. When the database returns the results, LINQ to SQL translates the results back into objects.
ADO.NET Entity Framework
The ADO.NET Entity Framework is designed to enable developers to create data access applications by programming against a conceptual application model instead of programming directly against a relational storage schema. The goal is to decrease the amount of code and maintenance required for data-oriented applications.
ADO.NET Data Services
The ADO.NET Data Services framework is used to deploy data services on the Web or an intranet. The data is structured as entities and relationships according to the specifications of the Entity Data Model. Data deployed on this model is addressable by standard HTTP protocol.
XML and ADO.NET
ADO.NET leverages the power of XML to provide disconnected access to data. ADO.NET was designed hand-in-hand with the XML classes in the .NET Framework; both are components of a single architecture.
ADO.NET and the XML classes in the .NET Framework converge in the DataSet object. The DataSet can be populated with data from an XML source, whether it is a file or an XML stream. The DataSet can be written as World-Wide Web Consortium (W3C) compliant XML that includes its schema as XML schema definition language (XSD) schema, regardless of the source of the data in the DataSet. Because of the native serialization format of the DataSet is XML, it is an excellent medium for moving data between tiers, making the DataSet an optimal choice for remoting data and schema context to and from an XML Web service.
2、ADO.NET Data Platform
The ADO.NET Data Platform is a multi-release strategy to decrease the amount of coding and maintenance required for developers by enabling them to program against conceptual entity data models. This platform includes the ADO.NET Entity Framework and related technologies.
Entity Framework
The ADO.NET Entity Framework is designed to enable developers to create data access applications by programming against a conceptual application model instead of programming directly against a relational storage schema. The goal is to decrease the amount of code and maintenance required for data-oriented applications.
Entity Data Model (EDM)
An Entity Data Model (EDM) is a design specification that defines application data as sets of entities and relationships. Data in this model supports object-relational mapping and data programmability across application boundaries.
EDM data types and relationships are defined in an conceptual model. This is an XML schema written in conceptual schema definition language (CSDL). The conceptual model is used to build programmable classes that represent application data. Developers can extend these objects as required to support various application needs.
Object Services
Object Services allow programmers to interact with the conceptual model through a set of common language runtime (CLR) classes. These classes can be automatically generated from the conceptual model or can be developed independently to reflect the structure of the conceptual model. Object Services also provides infrastructure support for the Entity Framework, including services such as state management, change tracking, identity resolution, loading and navigating relationships, propagating object changes to database modifications, and query building support for Entity SQL.
LINQ to Entities
LINQ to Entities is a language-integrated query (LINQ) implementation that allows developers to create strongly-typed queries against the Entity Framework object context by using LINQ expressions and LINQ standard query operators. LINQ to Entities allows developers to work against a conceptual model with a very flexible object-relational mapping across Microsoft SQL Server and third-party databases.
Entity SQL
Entity SQL is a text-based query language designed to interact with an Entity Data Model. Entity SQL is an SQL dialect that contains constructs for querying in terms of higher-level modeling concepts, such as inheritance, complex types, and explicit relationships. Developers can also use Entity SQL directly with Object Services.
EntityClient
EntityClient is a new .NET Framework data provider used for interacting with an Entity Data Model. EntityClient follows the .NET Framework data provider pattern of exposing EntityConnection and EntityCommand objects that return an EntityDataReader. EntityClient works with the Entity SQL language, providing flexible mapping to storage-specific data providers. For more information, see EntityClient and Entity SQL.
ADO.NET Data Services
The ADO.NET Data Services framework is used to deploy data services on the Web or on an intranet. The data is structured as entities and relationships according to the specifications of the Entity Data Model. Data deployed on this model is addressable by standard HTTP protocol.
Entity Data Model Tools
The Entity Framework provides command-line tools, wizards, and designers to facilitate building EDM applications. The EntityDataSource control supports data binding scenarios based on the EDM. The programming surface of the EntityDataSource control is similar to other data source controls in Visual Studio.
3、LINQ and ADO.NET
Today, many business developers must use two (or more) programming languages: a high-level language for the business logic and presentation layers (such as Visual C# or Visual Basic), and a query language to interact with the database (such as Transact-SQL). This requires the developer to be proficient in several languages to be effective, and also causes language mismatches in the development environment. For example, an application that uses a data access API to execute a query against a database specifies the query as a string literal by using quotation marks. This query string is un-readable to the compiler and is not checked for errors, such as invalid syntax or whether the columns or rows it references actually exist. There is no type checking of the query parameters and no IntelliSense support, either.
Language-Integrated Query (LINQ) enables developers to form set-based queries in their application code, without having to use a separate query language. You can write LINQ queries against various enumerable data sources (that is, a data source that implements the IEnumerable interface), such as in-memory data structures, XML documents, SQL databases, and DataSet objects. Although these enumerable data sources are implemented in various ways, they all expose the same syntax and language constructs. Because queries can be formed in the programming language itself, you do not have to use another query language that is embedded as string literals that cannot be understood or verified by the compiler. Integrating queries into the programming language also enables Visual Studio programmers to be more productive by providing compile-time type and syntax checking, and IntelliSense. These features reduce the need for query debugging and error fixing.
For more information, see Introduction to LINQ. Also see the LINQ General Programming Guide, which contains detailed information about how to use LINQ technologies.
There are three separate ADO.NET Language-Integrated Query (LINQ) technologies: LINQ to DataSet ,LINQ to SQL, and LINQ to Entities. LINQ to DataSet provides richer, optimized querying over the DataSet and LINQ to SQL enables you to directly query SQL Server database schemas, and LINQ to Entities allows you to query an Entity Data Model.
Transferring data from SQL tables into objects in memory is often tedious and error-prone. The LINQ provider implemented by LINQ to DataSet and LINQ to SQL converts the source data into IEnumerable-based object collections. The programmer always views the data as an IEnumerable collection, both when you query and when you update. Full IntelliSense support is provided for writing queries against those collections.
The following diagram provides an overview of how the ADO.NET LINQ technologies relate to high-level programming languages and LINQ-enabled data sources.
The following sections provide more information about LINQ to DataSet, LINQ to SQL, and LINQ to Entities.
LINQ to DataSet
The DataSet is a key element of the disconnected programming model that ADO.NET is built on, and is widely used. LINQ to DataSet enables developers to build richer query capabilities into DataSet by using the same query formulation mechanism that is available for many other data sources. For more information, see LINQ to DataSet.
LINQ to SQL
LINQ to SQL is a useful tool for developers who do not require mapping to a conceptual model. By using LINQ to SQL, you can use the LINQ programming model directly over existing database schema. LINQ to SQL enables developers to generate .NET Framework classes that represent data. Rather than mapping to a conceptual data model, these generated classes map directly to database tables, views, stored procedures, and user-defined functions.
With LINQ to SQL, developers can write code directly against the storage schema using the same LINQ programming pattern as in-memory collections and the DataSet, in addition to other data sources such as XML. For more information, see LINQ to SQL.
LINQ to Entities
Most applications are currently written on top of relational databases. At some point, these applications will need to interact with the data represented in a relational form. Database schemas are not always ideal for building applications, and the conceptual models of application are not the same as the logical models of databases. The Entity Data Model is a conceptual data model that can be used to model the data of a particular domain so that applications can interact with data as objects.
Through the Entity Data Model, relational data is exposed as objects in the .NET environment. This makes the object layer an ideal target for LINQ support, allowing developers to formulate queries against the database from the language used to build the business logic. This capability is known as LINQ to Entities..
4、ADO.NET DataSets
The DataSet object is central to supporting disconnected, distributed data scenarios with ADO.NET. The DataSet is a memory-resident representation of data that provides a consistent relational programming model regardless of the data source. It can be used with multiple and differing data sources, with XML data, or to manage data local to the application. The DataSet represents a complete set of data, including related tables, constraints, and relationships among the tables. The following illustration shows the DataSet object model.
DataSet object model
The methods and objects in a DataSet are consistent with those in the relational database model.
The DataSet can also persist and reload its contents as XML, and its schema as XML schema definition language (XSD) schema.
The DataTableCollection
An ADO.NET DataSet contains a collection of zero or more tables represented by DataTable objects. The DataTableCollection contains all the DataTable objects in a DataSet.
A DataTable is defined in the System.Data namespace and represents a single table of memory-resident data. It contains a collection of columns represented by a DataColumnCollection, and constraints represented by a ConstraintCollection, which together define the schema of the table. A DataTable also contains a collection of rows represented by the DataRowCollection, which contains the data in the table. Along with its current state, a DataRow retains both its current and original versions to identify changes to the values stored in the row.
The DataView Class
A DataView enables you to create different views of the data stored in a DataTable, a capability that is often used in data-binding applications. Using a DataView, you can expose the data in a table with different sort orders, and you can filter the data by row state or based on a filter expression.
The DataRelationCollection
A DataSet contains relationships in its DataRelationCollection object. A relationship, represented by the DataRelation object, associates rows in one DataTable with rows in another DataTable. A relationship is analogous to a join path that might exist between primary and foreign key columns in a relational database. A DataRelation identifies matching columns in two tables of a DataSet.
Relationships enable navigation from one table to another in a DataSet. The essential elements of a DataRelation are the name of the relationship, the name of the tables being related, and the related columns in each table. Relationships can be built with more than one column per table by specifying an array of DataColumn objects as the key columns. When you add a relationship to the DataRelationCollection, you can optionally add a UniqueKeyConstraint and a ForeignKeyConstraint to enforce integrity constraints when changes are made to related column values.
XML
You can fill a DataSet from an XML stream or document. You can use the XML stream or document to supply to the DataSet either data, schema information, or both. The information supplied from the XML stream or document can be combined with existing data or schema information already present in the DataSet.
ExtendedProperties
The DataSet, DataTable, and DataColumn all have an ExtendedProperties property. ExtendedProperties is a PropertyCollection where you can place custom information, such as the SELECT statement that was used to generate the result set, or the time when the data was generated. The ExtendedProperties collection is persisted with the schema information for the DataSet.
LINQ to DataSet
LINQ to DataSet provides language-integrated querying capabilities for disconnected data stored in a DataSet. LINQ to DataSet uses standard LINQ syntax and provides compile-time syntax checking, static typing, and IntelliSense support when you are using the Visual Studio IDE.
5、Side-by-Side Execution in ADO.NET
Side-by-side execution in the .NET Framework is the ability to execute an application on a computer that has multiple versions of the .NET Framework installed, exclusively using the version for which the application was compiled.
An application compiled by using one version of the .NET Framework can run on a different version of the .NET Framework. However, we recommend that you compile a version of the application for each installed version of the .NET Framework, and run them separately. In either scenario, you should be aware of changes in ADO.NET between releases that can affect the forward compatibility or backward compatibility of your application.
Forward Compatibility and Backward Compatibility
Forward compatibility means that an application can be compiled with an earlier version of the .NET Framework, but will still run successfully on a later version of the .NET Framework. ADO.NET code written for the .NET Framework version 1.1 is forward compatible with later versions.
Backward compatibility means that an application is compiled for a newer version of the .NET Framework, but continues to run on earlier versions of the .NET Framework without any loss of functionality. Of course, this will not be the case for features introduced in a new version of the .NET Framework.
The .NET Framework Data Provider for ODBC
Starting with version 1.1, the .NET Framework Data Provider for ODBC (System.Data.Odbc) is included as a part of the .NET Framework. The ODBC data provider is available to .NET Framework version 1.0 developers as a Web download from the Data Access and
If you have an application developed for the .NET Framework version 1.0 that uses the ODBC data provider to connect to your data source, and you want to run that application on the .NET Framework version 1.1 or a later version, you must update the namespace for the ODBC data provider to System.Data.Odbc. You then must recompile it for the newer version of the .NET Framework.
If you have an application developed for the .NET Framework version 2.0 or later that uses the ODBC data provider to connect to your data source, and you want to run that application on the .NET Framework version 1.0, you must download the ODBC data provider and install it on the .NET Framework version 1.0 system. You then must change the namespace for the ODBC data provider to Microsoft.Data.Odbc, and recompile the application for the .NET Framework version 1.0.
The .NET Framework Data Provider for Oracle
Starting with version 1.1, the .NET Framework Data Provider for Oracle (System.Data.OracleClient) is included as a part of the .NET Framework. The data provider is available to .NET Framework version 1.0 developers as a Web download from the Data Access and
If you have an application developed for the .NET Framework version 2.0 or later that uses the data provider to connect to your data source, and you want to run that application on the .NET Framework version 1.0, you must download the data provider and install it on the .NET Framework version 1.0 system.
Code Access Security
The .NET Framework data providers in the .NET Framework version 1.0 (System.Data.SqlClient, System.Data.OleDb) are required to run with FullTrust permission. Any attempt to use the .NET Framework k data providers from the .NET Framework version 1.0 in a zone with less than FullTrust permission causes a SecurityException.
However, starting with the .NET Framework version 2.0, all of the .NET Framework data providers can be used in partially trusted zones. In addition, a new security feature was added to the .NET Framework data providers in the .NET Framework version 1.1. This feature enables you to restrict what connection strings can be used in a particular security zone. You can also disable the use of blank passwords for a particular security zone. For more information
Because each installation of the .NET Framework has a separate Security.config file, there are no compatibility issues with security settings. However, if your application depends on the additional security capabilities of ADO.NET included in the .NET Framework version 1.1 and later, you will not be able to distribute it to a version 1.0 system.
SqlCommand Execution
Starting with the .NET Framework version 1.1, the way that ExecuteReader executes commands at the data source was changed.
In the .NET Framework version 1.0, ExecuteReader executed all commands in the context of the sp_executesql stored procedure. As a result, commands that affect the state of the connection (for example, SET NOCOUNT ON), only apply to the execution of the current command. The state of the connection is not modified for any subsequent commands executed while the connection is open.
In the .NET Framework version 1.1 and later, ExecuteReader only executes a command in the context of the sp_executesql stored procedure if the command contains parameters, which provides a performance benefit. As a result, if a command affecting the state of the connection is included in a non-parameterized command, it modifies the state of the connection for all subsequent commands executed while the connection is open.
Consider the following batch of commands executed in a call to ExecuteReader.
In the .NET Framework version 1.1 and later, NOCOUNT will remain ON for any subsequent commands executed while the connection is open. In the .NET Framework version 1.0, NOCOUNT is only ON for the current command execution.
This change can affect both the forward and backward compatibility of your application if you depend on the behavior of ExecuteReader for either version of the .NET Framework.
For applications that run on both earlier and later versions of the .NET Framework, you can write your code to make sure that the behavior is the same regardless of the version you are running on. If you want to make sure that a command modifies the state of the connection for all subsequent commands, we recommend that you execute your command using ExecuteNonQuery. If you want to make sure that a command does not modify the connection for all subsequent commands, we recommend that you include the commands to reset the state of the connection in your command. For example:
Microsoft SQL Server Native Client
Microsoft SQL Server Native Client contains the SQL OLE DB provider and SQL ODBC driver in one native dynamic link library (DLL) supporting applications using native-code APIs (ODBC, OLE DB and
Microsoft Data Access Components (MDAC)
The .NET Framework data providers for OLE DB and ODBC require MDAC 2.6 or a later version in all versions of the .NET Framework, and MDAC 2.8 SP1 is recommended. Although this requirement introduces no side-by-side execution issues, notice that MDAC does not currently support side-by-side execution. Therefore, it is important to verify that your application will continue to function correctly with the new version before upgrading the MDAC components for your installation.
For more information about MDAC, see the Data Access and
Windows Data Access Components (Windows DAC)
Windows Data Access Components (Windows DAC) 6.0 is a set of technologies included in Windows Vista to provide access to information across the enterprise. These technologies include the latest versions of the data access technologies included in MDAC: Microsoft ActiveX Data Objects (ADO), OLE DB, and Microsoft Open Database Connectivity (ODBC).
