DAAB, multiple databases and connection pooling

Topics: Data Access Application Block
Dec 11, 2010 at 9:49 PM

I have a web application that uses SQL Server as the back end.  I would like to use the DAAB for data access.  The only wrinkle is that every customer in the application has their own physical database in the SQL Server instance.  I know it is not an optimal setup but it is something I have inherited and unfortunately can not really be changed.  My first thought is that it is going to be difficult to take advantage of connection pooling because every customer has their own database.  I asked this question on a different site and someone suggested that all of my connections could go to the default database and then I could just prefix every query with the correct USE dbName statement in SQL.  If I am using stored procedures then he suggested that I could do the following with my connection object:  


So my question here is this:  is there a way using the DAAB that I could design my application so that every customer has a different physical database and yet still take advantage of connection pooling?



Dec 13, 2010 at 5:38 AM

Please let me know if I am missing anything regarding your scenario. Basically, having those customer database in place should not be a problem as long as you're able to cater them either having a connection string configured for each of the database or configure an existing connection string to use the necessary database your app should use.

As for connection pooling, it is enabled by default in DAAB. DAAB is built on top of the ADO.NET, therefore the same way on configuring connection pooling in ADO.NET should also work with DAAB.

Gino Terrado
Global Technologies and Solutions
Avanade, Inc.

Dec 13, 2010 at 11:57 AM

Maybe I didn't explain it very well.  What I am concerned about is that if every customer has their own database and their own connection string then I can't use connection pooling can I?  Connections can only be shared/pooled if they are a connection to the same database - right?  If I have 1000 customers and 1000 databases and 1000 different connection strings then SQL Server won't share any of those connections because they are all to different databases - right?


Dec 14, 2010 at 2:09 AM

Yes, but connnection pooling will occur for every user.   I don't see any performance problem since the database isn't centralized since each user will only access a local database.


Sarah Urmeneta
Global Technologies and Solutions
Avanade, Inc.

Dec 14, 2010 at 11:45 AM

I still don't feel like anyone is understanding what I am saying.  There are no local databases.  This is a web application.  There is one central SQL Server instance.  If there are 1000 customers using this application then there will be 1000 separate databases.  For example, Customer1 will have a database called Customer1DB.  Customer2 will have a database called Customer2DB, etc.  Each customer will probably have a few different users.  Here are some potential UserIDs:  Customer1UserA, Customer1UserB, Customer1UserC, Customer2UserA, Customer2UserB, etc.  I understand that the DAAB enables connection pooling by default.  But my point here is that I believe that connection pooling only shares connections that use the same *connection string*.  Right?  So in my situation that I described here Customer1UserA, Customer1UserB and Customer1UserC will share connections in their connection pool.  But Customer2UserA and Customer2UserB will share their connections in a *separate* pool - right?  But Customer1UserA and Customer2UserA will never share connections because they are in separate pools - right?  So if that is correct then I run the risk of having 1000 separate connection pools right?  I can't imagine that will work very well or scale very well.

I was wondering if there was some way to have all users use the same connection string so that they all connect to the same database (and use the same connection pool).  Then on any one particular call I could use code to change the database to the customer's actual database.  This way I could have only 1 connection pool - not 1000 different connection pools.  Does that make sense?  Am I describing this correctly?


Dec 15, 2010 at 7:26 AM

Hi Corey,

Sorry for the confusion.  Since DAAB is built on top of ADO.NET, DAAB doesn't do connection pooling itself, it relies on the underlying ADO.NET provider to do it. So taking from the Connection Pooling for ADO.NET documentation, same connection can be pooled as long as the configuration is the same. Quoted from the documentation - "Only connections with the same configuration can be pooled. ADO.NET keeps several pools at the same time, one for each configuration. Connections are separated into pools by connection string, and by Windows identity when integrated security is used.". For more information about connection pooling you can refer to - http://msdn.microsoft.com/en-us/library/8xx3tyca.aspx. Hope this answers your questions regarding sharing connection.

As for the problem of having multiple customer databases, I believe your initial approach to connect to a default database and use the prefix "USE DBNAME" will work and also the only approach mentioned in the documentation. Again quoting from the documentation the approach stated to avoid the problem - "Instead of connecting to a separate database for each user or group, connect to the same database on the server and then execute the Transact-SQL USE statement to change to the desired database. The following code fragment demonstrates creating an initial connection to the master database and then switching to the desired database specified in the databaseName string variable."

You can check Pool Fragmentation section in the documentation for more details regarding this. Hope this clears all things up :o)

Gino Terrado
Global Technologies and Solutions
Avanade, Inc.

Dec 15, 2010 at 1:48 PM


Thanks for the follow up.  Ok - so it sounds like I could use a single connection string for the application.  Then for each individual customer I could prefix my SQL calls with USE DBNAME.  Now my question is this:  with the DAAB is there any way that I could use stored procedures and also switch the database?  Here is some example code below:


//Create an instance of the default database for the application
var myDB = EnterpriseLibraryContainer.Current.GetInstance<Database>("myDefaultDB");

//Switch to the customer's database - Does this exist?  Is this possible?

//Call the stored procedure
using (IDataReader reader = myDB.ExecuteReader("MyStoredProcedure"))
  ...do stuff...

If this is not possible then it seems like I must use inline SQL statements instead of stored procedures.



Dec 15, 2010 at 5:55 PM

There's no way that I know of to switch databases without the "use" statement. I don't know if you could even do that in a sproc - I suspect (but haven't tested) that it wouldn't be allowed.

Have you actually measured the impact of the number of connections and pooling, or lack thereof? Do you know if you actually have a performance problem here? One big advantage of your current design is that, as you grow, you can move different customers to separate physical database servers, thus spreading the load.

So, have you measured your perf?



Dec 15, 2010 at 6:17 PM

No I have not measured performance at all yet.  I am in the design phases of building this application.  I am re-building an existing application that is 10 years old.  However I am sort of stuck with the database configuration.  The old application had horrible performance and I was suspecting that this was due in large part to each customer having their own connection string so there was very little if any connection pooling.  So I was trying to find out if there was a way that I could use the DAAB and still get maximum connection pooling for the newly designed application.  It sounds to me like I have the following choices:

1. Use DAAB and assume that this won't be a problem.  Every customer will have their own database and it might be fine.

2. Build a quick project that uses multiple databases and run some load tests to see if there really are performance issues.

3. Don't use the DAAB.  Instead hand code my database access.  That way I have access to the connection object and I can call myConn.ChangeDatabase("OtherDBName")

4. Don't use stored procedures at all.  Make my entire application use inline SQL Statements.  That way I can call "USE OTHERDBNAME" before every call.

Are there any other options that anyone can think of?

Thanks so much for your input - it really helps me think through this.


Apr 20, 2011 at 11:03 PM

This post dates a bit but I'm hitting the same issue as you "theburnetts". We have 1500 client databases and connection pool fragmentation is a performance issue for us.

A few months back I slapped together a basic ADO.Net solution that enabled us to go around the problem executing the "USE db" statement as suggested by MSDN but we are now trying to rebuild my DAL w/ DAAB. Therefore I was wondering if you were able to figure this out?

After all my reading so far it seems to me that since DAAB closes the connection after each Execute** command of the Database class, there is not a way to run 2 commands on the same connection.

I actually tried to do my "Use clientDb" and then execute my stored proc to only get an error message that tells me the stored proc does not exist in my database. Which would make sense since apparently the 2nd command is ran on a new connection instance that doesn't know about the "Use clientDb" command that was executed right before on the same Database object.

Anyone can please tell me I'm wrong and there is a way to go about connection pool fragmentation using DAAB 4.1? Otherwise I'm gonna have to go back to the bare naked ADO.Net solution that feels like using stone and wooden sticks at the computer age.

Thank you.

Apr 21, 2011 at 12:25 PM

Actually you don't need to use two separate Execute commands.  You can do it all in one Execute command.  Just make sure that your SQL looks like this:


You can separate different statements using a semi-colon.

Hope this helps,


Apr 21, 2011 at 4:40 PM

Hi Corey, thanks for the quick reply, but since we're using stored procedure i was wondering... does that mean we have to do something like:

"Use myDB; Exec mySP param1 param2 ..." ???

Apr 21, 2011 at 4:54 PM

Hmmm...I'm not sure.  You can try it.  I was thinking that for some reason that wouldn't work.  I am thinking that there might be permissions issues.  My guess is that SQL Server wouldn't let you do that because of permissions.  But give it a try and see if it works.


Apr 21, 2011 at 5:29 PM

That's cool, I was thinking the same w/ the permission issue on the server side. Anyway I was working late last night and i figured out a way relying on my old code to do the double command (use db + exec stored proc), but using some parts of the application block code for passing arguments by order convention instead of specifying the column name w/ each one. 

It's a bit hacky but obviously the DAAB wasn't made to solve connection pool fragmentation issues when using Stored Procedures.

Thanks for your replies Corey.

Jul 18, 2011 at 3:17 PM

You should be able to prefix your SP name with the db name.  E.g. assume you have a stored procedure dbo.MyProc in databases ClientDBA and ClientDBBL: EXEC ClientDBA.dbo.MyProc vs ClientDBB.dbo.MyProc.

So you could cache some user-based database prefix, and just append that prefix everywhere you are calling your stored procedures.

With that said, I had a similar configuration challenge.  In my case, the reality was that we had a few dozen clients, with dozens to hundreds of users per client.  If you have a similar breakdown, consider using a dedicated connection string per client.  True, if you have 1000 clients, you will have 1000 "pools", but do you really have 1000 client dbs?  Or, say, 15?  15 pools with a few hundred users per pool is probably manageable, and offers some advantages when profiling (e.g. easily trace connections for a single client, using SQL's Resource Governor to ensure 1 client does not crush your SQL performance for other clients, etc.).