Want to use identity returned from insert in subsequent insert in a transaction

Question

I'm using Rob Conery's Massive for database access. I want to wrap a transaction around a couple of inserts but the second insert uses the identity returned from the first insert. It's not obvious to me how to do this in a transaction. Some assistance would be appreciated.

var commandList = new List<DbCommand>
    {
        contactTbl.CreateInsertCommand(new
            {
                newContact.Name,
                newContact.Contact,
                newContact.Phone,
                newContact.ForceChargeThreshold,
                newContact.MeterReadingMethodId,
                LastModifiedBy = userId,
                LastModifiedDate = modifiedDate,
            }),
        branchContactTbl.CreateInsertCommand(new
            {
                newContact.BranchId,
                ContactId = ????, <-- how to set Id as identity from previous command
            }),
    };

pkmiec · Answer 1 · 2013-02-26T19:58:16.127

2

Make a query between those two inserts, this method from Massive may be useful:

public object Scalar(string sql, params object[] args) {
    object result = null;
    using (var conn = OpenConnection()) {
        result = CreateCommand(sql, conn, args).ExecuteScalar();
    }
    return result;
}

Your sql will be = "select scope_identity()"

UPDATE 2013/02/26

Looking again at the Massive code there is no reliable way to retrieve last inserted ID.

Code above will work only when connection that makes "select scope_identity()" is pooled. (It must be the same connection that made insert).

Massive table.Insert(..) method returns Dynamic that contains ID field, which is filled with "SELECT @@IDENTITY". It gets last inserted ID from global scope, which is obvious bug (apparent in multithreading scenarios).

edited Feb 26 '13 at 19:58

answered Mar 23 '11 at 21:51

pkmiec

2,594
18
16

Thanks Dooh - bearing in mind that I need to feed the identity into the next command this isn't going to fit the Massive pattern. At the moment I'm working around it by pulling code from Massive into my class which is less than ideal. – David Clarke Mar 23 '11 at 23:02
1

Calling Scalar would not return the requested value since it will open a new connection (see Massive.cs) use `var newID = table.Insert(...)` Then inspect `newId.ID` see also http://stackoverflow.com/questions/15075913/using-massive-insert-i-get-dbnull-when-trying-to-get-scope-identity – Ron Harlev Feb 25 '13 at 20:53
Hmm, you're right. I guess it mostly works because of connection pooling. However looking at Massive code, it's bugged, @@Identity gets last inserted ID from global scope, not from current connection scope. – pkmiec Feb 26 '13 at 19:11
Bear in mind you never want to return @@identity for this purpose (uyou will lose data integrity if someone puts a trigger onteh table to insert to another table with an identity) and if this Massive does that, the person who wrote it is clearly not competent at accessing sql server databases. If he made a mistake this basic then who knows how badly the rest is written. – HLGEM Feb 26 '13 at 19:41

score 1 · Accepted Answer · answered Feb 26 '13 at 19:44

1

Can you just do it in a stored proc? The you can use scope_identity or better yet the output clause to get the value(s) you need. And all the inserts to all the tables are in one transaction which can be rolled back if any of them fail.

answered Feb 26 '13 at 19:44

HLGEM

94,695
15
113
186

Want to use identity returned from insert in subsequent insert in a transaction

2 Answers2