How to manage this situation with Dependency Injection correctly

Question

Imagine you have a WebSite in which every user could put a code inside a box, then press "search" to find informations about a product. The code will match the pattern: SHOPNAME-PROGRESSIVENUMBER, for example: EVERYDAYSHOES-002, or NEWGAMES-005.

When the user will press "search" button, my code will:

1. Identify the shop
2. Get a connection string to connect to a DB, which is different for every shop
3. Invoke a query on that DB with that connection string.

The first thing that came to my mind to accomplish this, was to code something like:

public interface IProductInformationsProvider
{
    string GetProductName(int productCode);
}

public class SQLProductInformationsProvider : IProductInformationsProvider
{
    ... 

    public SQLProductInformationsProvider(
        string connectionString,
        ILogService logService)
    {
        ...
    }

    public string GetProductName(int productCode)
    {
        ...
    }
}

To get an istance of IProductInformationsProvider I would use a DI container and do something like:

...

public void SearchButtonHandler(Dictionary<string, string> shopsConnectionStringsMap)
{
    using (var container = new WindsorContainer())
    {
        container.Register(
            Component.For<ILogService>()
            .ImplementedBy<ConsoleLogService>());

        container.Register(
            Component.For<IProductInformationsProvider>()
            .ImplementedBy<SQLProductInformationsProvider>()
            .DependsOn(
                Dependency.OnValue("connectionString", shopsConnectionStringsMap[parsedShopName]));

        var productInformationsProvider = container.Resolve<IProductInformationsProvider>();
        var productName = productInformationsProvider.GetProductName(theCode);
        // Do something with productName
    }
}

My questions are:

1. Is it good or bad (and why) doing Register - Resolve on every click on my button?
2. If it is bad, how can I refactor this to make it right in relation to DI pattern?

For the second question, what I thought:

1. Just call Resolve overriding connectionString on every button click

2. Resolve all possible SQLProductInformationsProvider, cache them in a dictionary and use the needed one

3. Remove connectionString from the constructor and put it on a method SetConnectionString(string connectionString), but in this case I would have to worry more about thread-safety.

All those three ideas don't satisfy me, they give me the feeling I misunderstood something. What am I missing / how can I change my point of view?

Answer 1

A solution I thought is:

public interface IProductInformationsProvider
{
    string GetProductName(int productCode);
}

public interface IConnectionStringProvider
{
    string GetConnectionString(string shopName);
}

public class SQLProductInformationsProvider : IProductInformationsProvider
{
    ... 

    public SQLProductInformationsProvider(
        IConnectionStringProvider connectionStringProvider,
        ILogService logService)
    {
        ...
    }

    public string GetProductName(string shopName, int productCode)
    {
        string connectionString = connectionStringProvider.GetConnectionString(shopName);
        ...
    }
}

public void SearchButtonHandler(string shopName, int Code)
{
    // _productInformationsProvider already resolved
    var productName = _productInformationsProvider.GetProductName(shopName, Code);
    // Do something with productName
}