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

Question

I have this stored procedure that inserted record in table and return IDENTITY column value by using 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 this the correct way and safe?

Now, how to get OUTPUT value from stored procedure in asp.net(c#) ?

You need to use .ExecuteReader() (instead of .ExecuteNonQuery()) on your SqlCommand object since executing this stored procedure now returns a result set — marc_s
– marc_s, Commented Nov 24, 2015 at 11:13
You should check out Can we stop using AddWithValue() already? and stop using .AddWithValue() - it can lead to unexpected and surprising results... — marc_s
– marc_s, Commented Nov 24, 2015 at 11:18

marc_s · Accepted Answer · 2015-11-24 11:18:11Z

7

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

answered Nov 24, 2015 at 11:18

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Izzy Over a year ago

Just curious to know, I thought wrapping the connection inside using closes and disposes object itself. However you're still make a call to conn.Close()

marc_s Over a year ago

@Izzy: yes - it does - and I still prefer to be explicit about closing. Yes - the using block will dispose of the connection and close it in the process - I just use that as a backup and not as my main way of closing a connection (and the same applies to the SqlDataReader, too) - I use using as a safety net only - not the "normal" way of closing a reader or connection

Izzy Over a year ago

Ahh right, I get you..You got me worried a little there as I always let the using block dispose the connection for me

Nikhil Vartak · Accepted Answer · 2015-11-24 11:19:41Z

-1

OUTPUT will break if you decide to add a trigger in table in future. Also if there are multiple inserts, output will return you ID of each newly inserted row.

Alternatively You can use ExecuteScalar if it's a single insert operation.

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

C#:

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

answered Nov 24, 2015 at 11:19

Nikhil Vartak

5,1473 gold badges29 silver badges36 bronze badges

2 Comments

marc_s Over a year ago

If you assign the SCOPE_IDENTITY to a local variable - the result set will not be returned to the caller and therefore cannot be read - this is NOT going to work! Also: using ExecuteScalar() will break if you ever change this to handle multiple rows being inserted at once - OUTPUT will still work

Nikhil Vartak Over a year ago

@marc_s Original question shows there is only one insert.

Collectives™ on Stack Overflow

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

2 Answers 2

3 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related