Where to use (SqlConnection)

I am creating an environment for adding plugins to my application. Every plugin must implement a paragraph class. Each plugin is then compiled as a DLL, which the main application can find withDirectory.GetFiles(myPath, "*.dll")

It all works smoothly and I can create my plugins in the main application and use them. Each plugin is basically a dashboard widget that a user can add to their dashboard to show them some graphs or charts. This means that every plugin has a timer, and on every timer event, it updates the graph with data from the SQL application database.

So my question is, where do I put SqlConnection

? Create one SqlConnection

and pass it as a parameter to each plugin, or pass a connection string and each plugin create its own SqlConnection

?

If I pass the application SqlConnection

to a plugin, I would assume it would require some kind of connection management within the plugin. I obviously have to check that it is open and what should I do if it is specified ConnectionState.Fetching

or ConnectionState.Executing

? It just seems cumbersome.

But on the other hand, given that several users will run the application, and each user can have several plugins selected on his toolbar, it can contain up to several SqlConnections

. Is this desirable? Should I consider a third option, where the plugin passes its request to the host, which queues it up with other requests from other plugins and returns the result to the plugin after the request is complete? This way, at least for each user, there is only one SqlConnection

, no matter how many modules they have selected.

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

+3


source to share


2 answers


Personally, I would recommend passing the factory connection to your plugins and letting them create and use connections as they see fit. This means that your application is in control of the connection string (although they can potentially read it from the connection if you don't distract it too), but they are free to create and use connections as needed. As pointed out in the previous answer, if you just give them one connection, there is a lot of work in there to manage the multithreaded access issues and sharing issues you mentioned yourself.

You can do something simple:

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 (like opening, closing, deleting). There's a lot more you can do about it, to take different levels of control over the connection, try to detect invalid modules, etc.



EDIT

Absolutely, your plugins should use the operator using()

:

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

      

Keeping in mind that your application biases portability to support these plugin connections, the plugin has to clear the connection when it is associated with it, whether by a statement using()

or actually calling Dispose()

after it has done what it did. If this is some kind of "public" API, it should be part of your documentation.

+4


source


You shouldn't share the connection between different plugins, and not create it inside the plugin or even for every request. This will not hurt performance, they are designed to be used that way.



See SqlConnection and Multithreading

+2


source







All Articles