c# sql: get auto incrementing id after inserting data

Question

I'm working on a system that increments has an auto incrementing index used as an identifier. But this gives me a problem as I would like to have the identifiers after doing the insert.

What I have in C# is:

string commandtxt = "INSERT INTO DATA(POINT) VALUES(@DP)";

SqlConnection conn = new SqlConnection(WebConfigurationManager.AppSettings["ConnectionInfo"].ToString());

SqlCommand sqlCommand = new SqlCommand(commandtxt, conn);
sqlCommand.Parameters.AddWithValue("@DP", point);

conn.Open();
sqlCommand.ExecuteNonQuery();
int record id = ????

I believe you would have to use a second query: select max(ID) from data — apomene, Aug 06 '15 at 07:51
Check here - http://stackoverflow.com/questions/5228780/how-to-get-last-inserted-id — Puneet Chawla, Aug 06 '15 at 07:51
@apomene: that's a *really bad* idea which could return a totally wrong value in a busy system...... — marc_s, Aug 06 '15 at 07:52

score 4 · Accepted Answer · answered Aug 06 '15 at 07:48

4

replace

 string commandtxt = "INSERT INTO DATA(POINT) VALUES(@DP)";
 sqlCommand.ExecuteNonQuery();

with

string commandtxt = "INSERT INTO DATA(POINT) VALUES(@DP) SELECT SCOPE_IDENTITY()";
int ID = (int)sqlCommand.ExecuteScalar();

answered Aug 06 '15 at 07:48

fubo

42,334
17
98
130

c# sql: get auto incrementing id after inserting data

1 Answers1