[Fixed] How to return SCOPE_IDENTITY as value to use in angular app

Issue

I’m struggling to return the ID of my newly inserted table row for further use in my Angular app.

Any suggestions on how to send it while I’m posting? Can’t wrap my head around it.

public string Post(Profile profile)
{
        try
        {
            string query = "INSERT INTO dbo.Profile([ProfileName], [FName], [LName], [DateOfJoining], [Type], [LogoFile]) VALUES(@f1, @f2, @f3, @f4, @f5, @f6)";
            query += " SELECT SCOPE_IDENTITY()";

            DataTable table = new DataTable();

            using (var con = new SqlConnection(ConfigurationManager.ConnectionStrings["BillingApp"].ConnectionString))
            using (var cmd = new SqlCommand(query, con))
            using (var da = new SqlDataAdapter(cmd))
            {
                cmd.Parameters.Add(new SqlParameter("@f1", profile.ProfileName));
                cmd.Parameters.Add(new SqlParameter("@f2", profile.FName));
                cmd.Parameters.Add(new SqlParameter("@f3", profile.LName));
                cmd.Parameters.Add(new SqlParameter("@f4", profile.DateOfJoining));
                cmd.Parameters.Add(new SqlParameter("@f5", profile.Type));
                cmd.Parameters.Add(new SqlParameter("@f6", profile.LogoFile));
                cmd.CommandType = CommandType.Text;

                profile.ProfileID = Convert.ToInt32(cmd.ExecuteScalar());

                da.Fill(table);
            }

            return "Added successfully!";
        }
        catch (Exception)
        {
            return "failed to Add";
        }
}

This is how I trigger it from Angular:

addProfile(val:any) {
    return this.http.post(this.APIUrl + '/Profile',val);
}

Solution

This is something you can implement using the return parameter from a SQL Stored Procedure.

This can then negate the need to use an expensive DataAdapter object to return a recordset where you only need the single Identity value.

Simply create your insert statement as a Stored Procedure, return the Identity value and call it using the connection.executeNonQuery() method. You can then access the return value using the parameters collection.