1

Given the following code (which is mostly irrelevant except for the last two lines), what would your method be to get the value of the identity field for the new record that was just created? Would you make a second call to the database to retrieve it based on the primary key of the object (which could be problematic if there's not one), or based on the last inserted record (which could be problematic with multithreaded apps) or is there maybe a more clever way to get the new value back at the same time you are making the insert?

Seems like there should be a way to get an Identity back based on the insert operation that was just made rather than having to query for it based on other means.

public void Insert(O obj)
{
    var sqlCmd = new SqlCommand() { Connection = con.Conn };
    var sqlParams = new SqlParameters(sqlCmd.Parameters, obj);
    var props = obj.Properties.Where(o => !o.IsIdentity);

    InsertQuery qry = new InsertQuery(this.TableAlias);
    qry.FieldValuePairs = props.Select(o => new SqlValuePair(o.Alias, sqlParams.Add(o))).ToList();

    sqlCmd.CommandText = qry.ToString();
    sqlCmd.ExecuteNonQuery();
}

EDIT: While this question isn't a duplicate in the strictest manner, it's almost identical to this one which has some really good answers: Best way to get identity of inserted row?

Community
  • 1
  • 1
Brandon Moore
  • 8,590
  • 15
  • 65
  • 120

4 Answers4

2

It strongly depends on your database server. For example for Microsoft SQL Server you can get the value of the @@IDENTITY variable, that contains the last identity value assigned.

To prevent race conditions you must keep the insert query and the variable read inside a transaction.

Another solution could be to create a stored procedure for every type of insert you have to do and make it return the identity value and accept the insert arguments.

Otherwise, inside a transaction you can implement whatever ID assignment logic you want and be preserved from concurrency problems.

SchmitzIT
  • 9,227
  • 9
  • 65
  • 92
Andrea Colleoni
  • 5,919
  • 3
  • 30
  • 49
  • His database server is sql serve as per given objects (SqlCommand). Others have other prefixes (OracleCommand etc.). – TomTom Feb 09 '12 at 07:11
  • +1 for making me realize that there is no concurrency problem if I do it in a transaction. Kind of surprised I didn't think of that. – Brandon Moore Feb 09 '12 at 07:21
  • Why -1? Where did I say something wrong? I gave MSSQL answer, and the preamble is a general consideration, so what's the problem? – Andrea Colleoni Feb 09 '12 at 07:22
  • 1
    Ha, it's funny that someone downvoted you because you overlooked the tags but you were the only one who was actually helpful. I think some people on here are more interested in being critical than being helpful. – Brandon Moore Feb 09 '12 at 07:24
  • fyi, found this that shows one other way using OUTPUT: http://stackoverflow.com/questions/42648/best-way-to-get-identity-of-inserted-row. I think that could be used in my code by adding parameter objects to the sqlcommand, but not positive. – Brandon Moore Feb 09 '12 at 07:42
1

Afaik there is not finished way. I solved by using client generated ids (guid) so that my method generated the id and returns it to the caller.

Perhaps you can analyse some SqlServer systables in order to see what has last changed. But you would get concurrency issues (What if someone else inserts a very similar record).

So I would recommend a strategy change and generate the id's on the clients

Boas Enkler
  • 12,264
  • 16
  • 69
  • 143
  • That's a good thought. I 'really' prefer not to use guids though because in QA we often are looking at data and communicating the id values to each other and that's a lot more cumbersome with guids. I could use a strategy that seeds different clients with a starting value so that don't overlap but that certainly doesn't match the simplicity of just letting sql server dole out the numbers for me. Seems like I saw a way to do this long ago but I just can't seem to remember what it is now.... (of course, I could be dreaming this up too but I hope not) – Brandon Moore Feb 09 '12 at 07:15
  • 1
    +1 because this is a good idea that I think will be helpful to others who read this. – Brandon Moore Feb 09 '12 at 07:30
1

You can take a look at : this link.

I may add that to avoid the fact that multiple rows can exist, you can use "Transactions", make the Insert and the select methods in the same transaction.

Good luck.

SidAhmed
  • 2,332
  • 2
  • 25
  • 46
0

The proper approach is to learn sql.

You can do a SQL command followed by a SELECT in one run, so you can go in and return the assigned identity.

See

TomTom
  • 61,059
  • 10
  • 88
  • 148
  • This is approach has already been pointed out as problematic in my question and in the first posters answers. You don't know that someone else didn't insert a record between the time it took you to insert yours and get the identity. – Brandon Moore Feb 09 '12 at 07:17
  • Unless by "one run" you mean that literally (in one call to the db rather than two) in which case I'd definintely be interested in hearing how to do that. – Brandon Moore Feb 09 '12 at 07:18
  • Ah, just read Andrea's answer and realized I can do it in a transaction. – Brandon Moore Feb 09 '12 at 07:22