5000 new objects

Smirk posted on Sunday, November 12, 2006

I have to read in 5000 records from an Excel spreadsheet, turn them into business objects and save them to my new database.

If these objects are root objects, Save is called on each instance in turn, thereby generating 5000 object.Save() calls, with the corresponding 5000 connection.Open() calls...

If I use a BusinessListBase derived object and have each of my 5000 objects be a child of the list, my understanding is that each child's Save() method is still called, resulting in the same situation as above.

So my question is, have I missed one of the base classes opening the connection once, saving each item in turn and the closing the connection when done?

Thanks for any pointers...

Mark Haley

RockfordLhotka replied on Sunday, November 12, 2006

If you use a BLB collection to contain your objects, then your objects would be child objects, not root objects. In that case, your collection code's DataPortal_Update() could open a single connection, and pass that connection to each child object's internal/Friend Insert(), Update() or Delete() method.

Obviously you still need to be careful in terms of your database. Updating 5000 rows of data in a single transaction could cause escalation to a table lock, which could have broad negative effects on other users as well as this particular task. But that's a database issue, not really an object issue, and the problems/solutions vary from database to database.

Smirk replied on Sunday, November 12, 2006

Thanks, Rocky!

You know, I thought that I had read exactly what you described above, but I looked and looked in chapter 8 and couldn't find it.

Turns out I was looking at the Project class with it's Resourses collection and it was in the Resource class that you showed how to do connection sharing.

Thanks!

Mark Haley

Smirk replied on Sunday, November 12, 2006

By the way, this particular use is local on my development machine with me as the only user, so it's not going to affect any other users if the table is in use for a bit...

Mark

William replied on Sunday, November 12, 2006

This is just my thought from another view.

If you trust your data that you kept in Excel, then you don't necessarily need to create a full featured object hierarchy to just import the data. Because you trust your own data, then data validation can be kept to the minimum. Thus, as an alternate approach, you can import your data "more directly" into the database through the use of CSLA.CommandBase and ADO.NET DataSet object might be more efficient.

Regards,

William

Smirk replied on Sunday, November 12, 2006

William,

You are correct that this could be done more simply than with full-blown CSLA objects, but I have three reasons for doing it via objects:

1. I'm just using this as a "get my feet wet" excersise in CSLA

2. I actually do not trust the excel data - it is published by an external source (completely out of my influence) and needs some reformatting, trimming-down and enhancement.

3. Once the data is in the database, it will be accessed by different CSLA business objects but the validation that was used previously will apply in the new objects and is therefore reusable.

-- Although I do like the CommandBase idea and might do some thinking along that line to see what it turns up... Thanks

Mark

DW_CT replied on Monday, November 13, 2006

I'm just starting to get my feet wet in CSLA as well, so these may be dumb questions. Wouldn't the inherent ADO.Net connection pooling deal with this behind the scenes? So, while there may be 5000 connection.Open calls in the code, there may only be, say, 5 connections actually made? Of course, how you sequence the 5000 calls would probably play a big part in what actually happens (you wouldn't be able to reuse a pooled connection if all the calls were simultaneous).

RockfordLhotka replied on Monday, November 13, 2006

Technically that's true. However, retrieving a connection from the pool isn't totally free, and in a tight loop over 5000 objects that overhead can become apparent.

Additionally, if there's the desire for transactional protection, opening these connections would require the use of the DTC, which is at least 15% overhead all by itself.