Home
Help
Register
Log in

Search

 
   Active Threads  

You are here: Home > LLBLGen Pro > LLBLGen Pro Runtime Framework> Dynamic Change of Database
 

Pages: 1
LLBLGen Pro Runtime Framework
Dynamic Change of Database
Page:1/1 

  Print all messages in this thread  
Poster Message
pandu
User



Location:

Joined on:
18-May-2006 07:20:06
Posted:
86 posts
# Posted on: 03-Dec-2007 06:44:41.  
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.
  Top
Walaa
Support Team



Location:

Joined on:
21-Aug-2005 16:03:48
Posted:
14549 posts
# Posted on: 03-Dec-2007 08:13:25.  
To change the catalogName at runtime, use the following overload of the DataAccessAdapter CTor
Code:
public DataAccessAdapter(string connectionString, bool keepConnectionOpen, CatalogNameUsage catalogNameUsageSetting, string catalogNameToUse)

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


  Top
pandu
User



Location:

Joined on:
18-May-2006 07:20:06
Posted:
86 posts
# Posted on: 03-Dec-2007 11:27:47.  
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?

  Top
goose
User



Location:
Central America
Joined on:
06-Aug-2007 18:21:05
Posted:
388 posts
# Posted on: 03-Dec-2007 16:58:03.  
This should work:
Code:
yourAdapter.CatalogNameUsageSetting = CatalogNameUsage.ForceName;
yourAdapter.CatalogNameToUse = "xxx";


gansodesoya  Top
pandu
User



Location:

Joined on:
18-May-2006 07:20:06
Posted:
86 posts
# Posted on: 04-Dec-2007 07:13:23.  
Thanks Goose.

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

Thanks again.
  Top
Walaa
Support Team



Location:

Joined on:
21-Aug-2005 16:03:48
Posted:
14549 posts
# Posted on: 04-Dec-2007 08:33:42.  
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?


  Top
pandu
User



Location:

Joined on:
18-May-2006 07:20:06
Posted:
86 posts
# Posted on: 06-Dec-2007 15:54:21.  
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.

  Top
Walaa
Support Team



Location:

Joined on:
21-Aug-2005 16:03:48
Posted:
14549 posts
# Posted on: 07-Dec-2007 10:52:43.  
Quote:
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.


  Top
pandu
User



Location:

Joined on:
18-May-2006 07:20:06
Posted:
86 posts
# Posted on: 03-Jun-2008 08:32:29.  
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.

Code:

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. Regular Smiley

Thanks.
  Top
Walaa
Support Team



Location:

Joined on:
21-Aug-2005 16:03:48
Posted:
14549 posts
# Posted on: 03-Jun-2008 09:01:18.  
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.

Code:
<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);


  Top
pandu
User



Location:

Joined on:
18-May-2006 07:20:06
Posted:
86 posts
# Posted on: 03-Jun-2008 16:45:31.  
Walaa:

This worked great.....

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

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.

  Top
Walaa
Support Team



Location:

Joined on:
21-Aug-2005 16:03:48
Posted:
14549 posts
# Posted on: 04-Jun-2008 10:55:05.  
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


  Top
pandu
User



Location:

Joined on:
18-May-2006 07:20:06
Posted:
86 posts
# Posted on: 16-Jun-2008 13:14:25.  
Hi:

All that worked out well...

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

Here is the code:

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.
  Top
pandu
User



Location:

Joined on:
18-May-2006 07:20:06
Posted:
86 posts
# Posted on: 16-Jun-2008 15:25:03.  
Thank you for all your help.

It is working fine now.

Thanks again.


  Top
Pages: 1  


Powered by HnD ©2002-2007 Solutions Design
HnD uses LLBLGen Pro

Version: 2.1.12172008 Final.