Hi,

From what I have read, there is no way to explicitly set the value of an identity column. Is this really the case? What I would like to do is be able to set identity values in my code so that I can test my objects without requiring a database dependency. I suppose I could use Mock Objects...

You can't save them into the db, but you can set identity field values. In adapter that's no problem. In selfservicing you use: entity.Fields[index].ForcedCurrentValueWrite(value) to set the field.

Otis wrote:

You can't save them into the db, but you can set identity field values.

Ummm..what's the point of being able to create them in memory and not save them into the db?

So the question becomes how do i send some hand-crafted SQL like this:

set IDENTITY_INSERT Item on

to temporarily allow me to insert specific IDENTITY values while i'm populating the Item table from a horrible previous database ??

StueyNZ wrote:

Otis wrote:

You can't save them into the db, but you can set identity field values.

Ummm..what's the point of being able to create them in memory and not save them into the db?

So you can pass the entity to FetchEntity() for example to get it fetched .

So the question becomes how do i send some hand-crafted SQL like this:

set IDENTITY_INSERT Item on

to temporarily allow me to insert specific IDENTITY values while i'm populating the Item table from a horrible previous database ??

Using a proc which is called on teh same connection? (adapter: keep connection open)

Otis wrote:

Using a proc which is called on the same connection? (adapter: keep connection open)

Damn - I wrote the migration code using SelfService - any clues? or do I have to change to Adapter? Because we have to be using the same connection for the "Set IDENTITY_INSERT" to have the desired effect.

StueyNZ wrote:

Otis wrote:

Using a proc which is called on the same connection? (adapter: keep connection open)

Damn - I wrote the migration code using SelfService - any clues? or do I have to change to Adapter? Because we have to be using the same connection for the "Set IDENTITY_INSERT" to have the desired effect.

You can accomplish the same thing in selfservicing, by passing a Transaction object, and by adding the entity/entities to be saved to that transaction prior to saving them. That should do it. (Haven't tested this though). Every proc call method has an overload which accepts a Transaction object.

Otis wrote:

You can accomplish the same thing in selfservicing, by passing a Transaction object, and by adding the entity/entities to be saved to that transaction prior to saving them. That should do it. (Haven't tested this though).

Thanks, got it working - for those who are interested:

trx = new Transaction(IsolationLevel.ReadUncommitted, "MakeSomeItems");

// We need to be allowed to insert into IDENTITY field in Item
IDbCommand cmd = trx.ConnectionToUse.CreateCommand();
cmd.Transaction = trx.PhysicalTransaction;
cmd.CommandText = "set IDENTITY_INSERT [Kiosk].[dbo].[Item] on"; 
cmd.ExecuteNonQuery();

<!-- blah blah blah Make an Item or 500 -->

trx.Add(item);
item.Save();

// We've finished playing silly buggers with IDENTITY column in Item
cmd = trx.ConnectionToUse.CreateCommand();
cmd.Transaction = trx.PhysicalTransaction;
cmd.CommandText = "set IDENTITY_INSERT [Kiosk].[dbo].[Item] off";
cmd.ExecuteNonQuery();

//We're done
trx.Commit();