Last month in "ADO.NET Dos," I presented tips for writing effective ADO.NET database applications. Naturally, no presentation of dos is complete without a corresponding list of don'ts. Knowing what not to do is important because violating these precautions can ruin your ADO.NET applications' performance or compromise the security of your SQL Server system.
7. Don't Treat ADO.NET Like ADO
Unlike ADO, ADO.NET is designed to work in a disconnected mode, maintaining an independent copy of the data for each client. ADO.NET applications are designed to work with cached data stores, quickly connecting and disconnecting from the database to retrieve and update data.
6. You Don't Have to Use SqlCommandBuilder
SqlCommandBuilder automatically generates INSERT, UPDATE, and DELETE statements that the DataAdapter uses to post DataSet updates to the source database. But you can boost performance by not using SqlCommandBuilder and instead coupling your own stored procedures to the DataAdapter's InsertCommand, UpdateCommand, and DeleteCommand properties.
5. Don't Overlook the DataView
The DataView lets you bind to both Web and WinForms applications and can contain a custom subset of the information from the DataTable. You can use the DataView Expressions property to extend the data in the DataTable by using calculated columns.
4. You Don't Need to Use a DataSet
If you're retrieving data from a single table, you can avoid the DataSet's overhead by creating one instance of a DataTable independent of a DataSet. Then, you can access the data in the DataTable and bind to it without needing a DataSet.
3. Don't Think of the DataSet as a Small Database
Sometimes, beginning ADO.NET programmers try to make the DataSet into a smaller version of the underlying database. You're better off thinking of the DataSet as a local data cache that contains only the data your application needs.
2. Don't Embed Connection Strings
Embedding hard-coded connection strings in your application can compromise security. If you can, take advantage of integrated security. If you can't, use integrated security to store your application's connection strings externally in Active Directory (AD) or in a secured configuration file.
1. Don't Retrieve Unnecessary Data
Retrieving too much data is the best way to kill your application's performance. Excessive data taxes the network and consumes valuable system resources that large-scale Web applications need. Review your SELECT statements to make sure they're using the appropriate WHERE clauses and retrieving only the columns they require.
