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.
十年的宿豫网站建设经验，针对设计、前端、开发、售后、文案、推广等六对一服务，响应快，48小时及时工作处理。全网整合营销推广的优势是能够根据用户设备显示端的尺寸不同，自动调整宿豫建站的显示方式，使网站能够适用不同显示终端，在浏览器中调整网站的宽度，无论在任何一种浏览器上浏览网站，都能展现优雅布局与设计，从而大程度地提升浏览体验。创新互联从事“宿豫网站设计”,“宿豫网站推广”以来，每个客户项目都认真落实执行。
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.NETDataSet object in order to be exposed to the user in an ad hoc manner, combined with data from multiple sources, or passed between tiers. TheDataSet 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 theDataSet.
.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. TheConnection object provides connectivity to a data source. TheCommand object enables access to database commands to return data, modify data, run stored procedures, and send or retrieve parameter information. TheDataReader provides a high-performance stream of data from the data source. Finally, theDataAdapter provides the bridge between theDataSet object and the data source. TheDataAdapter usesCommand objects to execute SQL commands at the data source to both load theDataSet with data and reconcile changes that were made to the data in theDataSet back to the data source.
The DataSet
The ADO.NETDataSet 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. TheDataSet contains a collection of one or moreDataTable objects consisting of rows and columns of data, and also primary key, foreign key, constraint, and relation information about the data in theDataTable objects. The following diagram illustrates the relationship between a .NET Framework data provider and aDataSet.
ADO.NET architecture
Choosing a DataReader or a DataSet
When you decide whether your application should use aDataReader (seeRetrieving Data Using a DataReader (ADO.NET)) or aDataSet (seeDataSets, DataTables, and DataViews (ADO.NET)), consider the type of functionality that your application requires. Use aDataSet to do the following:
If you do not require the functionality provided by theDataSet, you can improve the performance of your application by using theDataReader to return your data in a forward-only, read-only manner. Although theDataAdapter uses theDataReader to fill the contents of aDataSet (seePopulating a DataSet from a DataAdapter (ADO.NET)), by using theDataReader, you can boost performance because you will save memory that would be consumed by theDataSet, and avoid the processing that is required to create and fill the contents of theDataSet.
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 theDataSet object. TheDataSet can be populated with data from an XML source, whether it is a file or an XML stream. TheDataSet 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 theDataSet. Because of the native serialization format of theDataSet is XML, it is an excellent medium for moving data between tiers, making theDataSet 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 anconceptual 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 exposingEntityConnection andEntityCommand objects that return anEntityDataReader. EntityClient works with the Entity SQL language, providing flexible mapping to storage-specific data providers. For more information, seeEntityClient 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 noIntelliSense 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 theIEnumerable interface), such as in-memory data structures, XML documents, SQL databases, andDataSet 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, andIntelliSense. These features reduce the need for query debugging and error fixing.
For more information, seeIntroduction to LINQ. Also see theLINQ 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 theDataSet 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 intoIEnumerable-based object collections. The programmer always views the data as anIEnumerable collection, both when you query and when you update. FullIntelliSense 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
TheDataSet 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 intoDataSet by using the same query formulation mechanism that is available for many other data sources. For more information, seeLINQ 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 theDataSet, in addition to other data sources such as XML. For more information, seeLINQ 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
TheDataSet object is central to supporting disconnected, distributed data scenarios with ADO.NET. TheDataSet 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. TheDataSet represents a complete set of data, including related tables, constraints, and relationships among the tables. The following illustration shows theDataSet object model.
DataSet object model
The methods and objects in aDataSet are consistent with those in the relational database model.
TheDataSet 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 byDataTable objects. TheDataTableCollection contains all theDataTable objects in aDataSet.
ADataTable is defined in theSystem.Data namespace and represents a single table of memory-resident data. It contains a collection of columns represented by aDataColumnCollection, and constraints represented by aConstraintCollection, which together define the schema of the table. ADataTable also contains a collection of rows represented by theDataRowCollection, which contains the data in the table. Along with its current state, aDataRow retains both its current and original versions to identify changes to the values stored in the row.
The DataView Class
ADataView enables you to create different views of the data stored in aDataTable, a capability that is often used in data-binding applications. Using aDataView, 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
ADataSet contains relationships in itsDataRelationCollection object. A relationship, represented by the DataRelation object, associates rows in oneDataTable with rows in anotherDataTable. A relationship is analogous to a join path that might exist between primary and foreign key columns in a relational database. ADataRelation identifies matching columns in two tables of aDataSet.
Relationships enable navigation from one table to another in aDataSet. The essential elements of aDataRelation 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 ofDataColumn objects as the key columns. When you add a relationship to theDataRelationCollection, you can optionally add aUniqueKeyConstraint and aForeignKeyConstraint to enforce integrity constraints when changes are made to related column values.
XML
You can fill aDataSetfrom an XML stream or document. You can use the XML stream or document to supply to theDataSet 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 theDataSet.
ExtendedProperties
TheDataSet,DataTable, andDataColumn all have anExtendedProperties property.ExtendedProperties is aPropertyCollection 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. TheExtendedProperties collection is persisted with the schema information for theDataSet.
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 theData 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 toSystem.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 toMicrosoft.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 theData 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 aSecurityException.
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 thatExecuteReader executes commands at the data source was changed.
In the .NET Framework version 1.0,ExecuteReader executed all commands in the context of thesp_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 thesp_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 toExecuteReader.
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 ofExecuteReader 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 usingExecuteNonQuery. 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 theData 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).
