c# - asp.net如何从存储过程中获取OUTPUT值

Question

我有这个存储过程，它在表中插入记录并使用 Output Inserted.KID 返回 IDENTITY 列值:

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# 代码:

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!");
}

这是正确的方法并且安全吗？

现在，如何从 asp.net(c#) 中的存储过程获取 OUTPUT 值？

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