Code Samples

ADO.Net Best Practices


1. Use DataSet Schema
I hate roundtrips to the database. I especially hate roundtrips to the database when a failure is caused by a violation of the schema. DataSet schema is great at preventing this. By defining your schema in the DataSet, you will be alerted when the data is incorrect earlier in the process. For example, if I create a constraint in my DataSet to make sure that all social security numbers are unique, when I try to add a user twice, the DataSet will let me know (instead of waiting until I update the database and fail).


In addition, by using DataSet relationships, you can pass along object trees that are inter-related, thereby eliminating the need for a set of classes to map the relational data to a hierarchical model.


2. Use Typed DataSets to Create Business Rule Layers
We can create Typed DataSets to do all the hard work in handling the database and serialization. By inheriting from those Typed DataSets, we can write our business rules about our data without having to craft a whole mapping between our data and the database's data. By starting with a Typed DataSet, you are simply reducing the need to write a new interface into the data or any of the database code you would ordinarily use. As the schema of your data changes, you can regenerate the Typed DataSet and modify your code that inherits from the Typed DataSet. The important distinction is that you will need to write less code to start, and even less code as the schema changes.
3. Reduce Roundtrips to the Database
Roundtrips are expensive. Repeat it with me please … roundtrips are expensive. Reducing the number of times you need to call the database will increase the performance of your client code dramatically. Reducing roundtrips will not reduce load on the server by a substantial amount, but the client (or Web site) code will benefit greatly. ADO.NET supports doing queries that return multiple resultsets (although not all managed providers support this), so fetching or updating data in a batch will increase performance.


4. Cache Data Early and Often
ADO.NET is, so to speak, an elaborate caching engine. In Web applications, caching of data is an enormous performance aid. Do not let the database become the bottleneck that slows your application. By caching all reusable information either in a middle tier or on Web servers, your applications will not only scale but also perform well.


Caching is very useful in desktop applications as well. Usually the payoff for caching in a desktop application is not very noticeable until you reach a huge number of users, but the benefits of using Windows Forms' data binding outweighs any additional work required to cache in desktop applications.


5. Get and Use a DBA
I really like database administrators (DBAs). If you have the luxury of having one on your project, please use him or her to help review your database designs, including stored procedures and schema.


Typically DBAs are experts on one or a small handful of database engines. Let them be the expert in that domain. I have been at too many companies where developers were reluctant to use their DBA because of silly turf wars or other political reasons. Believe me, they are usually your best ally as far as getting the database engine to purr.


Lastly, if they know your database code, they can help mitigate problems in a delivered environment by tuning the database code to eke out performance gains at runtime. They want to be used for more than backing up the database every night. And they are usually much too qualified to be doing only that sort of work.


6. Isolate Developers from the Database
Yes, we can create a business logic layer to isolate some developers from the database, but I would like to go a step farther. Use stored procedure for absolutely everything possible. Why? This further isolates the business logic layer from the database.


We have known for years that creating APIs that hide the implementation details from the day-to-day developer has tremendous benefits. Unfortunately, we have not taken that same leap with the database. Yes, there are times when you have to know about the database to do very sophisticated querying, but in a majority of cases, calling a named stored procedure and getting back a well-known table format is all you really need to know. The particulars of whether you are joining to get that table or going directly at a table is well beyond the knowledge you need as a day-to-day developer. In addition, this lets the DBA tune, scale, or even reorganize the database without having to consult you or ask you to change any of your code. As long as the stored procedure returns what you need, you should never need to know what is happening under the covers. In addition, stored procedures provide a higher level of performance than dynamic queries.


The downside to using stored procedures, though, is that you will make it more difficult to write systems that can use multiple database engines. I think this cost is worth it, though. Instead of dummying down the system to have mediocre performance on all database engines, taking the extra time to write the stored procedures for every platform will increase the performance on all engines. However, this does take more time.


7. Use the DataReader Sparingly in ASP.NET
There is a need for DataReaders in some situations in ASP.NET, but be very careful—this is a common place for scalability and performance to degrade. If an ASP.NET page uses a DataReader, each and every hit to that page will require a connection to the database. There is no smart caching. You may get lucky and get some caching of the query compilation on the database server, but you will incur the performance penalty of the roundtrip to the database.


8. Use Connection Factories
There are several key reasons I recommend using factory classes to generate database connections:


Eases connection pooling: No matter the methodology of connection pooling (it is different in each managed provider), by having a single point of connection string creation, you can maximize the availability of pooled connections.


Isolates developers: By having a factory to generate connections, most developers will never need to know what a connection string for your organization looks like. In fact, the factory should be retrieving connection strings from outside code.


Isolates changes: Because there is one (and hopefully only one) place to create connections, any changes to the way connections are created are isolated to a single point of change.


9. Do Not Hard Code Connection Strings
Please do not use my examples in the book as a model of how to store connection strings. For clarity I have tried to make examples that are easy to read, but it is a very bad idea to have connection strings anywhere in code. The best idea is to keep connection strings outside the code. By embedding connection strings, anyone who can get their hands on your assembly will have a door through which they can access your database directly. Similarly, putting that same information in a web.config or a similar file on the Web server is likely to be a security risk as well. I usually default to putting them in machine.config or in a central repository that can be managed by nondevelopers (Active Directory is a good choice).


10. Keep Your Users Out of Your Database
I would suggest that you never add end users to your database just so they can use your application (be it Web or desktop). If they can get to your database through the application, they will also be able to get there outside the application. In that same light, please do not give the SYSTEM account, the IUSR_XXX account, or the ASPNET user direct access to your database. In each of these cases, a simple security failure will give a hacker direct access to your database. I suggest creating application-specific accounts on the database server that applications use to access the database. For example, I have created an ADONET user for my samples who has direct access to just the ADONET database. By using the database server's security, I could decide to have the ADONET user have just read-only access, or access to only stored procedures (not direct table access).


For more information, please refer the book "Pragmatic ADO.NET: Data Access for the Internet World"