Feature Companion
LANGUAGES:
All .NET Languages
ASP.NET
VERSIONS: 1.0 | 1.1
9 Data-Access Best Practices Tips
Follow this checklist to keep your code efficient.
By Jeffrey Hasan and Kenneth Tu
Good data-access code begins with making solid design
decisions. In addition, it helps if you follow a list of best practices, which
you can think of as a checklist that keeps your code efficient and might also
buy you extra performance. Here are some generally recognized best practices
for writing data-access code.
1. Use the Appropriate Data-Access Object
The easiest and smartest step you can take is always to
use the most appropriate ADO.NET data object for the given scenario. Always use
the DataReader's streaming data access for read-only data retrieval operations.
Use the DataSet object for data update operations only if you need to perform
the updates in disconnected mode. (Alternatively, you can write dedicated
update stored procedures that resolve updates for you). Use the DataView object
when you want to work with filtered views of a larger DataSet object. The
DataView object provides many of the benefits of the DataSet object, but
without as much overhead.
2. Use Stored Procedures, Not Embedded T-SQL
An accepted T-SQL design approach is to compile your Data
Manipulation Language (DML) statements into stored procedures. Stored
procedures execute much faster than T-SQL statements because they are
precompiled on the database server and are reusable. The ADO.NET Command object
lets you execute embedded T-SQL statements directly by assigning the statement
to the CommandText property and setting the CommandType enumeration to Text. We
have seen code listings that assemble a T-SQL string in code dynamically. But
you should always avoid this approach. Not only does T-SQL execute more slowly
than a stored procedure, but you also can introduce parsing errors into the
T-SQL statement, which in turn generates runtime errors. Worse yet, if your
application executes any dynamic T-SQL statement, you might inadvertently allow
the application to execute commands that modify the database structure (such as
dropping tables). Always execute stored procedures, and use input parameters
(preferably with strong typing).
You can minimize the size of the returned result set by
filling in only the records you need. This is especially important for database
query results that will be marshaled over the wire to Web clients. Also, avoid
using the * wildcard in SQL queries; always specify the exact fields you want
to extract.
3. Use Complex Stored Procedures, Not Multiple Retrievals
Use complex stored procedures that return multiple result
sets rather than making multiple calls to multiple stored procedures. ADO.NET
makes it easy to work with multiple result sets. For example, you can use the
SqlDataReader object's NextResult method. Here is a code listing that
demonstrates how to iterate through every record in every returned result set:
sqlDR = objDB.RunQueryReturnDR("MyStoredProcedure")
Dim arrResult(0) As String
Do
While sqlDR.Read() '
Position the pointer on the first record
i += 1
ReDim Preserve
arrResult(i)
arrResult(i) =
sqlDR("ProductNumber")
End While
Loop While (sqlDR.NextResult()) ' Move to the next resultset
If possible, write your stored procedures to batch-related
result sets. This helps reduce network traffic and overhead on the database
server.
4. Use SQL Data Types With SQL Server
The .NET Framework and SQL Server use different data types
that do not always convert with each other. The System.Data.SqlTypes namespace
provides a set of .NET Framework structures that represent SQL Server data
types in the managed environment. In addition, the SqlDataReader class provides
typed accessor methods that map retrieved field values into the appropriate
structure automatically. Always use typed accessor methods when retrieving SQL
Server data to avoid type-conversion errors.
5. Use Connection Pooling
Always use connection pooling. The SQL Server managed
provider supports connection pooling by default, with little effort required on
your part. The most work you must do is modify the connection string to
override default settings for connection pooling parameters.
6. Use Centralized Data-Access Functions
Always centralize your data-access functions in a
dedicated class file. This lets you maintain your database code in one central
location, which makes it easier to write and maintain. A data-access class
implements wrapper functions for basic data-access operations, including
executing a stored procedure and returning either a DataReader, DataSet,
XmlReader, or no return value at all.
These wrapper functions encapsulate the details of setting
up the Connection and Command objects, as well as any additional objects. This
code becomes repetitive, and the last thing you want to do is have the same
constructs included in dozens of locations throughout your code. Not only is
this difficult to maintain, but it inflates the size of the application
executable artificially.
Microsoft provides a .NET component named the Microsoft
Application Blocks for .NET, which contain optimized data-access wrapper
functions. You can read more about this, and download the code, at http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnbda/html/daab-rm.asp.
7. Use Good Programming Sense
The .NET managed runtime environment provides advanced
garbage collection designed to optimize resource usage and remove unnecessary
references. You should always implement, however, the fundamental housekeeping
rules that keep your code readable and efficient. In particular, always clear
object references when you are done with them. If you use data connections,
make sure you keep the connection open for as short a time as possible. Open
the connection right before it is needed, and close it as soon as it is no
longer required. Never leave open connections in your code, especially if you
are working with streaming data objects such as the DataReader. In summary,
close connections and clear unused object references - this is good coding
practice, and it makes good sense.
8. Use Exception Handling Appropriately
Exception handling is expensive, especially throwing
errors. Always implement structured error handling in your applications, but
design your code to avoid falling into exception handlers. This might seem
obvious, except to developers who are used to coding inline, such as when using
classic ASP. Inline code often uses On Error Resume Next constructs that let
code continue executing past errors in order to check for an expected error
result. This approach is unnecessary in the .NET managed environment. Design
your code to use exception handlers as destinations of last resort. Use the
multitude of error- and type-checking functions to detect errors before the
compiler is forced to raise a runtime error.
Throw exceptions only if absolutely necessary because this
is an expensive operation. The Exception classes provide a large amount of
information that might go unused by the calling code that receives the thrown
error. In this case, it is better to raise a custom error using Err.Raise than
to throw an exception. This operation transmits basic error information such as
an error number, source, and message, but it avoids expensive information such
as the detailed call stack.
Finally, if you provide more than one catch statement with
differing filter criteria, remember to order them from most specific type to
least specific type. For example:
Try
Catch SqlErr as SqlException
Catch err As Exception
Finally
End Try
9. Use Helper Technologies
ASP.NET provides technologies that complement ADO.NET in
providing optimized data access. In particular, ASP.NET supports a
sophisticated set of caching options ranging from page-level output caching to
data caching using the Cache API. Caching is an important consideration in
ASP.NET application design. It is considerably faster to read data from a cache
than it is to access it fresh from a data source. Caching reduces the number of
queries executed against the database and delivers data more responsively.
Caching does come with a price in terms of increased memory usage, particularly
if you are caching large DataSets. But caching is efficient in ASP.NET and
almost always proves to be worth the small price you pay in increased resource
usage. The one caveat with caching is it can provide only a stale view of data,
so you need to factor in the appropriate refresh rate into a caching implementation.
ASP.NET and the .NET Framework provide additional features
that can serve as helper technologies for data access. Above all, keep in mind
that with ASP.NET, you have access to the full .NET class framework, and this
alone is a powerful advantage - for data-access code and beyond.
Jeffrey Hasan and Kenneth Tu are technical
architects and software developers who specialize in Microsoft technologies at
InfoQuest Systems (http://www.infoquest.tv),
a leading provider of business intelligence applications and services for the
telecommunications and broadband industries. Their primary expertise is in .NET
enterprise application development, with a special focus on developing
enterprise Web applications using ASP.NET. They recently co-authored Performance Tuning and
Optimizing ASP.NET Applications (Apress). Read more about this and other
publications at http://www.asptechnology.net. E-mail Jeffrey
Hasan at mailto:Jeff@asptechnology.net.