. 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 the connection pool provided by the provider. This is a mechanism that is implemented internally by the provider to support transaction processing and is very efficient. It exists in the managed code. The pool is created based on the domain of each application and will not be destroyed before the application domain is detached.

You can use this form of connection pool transparently, but you should know the management method of the pool and the various configuration options that can be used to fine-tune the connection pool.

In many cases, the default connection pool settings of the SQL server. NET data provider may be sufficient for your applications. During the development and testing of. Net-based applications, we recommend that you simulate the planned communication mode to determine whether to modify the connection pool size.

Developers who need to generate Scalable high-performance applications should minimize the connection time and keep the connection open only when data is retrieved or updated. When the connection is closed, it is returned to the connection pool for reuse. In this case, the actual connection to the database will not be disconnected; however, if the connection pool is disabled, the actual connection to the database will be closed.

Developers should be very careful not to rely on the garbage collector to release the connection, because the connection may not be closed when the reference leaves the scope. This is a common cause of connection leakage. When a new connection is requested, this will cause a connection exception.

Configure the SQL server. NET data provider connection pool

You can use a group of name-value pairs (provided by the connection string) to configure the connection pool. For example, you can configure whether to enable the connection pool (enabled by default), the maximum and minimum capacity of the pool, and the length of time that the queuing requests to open the connection can be blocked. The following is an example connection string used to configure the maximum and minimum capacities of the pool.

"Server=(local); Integrated Security=SSPI; Database=Northwind; Max Pool Size=75; Min Pool Size=5"

After the connection is opened and the pool is created, multiple connections are added to the pool to increase the number of connections to the minimum number configured. Then, you can continue to add connections to the pool until the maximum number of pools configured is reached. When the maximum number of connections is reached, new requests to be connected will be queued for a configurable period of time.

More information

When using the SQL server. NET data provider connection pool, pay attention to the following aspects:

•

The connection is pooled by the exact match algorithm on the connection string. The pool mechanism is even sensitive to spaces between name-value pairs. For example, the following two connection strings will lead to two independent pools, because the second connection string contains additional space characters. SqlConnection conn = new SqlConnection( "Integrated Security=SSPI;Database=Northwind");conn.Open(); // Pool A is createdSqlConmection conn = new SqlConnection( "Integrated Security=SSPI ; Database=Northwind");conn.Open(); // Pool B is created (extra spaces in string)

•

The connection pool is divided into multiple transaction proprietary pools and a pool corresponding to the connections that are not currently registered in the transaction. For threads associated with a specific transaction context, the corresponding pool (the pool contains the connections registered in the transaction) is returned. This makes the use of registered connections a transparent process.

 

Ole db. NET data provider pool mechanism

The ole db. NET data provider pooled the connection by using the basic ole db resource pool. There are multiple options for configuring the resource pool:

•	You can use connection strings to configure, enable, or disable resource pools.
•	You can use the registry.
•	You can configure the resource pool programmatically.

To avoid deployment problems related to the Registry, do not use the registry to configure the ole db resource pool.

Monitoring connection pool

To monitor the application's use of the connection pool, you can use the event probe tool that comes with SQL Server, or use Microsoft Windows? 2000 Performance Monitor Tool attached to the operating system.

Use the SQL Server event probe to monitor the connection pool

1.	ClickStart, PointingPrograms, PointingMicrosoftSQLServerAnd then clickProfilerTo start the event probe.
2.	InFileMenu, pointingNewAnd then clickTrace.
3.	Provide connection details, and then clickOK.
4.	InTrace PropertiesIn the dialog box, clickEventsTab.
5.	InSelected event classesList, make sure thatAudit LoginAndAudit logoutThe event is displayed inSecurity AuditBelow. To make the trail clearer, delete all other events from the list.
6.	ClickRunTo start the trail. When the connection is established, you will seeAudit LoginEvent. When the connection is closed, you will seeAudit logoutEvent.

Use Performance Monitor to monitor connection pools

1.	ClickStart, PointingPrograms, PointingAdministrative ToolsAnd then clickPerformanceTo start the Performance Monitor.
2.	Right-click the image background, and then clickAddCounters.
3.	InPerformance objectIn the drop-down list, clickSQL Server: General Statistics.
4.	In the displayed list, clickUser connections.
5.	ClickAddAnd then clickClose.

 

Management Security

Although the database connection pool improves the overall scalability of applications, it means that you no longer can manage security at the database level. This is because to support the connection pool, the connection strings must be identical. If you need to track the database operations of each user, consider adding a parameter to pass the user identity and manually record user operations in the database. You need to add this parameter to each operation.

Use Windows Authentication

When connecting to SQL Server, you should use Windows Authentication because it provides many advantages:

1.	Security is easier to manage because you use a single (Windows) security model instead of an independent SQL server security model.
2.	Avoid embedding usernames and passwords in connection strings.
3.	The user name and password are not transmitted over the network in plaintext mode.
4.	By using the password expiration time, minimum length, and locking the account after multiple invalid login requests, the login security is improved.

 

Store connection strings

To store database connection strings, you can choose from multiple options, which have different levels of flexibility and security. Although hard encoding of the connection string in the source code provides optimal performance, the file system cache ensures that the performance reduction caused by storing the string in the file system is negligible. In almost all cases, the extra flexibility provided by external connection strings is preferred (it supports administrator configuration ).

When you choose to connect to the string storage method, the two most important things to note are security and configuration simplicity, followed by performance.

You can choose the following locations to store database connection strings:

•	In the application configuration file. For example, the web. config of the ASP. NET web application
•	In a Universal Data Link (udl) file (only supported by the ole db. NET data provider)
•	In the Windows Registry
•	In a custom file
•	In the COM + directory, the method is to use the build string (applicable only to service components)

By using Windows authentication to access SQL Server, you can avoid storing the user name and password in the connection string. If you require stricter security measures, consider storing the connection string in encrypted format.

For ASP. NET web applications, storing connection strings in the web. config file in encrypted format represents a secure and configurable solution.

NoteYou can setPersist Security infoSet the nameFalseTo prohibitSqlconnectionOrOledbconnectionObjectConnectionstringAttribute returns security-sensitive details (such as passwords ).

The following sections describe how to use various options to store the connection string, and introduce the relative advantages and disadvantages of various methods. This helps you make informed choices based on your specific application solution.

Use the XML application configuration file

Available <Appsettings> The element stores the database connection string in the custom settings section of the application configuration file. This element supports any key-value pair, as shown in the following code snippet:

<configuration> <appSettings>  <add key="DBConnStr"     value="server=(local);Integrated Security=SSPI;database=northwind"/> </appSettings></configuration>

Note<Appsettings> The element appears in <Configuration> Element, not followed by <System. Web>.

Advantages

•	Easy to deploy. The connection string is through regular. netXcopyDeployed together with the configuration file.
•	Easy to access by programming. PassConfigurationsettingsClassAppsettingsProperties, you can easily read the configured database connection string at runtime.
•	Supports dynamic updates (limitedASP. NET). If the Administrator. update the connection string in the config file. When you access this string (for stateless components, this may be the next time the client uses this component for data access requests), the changes will take effect.

Disadvantages

•

Security. Although ASP. net Internet Server Application Programming Interface (ISAPI) dynamic link library (DLL) prohibit the client from directly accessing. you may still want to avoid storing the detailed information on the front-end web server in plaintext. For extra security, store the connection string in the configuration file in encrypted format.

 

AvailableSystem. configuration. configurationsettingsStatic classAppsettingsProperties to retrieve custom application settings. The following code snippet describes this. The code snippet uses the nameDbconnstrCustom key:

using System.Configuration;private string GetDBaseConnectionString(){  return ConfigurationSettings.AppSettings["DBConnStr"];}

 

Author's blog:Http://blog.csdn.net/yangyifan0/