This project has moved. For the latest updates, please go here.
2

Closed

Bulk Insert Relational Data

description

I'm currently having to do some fairly complex workarounds to be able to bulk insert relational data.

Is there a way to get the new primary key for each inserted row?

Thanks,
Jeff Bowman
Fairbanks, Alaska
Closed Apr 24, 2016 at 7:24 AM by ErikEJ

comments

ErikEJ wrote Jul 24, 2015 at 7:58 AM

No, but you could use uniqueidentifier keys.

On the other hand, it could be a useful addition, but very unsure what to supply to the consumer.

Simply a list of longs?

ImPaul wrote Jul 24, 2015 at 10:49 AM

Is it possible to bulk insert relational data?
I tried below, it only saved the top layer of the class, and all the children classes are ignored.

This is my example. Where the company class consist a list of department, and each department consist a list of person.

ICollection<Company> => ICollection<Department> => ICollection<Person>

When I try
String connString = @"Data Source = |DataDirectory|\CompanyDatabase.sdf";
SqlCeBulkCopy bulkInsert = new SqlCeBulkCopy(connString);
bulkInsert.DestinationTableName = "Company";
bulkInsert.WriteToServer(CompanyClass);

It only save a list of company to the database. Department and Person are ignored.

Do you have a example on how to save multiple level of relational data to database?

wrote Jul 24, 2015 at 10:50 AM

ErikEJ wrote Jul 24, 2015 at 12:49 PM

ImPaul - no, complex types or object Graphs are not supported

InteXX wrote Jul 24, 2015 at 5:51 PM

I have an idea...

We could:
  1. Get the next identity values for the columns (assuming integers)
  2. Build the relationships' identity values in memory
  3. Set SqlCeBulkCopyOptions.KeepIdentity (handy option, thanks!)
  4. Perform the inserts
  5. Clean up by resetting AUTOINC_NEXT
See here for more on #5.

Thanks,
Jeff Bowman
Fairbanks, Alaska

ErikEJ wrote Aug 9, 2015 at 12:56 PM

@intexx can I close this?

InteXX wrote Aug 9, 2015 at 1:37 PM

@ErikEj: Sure, after you weigh in on my idea ;-)

ErikEJ wrote Aug 11, 2015 at 11:25 AM

@intexx Sounds like a viable solution, if you decide to not use GUIDs/uniqueidentifiers.

We/you could potentially add helper methods in this library.

One to get the identity values
 List<long> GetIdentityValues(SqlCeConnection connection, string tableName, int numberOfValuesToGet int)
and a method to Reset the seed (already implemented as part of the feature for disabling constraints):
 void ResetSeed(SqlCeConnection connection, string tableName)

InteXX wrote Aug 12, 2015 at 4:15 AM

List<long> GetIdentityValues()
Hm... I'm not sure I'm in favor of that approach.

I seems to assume that the insert action always captures the new identity values. Correct me please, but this sounds like a significant impact on performance.

Personally I'm not sure I'd be completely comfortable with accepting a plain List(Of Long) and assuming that they're all in a correct order for my data model.

Even though it's more work on the front end, I'd probably lean toward building out my identities before the insert. Just to play it safe, you know...

ErikEJ wrote Aug 12, 2015 at 8:58 AM

This will not impact perf, only give you a list of available Ids for the table, maybe the name should be:
 GetAvailableIdentityValues()

InteXX wrote Aug 12, 2015 at 9:40 AM

If the function call is to return anything the bulk insert action must always collect the identity values from the database engine, whether the function is to be called or not.

It's that process of collection, always performed before the optional function call, that I fear will impact the insert's performance.

Thanks,
Jeff Bowman
Fairbanks, Alaska

wrote Apr 24, 2016 at 7:24 AM