Execute Insert command and return inserted Id in Sql

Question

I am inserting some values into a SQL table using C# in MVC 4. Actually, I want to insert values and return the 'ID' of last inserted record. I use the following code.

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

ID is automatically incremented in the database when inserted.

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 ExecuteNonQuery denotes the numbers affecting the row. Instead of that I use

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

But it's not working. Please help me for solving this. And is there any code like cmd.ExecuteInsertAndGetID() (not working with my code).

You can take a look at http://stackoverflow.com/questions/9319532/return-value-from-sql-server-insert-command-using-c-sharp — Linky, Aug 22 '13 at 07:00

Ehsan · Accepted Answer · 2018-09-04T23:26:01.297

268

The following solution will work with sql server 2005 and above. You can use output to get the required field. inplace of id you can write your key that you want to return. do it like this

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 = Convert.ToInt32(cmd.ExecuteScalar());

        if (con.State == System.Data.ConnectionState.Open) con.Close();
            return modified;
    }
}

edited Sep 04 '18 at 23:26

answered Aug 22 '13 at 06:50

Ehsan

31,833
6
56
65

1

int modified = (int)cmd.ExecuteScalar(); – neel Aug 22 '13 at 11:18
15

Note that the column name must match the identity column name of the table. For example a table with this identity column name: `select EmployeeId, * from Employees` Requires this fragment in the insert statement: `output inserted.EmployeeId` – joshjeppson Nov 26 '14 at 15:37
1

How would you check if the query actually did the insert in this case? – ATD Sep 29 '16 at 22:04
10

I was getting an invalid cast error when trying to cast the `int modified = (int)cmd.ExecuteScalar();` to an int. I had to use Convert to convert it to an int. `int modified = Convert.ToInt32(cmd.ExecuteScalar());` – Baddack Mar 22 '17 at 21:14
below modification works for me. INSERT INTO Mem_Basic(Mem_Na,Mem_Occ) output INSERTED.ID VALUES(?, ?) – Ashutosh B Bodake Aug 05 '20 at 11:48
Please note that "OUTPUT" wouldn't work if there is UPDATE triggers on the table – Tommy Jul 07 '21 at 16:36

score 59 · Answer 2 · answered Aug 22 '13 at 06:50

59

Change the query to

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

This will return the last inserted ID which you can then get with ExecuteScalar

answered Aug 22 '13 at 06:50

Ken Keenan

9,818
5
32
49

1

this shows an error "InValidCast Exception was unhandled by user code" – neel Aug 22 '13 at 07:04
3

@neel, that is because scope_identity() returns numeric data type which you can cast only against decimal .net data type. Another way is to use Convert.To() series of functions to avoid cast problem. – Harsh Dec 12 '16 at 23:13
2

This answer is better because you don't have to type in the ID column of the table being inserted into. – goamn Jul 17 '17 at 23:44
1

SELECT SCOPE_IDENTITY() isn't working for me, but output INSERTED.ID is. – TiggerToo Mar 30 '18 at 15:15
1

@TiggerToo: What version of SQL Server are you using? It is possible that Microsoft have finally deprecated `SCOPE_IDENTITY()`. `OUTPUT INSERTED` appeared in IIRC SQL Server 2008 and has been the preferred way to do it since – Ken Keenan Mar 31 '18 at 14:18
1

int id = Convert.ToInt32(cmd.ExecuteScalar()); Fixed my cast error – Jeff Blumenthal Jun 23 '21 at 14:40

score 23 · Answer 3 · edited Apr 08 '17 at 19:18

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 these will help to 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;
        }
    }
}

If you have a procedure, you don't need to `select SCOPE_IDENTITY()` you just need to `return SCOPE_IDENTITY()` and then run it with `ExecuteNonQuery` and then read the `@RETURN_VALUE` Parameter — AaA, Sep 01 '22 at 02:11

score 1 · Answer 4 · answered Aug 22 '13 at 06:52

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.*/

score 1 · Answer 5 · answered Dec 28 '22 at 14:39

Can use OUTPUT Inserted.Id in Insert query linke this:

INSERT INTO Mem_Basic(Mem_Na,Mem_Occ) OUTPUT INSERTED.Id VALUES(@na,@occ);

Store response in result variable then read result[0].Id, Or can use ExecuteSingle and read result.Id directly to access inserted id or any other col data.

score 0 · Answer 6 · answered May 19 '21 at 06:42

SELECT SCOPE_IDENTITY() AS 'Identity'

this will return identity value inserted only within the current scope

SELECT @@IDENTITY AS 'Identity';

this is not limited to a specific scope So this will return the latest value of the identity column and which not necessarily the identity value from the same scope.

score 0 · Answer 7 · edited Nov 02 '21 at 08:31

0

If you using EF:

 Class Person{
 public int Id {get;set;}
 public string Name {get;set;}
}

You set Name value:

 Person p = new Person(){
  Name = "James"
};
DbContext.Persons.Add(p);
DbContext.SaveChanges();

//p.Id = new Id from database
//p.Name = James

You Just Save it you will get Id from the database.

edited Nov 02 '21 at 08:31

ouflak

2,458
10
44
49

answered Nov 02 '21 at 08:15

รัฐพล.James

61
1
7

score -2 · Answer 8 · edited May 05 '15 at 21:19

-2

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 identity value inserted into an identity column in the same scope. for more details http://technet.microsoft.com/en-us/library/ms190315.aspx

edited May 05 '15 at 21:19

Andomar

232,371
49
380
404

answered Aug 22 '13 at 07:01

Ajay

6,418
18
79
130

5

Would be nice, but ExecuteNonQuery only returns the number of rows affected, not the ID. Use ExecuteScalar instead https://learn.microsoft.com/en-us/dotnet/api/system.data.sqlclient.sqlcommand.executenonquery?f1url=https%3A%2F%2Fmsdn.microsoft.com%2Fquery%2Fdev15.query%3FappId%3DDev15IDEF1%26l%3DEN-US%26k%3Dk(System.Data.SqlClient.SqlCommand.ExecuteNonQuery);k(SolutionItemsProject);k(TargetFrameworkMoniker-.NETFramework,Version%3Dv4.6.2);k(DevLang-csharp)%26rd%3Dtrue&view=netframework-4.7.1 – Brandtware Dec 18 '17 at 14:26

Execute Insert command and return inserted Id in Sql

8 Answers8

Linked

Related