Prizes & Awards
My Profile
Active Members
TodayLast 7 Days
more...
|
 New Feature: Community Sites:
Create your own .NET community website and start earning from Google AdSense !
It's Free !
|
Managing Database Connections using ADO.NET
|
MANAGING DATABASE CONNECTIONS USING ADO.NET
Database connections represent a critical, expensive, and limited resource, particularly in a multitier Web application. Managing database connections significantly affect the overall scalability of application.
When managing database connections and connection strings, you should strive to:
• Help realize application scalability by multiplexing a pool of database connections
across multiple clients.
• Adopt a configurable and high performance connection pooling strategy.
• Use Windows authentication when accessing SQL Server.
• Avoid impersonation in the middle tier.
• Store connection strings securely.
• Open database connections late and close them early.
Using Connection Pooling:
Database connection pooling allows an application to reuse an existing connection from a pool instead of repeatedly establishing a new connection with the database.This technique can significantly increase the scalability of an application and also improves performance, because the significant time required to establish a new connection can be avoided.
Data access technologies such as ODBC and OLE DB provide forms of connection
Pooling which are largely transparent to the database client application.
OLE DB connection pooling is often referred to as session or resource pooling.
If you are using the SQL Server .NET Data Provider, use the connection pooling
support offered by the provider. It is a transaction-aware and efficient mechanism implemented internally by the provider, within managed code. Pools are created on a per application domain basis, and pools are not destroyed until the application domain is unloaded.
In many cases, the default connection pooling settings for the SQL Server .NET data provider may be sufficient for your application.
Developers building scalable, high performance applications should minimize the amount of time a connection is used. When a connection is closed, it is returned to the connection pool and made available for reuse. In this case, the actual connection to the database is not severed; however, if connection pooling is disabled, the actual connection to the database will be closed.
Choosing Pool Sizes:
Choosing pool sizes is very important for large-scale systems that manage the concurrent requests of many thousands of clients. We need to monitor connection pooling and the performance of application to determine the optimum pool sizes for your system. The optimum size also depends on the hardware on which you are running SQL Server.
During development, we might want to reduce the default maximum pool size (currently 100) to help find connection leaks. If we establish a minimum pool size, we will incur a small performance overhead when the pool is initially populated to bring it to that level, although the first few clients that connect will benefit.
Storing Connection Strings:
To store database connection strings, we have a variety of options with different
degrees of flexibility and security. Although hard coding a connection string within
source code offers the best performance, file system caching ensures that the performance
degradation associated with storing the string externally in the file system is
negligible.
When you are choosing an approach for connection string storage, the two most
important considerations are security and ease of configuration, closely followed by
performance.
Different ways for storing database connection strings:
• In an application configuration file; for example, Web.config for an ASP.NET Web
Application.
• In a Universal Data Link (UDL) file (supported only by the OLE DB .NET Data Provider)
• In the Windows registry
• In a custom file
By using Windows authentication to access SQL Server, you can avoid storing user names and passwords in connection strings. If your security requirements demand more stringent measures, consider storing the connection strings in encrypted format.
For ASP.NET Web applications, storing the connection strings in encryptedformat
within the Web.config file represents a secure and configurable solution.
Using XML Application Configuration Files:
You can use the element to store a database connection string in the
custom settings section of an application configuration file. This element supports
arbitrary key-value pairs.
value="server=(local);Integrated Security=SSPI;database=northwind"/>
Advantages
• Ease of deployment. The connection string is deployed along with the configuration
file through regular .NET xcopy deployment.
• Ease of programmatic access. The AppSettings property of the
ConfigurationSettings class makes reading the configured database connection string an easy task at run time.
• Support of dynamic update (ASP.NET only). If an administrator updates the
connection string in a Web.config file, the change will be picked up the next time
the string is accessed, which for a stateless component is likely to be the next time
a client uses the component to make a data access request.
Disadvantages:
• Security. Although the ASP.NET Internet Server Application Programming
Interface (ISAPI) dynamic-link library (DLL) prevents clients from directly
accessing files with a .config file extension and NTFS permissions can be used to
further restrict access, you might still want to avoid storing these details in clear
text on a front-end Web server. For added security, store the connection string in
encrypted format in the configuration file.
Using UDL Files:
The OLE DB .NET Data Provider supports Universal Data Link (UDL) file names in
its connection string. You can pass the connection string by using construction
arguments to the OleDbConnection object, or you can set the connection string by
using the object’s ConnectionString property.
For the OLE DB provider, to reference a UDL file with the connection string, use
“File Name=name.udl.”
Advantages:
• Standard approach. You might already be using UDL files for connection string
management.
Disadvantages:
• Performance. Connection strings that contain UDLs are read and parsed each
time the connection is opened.
• Security. UDL files are stored as plain text. You can secure these files by using
NTFS file permissions, but doing so raises the same issues as with .config files.
• SqlClient does not support UDL files. This approach is not supported by the
SQL Server .NET Data Provider, which you use to access SQL Server 7.0 and later.
Using the Windows Registry
Windows registry also be used to store the connection string, but not recommended due to deployment issues.
Advantages:
• Security. You can manage access to selected registry keys by using access control
lists (ACLs). For even higher levels of security, consider encrypting the data.
• Ease of programmatic access. .NET classes are available to support reading
strings from the registry.
Disadvantages:
Deployment. The relevant registry setting must be deployed along with your
application, somewhat defeating the advantage of xcopy deployment.
Using a Custom File
You can use a custom file to store the connection string.
• Advantages
o None.
• Disadvantages
o Extra coding. This approach requires extra coding and forces you to deal explicitlywith concurrency issues.
o Deployment. The file must be copied along with the other ASP.NET applicationfiles. Avoid placing the file in the ASP.NET application directory or subdirectoryto prevent it from being downloaded over the Web.
|
Responses
|
| Author: Mahesh Raj 07 Jun 2008 | Member Level: Gold Points : 1 | This is very good information,Continue posting such useful articles.
|
|