subsonic 3.0.0.3 times out on multiple save operations. MSSQL2005 - Solved.

Question

Hi to everyone,

I'm reading a csv file and insert the information in a sql 2005 database.

After about 250 object.save operations, it times out. here the code and the exact error message. this is not the first version of the code but it always give the same time out.

This is not a big database, only 2 tables. Is there something I'm not doing ? Does it open and close a connection for every save operation. All input on that problem is welcome.

List<shipment> oLstShipments = new List<shipment>();
while (oReader.ReadNextRecord())
{
  int iIdShipment;
  if (int.TryParse(oReader[0], out iIdShipment))
  {
    shipment oShipment = new shipment();
    oShipment.idShipment = iIdShipment;
    oShipment.dateDelivered = oReader[1];
    oShipment.inventoryGroup = oReader[2];
    oShipment.companyId = oReader[3];
    oShipment.shipTo = oReader[4];
    oShipment.carrier = oReader[5];
    oShipment.accountOwner = oReader[6];
    oShipment.accountNumber = oReader[7];
    oShipment.trackingNumber = oReader[8];
    oLstShipments.Add(oShipment);
  }
}
oReader.Dispose();
oSR.Dispose();

foreach (shipment oShip in oLstShipments)
{
  oShip.Save();
}

the error :

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 SubSonic.DataProviders.DbDataProvider.CreateConnection(String connectionString)

       at SubSonic.DataProviders.DbDataProvider.CreateConnection()

       at SubSonic.DataProviders.AutomaticConnectionScope..ctor(IDataProvider provider)

       at SubSonic.DataProviders.DbDataProvider.ExecuteReader(QueryCommand qry)

       at SubSonic.Repository.SubSonicRepository`1.Add(T item, IDataProvider provider)

       at invoiceRetriever.Data.shipment.Add(IDataProvider provider) in D:\programmation\ceb\invoiceRetriever\invoiceRetriever\ActiveRecord.cs:line 516

       at invoiceRetriever.Data.shipment.Save(IDataProvider provider) in D:\programmation\ceb\invoiceRetriever\invoiceRetriever\ActiveRecord.cs:line 531

       at invoiceRetriever.Data.shipment.Save() in D:\programmation\ceb\invoiceRetriever\invoiceRetriever\ActiveRecord.cs:line 525

       at invoiceRetriever.frmHaasFiles.cmdProcess_Click(Object sender, EventArgs e) in D:\programmation\ceb\invoiceRetriever\invoiceRetriever\frmFiles.cs:line 59

Test #1

for (int i = 1; i < 200; i++)
                    {
                        try
                        {
                            shipment oShipment = new shipment();
                            oShipment.idShipment = i;
                            oShipment.dateDelivered = "10/10/2009";
                            oShipment.inventoryGroup = "123";
                            oShipment.companyId = "1";
                            oShipment.shipTo = "shipToTest";
                            oShipment.carrier = "carrierTest";
                            oShipment.accountOwner = "me";
                            oShipment.accountNumber = "123456";
                            oShipment.trackingNumber = "track001";
                            oShipment.Save();
                        }
                        catch (Exception ex)
                        {
                            MessageBox.Show("failed at #: " + i + Environment.NewLine + ex.ToString());
                            break;
                        }

                    }

Exception raised:

failed at #: 267

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 SubSonic.DataProviders.DbDataProvider.CreateConnection(String connectionString)

   at SubSonic.DataProviders.DbDataProvider.CreateConnection()

   at SubSonic.DataProviders.AutomaticConnectionScope..ctor(IDataProvider provider)

   at SubSonic.DataProviders.DbDataProvider.ExecuteReader(QueryCommand qry)

   at SubSonic.Repository.SubSonicRepository`1.Add(T item, IDataProvider provider)

   at invoiceRetriever.Data.shipment.Add(IDataProvider provider) in D:\programmation\ceb\invoiceRetriever\invoiceRetriever\ActiveRecord.cs:line 516

   at invoiceRetriever.Data.shipment.Save(IDataProvider provider) in D:\programmation\ceb\invoiceRetriever\invoiceRetriever\ActiveRecord.cs:line 531

   at invoiceRetriever.Data.shipment.Save() in D:\programmation\ceb\invoiceRetriever\invoiceRetriever\ActiveRecord.cs:line 525

   at invoiceRetriever.frmHaasFiles.cmdProcess_Click(Object sender, EventArgs e) in D:\programmation\ceb\invoiceRetriever\invoiceRetriever\frmHaasFiles.cs:line 50

If I try to detach the db, it's gonna say 101 active connections (1 for the management studio and the rest the code.)

If I try with the list with the repo like I did previously I get this exception : System.Data.SqlClient.SqlException: The incoming tabular data stream (TDS) remote procedure call (RPC) protocol stream is incorrect. Too many parameters were provided in this RPC request. The maximum is 2100.

I'm pretty sure I do something wrong, I can't be the only one that wants to insert so many items in the db.

EDIT 12/13/2009 09:44:00 :

Here is the script to create the shipment table.

/****** Object:  Table [dbo].[shipment]    Script Date: 12/11/2009 14:33:23 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[shipment](
    [idShipment] [int] NOT NULL,
    [dateDelivered] [varchar](255) NULL,
    [inventoryGroup] [varchar](255) NULL,
    [companyId] [varchar](255) NULL,
    [shipTo] [varchar](255) NULL,
    [carrier] [varchar](255) NULL,
    [accountOwner] [varchar](255) NULL,
    [accountNumber] [varchar](255) NULL,
    [trackingNumber] [varchar](255) NULL,
    [cebnowaybill] [varchar](50) NULL,
 CONSTRAINT [PK_shipment] PRIMARY KEY CLUSTERED 
(
    [idShipment] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF

Answer 1

Hi Sylvain - a timeout is caused by the database not having an available connection and my guess is going to be that it's from a reader being left open somewhere.

First - why ReadNextRecord()? Did you mean to use Read()? Also you can pass the reader into Load() and it will load up the object for you.

Finally - I would suggest wrapping this in a using statement to make sure the reader and everything is closed off even if there's an error.

EDIT: In reading your responses below - do you have a primary key defined for your table?

Answer 2

Sylvain rather than tell you how to fix your problem I am going to tell you what you should do instead. So instead of worrying how to save each individual Item without having the connection get opened over 100 times you should do one Batch operation that saves all the records at once.

Below is an Example for the SimpleRepo but this can be done with Active Record as well

var repo=new SimpleRepository();
repo.AddMany(oLstShipments);

Answer 3

there is a bug in the published version 3.0.0.3 (july) but was not there in earlier version. it's a rdr not wrapped with a using or followed by rdr.close . it' gonna have the effect I mentionned. Connections being opened but not closed... meaning pool of connections becoming full.

it has been corrected since as discussed with Rob in a many emails onversation. You have to go in source tab on github to find the correction.

Thanks to you all that helped. Special thanks to Adam (for not giving up after so many responses) and also to Rob for answering my emails fast.

Subsonic is great and it was worth the time spent finding the answer to that problem.