5

I am trying to switch the identity off to insert my own value, steps I followed

  1. changed the property StoredGeneratedPattern value to None for the identity column
  2. changed the property StoredGeneratedPattern value to None in EDMX file by opening in xml format

Tried using the below code

using (TransactionScope scope = new TransactionScope(TransactionScopeOption.RequiresNew))
{
   int k = Context.ExecuteStoreCommand("SET IDENTITY_INSERT dbo.client ON");
   Context.ClientInfoes.Add(testclient);
   result = Context.SaveChanges();
   int j = Context.ExecuteStoreCommand("SET IDENTITY_INSERT dbo.client OFF");
   scope.Complete();
}

but I am still receiving the error

Cannot insert explicit value for identity column in table when IDENTITY_INSERT is set to OFF

Am I missing something? Are there any other options?

Vamsi
  • 95
  • 2
  • 10
  • Your context says ClientInfoes, but your sQL is dbo.client. does ClientInfoes map to client? or a clientinfoes table? – Erik Funkenbusch Oct 05 '12 at 17:35
  • Clientinfoes map to client, Client is the table in sql. Without the identity the insert works fine but my requirement is to pass custom client id. – Vamsi Oct 05 '12 at 17:48

2 Answers2

0

The data is stored in the database when you call TransactionScope.Complete, not when you call ClientInfoes.Add or Context.SaveChanges. So you can see that when the insert statement is called you have already switched IDENTITY INSERT back off.

Simply rearrange things...

using (TransactionScope scope = new TransactionScope(TransactionScopeOption.RequiresNew))
{
   int k = Context.ExecuteStoreCommand("SET IDENTITY_INSERT dbo.client ON");
   Context.ClientInfoes.Add(testclient);
   result = Context.SaveChanges();
   scope.Complete();
   int j = Context.ExecuteStoreCommand("SET IDENTITY_INSERT dbo.client OFF");
}

Better yet, do all your changes to IDENTITY_INSERT outside of the transaction since it's value is session specific (you can only have it switched on for one table per session).

MarkB
  • 174
  • 14
0

See the similar question here.

Eranga explains:

ExecuteSqlCommand will open the connection, execute the sql and then close it. So your next command will execute using a different connection.

The ExecuteSqlCommand method is similar to the ExecuteStoreCommand.

Daniel Liuzzi explains:

... the trick is packing everything into a single command...

So for example,

string sqlStatement = "SET IDENTITY_INSERT clientInfo ON;" +
string.Format("INSERT clientInfo (ClientInfoId, Column1, Column2) VALUES ({0}, {1}, {2}, '{3}');", testClient.ClientInfoId, testClient.Column1, testclient.Column2) +
"SET IDENTITY_INSERT clientInfo OFF";
context.ExecuteStoreCommand(sqlStatement);
Community
  • 1
  • 1
Rami A.
  • 10,302
  • 4
  • 44
  • 87
  • 1
    You rally want to remove that `string.Format` and properly parameterize your command instead; otherwise you're exposing yourself to SQL injection attacks. – Daniel Liuzzi Nov 06 '15 at 21:21