SqlDataAccess.InsertEntity returns DbNull for identity key about westwindtoolkit

[RickStrahl]

Hmmm... yes, this looks like a regression due to the fact that current versions always close connections that aren't a transactions. So either the entire code has to be wrapped into a transaction (so it stays on the same connection) or the command has to be combined.

This fix works:

Command.CommandText = FieldList.ToString().TrimEnd(',') + ") " +
                                 DataList.ToString().TrimEnd(',') + ")";

if (returnIdentityKey)
{
    Command.CommandText += ";\r\n" + "select SCOPE_IDENTITY()";
    return ExecuteScalar(Command);
}

int res = ExecuteNonQuery(Command);
if (res < 0)
    return null;

return res;

9af60b4

This may have adverse affects with other providers - if multi-line commands are not supported.

I also checked to see if I could get this to work with a transaction wrapper (which keeps the connection open between the two commands) but even that doesn't work to return the Id - it only works in the same command and execution context.

[ruthans]

Thanks Rick. You are correct, it is related to scope/execution context rather than the connection, thus wrapping in a transaction doesn't help.

The workaround you suggested works fine in my tests. I made a slightly different version though (SQL server specific), where I used an out variable instead. This way I can also get the affected row count (although it perhaps doesn't make much sense on single entity insert).

For the benefit of others, this was the workaround I used:

if (returnIdentityKey)
{
    Command.CommandText += "; select @SCOPEID = SCOPE_IDENTITY()";

    var parm = this.CreateParameter("@SCOPEID", null, System.Data.ParameterDirection.Output);
    parm.Size = 4;
    parm.DbType = System.Data.DbType.Int32;

    Command.Parameters.Add(parm);
}

int res = ExecuteNonQuery(Command);

if (res < 0)
    return null;

object result = res;

if (returnIdentityKey)
{
    result = Command.Parameters["@SCOPEID"].Value;
}

CloseConnection();

return result;