CODEWITHSUNDEEP
c#asp.netsql-serverstored-procedures
Farzaneh Talebi
Farzaneh Talebi

Reputation: 915

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

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#) ?

Upvotes: 3

Views: 4828

Answers (2)

Nikhil Vartak
Nikhil Vartak

Reputation: 5117

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

Upvotes: -1

marc_s
marc_s

Reputation: 754258

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

Upvotes: 7

Related Questions