SQL Connection/Command proper management (to avoid table LOCKS)?

177 Views Asked by At

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?

1

There are 1 best solutions below

1
On

I believe a "nolock" would work in this case...

FROM [table] with (nolock)

whenever you do a direct query.