I have a VB.Net Console application that has 4 modules. Each module is used to populate a public dataset. When I go to populate the last dataset I loop through the previous dataset and do a new sql call for each record in the previous dataset. Bear with me:). I hit up to 97 records and run into one of two problems. The Time Out connection pooling problem or Server time out problem. I have read through countless threads and have tried everything so I leave it to the experts.
What I have tried
1. Setting the Connection lifetime, Connection Max Pool, and Time out
2. Closing and setting the connection with in the loop to nothing with in the loop (which doesn't seem to release connections to the pool)
3. Creating a slightly different connection to use in the last module.
I would like to know the best practices for creating data structures like this and how to avoid these connections problem. Would it be better to use a Stored Proc or create a relation between tables on the ADO.net side instead??
Thanks so much in advanced!!