Bulk insert/update with autogeneration ids

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for Oracle
Post Reply
Partizan
Posts: 36
Joined: Fri 13 Nov 2009 10:18

Bulk insert/update with autogeneration ids

Post by Partizan » Mon 23 Sep 2013 14:07

Hi guys!

1. Is there a way to perform bulk insert/update without switching StoreGenerationPattern from Identity to None?

2. If answer to (1) is no, can we retrieve metadata about table/column names corresponding to entities and about constraints defined in the model to build SQL query manually with Array Binding usage? As I understand Array Binding would help there?

MariiaI
Devart Team
Posts: 1472
Joined: Mon 13 Feb 2012 08:17

Re: Bulk insert/update with autogeneration ids

Post by MariiaI » Wed 25 Sep 2013 13:33

1. Is there a way to perform bulk insert/update without switching StoreGenerationPattern from Identity to None?
We are investigating the possibility to implement this feature, but can't tell any timeframe at the moment. We will inform you when any results are available.
As I understand Array Binding would help there?
Yes, you could use Array binding -> http://www.devart.com/dotconnect/oracle ... yBind.html.
You could also try using the OracleLoader class -> http://www.devart.com/dotconnect/oracle ... oader.html.

JIC: we recommend you to use the OracleLoader class due to the fact, that it was improved since dotConnect for Oracle 7.9.322. For more information please refer to http://forums.devart.com/viewtopic.php?f=1&t=27982.
can we retrieve metadata about table/column names corresponding to entities and about constraints defined in the model to build SQL
It is possible to do by creating a custom template, which will generate the code according to the existing model (if you are working with Entity Developer, create template for it via the Model Explorer-> Templates-> New Blank Template; if it is EDM Wizard/Designer, create a standard T4-template).

If your model objects contain all columns of the corresponding database table and there are no inheritances/complex types, the generated code could be like this:

Code: Select all

public static void LoadDepts(OracleConnection connection, IEnumerable<Dept> allDepts) {

   // check connection and allDepts
   // ...

   // Load data
   OracleLoader loader = new OracleLoader("YOUR_TABLE_NAME", connection);
   loader.CreateColumns();
   loader.Open();
   foreach (var dept in allDepts) {
     loader.SetValue("DEPTNO", dept.Deptno);
     loader.SetValue("DNAME", dept.Dname);
     loader.SetValue("LOC", dept.Loc);
     loader.NextRow();
   }
   loader.Close();
}
JIC: If you don't have a lot of classes and your model and tables don't change, then this code could be written manually.

Shalex
Site Admin
Posts: 9543
Joined: Thu 14 Aug 2008 12:44

Re: Bulk insert/update with autogeneration ids

Post by Shalex » Mon 18 Jul 2016 08:49

Partizan wrote:1. Is there a way to perform bulk insert/update without switching StoreGenerationPattern from Identity to None?
The current EF6 architecture doesn't allow to use Batch Updates with StoreGeneratedPattern=Identity/Computed.

A workaround for the case when the current context instance is used for the batch updates operation only: modify a metadata, used for creating context instance, by removing StoreGeneratedPattern=Identity/Computed from properties. This is easier to do with fluent mapping (this is possible with XML Mapping as well).

Post Reply