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

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.

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

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.

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.

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

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

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.

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.

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

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"

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.

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

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

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

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

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.

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?

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.