c# - Where to put using(SqlConnection) -

i creating framework add plugins application of mine. each plugin must implement abstact class. each plugin compiled dll main application can find directory.getfiles(mypath, "*.dll")

this works swimmingly , can instantiate plugins in main application , use them. each plugin dashboard widget user can add dashboard show him graphs or charts. means, each plugin has timer , on every timer event refreshes graph data application's sql database.

so question is, put sqlconnection? create 1 sqlconnection , pass parameter each plugin or pass connection string , have each plugin create own sqlconnection?

if pass application's sqlconnection plugin i'd imagine involve managing of connection inside plugin. i'd have check it's open , do if it's state connectionstate.fetching or connectionstate.executing? seems unwieldy.

but on other hand, considering multiple users running application , each user might have multiple plugins selected in dashboard, add number of sqlconnections. desirable? should consider third option plugin gives query host queues other queries other plugins , return result set plugin once query has executed? way @ least there's 1 sqlconnection every user, regardless of how many plugins they've selected.

to honest, last option seems rather complicated me , i'm not quite sure yet how i'd implement that. if point me towards article explains similar, i'd appreciate it.

personally recommend pass connection factory plugins, , let them create , use connections see fit. means application in control of connection string (although potentially still read connection, unless abstract away), free create , use connections necessary. pointed out previous answer, if give them single connection there's lot of work manage issues multi-threading, , sharing issues mentioned yourself.

you 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 plugin responsible managing connection (e.g. opening, closing, disposing). there's lot more can take varying levels of control on connection, attempt detect misbehaving plugins, etc.

edit

absoutely, plugins should use using() statement:

public void mypluginmain(isqlconnectionfactory factory) {    using(var connection = factory.generateconnection())    {       // work    } } 

keeping in mind app shifting resposibility maintaining connections plugins, plugin must clean connection when it's done it, whether that's using() statement, or calling dispose() after it's done has done. if kind of 'public' api, should part of documentation.