using a static DataContext in a WCF Service causing errors?

Question

I have a WCF Service that maintains several connections to various databases. I am using Linq to Sql Objects.

The approach I have taken is that I have a list of available connections in a database, and when I call the service I see if a DataContext already exists, and if not I create a new DataContext

private static Dictionary<String, GenericDataClassesDataContext> _db = new Dictionary<string,GenericDataClassesDataContext>(); 

private bool AddConnection(string applicationName, string connectionString)
{
    try
    {
        //Test Connection
        SqlConnection testConn = new SqlConnection(connectionString);
        testConn.Open();
        string commandString = "Select * from ModelEntity";
        SqlCommand sqlCmd = new SqlCommand(commandString, testConn);
        SqlDataReader dataReader = sqlCmd.ExecuteReader();

        //if exists remove
        if (_db.ContainsKey(applicationName))
            _db.Remove(applicationName);

        //add connection
        _db.Add(applicationName, new GenericDataClassesDataContext(connectionString));
        _db[applicationName].ObjectTrackingEnabled = false;

        return true;
    }
    catch
    {
        return false;
    }
}

private bool CheckConnection(string applicationName)
{          
    try
    {
        if (!_db.ContainsKey(applicationName))
        {
            _genericConnection.Open();
            SqlCommand thisCommand = new SqlCommand("GetConnection", _genericConnection);
            thisCommand.CommandType = CommandType.StoredProcedure;
            thisCommand.Parameters.Add(
                new SqlParameter("@Name", applicationName));

            SqlDataReader thisReader = thisCommand.ExecuteReader();
            while (thisReader.Read())
            {
                string conString = thisReader[1].ToString();
                if (AddConnection(applicationName, conString) == false)
                    return false;
            }

            thisReader.Close();
        }                

        return true;
    }
    finally
    {
        if (_genericConnection != null)
        {
            _genericConnection.Close();
        }                   
    }
}

Example Call

public ReferenceValue[] GetReferenceValues(string applicationName)
{
    if (CheckConnection(applicationName))
        return _db[applicationName].ReferenceValues.ToArray();

    return null;
}

(In most the examples I see new DataContext is created for each service call, so I am now thinking I may have a serious design flaw)

The problem I am having is that

A\ When an error occurs in the WCF application my connection seems to 'break' for subsequent calls (for a while at least)

B\ I sometimes get an error returned: "Object reference not set to an instance of an object."

Is this the wrong approach to take, do I need to open a connection every time there is a call to my service?

Answer 1

I have updated my wcf Service so it establishes the datacontext on each method call, and it looks like that has solved my issues.

Answer 2

Don't use a static DataContext instance. It (and the SQLConnection it has) is not threadsafe.