Best way to insert entities with dependencies

Nov 25, 2009 at 3:44 PM
Edited Nov 25, 2009 at 3:50 PM

Hi,

In my project, I'm loading some flat-files (CSV) into a Retina-modelled database.  These files contain ID numbers which relate to key fields in other files.  

I'm successfully adding in the 'lookup' entities, but inserting the 'parent' entities is proving rather clunky.

For example, the 'Status.CSV' file might contain:

1,Confimed
2,Pending
3,In Progress 

... and the 'Jobs.CSV' file might contain:

1234,FirstJob,1
5678,SecondJob,2
9012,ThirdJob,1
3456,FourthJob,3

(where the columns are JobID, JobName, StatusID [which links to the first ID column in Status.CSV])

At the moment, when I'm creating the Job entities from the file's data, I'm having to do a context.Retrieve to get the relevant Status entity whose ID matches the third column in the Jobs file.  Obviously, in a less trivial case where a file has many ID numbers which would link to other entities, this becomes very difficult to manage.

Is there some way to do this easily via the facilities which Retina.NET provides?  Or am I best bulk-inserting this data into the db via another method, once Retina's created the tables using my entity definitions?

Thanks,
Chris 

 

 

 

Coordinator
Nov 28, 2009 at 11:40 PM

Sorry but I don't quite follow you....   and by the way, there are other methods to perform a bulk insert into an SQL Server database much faster than executing every insert (but none of them are supported by Retina.NET directly).

For the IDs issue, I don't see any problem. For example, if the entity has an IDENTITY PK the value is automatically populated in the Entity after the INSERT, so no need for a retrieve here. On the other hand, if the PKs aren't IDENTITIES (and that seems to be the case if you want to preserve the IDs read from the CSV file) you don't need either to read the ID value from the database, so I'm lost here.

Anu other information or detail to help me understand?

Thanks in advance,

Andres.