. NET Data Access Architecture Guide One

Access | data | system

One of the major changes introduced by Ado.net is the substitution of the combination of DataTable,DataSet,DataAdapter and DataReader objects for ADO The Recordset object. A DataTable represents a collection of rows in a single table, similar to a Recordsetin this respect. A DataSet represents a collection of DataTable objects, including relationships and constraints that bind various tables together. In fact, theDataSet is an in-memory relational structure with built-in XML support.

One of the main features of the DataSet is that it does not understand the underlying data source that might be used to populate it. It is a discrete, stand-alone entity that represents a collection of data and can be passed between components through different tiers of multi-tier applications. It can also be serialized as an XML data stream, which makes it ideal for data transfer between different kinds of platforms. Ado.net uses DataAdapter objects to transfer data to and from the underlying data source, or from a data source. The DataAdapter object also provides enhanced bulk update functionality that was previously associated with the Recordset .

Ado.net relies on the services of the. NET data provider. These providers provide access to the underlying data source and include four main objects (Connection,Command,DataReader , and DataAdapter ).

Currently, Ado.net comes with two types of providers: Bridge provider and Native provider. Bridge providers, such as those provided for OLE DB and ODBC, can use databases designed for previous data access technologies. Native providers, such as SQL Server and Oracle providers, often provide performance improvements, in part because of the lack of an abstraction layer.

namespace Organizational Structure

The types (classes, structs, enumerations, and so on) associated with each. NET data provider are located in their respective namespaces:

•	System.Data.SqlClient. Contains SQL Server. NET data Provider types.
•	System.Data.OracleClient. Contains an Oracle. NET data Provider.
•	System.Data.OleDb. Contains an OLE DB. NET data provider type.
•	System.Data.Odbc. Contains an ODBC. NET data provider type.
•	System.Data. Contains types that are independent of the provider, such as datasets and DataTable.

Within their associated namespaces, each provider provides an implementation of the Connection,Command,DataReader , and DataAdapter objects. The SqlClient implementation prefix is "SQL" and the OLE DB implementation prefix is "OLE DB". For example, the SqlClient implementation of theConnection object is SqlConnection, and the OLE DB implementation is OleDbConnection . Similarly, the two implementations of theDataAdapter object are SqlDataAdapter and OleDbDataAdapterrespectively.

Stored procedures vs. Direct SQL

Most of the code fragments shown in this document use the SqlCommand object to invoke the stored procedure to perform database operations. In some cases, you will not see the SqlCommand object because the stored procedure name is passed directly to the SqlDataAdapter object. Internally, this will still result in the creation of the SqlCommand object.

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

•	Stored procedures can generally improve performance because the database optimizes the data access plan used by the stored procedure and can cache the plan for future reuse.
•	You can set the security protection for individual stored procedures in the database separately. Clients do not have access to the underlying table, and they gain permission to execute stored procedures.
•	Stored procedures can simplify maintenance because it is often easier to modify stored procedures than to change hard-coded SQL statements in deployed components.
•	Stored procedures add an extra level of abstraction to the underlying database schema. The client of the stored procedure and the implementation details of the stored procedure are isolated from each other, and the infrastructure is isolated from each other.
•	Stored procedures can reduce network traffic because you can execute SQL statements in bulk, rather than sending multiple requests from the client.

SQL Server online documentation It is strongly recommended that you do not use "sp_" as the name prefix to create any stored procedures, because such names have been assigned to system stored procedures. SQL Server always finds stored procedures that begin with sp_ in the following order:

1.	Locate the stored procedure in the primary database.
2.	Locate a stored procedure based on any qualifier (database name or owner) provided.
3.	Use dbo as owner to find stored procedures (if no owner is specified).

Properties and constructor parameters

You can set a specific property value for a Ado.net object by using the constructor parameter, or you can set the property value directly. For example, the following code fragment is functionally equivalent.

Use constructor arguments to configure command Objectsqlcommand cmd = new SqlCommand ("SELECT * FROM Products", conn); The above line are 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 approaches are trivial because setting and getting properties for. NET objects is more efficient than performing similar operations on COM objects.

Which method you choose depends on your personal preferences and coding style. However, explicitly setting properties can make your code easier to understand (especially when you are unfamiliar with the Ado.net object model) and debugging.

Managing Database connections

A database connection represents a critical, expensive, and limited resource, especially in a multi-tier Web application. It is necessary to properly manage connections because the approach you take may significantly affect the overall scalability of your application. Also, consider carefully where to store the connection string. You need to use a configurable and secure location.

When managing database connections and connection strings, you should strive to:

•	Help implement the scalability of your application by Multiplexing database connection pools across multiple clients.
•	Adopt a configurable, high-performance connection pooling strategy.
•	In accessing SQL? Server, use Windows authentication.
•	Avoid impersonation in the middle tier.
•	Securely stores connection strings.
•	Open the database connection as late as possible and close it as early as possible.

This section discusses connection pooling and helps you select the appropriate connection pool policy. This section also considers how database connection strings should be managed, stored, and manipulated. Finally, this section presents two coding patterns that can be used to help ensure that the connection is reliably shut down and returned to the connection pool.

Pool mechanism for SQL Server. NET data Providers

If you are using a SQL Server. NET data Provider, use the connection pooling support provided by this provider. This is a mechanism that is implemented internally by the provider and is very efficient, and exists in managed code. Pools are created based on the domain of each application and are not destroyed until the application domain is uninstalled.

This form of connection pooling can be used transparently, but you should know how the pool is managed and the various configuration options that you can use to fine-tune the connection pool.

In many cases, the default connection pool settings for your SQL Server. NET data provider may be sufficient for your application. During development and testing of. NET-based applications, it is recommended that you simulate the planning communication pattern to determine whether you need to modify the connection pool size.

Developers who need to build scalable high-performance applications should minimize the time they use the connection and keep the connection open only when retrieving or updating data. When the connection is closed, it is returned to the connection pool and is available for reuse. In this case, the actual connection to the database will not be cut off, but 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 able to close when the reference leaves the scope. This is a common source of connection leaks, which causes a connection exception when a new connection is requested.

Configure the SQL Server. NET Data Provider Connection pool

You can configure a connection pool by using a set of name-value pairs (provided by a connection string). For example, you can configure whether connection pooling is enabled (enabled by default), the maximum capacity and minimum capacity of the pool, and the length of time that queued requests to open a connection can block. The following is a sample connection string that configures the maximum capacity and minimum capacity of the pool.

"Server= (local); Integrated SECURITY=SSPI; Database=northwind; Max Pool size=75; Min Pool size=5 "

When you open a connection and create a pool, multiple connections are added to the pool to increase the number of connections to the minimum number of configurations. You can then continue to add connections to the pool until the maximum number of pools configured is reached. When the maximum number is reached, a new request to open the connection is queued for a configurable amount of time.

More information

When using a SQL Server. NET data provider to connect to a pool, note the following:

•

The connection is pooled by a full matching 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 result in two separate pools, because the second connection string contains extra 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 that corresponds to a connection that is not currently enlisted in a transaction. For a thread that is associated with a particular transaction context, a connection is returned for the pool that contains the connections that are enlisted in the transaction. This makes it a transparent process to use a registered connection.

Pool mechanism for OLE DB. NET data Providers

OLE DB. NET data providers pool connections by using the underlying OLE DB resource pool. There are several choices for configuring a resource pool:

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

To avoid registry-related deployment issues, do not use the registry to configure the OLE DB resource pool.

Monitor Connection Pool

To monitor the application's use of connection pooling, you can use the profiler tools that are included with SQL Server, or use Microsoft Windows? 2000 Performance Monitor tools included with the operating system.

Monitoring connection pooling with SQL Server Profiler

1.	Click Start, point to Programs, point to MicrosoftsqlServer, and then click Profiler To start profiler.
2.	On the File menu, point to New, and then click Trace.
3.	Provide connection details, and then click OK.
4.	In the Trace Properties dialog box, click the Events tab.
5.	In the Selected event classes list, make sure that Audit Login and Audit Logout events appear under the security Audit . To make your trace clearer, remove all other events from the list.
6.	Click Run to start the trace. When the connection is established, you will see the Audit Login event, and when the connection closes, you will see the Audit Logout event.

Monitoring connection pooling with Performance Monitor

1.	Click Start, point to Programs, point to administrative Tools, and then click Performance to start Performance Monitor.
2.	Right-click the graphics background, and then click Addcounters.
3.	In the performance object drop-down list, click SQL server:general Statistics.
4.	In the list that appears, click User Connections.
5.	Click Add, and then click Close.

Manage security

Although database connection pooling increases the overall scalability of your application, this means that you no longer have the ability to manage security at the database level. This is because, to support connection pooling, the connection string must be exactly the same. If you need to track database operations for each user, consider adding a parameter so that you can pass the user identity and manually record user actions in the database. You need to add this parameter to each operation.

Using Windows Authentication

When you connect to SQL Server, you should use Windows authentication because it provides a number of benefits:

1.	Security is easier to manage because you use a single (Windows) security model rather than a stand-alone SQL Server security model.
2.	You can avoid embedding user names and passwords in the connection string.
3.	User names and passwords are not passed through the network in clear text.
4.	Logon security is improved by adopting a password expiration date, a minimum length, and locking an account after multiple invalid logon requests.

Storing connection strings

To store database connection strings, there are several choices that have different levels of flexibility and security. Although the connection string is hard-coded in the source code to provide optimal performance, the file system cache ensures that the performance degradation caused by storing the string externally to the file system is negligible. In almost all cases, people prefer the extra flexibility provided by the external connection string (which supports administrator configuration).

When you choose a connection string storage method, the two most important things to note are security and ease of configuration, followed by performance.

You can select the following location to store the database connection string:

•	In the application configuration file; For example, the web.config of the ASP.net Web application
•	In a universal data Link (UDL) file (supported only by OLE DB. NET data providers)
•	In the Windows registry
•	In a custom file
•	In the COM + catalog, the method is to use a build string (for service components only)

By using Windows authentication to access SQL Server, you can avoid storing user names and passwords in the connection string. If your security requirements require more stringent action, consider storing the connection string in an encrypted format.

For asp.net Web applications, storing connection strings in encrypted format within a Web.config file represents a secure, configurable solution.

Note You can set the Persist security Info named value to falsein the connection string to prevent passing SqlConnection or the ConnectionString property of the OleDbConnection object returns security-sensitive details, such as a password.

The following sections discuss how to use various selections to store connection strings, and describe the relative advantages and disadvantages of each method. This content helps you make informed choices based on your specific application scenarios.

Using an XML application configuration file

You can use the <appSettings> element to store the database connection string in the custom settings section of the application configuration file. This element supports any key-value pairs, as shown in the following code fragment:

<configuration> <appSettings>  <add key= "dbconnstr"     value= server= (local) integrated Security=sspi;database=northwind "/> </appSettings></configuration>

Note <appSettings> elements appear under <configuration> elements and are not immediately following the <system.web behind the >.

Advantages

•	easy to deploy . The connection string is deployed with the configuration file through a regular. NET xcopy deployment.
•	easy to access programmatically . The AppSettings property of the ConfigurationSettings class enables you to easily read a configured database connection string at run time.
•	Support for dynamic updates (limited to ASP. NET). If the administrator updates the connection string in the Web.config file, the next time that the string is accessed (this may be the client using the component to make a data access request for a stateless component), the changes will take effect.

Disadvantages

•

security . Although the ASP.net Internet Server Application Programming Interface (ISAPI) dynamic-link library (DLL) prevents clients from directly accessing files with the. config file name extension, and you can further restrict access with NTFS permissions, you may still want to avoid using plaintext in the front-end W The EB server stores these details. For additional security, store the connection string in the configuration file in encrypted format.

You can use the static AppSettings property of the System.Configuration.ConfigurationSettings class to retrieve custom application settings. This is described in the following code fragment, which takes the custom key named Dbconnstr , which was previously cited:

Using System.configuration;private string getdbaseconnectionstring () {return  configurationsettings.appsettings ["Dbconnstr"];