0

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?

Danicco
  • 1,573
  • 2
  • 23
  • 49

1 Answers1

0

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

FROM [table] with (nolock)

whenever you do a direct query.

alybaba726
  • 390
  • 4
  • 14
  • I'll start using that for queries and procedures, but in the case I need a lock, could proper management of connection/commands solve it? I was thinking that if I use only one command and the application sends another request, the command would be "queued" to run after the current one finishes (as if it were a single user running multiple queries at once) – Danicco Jun 13 '14 at 12:14