9 Data-Access Best Practices Tips

Feature Companion

LANGUAGES:All .NET Languages

ASP.NETVERSIONS: 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 designdecisions. In addition, it helps if you follow a list of best practices, whichyou can think of as a checklist that keeps your code efficient and might alsobuy you extra performance. Here are some generally recognized best practicesfor writing data-access code.

1. Use the Appropriate Data-Access Object

The easiest and smartest step you can take is always touse the most appropriate ADO.NET data object for the given scenario. Always usethe DataReader's streaming data access for read-only data retrieval operations.Use the DataSet object for data update operations only if you need to performthe updates in disconnected mode. (Alternatively, you can write dedicatedupdate stored procedures that resolve updates for you). Use the DataView objectwhen you want to work with filtered views of a larger DataSet object. TheDataView object provides many of the benefits of the DataSet object, butwithout as much overhead.

2. Use Stored Procedures, Not Embedded T-SQL

An accepted T-SQL design approach is to compile your DataManipulation Language (DML) statements into stored procedures. Storedprocedures execute much faster than T-SQL statements because they areprecompiled on the database server and are reusable. The ADO.NET Command objectlets you execute embedded T-SQL statements directly by assigning the statementto the CommandText property and setting the CommandType enumeration to Text. Wehave seen code listings that assemble a T-SQL string in code dynamically. Butyou should always avoid this approach. Not only does T-SQL execute more slowlythan a stored procedure, but you also can introduce parsing errors into theT-SQL statement, which in turn generates runtime errors. Worse yet, if yourapplication executes any dynamic T-SQL statement, you might inadvertently allowthe application to execute commands that modify the database structure (such asdropping tables). Always execute stored procedures, and use input parameters(preferably with strong typing).

You can minimize the size of the returned result set byfilling in only the records you need. This is especially important for databasequery results that will be marshaled over the wire to Web clients. Also, avoidusing the * wildcard in SQL queries; always specify the exact fields you wantto extract.

3. Use Complex Stored Procedures, Not Multiple Retrievals

Use complex stored procedures that return multiple resultsets rather than making multiple calls to multiple stored procedures. ADO.NETmakes it easy to work with multiple result sets. For example, you can use theSqlDataReader object's NextResult method. Here is a code listing thatdemonstrates 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 PreservearrResult(i)

            arrResult(i) =sqlDR("ProductNumber")

    End While

Loop While (sqlDR.NextResult()) ' Move to the next resultset

If possible, write your stored procedures to batch-relatedresult sets. This helps reduce network traffic and overhead on the databaseserver.

4. Use SQL Data Types With SQL Server

The .NET Framework and SQL Server use different data typesthat do not always convert with each other. The System.Data.SqlTypes namespaceprovides a set of .NET Framework structures that represent SQL Server datatypes in the managed environment. In addition, the SqlDataReader class providestyped accessor methods that map retrieved field values into the appropriatestructure automatically. Always use typed accessor methods when retrieving SQLServer data to avoid type-conversion errors.

5. Use Connection Pooling

Always use connection pooling. The SQL Server managedprovider supports connection pooling by default, with little effort required onyour part. The most work you must do is modify the connection string tooverride default settings for connection pooling parameters.

6. Use Centralized Data-Access Functions

Always centralize your data-access functions in adedicated class file. This lets you maintain your database code in one centrallocation, which makes it easier to write and maintain. A data-access classimplements wrapper functions for basic data-access operations, includingexecuting a stored procedure and returning either a DataReader, DataSet,XmlReader, or no return value at all.

These wrapper functions encapsulate the details of settingup the Connection and Command objects, as well as any additional objects. Thiscode becomes repetitive, and the last thing you want to do is have the sameconstructs included in dozens of locations throughout your code. Not only isthis difficult to maintain, but it inflates the size of the applicationexecutable artificially.

Microsoft provides a .NET component named the MicrosoftApplication Blocks for .NET, which contain optimized data-access wrapperfunctions. 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 advancedgarbage collection designed to optimize resource usage and remove unnecessaryreferences. You should always implement, however, the fundamental housekeepingrules that keep your code readable and efficient. In particular, always clearobject 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. Openthe connection right before it is needed, and close it as soon as it is nolonger required. Never leave open connections in your code, especially if youare working with streaming data objects such as the DataReader. In summary,close connections and clear unused object references - this is good codingpractice, and it makes good sense.

8. Use Exception Handling Appropriately

Exception handling is expensive, especially throwingerrors. Always implement structured error handling in your applications, butdesign your code to avoid falling into exception handlers. This might seemobvious, except to developers who are used to coding inline, such as when usingclassic ASP. Inline code often uses On Error Resume Next constructs that letcode continue executing past errors in order to check for an expected errorresult. This approach is unnecessary in the .NET managed environment. Designyour code to use exception handlers as destinations of last resort. Use themultitude of error- and type-checking functions to detect errors before thecompiler is forced to raise a runtime error.

Throw exceptions only if absolutely necessary because thisis an expensive operation. The Exception classes provide a large amount ofinformation that might go unused by the calling code that receives the thrownerror. In this case, it is better to raise a custom error using Err.Raise thanto throw an exception. This operation transmits basic error information such asan error number, source, and message, but it avoids expensive information suchas the detailed call stack.

Finally, if you provide more than one catch statement withdiffering filter criteria, remember to order them from most specific type toleast 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 inproviding optimized data access. In particular, ASP.NET supports asophisticated set of caching options ranging from page-level output caching todata caching using the Cache API. Caching is an important consideration inASP.NET application design. It is considerably faster to read data from a cachethan it is to access it fresh from a data source. Caching reduces the number ofqueries executed against the database and delivers data more responsively.Caching does come with a price in terms of increased memory usage, particularlyif you are caching large DataSets. But caching is efficient in ASP.NET andalmost always proves to be worth the small price you pay in increased resourceusage. 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 featuresthat can serve as helper technologies for data access. Above all, keep in mindthat with ASP.NET, you have access to the full .NET class framework, and thisalone is a powerful advantage - for data-access code and beyond.

Jeffrey Hasan and Kenneth Tu are technicalarchitects and software developers who specialize in Microsoft technologies atInfoQuest Systems (http://www.infoquest.tv),a leading provider of business intelligence applications and services for thetelecommunications and broadband industries. Their primary expertise is in .NETenterprise application development, with a special focus on developingenterprise Web applications using ASP.NET. They recently co-authored Performance Tuning andOptimizing ASP.NET Applications (Apress). Read more about this and otherpublications at http://www.asptechnology.net. E-mail JeffreyHasan at mailto:[email protected].