Browse DevX
Sign up for e-mail newsletters from DevX


ADO.NET Best Practices : Page 2

ADO.NET is a powerful toolbox but it's not a software magic wand. Learn about common best practices for using three key element of any data access strategy: connections, security, and transactions.




Building the Right Environment to Support AI, Machine Learning and Deep Learning

Connection String Storage
When you need to operate on a database, a connection string is needed. How do you retrieve this critical information for use in your application(s)? By far, hard-coded strings offer the best performance because they compile directly into your application code However, hard-coded strings also diminish flexibility and require you to recompile the application if the string changes.

External storage offers much greater flexibility at the price of extra overhead. All in all, the impact of this extra burden on performance is negligible; it may pose some security issues, however. Attackers, in fact, might tamper with external files and corrupt them. If you want to consider external storage these are a few options: .config files, UDL files, Windows registry.

The .NET Framework configuration files are easy to access programmatically and are deployed as plain text files. Their text-based nature is the cause of their most significant drawback if they contain passwords, because they are stored as clear text by default. A tailor-made engine for encrypting and decrypting is possible to arrange, but the task is completely up to you.

UDL files are text files recognized by OLE DB providers. This means that the SQL Server managed provider doesn't support them. UDL files suffer from the same security issues as .config files and don't offer greater advantages.

Finally, you can use the Windows registry as a naturally secure storage medium. The registry is a system repository for critical information. It reaches a high level of security especially if combined with encryption. The main drawback of using the registry is that it poses some deployment issues. You need to create registry keys (and optionally store encrypted data in it) and read their contents back. The .NET Framework provides a set of wrapper classes that call into the underlying Win32 API. None of these classes, though, supply encryption capabilities. The aspnet_setreg.exe utility (see Knowledge Base article 329290) can be used to create a registry key under the HKEY_LOCAL_MACHINE hive and store their user name and password. The following code snippet shows the typical command line of the utility.

aspnet_setreg.exe -k "Software\MyData" -u:userID -p:password

It encrypts the specified UserID and password and stores that into the given key.

Connection Pooling
Database connections are not only critical to create, but it is critical that you reuse them. Connection pooling allows you to reuse an existing object from a pool instead of repeatedly establishing a new connection with the database. In this way, a limited number of connections can serve a much larger number of clients.

Avoid mixing manual and automatic transaction in the same context. If you're not particularly concerned about performance, consider using automatic transactions even against a single database because it results in simpler (though slower) code.
Each connection pool is associated with a distinct connection string and its transaction context. When you open a new connection, the data provider attempts to find a match between the specified connection string and a string already used by the connection pool. If no match is found, the data provider will create a new connection and add it to the pool. Once created, connection pools are not destroyed until the process ends. You may think that this results in a performance hit, but it really doesn't. Maintenance of inactive or empty pools requires only minimal overhead.

When a connections pool is created, multiple connection objects are created and added so that the minimum size is reached. Next, connections are added to the pool on demand, up to the maximum pool size. If no usable connection is available, the pool handler attempts to create a new connection object. When the maximum pool size is reached, the request is queued and served as soon as an existing connection object is released to the pool. The process of creating new connections is serialized and requests hit the server one at a time.

You should avoid trying to create connection strings programmatically. If you build a string by concatenating input data together, you expose yourself to the risk of code injection attacks. To avoid that, you should first validate the data you're putting in the connection string. This little precaution could save you from sneaky and harmful attacks.

Comment and Contribute






(Maximum characters: 1200). You have 1200 characters left.



Thanks for your registration, follow us on our social networks to keep up-to-date