I'm setting up a connection factory for a .NET project, and I'd like to ask what is the best way for it. I have an issue with the previous Log class not being able to write the logs properly because of table LOCKS (or so they say), so I'm tasked with setting up a new data layer that (hopefully) solves this along some other minor issues.
For now, the code:
public sealed class ConnectionFactory
{
//Will be SQL only
private static SqlConnection sqlConnection = null;
private static string connectionString = ConfigurationManager.ConnectionStrings["Development"].ConnectionString;
public static SqlConnection GetConnection()
{
if(sqlConnection == null)
{
sqlConnection = new SqlConnection();
sqlConnection.Open();
}
return sqlConnection;
}
}
I'll be using mostly procedures, but may have one or another weird request and we'll type a query if needed, so I'm think I have to add a SqlCommand somehow:
private static SqlCommand sqlCommand = null;
public static SqlCommand GetCommand()
{
//Check if sqlConnection is not null (omitted)
if(sqlCommand == null)
{
sqlCommand = sqlConnection.CreateCommand();
}
return sqlCommand;
}
However, should this command be static? Or should I create a new one each time a new query is going to be executed? Thinking in avoiding locks mostly, can it be caused by having multiple commands or only multiple connections? How do I avoid and deal with this properly?
I believe a "nolock" would work in this case...
whenever you do a direct query.