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 connection 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 can not remember sorry. Hopefully you will find them equally as useful as I do.
Best Practice #1
Always use native .Net data providers.
The reasoning:
It has been proven by using the native .Net data providers always perform better and allow you to take advantage of both the .Net framework and the full power of the underlying 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.
The Reasoning:
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
It is always best to use Windows authentication mode when connecting to your SQL Server database, this really applies mostly to Windows Forms applications.
The Reasoning:
Windows authentication is always much safer as the username and password do not pass over the wire.
Best Practice #4
Always use an asynchronous delegate when establishing a connection from a Windows Forms application.
The Reasoning:
This will prevent the user interface from seeming to seizing up as the application attempts to connect to the underlying database.
Best Practice #5
Prefer to use the sorting methods on the SQL Server such as the ORDER BY, HAVING and GROUP BY statements.
The Reasoning:
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 #6
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.
The Reasoning:
By limiting the amount of information you send through the wire you make the application seem faster and this also allows for a more scaleable design.
Best Practice #7
It is always best to use the CommandBehavior.CloseConnection enumerated value when you invoke the ExecuteReader method of a Command object.
The reasoning:
This allows for better connection pooling as the connections that are opened are returned quickly.
Best Practice #8
It is always best to cancel before closing a DataReader object if you are finished reading any more rows.
The reasoning:
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 #9
It is always best to use a parameterized command over dynamic SQL queries.
The reasoning:
This will improve performance and reduce the a SQL injection attack while also making your code much more easier to maintain.
Best Practice #10
It is always best to access tables through views and stored procedures over other methods like dynamic SQL queries.
The reasoning:
The stored procedures and views do not add any overhead to a SQL server while providing some level of indirection which allow you to change the structure of the database table without drastically affecting your client code.
Best Practice #11
It is always best to implement some sort of resultset pagination when dealing with results of 50 or more rows.
The reasoning:
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 #12
It is always best to close a transaction as quickly as possible.
The reasoning:
When a transaction occurs one or more rows are locked which means other users or applications can not access them. By using as short of a transaction as possible you ensure the scalability and stability of your application.
Best Practice #13
Never rely on the default behavior of the DataAdapter object for managing concurrency issues with your database.
The reasoning:
The DataAdapter object relies on the underlying which will leave itself in an inconsistent state if an update occurs, this is because ADO.Net will only throw an exception and not resolve the actual conflict at hand.
Best Practice #14
It is usually best to implement a timestamp field when you are using optimistic concurrency.
The reasoning:
This will allow to more easily detect when another user has updated the database.