Design Checklist for Scaling .NET Applications – ADO.NET

This is a handy design checklist to check when you are writing a ADO.NET implementation.


Things to check for when using Connection Object

  • Pool connections.
  • Explicitly close connections.
  • Specify CommandBehavior.CloseConnection if you are using DataReader
  • For single operations of Fill/Update do not open a connection explicitly
  • Avoid checking the State property of OleDbConnection.

Things to check for when using Parameter Object

  • Use Parameters collections when calling stored procedures/building SQL Statements.
  • Use the Parameters collection when you build SQL statements.
  • Explicitly create stored procedure parameters.
  • Specify parameter types.
  • Cache stored procedure SqlParameter objects.

Things to check for when using DataReader Object

  • Always Close DataReader objects.
  • Use CommandBehavior.CloseConnection to close connections.
  • Always Cancel pending data.
  • Use CommandBehavior.SequentialAccess with ExecuteReader.
  • Use GetOrdinal when using an index-based lookup.

Things to check for when using DataSets

  • Reduce serialization.
  • Use primary keys and Rows.Find for indexed searching.
  • Use a DataView for repetitive non-primary key searches.
  • Use optimistic concurrency model for datasets.

Things to check for in Exception Handling

  • Always check the ConnectionState property when handling exceptions.
  • Always use try/finally to clean up resources
  • Do not use generic exceptions. Use specific handlers to catch specific exceptions.


  • Use SQL transactions for server controlled-transactions on a single data storage
  • Use ADO.NET transactions for client-controlled transactions on a single data storage.
  • Use Distributed Transaction Coordinators (DTC) for transactions that span multiple data storage’s,
  • Keep transactions as short as possible.
  • Use the appropriate isolation level.
  • Avoid code that can lead to deadlock.


  • Avoid unnecessary type conversions


Source : Scaling .NET Applications