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

connection-poolingsubsonicsubsonic3

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

Best Answer

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

Related Topic