How to do Identity Insert with the PM.

I am not quite sure why you have this requirement.  It might help if I knew this.  Do you have a requirement that you be able to insert very specific values, or do you simply need to guarantee that the number you insert on the client is not going to be changed by the database?  If it is the latter, DevForce already guarantees this behavior.

This is what DevForce Help says:

Sql Server Identity Id Generation

The default DevForce processing for SQL Server tables with an identity column uses two SQL Server features which require that the connected user have elevated privileges:

·         DBCC CHECKIDENT, so we can re-set the curren seed for the next identity value.

·         SET IDENTITY_INSERT, so we can specify the Identity column value on insert.

When DevForce issues those commands, it must be logged in either as the owner of the tables or as a member of the sysadmin, db_owner or db_ddladmin roles.

This shouldn’t be an issue in an n-tier deployment where the DevForce process issuing these commands is running in a secure environment on a host machine.

Two-tier (“Client/Server”) applications may be ok too, especially if they execute on a secure LAN and the connection string specifies Windows authentication.

Some applications don’t fit these criteria. They are two-tier and n-tier, for some reason, is not a current option. Perhaps they can’t use Windows authentication and must specify a username and password in the connection string. No one wants connection credentials with administrator rights floating about.

Perhaps the application can switch from Identity Ids to a table-drive custom id design. That won’t be an option if there are legacy applications referencing the same data.

Note that our SqlServerIdentityIdGenerator doesn’t allow you to use a “specific” value, just the “next” one.  You can implement a custom IIdentityIdGenerator and possibly accomplish the “specific” value part.  But when the generator is called to map temp to real it only knows about temp ids, you’d have to use the EntityColumn in the temp id to get to the Entity.  The mapping also is done for all records to be inserted – if user wanted it done one-by-one they’d have to do a ForceIdFixup.  So, I’d say potentially doable with a custom implementation, depending on user’s requirements.

If the IIdentityIdGenerator doesn’t work, then RPC might work, or anything where they’d use the AdoHelper to directly work with the connection and command objects.