Dear Friends,

This "Dynamic ConnectionStrings" topic can be found at multiple places in the forum, but without real solution. I am reopening this issue once again with a hope to find working solution to this problem.

Here is the common scenario

"I am planning to use centralized database for user accounts and after authentication, the user(s) are connected to their corresponding database(s) in web portal. The login interface would ask for the following

Company Name: xxxxxx User ID: xxxxxx Password: xxxxxx

The user will be authenticated from central database, the connection string will be picked up from database or probably created at runtime. The portal is maintaining separate database for each user but with same schema. "

I have researched on forum for one-week literally to find the answer but couldn't succeed. There are some partial answers found in threads like

• LLBGEn is using wrong database name at run time (http://www.llblgen.com/tinyforum/Messages.aspx?ThreadID=3397&HighLight=1) Building a multi-tenacy web app(http://www.llblgen.com/tinyforum/Messages.aspx?ThreadID=5209)

I do know to set the connection string for a DataAccessAdapter object by using its ConnectionString property, or using the DataAccessAdapter constructor overload. Also read "Using the generated code -> Application configuration through .config files" > the Catalog name overwriting section. Also read the following related comment by "Wala" "- You may use Catalog name overwriting (SqlServer only) to specify an empty string. In that case, the DQE will not specify a catalog name in the generated SQL elements, which will make the SQL target the catalog specified in the connection string."

I have still unanswered questions in my mind like

• Should I use a separate LLBLProjects for User Database & Application Database or a single project can include DAL for both databases?

I am using Declarative Data Binding with Adapter in some forms. How it is ensured that DataAdapter is connected to right customer database?

Is there any way to setup ConnectionString once during User Sign-In to avoid using overloaded DataAdapter constructors (as it increases lines of code and also difficult to implement, if application is built half-way)

The things are jumbled up in my mind. It would be highly appreciatable, if some body from support team provides us a working sample of asp.net project for the above mentioned scenario. I hope, it would be very useful for the LLBLGen Pro Community.

Thanks in Advance.

First question: Do you need dynamic connectionStrings to access different database servers but using the same catalog name? Or do you want to access different catalog names in the same database server?

Walaa wrote:

First question: Do you need dynamic connectionStrings to access different database servers but using the same catalog name? Or do you want to access different catalog names in the same database server?

I have different catalog names(Client Databases) in the same database server and I am using Adapter Model.

Just as a thought - is there any reason why each customer needs their own database ? This is usually a bad idea for scalabilty and maintainence if you need to support any sensible number of customers. It is usually a better solution to architect your application database so that it can support multiple users- ie with a users table, and each other table has a userId column which identified which user the row belongs to. Would this be a viable solution for you ? It also simplifies a lot of the issues that we answer below

Assuming you are stuck with one application database per user :-

1) I would use seperate projects for the User and Application databases - there is no real reason why you should not have them all in one project, but it keeps things tidier and easier to manage.

2) If you want the DataAccessAdapter from a single project (ie for your Application databases) to connect to multiple database, then there is no way you can use the connection strings from the app.config file - you have to supply then dynamically in the constructor when the adapter is created. It would be possible to do this once at User sign in, but that would imply that you would have to keep the instance of the adapter alive for the duration of the users session = this is usually a bad idea for scalability and performance.

What you need to do in this case is cache in memory the connection string per user when they sign in, and instantiate DataAccessAdapters with the correct connection as needed.

You also need to use dynamic catalog name overwriting as described here http://www.llblgen.com/documentation/2.6/Using%20the%20generated%20code/Adapter/gencode_dataaccessadapter_adapter.htm#connectionstrings rather than by specifing it in the app.config table.

3) If you are using declarative data binding, you will need to use LivePersistence set to false as this will enable you to provide the connection strings to the DataAccessAdapters.

Hope this is helpful. Sorry we can't provide more of a concrete example - due to the way the support team is organised we simply don't have time to put together complete solutions like this - and Frans has his hands full with V3

Your best bet is simply to make a start on trying to build something, and then come back to us with anymore specifc questions.