DISABLE KEYS

[Paul]

Hi,
    I am trying to copy data from one table in one DB to a replica table in another DB. The problem is that when I do this.DataContext.Add(record) the id that I passed in gets changed to what the DB generates. If I was to do this in an SQL statement I could use DISABLE KEYS which would allow me to insert the record with the ID I provide.

How can I achieve this in ORM?

Or is there another way in which you can recommend me copying a whole table from one DB to another and keep the ids the same.

Thanks

[Boris Georgiev]

Hello Paul,

Currently such feature is not available out of the box in OpenAccess ORM. In order to achieve that, most probably some custom executions will be necessary, and we might be able to assist you on defining them. In order for us to understand your scenario and assist you in the best possible way, could you please provide us some more information:

1) Are there any existing entities in the replica table?
2) Are you using Domain Class Mapping or Fluent Mapping?

I am looking forward to hearing from you.


Regards,
Boris Georgiev
Telerik

[Paul]

Hi,
    no I clear the table first so there will be no existing entities in the table. I think I'm using Domain class mapping, I update my entitiesmodel from the database rather than the other way round.

I did manage to workround this by doing this.DataContext.ExecuteNonQuery(insertSQL, params) and works but am happy to hear what you suggest.

Thanks

[Boris Georgiev]

Hello Paul,

I am glad to see that you have found a solution. 

If you have to copy the entities from one table to its replica in another database and you should execute this operation only once, your solution is the best one. 

There are other approaches which are more complicated and need more time for implementation and execution. If you are interested I can prepare a sample application to demonstrate one of these approaches. 

In case you want to share your experience with our community how you solved this issue, I will suggest you to prepare a code library for our Code Libraries section for which you will be rewarded with Telerik Points.

Regards,
Boris Georgiev
Telerik

[Paul]

Hi Boris,
             If possible that would be great if you could prepare a sample application to demonstrate one of the approaches you mention as I could then modify that application to show the approach I'm currently using and add it to the code library.

 Even if you don't manage to create a sample application I will still try and find some time over the next few weeks to knock up a sample one of my own to add to the library demonstrating the approach I'm using.

Thanks

[Boris Georgiev]

Hello Paul,

I have prepared and attached a sample application which demonstrates one approach how to copy the data from one table to another table. There are two databases with one table that I have prepared for the sample and their scripts can be found in the attached zip. The table in the first database contains some rows and the table in the second database doesn't contain any rows.

For the sample I used two domain models created for each database. There are some modifications which I made in the domain model with the replica table.
1) There is a property "IdentityMechanism" for each Domain Class in the Visual Designer. This property has "DatabaseServerCalculated" value when the table uses the AutoIncremental mechanism for the Primary Key column. The value should be changed to default.
2) In the Model Settings dialog, in the Backend Configuration tab page, there is the Connection Pool tab which provides overview of the Connection Pool settings. In the Init SQL text box, you can specify an SQL statement which will be executed on each newly created connection. There I added the following SQL statement: 

ALTER TABLE replicatable DISABLE KEYS

So when the connection is created the AutoIncremental mechanism for the replicatable will be disabled.
 
Unfortunately the Domain Model created for the replica database could be used only to copy the entities from the original table to the replica table. If you want to use a Domain Model which represents the database in its actual form(without any changes) you should use another Domain Model.

Please let me know if you have any questions.

Regards,
Boris Georgiev
Telerik