It can hardly be said that any serious programmer has had to deal with database programming at least some time in their careers. So it would be logical then to make sure your code to these underlying databases are as efficient as possible. Hopefully I will share some of the best practices I have learned in dealing with ADO.Net programming. These techniques were learned from a variety of sources, many of them I cannot remember sorry but are public Internet sources. Hopefully you will find them equally as useful as I do.

Best Practice #1

Always use built in .Net data providers.

The built in .Net data providers allow you to take advantage of both the .Net framework and the full power of the database.

Best Practice #2

Always use a config file to store your connection strings. Also it might be a good idea to encrypt these connection strings especially if stored in a dubious location.

It is always best to store data that might change in a location outside of your application where you can easily update the connection strings. Also encrypting the connection strings is always a good idea from a security standpoint.

Best Practice #3

Prefer to use the sorting methods on the SQL Server such as the ORDER BY, HAVING and GROUP BY statements.

By performing the sorting on the server side as opposed to the client side you save time because the server can perform the work faster.

Best Practice #4

You should always try to limit the number of rows in a resultset. This can be performed typically by using the TOP keyword or other similar methods.

By limiting the amount of information you send through the wire you make the application seem faster.

Best Practice #5

It is always best to use CommandBehavior.CloseConnection when you use the ExecuteReader method of a Command object.

This allows for better connection pooling as the connections that are opened are returned quickly.

Best Practice #6

It is always best to cancel before closing a DataReader object if you are finished reading any more rows.

The close method of the DataReader class continues to read all remaining rows before it finally closes the object. This is a wasteful use of resources.

Best Practice #7

It is always best to use a parameterized command (usually a stored procedure) over dynamic SQL queries.

This will improve performance and reduce the chance SQL injection attack while also making your code much more easier to maintain.

Best Practice #8

It is always best to implement some sort of resultset pagination when dealing with results of 50 or more rows.

Although not an easy task in most cases using this technique you can increase performance on both your server database and your client application as less overhead and network traffic is taking place at any one time.

Best Practice #9

It is usually best to implement a timestamp field against all rows – I usually have ‘creationdate’ and ‘lastupdate’ fields.

This will allow you to detect when the database has been updated, and makes it much easier to check your code is interacting with the data source correctly.

Best Practice #10

Don't return data via ordinals e.g SELECT *

Although a SELECT * may be the quickest way of coding it is certainly the slowest way of returning all the columns - and how often do you actually use all the columns? Specify the columns you are selecting.