SubSonic 3.0.0.3 t4 template generation on lage database runs out of connections

Question

When running the templates against a database with 1400+ tables I get the following error. The server shows hundreds of connections. Does anyone know if this is a problem with template generation in general, or with these templates specifically. Other, smaller DBs generate ok for me.

Running transformation: System.InvalidOperationException: Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool. This may have occurred because all pooled connections were in use and max pool size was reached.

at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection) at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory) at System.Data.SqlClient.SqlConnection.Open() at Microsoft.VisualStudio.TextTemplating8D8967BD3E8719BDA6DD9945992440F1.GeneratedTextTransformation.GetCommand(String sql) in c:\POS\POS.Win\Templates\SQLServer.ttinclude:line 13 at Microsoft.VisualStudio.TextTemplating8D8967BD3E8719BDA6DD9945992440F1.GeneratedTextTransformation.LoadFKTables(String tableName) in c:\POS\POS.Win\Templates\SQLServer.ttinclude:line 179 at Microsoft.VisualStudio.TextTemplating8D8967BD3E8719BDA6DD9945992440F1.GeneratedTextTransformation.LoadTables() in c:\POS\POS.Win\Templates\SQLServer.ttinclude:line 131 at Microsoft.VisualStudio.TextTemplating8D8967BD3E8719BDA6DD9945992440F1.GeneratedTextTransformation.TransformText() in c:\POS\POS.Win\Templates\ActiveRecord.tt:line 21

Answer 1

Well, I might suggest that running T4 on 1400 tables is probably not the best idea. Given that - you can spelunk the T4 code (in SQLServer.tt) to see how we load the tables (in LoadTables) and doctor the connections as required.

1400 classes generated - would be fun to guess the file size of the final output...

Answer 2

I had this same problem with a very large database (lots of tables, views, and SPs). I added the following to my web.config connection string for Sql Server 2005 - Connect Timeout=60;

This seemed to do the trick.

You can also dig throught the .tt and .ttinclude files and set the cmd.CommandTimeout too.

Also, here is the const to set for specific tables:

const string TABLE_SQL=@"SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE='BASE TABLE' and table_name = 'myTableA' or table_name = 'myTableB'

Answer 3

I had this exact problem with a 250 table database. Poking around the SQLServer.ttinclude, I found this:

var cmd=GetCommand(sql);   
cmd.Parameters.AddWithValue("@tableName",table);   
var result=cmd.ExecuteScalar();  
cmd.Dispose();  
if(result!=null)  
    pk=result.ToString();

Changed it to this:

using (var cmd=GetCommand(sql))  
{  
        cmd.Parameters.AddWithValue("@tableName",table);  
        int x = 0;  
 if (table == "tbl_Address")  
  x++;  
 var result=cmd.ExecuteScalar();  

 if(result!=null)  
  pk=result.ToString();     

 cmd.Connection.Close();      
}

The connection was not getting closed and you run out of connections in the pool after 100. Any DB with more than 100 tables would hit this issue. It's pretty awesome that I could fix this without touching SubSonic.Core.

Now if I can just spend the time to figure out Git, I would post this fix back to the subsonic project. :-D