I’ve developed an application that reads a file, maps it and stores info on the database. For some columns we need the primary key of an object in the database, and if the record does not exist we need to create it, for that purpose we got a class called ReferenceSolver which is abstract and has many implementations that will check if the object exists and create it if necessary.

Since I want the operation to be atomic and create the main and object and the referenced objects in one transaction I’m using a class called TransactionBuilder to which I pass all the queries that I want to run. The problem I found is that I don’t know how to pass the reference to TransactionBuilder every child of ReferenceSolver, I worked around it by using a Singleton but is raising plenty of red flags on my head.

The logic in the relevant method for ReferenceSolver like this in:

    internal override string GetReferencedObjectKey(string referenceValue, Dictionary<string, string> record)     {         SQLConnector connector = new SQLConnector(LogImporter.ConnectionString);          using (var reader = connector.ExecuteQuery(string.Format("SELECT W6Key FROM {0} WHERE {1} = '{2}'", TableName, ReferenceField, referenceValue)))         {             if (reader.Read())             { return reader["W6Key"].ToString(); }             else             {                 int key = KeyHelper.GetNextFreeKey(TableName);                 transactionBuilder.ExecuteQuery(GetInsertQuery());                  return key.ToString();             }         }     } 

Is there any way that I can pass the same instance of the object without using a singleton that I haven’t thought of?? I also thought of raising an event to request the creation of the object but I’m not sure if this is a good solution either.