Identity insert

[Bernhard]

Hi telerik support team!

I am still working with one of the latest Vanatec Open Access versions.
Now I have to write an application (SQLServer2000/2005) where I have to insert rows into a table and set the value of the primary key column manually although the column is defined as an Identity(1,1)-column.

In T-Sql you can do that by:

SET IDENTITY_INSERT MyTable ON
Insert into MyTable(MySN) values(1234)
SET IDENTITY_INSERT MyTable OFF

 Is there any possibility to do that with your OR-mapper?

Greets,
Berni

[Alexander]

Hello Bernhard,

Yes, it is possible. When creating new object you can specify the value of its identity property:

scope.Transaction.Begin();

Group g = new Group();
g.GroupId = 1234;

scope.Add(g);
scope.Transaction.Commit();

However, it is your responsibility to check whether the set value is an unique identifier. If it is not, an exception will be raised.
Note that updating the ID property of already persisted object is not currently supported.

All the best,
Alexander
the Telerik team

Check out Telerik Trainer, the state of the art learning tool for Telerik products.

[Bernhard]

Hi Alexander,

Sorry, but the example you just mentioned will not work because the "scope.Transaction.commit()"-line will result in a
DataStoreException thrown by the SQL-Server because the Primary-key column is pecified as:

MySN    int    identity(1,1)    not null

Exception: (sorry, German VS.NET)
{"Insert of '682077847-1' failed: OpenAccess.RT.sql.SQLException: Ein expliziter Wert für die Identitätsspalte kann nicht in der 'MyTable'-Tabelle eingefügt werden, wenn IDENTITY_INSERT auf OFF festgelegt ist.\nINSERT INTO MyTable (MySN) VALUES (?)\n(set event logging to all to see parameter values) OpenAccess.RT.sql.SQLException: Ein expliziter Wert für die Identitätsspalte kann nicht in der 'MyTable'-Tabelle eingefügt werden, wenn IDENTITY_INSERT auf OFF festgelegt ist."}

Is there some option that I can specify in the App.config or something else to enable identity-inserts?

[Thomas]

Hello Bernhard,

i think the easiest way is to have a second configuration where the AUTOINC key generator for the table MyTable is turned off. The reason is, that not only must the SQL server allow to enter a client side value, but the value must also be given by the OpenAccess runtime. When AUTOINC is used, there will be no value passed to the server.

In addition, the configuration must also have an initSql string of 

<initSql>SET IDENTITY_INSERT MyTable ON<initSql>

so that the connection is slightly altered in behavior and allows values to flow into identity columns.

Hope this helps,

Thomas

[Bernhard]

Thanks for your reply Thomas, that took me a step further!

Hmmm..., but now I ran against the next wall.
It is not so simple to explain:
The example I mentioned before is working now and I am able to manually insert the key into the identity column.

But for the programm that I have to write, I need to insert rows into more than one table and I have to set the value of the identity colum for all those rows. SQL-Server allows only 1 Table to have the "IDENTITY_INSERT"-option to be turned on at the same time.

For example:
I have a table 'Address' and a Table 'Contact' with relational rows

To insert an Address and a corresponding Contact and set the identity-column for both manually in T-SQL I would have to do something like:

SET IDENTITY_INSERT Address ON
insert into Address(AddressSN, CompanyName) values(1234, 'My Company')
SET IDENTITY_INSERT Address OFF
SET IDENTITY_INSERT Contact ON
insert into Contact(ContactSN, toAddressSN, LastName) values(9876, 1234, 'Miller')
SET IDENTITY_INSERT Contact OFF


@Open Access: The <initSql>-property is connection based and as I mentioned before it is not allowed to specify more than 1 'IDENTITY_INSERT' Tables at the same time. (Restriction by SQLServer)

so something like...
 

 <

 

initSql>SET IDENTITY_INSERT [Address] ON</initSql>

 

 <initSql1>SET IDENTITY_INSERT [Contact] ON</initSql1> ...will result in...

Error executing initSQL on new Connection: OpenAccess.RT.sql.SQLException: IDENTITY_INSERT ist für die 'MultiDbTest.dbo.Address'-Tabelle bereits auf ON festgelegt. Der SET-Vorgang für die 'Contact'-Tabelle kann nicht ausgeführt werden.

----------

So there is no way to define a table/class-based sql statement that will be executed before /after an insert that I can define, or?

So I would have to use different ObjectScopes for each table where I want to insert the value of the identity-column manually and dynamically specify the <initSQL> - tag, or is there another chance to solve this?

Best regards,
Berni

[Thomas]

Hello Bernhard,

we currently do not support such a thing as a initSql/deInitSql per table. And as you mentioned, the SQL server allows only one table per session with identity_insert on (for whatever reason).

So it looks like that is not going to help you much.

Only one solution comes into my mind, and that is the usage of stored procedures. We are currently working on supporting this, and that could provide you with a solution as the SP used for inserting things could be wrapped in a set identity_insert on/off frame. A concrete date when this functionality is available cannot be given out, but we are targeting it for the Q1 release. Like the solution already proposed this would mean, that a second configuration must be used when you need to insert the values manually, and no mixing of the approaches is possible within one objectscope.

Or you need to removing the identity(1,1)  temporarily from the table(s), requiring a schema change for certain parts of the application.

Sincerely yours,
Thomas

[Bernhard]

Thank you for the quick answer Thomas.

I will keep the SP-feature you want to build in Q1-release in mind. Sounds good to me.

So for my current project using ADO.Net seems to be the best approach to me, especially when it comes to a massive use of SP. 
Working with the persistent classes in memory would have been much more comfortable but this project should not get too big, so I can accept the technological step backward.

Best regards,
Berni