How to read SELECT after INSERT in Dapper

Question

I'm new to dapper. I'm trying to put validation on my storedprocedures. Below is my Code:

USE [DapperDB]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[sp_blzr_Video_Insert]

            @Title varchar(128), 
            @DatePublished date, 
            @IsActive bit
AS
BEGIN
    SET NOCOUNT ON;
    IF EXISTS(SELECT Title FROM Video WHERE Title = @Title)
        BEGIN
            SELECT 0 as ReturnID, 'Title is already in the database. Please check your data entry' as ReturnMessage;
        END
    ELSE
        BEGIN
            INSERT INTO Video
            (   
                Title, 
                DatePublished, 
                IsActive
            )
            VALUES        
            (
                @Title, 
                @DatePublished, 
                @IsActive
            )

            SELECT 1 as ReturnID, 'Success' as ReturnMessage;
        END
END

Here is my c# Code. This is where I'm Stuck. I dont know how to consume the Select Statement after Insert.

//Add (Create/Insert)
public async Task VideoInsert(Video video)
{
    var result = new ResultFromSP();

    using (var conn = new SqlConnection(_configuration.Value))
    {
        var parameters = new DynamicParameters();
        parameters.Add("Title", video.Title, DbType.String);
        parameters.Add("DatePublished", video.DatePublished, DbType.DateTime);
        parameters.Add("isActive", video.isActive, DbType.Boolean);
        //Stored Proc
        await conn.ExecuteAsync("sp_blzr_Video_Insert", parameters, commandType:CommandType.StoredProcedure);
    }
}

I would like to consume ex: "'SELECT 1, 'Success!'"? In my application if the first column is 1, means that the insert is successful. After that I will make an alert that the insert is "Successful".

Answer 1

You can change the execute to

var output = await conn.QueryFirstAsync<dynamic>(“EXEC p_blzr_Video_Insert @Title, @DatePublished, @IsActive”, parameters);

var returnId = (int) output.ReturnID;
var returnMessage = (string) output.ReturnMessage;

This uses the query method to read the results of the stored procedure and a dynamic result so you don’t need to create a class.