Execute Insert command and return inserted Id in Sql

Question

Hi, I am inserting some values into the SQL table using C# in MVC4. Actually, I want to insert values and return the 'ID' of the 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 increment in 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 not working. Please help me for solving this. And is there any code like cmd.ExecuteInsertAndGetID() (not working with my code).

Answer 1

If you are using SQL Server 2005 and above. Then you can use the output to get the required field. in place of id, you can write your key that you want to return. 

this way:

Use the code given below 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;

    }

}

Use the code given below for previous versions (i.e. before SQL SERVER 2005):

    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;

    }

}

You can learn in-depth about SQL statements, queries and become proficient in SQL queries by enrolling in our industry-recognized Microsoft SQL server certification.