Execute Insert command and return inserted Id in Sql

C#SqlSql Serverasp.net Mvc-4

C# Problem Overview

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).

C# Solutions

Solution 1 - C#

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;
    }
}

Solution 2 - C#

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

Solution 3 - C#

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;
        }
    }
}

Solution 4 - C#

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

Solution 5 - C#

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.

Solution 6 - C#

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.

Solution 7 - C#

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

Categories
Recommended C# Solutions
Recommended Sql Solutions
Recommended Sql Server Solutions
Recommended asp.net Mvc-4 Solutions

Previous Solution:

How can I transform between the two styles of public key format, one "BEGIN RSA PUBLIC KEY", the other is "BEGIN PUBLIC KEY"

OpensslX509Ssh KeysPublic KeyPkcs#1

Next Solution:

100% width Twitter Bootstrap 3 template

HtmlCssTwitter BootstrapLayoutTwitter Bootstrap-3

Attributions

All content for this solution is sourced from the original question on Stackoverflow.

The content on this page is licensed under the Attribution-ShareAlike 4.0 International (CC BY-SA 4.0) license.

Content TypeOriginal AuthorOriginal Content on Stackoverflow
QuestionneelView Question on Stackoverflow
Solution 1 - C#EhsanView Answer on Stackoverflow
Solution 2 - C#Ken KeenanView Answer on Stackoverflow
Solution 3 - C#Humayoun KabirView Answer on Stackoverflow
Solution 4 - C#SasidharanView Answer on Stackoverflow
Solution 5 - C#Akshay DeodharView Answer on Stackoverflow
Solution 6 - C#รัฐพล.JamesView Answer on Stackoverflow
Solution 7 - C#AjayView Answer on Stackoverflow