Application Name :
When a connection is made to SQL Server there are by default no way for SQL Server to know what software is making the connection.
If several apps are using a shared SQL Server and there are problems caused by a certain connection, DBA’s or Operations team will be able to locate that connection by SPID. However determining what application or website is responsible for the connection causing the error is takes way harder.
But if the developer includes the “Application Name” property in the connection string then it will be very easy for DBA or Operations team to see the name of the application that is causing the trouble. This will save hours for the DBA and it will make the developer popular amongst the DBAs.
Along with the SQL Server, Database, Username and Password, don’t forget to include the Application Name in your connection string. It’s an optional parameter, but it can be a lifesaver.
Data Source=myServer;
Initial Catalog=myDB;
User Id=myUsername;
Password=myPassword;
Application Name=myApp;
It becomes very handy to identify the queries using SQL Profiler. Without Application Filter, its lot more invasive.
Connection Pooling:
Always close the connection when you are finished using it so that the connection will be returned to the pool.Its saves ton of headache for DBA’s . Troubleshooting connection pool issues in very active database is not a trivial task.
Microsoft Blurb:
We strongly recommend that you always close the connection when you are finished using it so that the connection will be returned to the pool. You can do this using either the Close or Dispose methods of the Connection object, or by opening all connections inside a using statement in C#, or a Using statement in Visual Basic. Connections that are not explicitly closed might not be added or returned to the pool.
Do not call Close or Dispose on a Connection, a DataReader, or any other managed object in the Finalize method of your class. In a finalizer, only release unmanaged resources that your class owns directly. If your class does not own any unmanaged resources, do not include a Finalize method in your class definition.
Retry Pattern:
Implements sound Retry Mechanism in Data Layer for occasional timeouts and database connectivity issues.
Handling NULLS and Empty Result Sets:
Graciously Handle NULLS and Empty Result Sets from the Database. This improves the performance of the Application and avoids all the NULL exceptions noise which makes everyone’s life better.
References:
Connection Pooling : https://msdn.microsoft.com/en-us/library/8xx3tyca(v=vs.110).aspx
Retry Design Pattern : https://msdn.microsoft.com/en-us/library/dn589788.aspx
Handling NULLS : https://blogs.harvard.edu/lianaleahy/2006/08/14/working-with-null-values-in-the-net-framework/