Execute Insert command and return inserted Id in Sql

Question

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

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

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