Run the Insert command and return the inserted identifier in Sql

Question

Hi, I am inserting some values ​​into sql table using C # in MVC4. Actually I want to insert values ​​and return the "ID" of the last record inserted. I am using the following code.

public class MemberBasicData { public int Id { get; set; } public string Mem_NA { get; set; } public string Mem_Occ { get; set; } } 

The identifier automatically increases when the database is added.

 public int CreateNewMember(string Mem_NA, string Mem_Occ ) { using (SqlConnection con=new SqlConnection(Config.ConnectionString)) { using(SqlCommand cmd=new SqlCommand("INSERT INTO Mem_Basic(Mem_Na,Mem_Occ) VALUES(@na,@occ)",con)) { cmd.Parameters.AddWithValue("@na", Mem_NA); cmd.Parameters.AddWithValue("@occ", Mem_Occ); con.Open(); int modified = cmd.ExecuteNonQuery(); if (con.State == System.Data.ConnectionState.Open) con.Close(); return modified; } } } 

I know that ExecuteNonQuery stands for numbers affecting a string. Instead i use

 int modified = (int)cmd.ExecuteScalar(); 

But does not work. Please help me solve this. And is there any code like cmd.ExecuteInsertAndGetID () (doesn't work with my code).

Answer 1

The following solution will work with SQL Server 2005 and higher. You can use the output to get the required field. In the id place, you can write your key that you want to return. do it like that

FOR SQL SERVER 2005 and above

  using(SqlCommand cmd=new SqlCommand("INSERT INTO Mem_Basic(Mem_Na,Mem_Occ) output INSERTED.ID VALUES(@na,@occ)",con)) { cmd.Parameters.AddWithValue("@na", Mem_NA); cmd.Parameters.AddWithValue("@occ", Mem_Occ); con.Open(); int modified =(int)cmd.ExecuteScalar(); if (con.State == System.Data.ConnectionState.Open) con.Close(); return modified; } } 

FOR previous versions

  using(SqlCommand cmd=new SqlCommand("INSERT INTO Mem_Basic(Mem_Na,Mem_Occ) VALUES(@na,@occ);SELECT SCOPE_IDENTITY();",con)) { cmd.Parameters.AddWithValue("@na", Mem_NA); cmd.Parameters.AddWithValue("@occ", Mem_Occ); con.Open(); int modified = cmd.ExecuteScalar(); if (con.State == System.Data.ConnectionState.Open) con.Close(); return modified; } } 

Answer 2

Change the request to

 "INSERT INTO Mem_Basic(Mem_Na,Mem_Occ) VALUES(@na,@occ); SELECT SCOPE_IDENTITY()" 

This will return the last inserted identifier that you can get with ExecuteScalar

Answer 3

SQL Server Stored Procedure:

 CREATE PROCEDURE [dbo].[INS_MEM_BASIC] @na varchar(50), @occ varchar(50), @New_MEM_BASIC_ID int OUTPUT AS BEGIN SET NOCOUNT ON; INSERT INTO Mem_Basic VALUES (@na, @occ) SELECT @New_MEM_BASIC_ID = SCOPE_IDENTITY() END 

C # code:

 public int CreateNewMember(string Mem_NA, string Mem_Occ ) { // values 0 --> -99 are SQL reserved. int new_MEM_BASIC_ID = -1971; SqlConnection SQLconn = new SqlConnection(Config.ConnectionString); SqlCommand cmd = new SqlCommand("INS_MEM_BASIC", SQLconn); cmd.CommandType = CommandType.StoredProcedure; SqlParameter outPutVal = new SqlParameter("@New_MEM_BASIC_ID", SqlDbType.Int); outPutVal.Direction = ParameterDirection.Output; cmd.Parameters.Add(outPutVal); cmd.Parameters.Add("@na", SqlDbType.Int).Value = Mem_NA; cmd.Parameters.Add("@occ", SqlDbType.Int).Value = Mem_Occ; SQLconn.Open(); cmd.ExecuteNonQuery(); SQLconn.Close(); if (outPutVal.Value != DBNull.Value) new_MEM_BASIC_ID = Convert.ToInt32(outPutVal.Value); return new_MEM_BASIC_ID; } 

I hope this helps you ....

You can also use this if you want ...

 public int CreateNewMember(string Mem_NA, string Mem_Occ ) { using (SqlConnection con=new SqlConnection(Config.ConnectionString)) { int newID; var cmd = "INSERT INTO Mem_Basic(Mem_Na,Mem_Occ) VALUES(@na,@occ);SELECT CAST(scope_identity() AS int)"; using(SqlCommand cmd=new SqlCommand(cmd, con)) { cmd.Parameters.AddWithValue("@na", Mem_NA); cmd.Parameters.AddWithValue("@occ", Mem_Occ); con.Open(); newID = (int)insertCommand.ExecuteScalar(); if (con.State == System.Data.ConnectionState.Open) con.Close(); return newID; } } } 

Answer 4

 using(SqlCommand cmd=new SqlCommand("INSERT INTO Mem_Basic(Mem_Na,Mem_Occ) " + "VALUES(@na,@occ);SELECT SCOPE_IDENTITY();",con)) { cmd.Parameters.AddWithValue("@na", Mem_NA); cmd.Parameters.AddWithValue("@occ", Mem_Occ); con.Open(); int modified = cmd.ExecuteNonQuery(); if (con.State == System.Data.ConnectionState.Open) con.Close(); return modified; } 

SCOPE_IDENTITY Returns the last authentication value inserted in the identifier column in the same scope. for more details http://technet.microsoft.com/en-us/library/ms190315.aspx

Answer 5

 USE AdventureWorks2012; GO IF OBJECT_ID(N't6', N'U') IS NOT NULL DROP TABLE t6; GO IF OBJECT_ID(N't7', N'U') IS NOT NULL DROP TABLE t7; GO CREATE TABLE t6(id int IDENTITY); CREATE TABLE t7(id int IDENTITY(100,1)); GO CREATE TRIGGER t6ins ON t6 FOR INSERT AS BEGIN INSERT t7 DEFAULT VALUES END; GO --End of trigger definition SELECT id FROM t6; --IDs empty. SELECT id FROM t7; --ID is empty. --Do the following in Session 1 INSERT t6 DEFAULT VALUES; SELECT @@IDENTITY; /*Returns the value 100. This was inserted by the trigger.*/ SELECT SCOPE_IDENTITY(); /* Returns the value 1. This was inserted by the INSERT statement two statements before this query.*/ SELECT IDENT_CURRENT('t7'); /* Returns value inserted into t7, that is in the trigger.*/ SELECT IDENT_CURRENT('t6'); /* Returns value inserted into t6. This was the INSERT statement four statements before this query.*/ -- Do the following in Session 2. SELECT @@IDENTITY; /* Returns NULL because there has been no INSERT action up to this point in this session.*/ SELECT SCOPE_IDENTITY(); /* Returns NULL because there has been no INSERT action up to this point in this scope in this session.*/ SELECT IDENT_CURRENT('t7'); /* Returns the last value inserted into t7.*/