Hi:

I have a Web app developed using database but name of the database will be changed based on the User Login. Only name is changed, but the schema and the structure of the table remain same.

How do I do this? Can I change the Generated Code to achieve the result I need.

I use Adapter with SQL Server 2005.

Thanks.

To change the catalogName at runtime, use the following overload of the DataAccessAdapter CTor

public DataAccessAdapter(string connectionString, bool keepConnectionOpen, CatalogNameUsage catalogNameUsageSetting, string catalogNameToUse)

Set the catalogNameUsageSetting to ForceName, and set the catalogNameToUse to the required catalog name.

Thanks Walaa.

I have written major part of application with the code like:

Dim da as New DataAccessAdapter

Without touching these kind of existing code, Can I change the generated code to access different databases in runtime?

This should work:

yourAdapter.CatalogNameUsageSetting = CatalogNameUsage.ForceName;
yourAdapter.CatalogNameToUse = "xxx";

Thanks Goose.

Can I do this inside the Generated Code of DataAccessAdapter and all other code can work without any changes?

Thanks again.

Yes you can do this if you want. But how would these properties be changed dynamically? Shouldn't they be set from your application code, according to which catalog/database is needed at a certain point of time?

Yes. Based on User Login, the Database name will be selected and should be applied in runtime.

If I have a way to apply this Database name in DataAccessAdapter class, that will be great.

Also, I would like to apply the Database Name to the LLBL's Datasource Control objects in both Live Persistence = True and False without touching the custom code, but changing the Generated code.

Any help to achieve this is greatly appreciated.

Can I do this inside the Generated Code of DataAccessAdapter and all other code can work without any changes?

Yes you can but take care of overwriting it when re-generating the code.

Modify the Default CTor of the DataAccessAdapter to call the InitClass(connectionString, keepConnectionOpen, catalogNameUsageSetting, catalogNameToUse, null, null); With the appropriate values instead of the default or empty values. Just like the overload I posted earlier. But then you should find a way to get the needed catalog name inside the default CTor.

Waala:

This worked really nice.

but i have a new problem.

I have a LLBLGen project with two databases included. One is "Public" and another one is "Private".

At the time of login, I am taking the correct database name based on the logged user and changing it at run time in DataAccessAdapter to replace the "Private" Database.

Dim localDatabase As String = Str(System.Web.HttpContext.Current.Session("GlobalAccountID"))
InitClass(ReadConnectionStringFromConfig(), False, CatalogNameUsage.ForceName, localDatabase, Nothing, Nothing)

Since the LLBLGen Project has Public database included, when accessing tables from "Public" database, I get error:

Invalid object name ' 1078.dbo.Application'

Where Application is belongs to the "Public" Database.

Where 1078 is the database for the logged user.

I hope you get the picture.

How do I handle this situation? Do I need create one LLBLGen project for "Private" and another one for "Public"?

Any help is appreciated.

Thanks.

Try this:

Add the following to the web.config file. Catalog overwrite fr the Private should be left empty, which will let the system use the catalog name specified in the connection string.

<configSections>
    <section name="sqlServerCatalogNameOverwrites" type="System.Configuration.NameValueFileSectionHandler, System, Version=1.0.5000.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" />
</configSections>

<sqlServerCatalogNameOverwrites>
    <add key="Public" value="Public" />
    <add key="Private" value="" />
</sqlServerCatalogNameOverwrites>

And then all you need to do is pass the specific connection string for this user, instead of the ReadConnectionStringFromConfig(), and leave the rest of the parameters passed as they are by default

eg.

InitClass(myConnectionString, false, CatalogNameUsage.Default, String.Empty, null, null);

Walaa:

This worked great.....

But... sorry, always there is a but...

After adding that section to the web.config, for some reason, the Session variables are not available inside DataAccessAdapter.

At login, I am assigning "DatabaseName" to a Session("CurrentDatabase"). This value is coming from a table from "Public" Database.

But...

If I assign the same value to a Application object, it is accessible inside DataAccessAdapter.

What am I missing? I will continue to hunt for the answer.

Meanwhile, if you have any idea, let me know.

Thanks.

You probably need to reference system.web.dll in your DBSpecific project, to be able to use System.Web.HttpContext.Current.Session

(edit) A good post: http://adventuresdotnet.blogspot.com/2007/10/when-you-have-classes-that-need-to-be.html

Hi:

All that worked out well...

But I am not able to retrieve the Session value inside DataAccessAdapter.

Here is the code:

Dim localDatabase As String = Trim(Str(System.Web.HttpContext.Current.Session("AccountNumber")))
Dim localConnectionString As String = "data source=\SQLEXPRESS;initial catalog= Private ;User ID=User;Password=password;persist security info=False;packet size=4096"
localConnectionString = localConnectionString.Replace("Private", localDatabase)
InitClass(localConnectionString, False, CatalogNameUsage.Default, String.Empty, Nothing, Nothing)

When I run with the above code, it gives an error:

The Database "0" cannot be open by the user "User".

But, If I change the Current.Session to Current.Application, it fetches the value.

What could be the issue?

Thanks.

Thank you for all your help.

It is working fine now.

Thanks again.