user6774270 user6774270 - 1 month ago 6
ASP.NET (C#) Question

Can I display result of stored procedure in asp.net?

Here's my stored procedure, In which I'm inserting records in database and if record exists, it gets updated. So my question is can I get result like How many records are updated and how many records are inserted ?

// stored procedure

ALTER PROCEDURE [dbo].[Update_F3_BC_Column_Mapping]
@tblF3_BC_Column_Mapping F3_BC_Column_MappingType READONLY
AS
BEGIN
SET NOCOUNT ON;
MERGE INTO F3_BC_Column_Mapping c1
USING @tblF3_BC_Column_Mapping c2
ON c1.Id=c2.Id
WHEN MATCHED THEN
UPDATE SET c1.Source = c2.Source
,c1.Destination = c2.Destination
WHEN NOT MATCHED THEN
INSERT VALUES(c2.Id, c2.Source, c2.Destination);
END


//c# code

connection();
{
using (SqlCommand cmd = new SqlCommand("Update_F3_BC_Column_Mapping"))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Connection = con;
cmd.Parameters.AddWithValue("@tblF3_BC_Column_Mapping", dtMap);
con.Open();
cmd.ExecuteNonQuery();
con.Close();
}
}

Answer

To get 2 different numbers, inserted and updated, you can alter your stored procedure to allow a couple of output variables, and handle them as parameters in c#.

In the header of your procedure, add 2 output parameters:

ALTER PROCEDURE [dbo].[Update_F3_BC_Column_Mapping]
@tblF3_BC_Column_Mapping F3_BC_Column_MappingType READONLY,
@updated_count int output,
@inserted_count int output
AS

In the body, declare a table variable to hold the output from the merge;

BEGIN
    SET NOCOUNT ON;
    declare @outputtable table ([action] nvarchar(10), [count] int);

And then add a (last) line to the merge to fill this table:

    MERGE INTO F3_BC_Column_Mapping c1
    USING @tblF3_BC_Column_Mapping c2
        ON c1.Id=c2.Id
    WHEN MATCHED THEN
        UPDATE SET c1.Source = c2.Source
              ,c1.Destination = c2.Destination
    WHEN NOT MATCHED THEN
        INSERT VALUES(c2.Id, c2.Source, c2.Destination)
    OUTPUT $action , 1   INTO @outputtable;

Now you can populate your output variables by counting rows in this output table to end your stored procedure:

    select @updated_count = sum([count]) from @outputtable where [action] = 'UPDATE';
    select @inserted_count = sum([count]) from @outputtable where [action] = 'INSERT';
END

(All of the code rows posted so far make up your new stored procedure.)

You now need to add 2 additional parameters to you c# code:

connection();
{
    using (SqlCommand cmd = new SqlCommand("Update_F3_BC_Column_Mapping"))
    {
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Connection = con;
        cmd.Parameters.AddWithValue("@tblF3_BC_Column_Mapping", dtMap);

        cmd.Parameters.Add("@updated_count", SqlDbType.Int).Direction = ParameterDirection.Output;
        cmd.Parameters.Add("@inserted_count", SqlDbType.Int).Direction = ParameterDirection.Output;

And after you have run your query, you can access the outputs by using the value property:

        con.Open();
        cmd.ExecuteNonQuery();

        int updated = Convert.ToInt32(cmd.Parameters["@updated_count"].Value);
        int inserted = Convert.ToInt32(cmd.Parameters["@inserted_count"].Value);

and then finish off your code:

        con.Close();
    }
}