Where to put using(SqlConnection)

Question

I am creating a framework to add plugins to an application of mine. Each plugin must implement an abstact class. Each plugin is then compiled as a DLL which the main application can find with Directory.GetFiles(myPath, "*.dll")

This all works swimmingly and I can instantiate my plugins in the main application and use them. Each plugin is basically a dashboard widget that the user can add to his dashboard to show him some graphs or charts. This means, each plugin has a timer and on every timer event it refreshes the graph with data from the application's SQL database.

So my question is, where do I put the SqlConnection? Do I create one SqlConnection and pass it as a parameter to each plugin or do I pass the connection string and have each plugin create its own SqlConnection?

If I pass the application's SqlConnection to the plugin then I'd imagine it would involve some managing of the connection inside the plugin. I'd obviously have to check that it's open and what do I do if it's state is ConnectionState.Fetching or ConnectionState.Executing? It just seems unwieldy.

But on the other hand, considering that multiple users will be running the application and each user might have multiple plugins selected in his dashboard, it could add up to a number of SqlConnections. Is that desirable? Should I consider a third option where the plugin gives its query to the host which queues it up with other queries from other plugins and return the result set to the plugin once the query has executed? That way at least there's only one SqlConnection for every user, regardless of how many plugins they've selected.

To be honest, this last option seems rather complicated to me and I'm not quite sure yet how I'd implement that. If anyone could point me towards an article that explains something similar, I'd really appreciate it.

Answer 1

Personally what I would recommend is to pass a connection factory to your plugins, and let them create and use the connections as they see fit. This means that your application is in control of the connection string (although they could potentially still read it from the connection, unless you also abstract that away), but they are free to create and use connections as necessary. As pointed out by a previous answer, if you just give them a single connection there's a lot of work to manage issues with multi-threading, and the sharing issues that you mentioned yourself.

You could do something as simple as:

public interface ISqlConnectionFactory
{
   SqlConnection GenerateConnection();
}

public class SqlConnectionFactory : ISqlConnectionFactory
{
   private readonly string _connectionString;

   public SqlConnectionFactory()
   {
      _connectionString = "your connection string here";
   }

   public SqlConnection GenerateConnection()
   {
       return new SqlConnection(_connectionString);
   }
}

And then the plugin is responsible for managing the connection (e.g. opening, closing, disposing). There's a lot more that you can do with this to take varying levels of control over the connection, attempt to detect misbehaving plugins, etc.

EDIT

Absoutely, your plugins should use the using() statement:

public void MyPluginMain(ISqlConnectionFactory factory)
{
   using(var connection = factory.GenerateConnection())
   {
      // Do the work
   }
}

Keeping in mind that your app is shifting the resposibility for maintaining those connections to the plugins, the plugin must clean up the connection when it's done with it, whether that's by the using() statement, or actually calling Dispose() after it's done what it has done. If this is any kind of 'public' API, this should be part of your documentation.

Answer 2

You should not share the connection between the different plugins, instead create one within the plugin, or even for every query. That does not hurt performance, they are designed to be used like that.

See SqlConnection and multithreading