Self-service connection string...

Posts   
 
    
pilotboba
User
Posts: 434
Joined: 05-Aug-2005
# Posted on: 31-Jan-2006 20:27:40   

Any chance of adding a thread safe way to specify a connection string in the self-service?

If not, any work arounds for this? Self-service would be adequate for our usess if it weren't for this issue?

BOb

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39590
Joined: 17-Aug-2003
# Posted on: 31-Jan-2006 21:24:24   

connection strings are global, because there's no central object which controls your current session/connection with the db and all the actions. So it's not clear to me why you want to set the connection string often as it affects the whole application.

Frans Bouma | Lead developer LLBLGen Pro
pilotboba
User
Posts: 434
Joined: 05-Aug-2005
# Posted on: 31-Jan-2006 21:29:50   

Otis wrote:

connection strings are global, because there's no central object which controls your current session/connection with the db and all the actions. So it's not clear to me why you want to set the connection string often as it affects the whole application.

Because it is a web application and each user could be connecting to a different database.

For example, user1 is using the Production database, User2 is using the Training database, user3 is using the Testing database.

We store which db a user has selected in his current session/profile and use this connection string for all of that users data access. One app / multiple connection strings.

BOb

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39590
Joined: 17-Aug-2003
# Posted on: 31-Jan-2006 21:44:56   

You then should use adapter, selfservicing isn't suitable for that situation. To 'serve itself' it has to be able to do things without using some sort of adapter/session/context object. simple_smile

Frans Bouma | Lead developer LLBLGen Pro
pilotboba
User
Posts: 434
Joined: 05-Aug-2005
# Posted on: 31-Jan-2006 22:38:06   

Otis wrote:

You then should use adapter, selfservicing isn't suitable for that situation. To 'serve itself' it has to be able to do things without using some sort of adapter/session/context object. simple_smile

Sure, but currently you have the utility class that allows you to set the connection string programatically. I assume you are storing it somewhere global like in the application object?

I would like to be able to set it in perhaps the BeginRequest event of the application object and you could store it either in HttpContext or in the Thread or even session? This would allow me to do what I do.

It still is "self-serving" it is just getting a connection string from a thread / session specific place rather than an app global location like config or such?

Or you could use the provider model pattern (a la membership, profile in 2.0) to get the connection string, and I could create a provider that would return the connection string from the users profile. Just a thought.

BOb

gabrielk avatar
gabrielk
User
Posts: 231
Joined: 01-Feb-2005
# Posted on: 01-Feb-2006 11:22:55   

This is a very hot issue for me to.. I set it in the Global.asax on the event the thread starts.

I've tested it lightly now with trying to refreshing in 2 different browsers on different db's. But I would like to know if it's possible that the global setting can be changed between 2 threads... And how does IIS work with request, parallel or serial? That would make stuff clearer..

Thanks, Gab

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39590
Joined: 17-Aug-2003
# Posted on: 01-Feb-2006 11:29:47   

pilotboba wrote:

Otis wrote:

You then should use adapter, selfservicing isn't suitable for that situation. To 'serve itself' it has to be able to do things without using some sort of adapter/session/context object. simple_smile

Sure, but currently you have the utility class that allows you to set the connection string programatically. I assume you are storing it somewhere global like in the application object?

No in a static variable in DbUtils: DbUtils.ActualConnectionString.

As DbUtils contains solely static methods, it's a bit complicated to transform this to what you want, however I do think it's possible.

I would like to be able to set it in perhaps the BeginRequest event of the application object and you could store it either in HttpContext or in the Thread or even session? This would allow me to do what I do.

It still is "self-serving" it is just getting a connection string from a thread / session specific place rather than an app global location like config or such?

Or you could use the provider model pattern (a la membership, profile in 2.0) to get the connection string, and I could create a provider that would return the connection string from the users profile. Just a thought.

All code of selfservicing uses instances, except for DbUtils, as they're a set of utility methods, like produce a connection, produce a dataadapter etc.

The dbUtils template isnt big so you should be able to alter it pretty easily and use your copy in a custom template set.

In HelperClasses\DbUtils.cs you'll find a method called CreateConnection(). In there, it checks if the static variable ActualConnectionString is set, if not, it reads from the config file and sets the variable with that value.

You should do the following: 1) add a new private static member variable which will be the lock object for the critical section:


private static object _semaphore = new object();

2) alter the CreateConnection() in this way or similar:


/// <summary>
/// Creates a new closed SqlConnection object based on the connection string read from the *.config file of the appdomain.
/// The connection string is stored in a key with the name defined in the constant connectionKeyString, mentioned above.
/// </summary>
/// <returns>A ready to use, closed, sqlconnection object</returns>
public static SqlConnection CreateConnection()
{
    lock(_semaphore)
    {
        // read string from HttpContext.Current.Session
        string connectionStringToUse = HttpContext.Current.Session["userConnectionString"];
        // create the actual connection object:
        return CreateConnection(connectionStringToUse);
    }
}

As this change is very small, you can first try it in a generated code project you already have and see how it behaves. Due to the lock, it might be that that routine will be a bottleneck under heavy load, though it should be relatively minor as creating a connection from the pool is very fast.

Keep in mind that connection pooling will be different: if every user has his own connection string, the connection pool has to be bigger than expected as pooling is done on connection string.

I wonder if it's possible to use windows authentication and impersonation, as the users are known in the db anyway. Either way, IF possible, try to implement a role based security system yourself which authenticates the users against a table row in the DB, places the user in a role and using that set of credentials offers things to the user. This is much more scalable, however requires some more work, and of course a 'different' application to manage users and passwords, which can be a problem if the customer wants everything integrated in Active Directory (for example), in which case you can fall back on windows authentication and impersonation (which requires 1 connection string and thus not a problem)

To recap: IF possible avoid multiple connection strings with many users, it's not performing because you can't use connection pooling or you need a large pool. This is an ADO.NET thing. Also, if you need dynamic database/user selection no matter what, consider adapter, as that's designed for this, not selfservicing. In a winforms app, running on a desktop: no problem, as you have a single user, so you can use selfservicing there, but in a webapp, IF you need custom connection strings per user, use the trick above to modify selfservicing to handle this for you, though if possible, use different ways to manage multi-user applications, as multiple connection strings is mitigating the usage of a connection pool.

Frans Bouma | Lead developer LLBLGen Pro
gabrielk avatar
gabrielk
User
Posts: 231
Joined: 01-Feb-2005
# Posted on: 01-Feb-2006 13:55:35   

Thanks a lot for this piece of information!

How hard/involved would a transfer from SelfService to Adaptor be, if it is possible. And are there any starting points?

Thanks Gab

Walaa avatar
Walaa
Support Team
Posts: 14946
Joined: 21-Aug-2005
# Posted on: 01-Feb-2006 16:04:49   

How hard/involved would a transfer from SelfService to Adaptor be, if it is possible. And are there any starting points?

It shouldn't be hard, as most of the things you are used to from the Selfservicing, just some concepts and the Use of the DataAccessAdapter calss.

Just check the LLBLGen Pro documentation, and start from here "Concepts - Template sets"

JimHugh
User
Posts: 191
Joined: 16-Nov-2005
# Posted on: 01-Feb-2006 17:09:56   

The biggest hurdle that I have found when using the Adapter model is that "Adapter doesn't support load-on-demand, also known as 'lazy loading' like SelfServicing does".(from the Prefetch Paths section of the manual).

It's not hard to do since the available Prefetch paths are all defined when the code is generated, it is just a signifigant code change.

pilotboba
User
Posts: 434
Joined: 05-Aug-2005
# Posted on: 01-Feb-2006 19:14:32   

Otis wrote:

To recap: IF possible avoid multiple connection strings with many users, it's not performing because you can't use connection pooling or you need a large pool. This is an ADO.NET thing. Also, if you need dynamic database/user selection no matter what, consider adapter, as that's designed for this, not selfservicing. In a winforms app, running on a desktop: no problem, as you have a single user, so you can use selfservicing there, but in a webapp, IF you need custom connection strings per user, use the trick above to modify selfservicing to handle this for you, though if possible, use different ways to manage multi-user applications, as multiple connection strings is mitigating the usage of a connection pool.

We don't actually have 1 connection string per user but per db. So, for each DB we have a single connection string (esentially only initial catalog is different). So, many users working with the same database use the same connection string.

So, connection pooling still works per db. But, I guess you are saying with adapter we could use a single connection string for ALL dbs. I don't see a major advantage to that. Also, it would mean changing the database setup portion of our Config app.

I know I can use adapter... simple_smile

We can't use impersonation cause that we require each user has a windows domain account. It's for that reason that we use forms authentication.

I will look at your suggestion of modifying the utiltiy method.

BOb

jds4peace
User
Posts: 12
Joined: 16-Nov-2006
# Posted on: 13-Dec-2006 07:39:11   

What do you think of this...

namespace PSLlblGen.HelperClasses { /// <summary> /// General utility methods used for SqlServer usage by the framework. /// </summary> public partial class DbUtils { #region Public Static Members [ThreadStatic] public static string ActualConnectionString = string.Empty;

I haven't tested it yet, but if every web request is given its own thread and this attribute does what intellisense says it does...

~JDS

Walaa avatar
Walaa
Support Team
Posts: 14946
Joined: 21-Aug-2005
# Posted on: 13-Dec-2006 08:09:17   

Please don't Hijack older threads as stated here: http://www.llblgen.com/TinyForum/Messages.aspx?ThreadID=7722

I haven't tested it yet, but if every web request is given its own thread and this attribute does what intellisense says it does...

I don't believe so, a web request use whatever thread is available from the threads pool. Please check this Article

Mastermind
User
Posts: 9
Joined: 14-Jul-2011
# Posted on: 26-Jan-2012 15:34:27   

Otis wrote:

Otis wrote:

You then should use adapter, selfservicing isn't suitable for that situation. To 'serve itself' it has to be able to do things without using some sort of adapter/session/context object. simple_smile

You should do the following: 1) add a new private static member variable which will be the lock object for the critical section:


private static object _semaphore = new object();

2) alter the CreateConnection() in this way or similar:


/// <summary>
/// Creates a new closed SqlConnection object based on the connection string read from the *.config file of the appdomain.
/// The connection string is stored in a key with the name defined in the constant connectionKeyString, mentioned above.
/// </summary>
/// <returns>A ready to use, closed, sqlconnection object</returns>
public static SqlConnection CreateConnection()
{
    lock(_semaphore)
    {
        // read string from HttpContext.Current.Session
        string connectionStringToUse = HttpContext.Current.Session["userConnectionString"];
        // create the actual connection object:
        return CreateConnection(connectionStringToUse);
    }
}

We're using LLBLGen 3. There's no DBUtils class, and the CommonDaoBase class does not contain the CreateConnection methode. Where should I place the semaphore in this case?

Walaa avatar
Walaa
Support Team
Posts: 14946
Joined: 21-Aug-2005
# Posted on: 27-Jan-2012 09:52:36   

That's a breaking change starting from v.3.0, and here is what's stated in the docs:

SelfServicing: DbUtils has been removed. The static settings and methods are moved to the DaoBase class. The Db specific methods for sqlserver, like SetArithAbortFlag and SetSqlserverCompatibilityLevel are moved to the new class CommonDaoBase, which is located in the DaoClasses namespace. As DbUtils' public properties are not used frequently in an application, breaking code is minimal: rename the call / reference to the DbUtils member to a call to the CommonDaoBase class method / property instead. The DbUtils.CreateTransaction(3) method has been removed (there's no replacement in CommonDaoBase). The reason is that 'name' is no longer used. Use the overload which don't accept a name. This method also has been moved to DaoBase / IDao and is now no longer static. It's not likely you use this method in practice.

Still using the Adapter template set is recommended in these cases.

Next time please don't hijack older threads, instead create new ones and link to the older ones if needed.