How to get OUTPUT value from stored procedure in asp.net

Question

I have this stored procedure that inserted a record into a table and returned the column value IDENTITYwith Output Inserted.KID:

ALTER procedure [dbo].[InsertNode]
(
    @FName nvarchar(50),
    @Lname nvarchar(50),
    @CDesc nvarchar(max),
    @ParentID int
)
as
begin
    insert into Chart (FName , Lname ,CDesc, ParentID ) 
    Output Inserted.KID
    values (@FName, @Lname, @CDesc, @ParentID)
end

C # code:

public void InsertNode(string FName, string LName, string cDesc, int pid)
{
    try
    {
        using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["ConStr"].ConnectionString))
        {
            using (SqlCommand cmd = new SqlCommand("InsertNode", con))
            {
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@FName", FName);
                cmd.Parameters.AddWithValue("@Lname", LName);
                cmd.Parameters.AddWithValue("@CDesc", cDesc);
                cmd.Parameters.AddWithValue("@ParentID", pid);
                con.Open();
                Int32 retVal = cmd.ExecuteNonQuery();
            }
        }
    }
    catch (Exception Ex)
    {
        Response.Write( "ERROR: Unable to save data !!</br>" + Ex.Message);
    }

    Response.Write( "Data Saved Successfully!");
}

Is it right and safe?

Now, how to get the value OUTPUTfrom a stored procedure in asp.net (C #)?

Answer 1

string connectionString = ConfigurationManager.ConnectionStrings["ConStr"].ConnectionString;
string insertStoredProcName = "dbo.InsertNode";

using (SqlConnection conn = new SqlConnection(connectionString))
using (SqlCommand cmd = new SqlCommand(insertStoredProcName, conn))
{
    cmd.CommandType = CommandType.StoredProcedure;

    -- set up paramters - DO NOT USE AddWithValue !!        
    cmd.Parameters.Add("@FName", SqlDbType.VarChar, 100).Value = FName;
    cmd.Parameters.Add("@Lname", SqlDbType.VarChar, 100).Value = LName;
    cmd.Parameters.Add("@CDesc", SqlDbType.VarChar, 100).Value = cDesc;
    cmd.Parameters.Add("@ParentID", SqlDbType.Int).Value = pid;

    conn.Open();

    using(SqlDataReader rdr = cmd.ExecuteReader())
    {
        while (rdr.Read())
        {
            -- read all returned values - if you only ever insert
            -- one row at a time, there will only be one value to read
           int insertedId = rdr.GetInt32(0);
        }

        rdr.Close();
    }

    conn.Close();
}

Answer 2

OUTPUT will break if you decide to add a trigger to the table in the future. Also, if there are multiple inserts, the output returns your identifier for each newly inserted row.

ExecuteScalar, .

ALTER procedure [dbo].[InsertNode]
(
    @FName nvarchar(50),
    @Lname nvarchar(50),
    @CDesc nvarchar(max),
    @ParentID int
)
as
begin
    declare @KID int

    insert into Chart (FName , Lname ,CDesc, ParentID ) 
    values (@FName, @Lname, @CDesc, @ParentID)

    SELECT @KID = SCOPE_IDENTITY();
end

#:

int KID = Convert.ToInt32(cmd.ExecuteScalar());