. NET data access architecture guide 1

One of the major changes introduced by ADO. NET is the useDataTable,DataSet,DataAdapterAndDataReaderThe combination of objects replaces ADORecordsetObject.DataTableRepresents a set of rows in a single table, which is similarRecordset.DataSetIndicatesDataTableObject set, including the relationship and constraints that bind various tables together. Actually,DataSetIs a relational structure with built-in XML support in memory.

DataSetOne of its main features is that it does not understand the basic data sources that may be used to fill it. It is a discontinuous and independent entity used to represent a data set and can be passed between components through different layers of multi-tier applications. It can also be serialized as an XML data stream, which makes it very suitable for data transmission between different types of platforms. Use ADO. NETDataAdapterObject transfers dataDataSetAnd the basic data source, or from the data source.DataAdapterThe object also providesRecordsetThe associated enhanced batch update function.

ADO. NET depends on the services of. NET data providers. These providers provide access to the basic data source and include four main objects (Connection,Command,DataReaderAndDataAdapter).

Currently, ADO. NET comes with two types of providers: The Bridge provider and the Native provider. Through the Bridge provider (such as those provided for ole db and ODBC), you can use databases designed for previous data access technologies. Native providers (such as SQL Server and Oracle providers) are generally able to improve performance, partly because an abstraction layer is missing.

Namespace Structure

The types (classes, structures, enumerations, etc.) associated with various. NET data providers are located in their namespaces:

•	System. Data. SqlClient. Contains the SQL Server. NET data provider type.
•	System. Data. OracleClient. Contains Oracle. NET data providers.
•	System. Data. OleDb. Contains the ole db. NET data provider type.
•	System. Data. Odbc. Contains the ODBC. NET data provider type.
•	System. Data. Contains a type independent of the provider, suchDataSetAndDataTable.

Each provider provides a pairConnection,Command,DataReaderAndDataAdapterObject implementation.SqlClientThe implemented prefix is "SQL", whileOleDbThe implementation prefix is "OleDb ". For example,ConnectionObjectSqlClientImplementation isSqlConnection, AndOleDbImplementation isOleDbConnection. Similarly,DataAdapterThe two implementations of the object areSqlDataAdapterAndOleDbDataAdapter.

Stored Procedure and direct SQL

Most of the code snippets shown in this document useSqlCommandObject To Call The stored procedure to perform database operations. In some cases, you will not seeSqlCommandBecause the stored procedure name is directly passedSqlDataAdapterObject. Internally, this still causes the creationSqlCommandObject.

You should use stored procedures instead of Embedded SQL statements for the following reasons:

•	Stored procedures generally improve performance because the database can optimize the data access plan used by stored procedures and cache the plan for future reuse.
•	You can set security protection for each stored procedure in the database. The client does not have to have access to the basic table to obtain the permission to execute the stored procedure.
•	Stored Procedures simplify maintenance because modifying stored procedures is usually easier than modifying hard-coded SQL statements in deployed components.
•	Stored Procedures provide an additional abstraction level for the basic database architecture. The Implementation Details of the client and stored procedure of the stored procedure are isolated from each other, and are also isolated from the infrastructure.
•	Stored Procedures can reduce network traffic because SQL statements can be executed in batches instead of sending multiple requests from the client.

SQL Server online documentation strongly recommends that you do not use "sp _" as the name prefix to create any stored procedures, because such names have been specified to the system stored procedures. SQL Server Always searches for stored procedures starting with sp _ in the following order:

1.	Search for stored procedures in the primary database.
2.	Search for stored procedures based on any provided qualifier (Database Name or owner.
3.	UseDboAs the owner to find the Stored Procedure (if no owner is specified ).

 

Attribute and constructor Parameters

You can use the constructor parameters to set the specific property values of the ADO. NET object, or directly set the property values. For example, the following code snippets are functionally equivalent.

// Use constructor arguments to configure command objectSqlCommand cmd = new SqlCommand( "SELECT * FROM PRODUCTS", conn );// The above line is functionally equivalent to the following// three lines which set properties explicitlysqlCommand cmd = new SqlCommand();cmd.Connection = conn;cmd.CommandText = "SELECT * FROM PRODUCTS";

From a performance perspective, the differences between the two methods are insignificant, because setting and retrieving properties for. NET objects is more efficient than performing similar operations on COM objects.

The method you choose depends on your personal preferences and encoding style. However, setting attributes clearly makes the code easier to understand (especially when you are not familiar with the ADO. NET object model) and debugging.

Manage database connections

Database connections represent a key, expensive, and limited resource, especially in multi-layer Web applications. Correct Connection Management is necessary because the method you take may significantly affect the overall scalability of your application. At the same time, you must carefully consider where to store the connection string. You must use configurable and secure locations.

When managing database connections and connection strings, efforts should be made:

•	By reusing the database connection pool among multiple clients, you can achieve application scalability.
•	Use configurable and high-performance connection pool policies.
•	When accessing SQL? Use Windows authentication.
•	Avoid simulation on the middle layer.
•	Securely store connection strings.
•	Open the database connection as late as possible and close it as early as possible.

This section describes the connection pool and helps you select an appropriate connection pool policy. This section also considers how to manage, store, and manipulate database connection strings. Finally, this section provides two encoding modes to help ensure that the connection is reliably closed and returned to the connection pool.

Pool mechanism of SQL Server. NET data provider

If you are using the SQL Server. NET data provider, use this provider to provide