Receive Select from Stored Procedure and get into an object in AspNet.Core

Question

I'm new to ASP.Net Core. Normally I send boolean from repository to execute stored procedure of Insert, Update or Delete:

public bool AddProduct(ProductInsertModel model)
{
    bool DoAddProduct()
    {
        try
        {
            using (var connection = _connectionManager.GetOpenConnection())
            {
                return connection.QueryFirstOrDefault<bool>("Product.usp_Product_Create",
                        param: model,
                        commandType: CommandType.StoredProcedure);
            }
       }
       catch (Exception ex)
       {
           return false;
       }
   }

   return DoAddProduct();
}

ProductInsert model (sent from repository):

public class ProductInsertModel
{
        public string Name { get; set; }
        public int BrandId { get; set; }
        public int SubcategoryId { get; set; }
        public int LanguageId { get; set; }
        public string ProductDetailName { get; set; }
        public string Description { get; set; }
        public string KeepMeSafeDescription { get; set; }
        public bool IsOneOfAKind { get; set; }
        public int Waist { get; set; }
        public int Hip { get; set; }
        public int Chest { get; set; }
        public int SkirtLong { get; set; }
        public decimal Price { get; set; }
        public DateTime CreationDate { get; set; }
        public DateTime ModifiedDate { get; set; }
        public string CreatedBy { get; set; }
        public string ModifiedBy { get; set; }
        public bool IsDeleted { get; set; }
}

Then I use ProductService to call the repository like this:

public bool Insert(ProductInsertModel model)
{
    bool DoInsert()
    {
        return _repo.AddProduct(model);
    }

    return DoInsert();
}

And finally in the controller, I call the ProductService:

public IActionResult Insert([FromBody]ProductInsertModel model)
{
        IActionResult DoInsert()
        {
            try
            {
                var rModel = _productService.Insert(model);
                return Ok(rModel);
            }
            catch (Exception ex)
            {
                return StatusCode(500, ex);
            }
        }

        return DoInsert();
}

Model:

public class ProductInsertModel
{
        public int ProductId { get; set; }
        public string Name { get; set; }
        public int ProductDetailId { get; set; }
        public int BrandId { get; set; }
        public int CategoryId { get; set; }
        public int SubcategoryId { get; set; }
        public int LanguageId { get; set; }
        public string ProductDetailName { get; set; }
        public string Description { get; set; }
        public string KeepMeSafeDescription { get; set; }
        public bool IsOneOfAKind { get; set; }
        public int Waist { get; set; }
        public int Hip { get; set; }
        public int Chest { get; set; }
        public int SkirtLong { get; set; }
        public decimal Price { get; set; }
        public DateTime CreationDate { get; set; }
        public DateTime ModifiedDate { get; set; }
        public string CreatedBy { get; set; }
        public string ModifiedBy { get; set; }
        public bool IsDeleted { get; set; }
        public int ProductPackageId { get; set; }
        public int PackageId { get; set; }
}

Stored procedure:

ALTER PROCEDURE [Product].[usp_Product_Create]
    @Name varchar(255) ,
    @BrandId int,
    @SubcategoryId int,
    @LanguageId int,
    @ProductDetailName varchar(255),
    @Description varchar(255),
    @KeepMeSafeDescription varchar(255),
    @IsOneOfAKind bit,
    @Waist int,
    @Hip int,
    @Chest int,
    @SkirtLong int,
    @Price decimal(19,2),
    @CreationDate datetime,
    @ModifiedDate datetime,
    @CreatedBy varchar(50),
    @ModifiedBy varchar(50),
    @IsDeleted bit
AS
BEGIN
    SET NOCOUNT ON;

    BEGIN TRY
    BEGIN TRANSACTION
        INSERT INTO Product.Product 
        VALUES (@Name, @BrandId, @SubcategoryId, @IsOneOfAKind);

        INSERT INTO Product.ProductDetail 
        VALUES (IDENT_CURRENT('Product.Product'), @LanguageId, @ProductDetailName,
                @Description, @KeepMeSafeDescription, @Waist, @Hip, @Chest,  
                @SkirtLong, @Price, @CreationDate, @ModifiedDate,
                @CreatedBy, @ModifiedBy, @IsDeleted);

        COMMIT TRANSACTION

        RETURN 1;
    END TRY
    BEGIN CATCH
        EXEC usp_Sql_Error_Codes 'usp_Product_Create'
        ROLLBACK TRANSACTION

        RETURN 0
    END CATCH
END

It works perfectly, now I want to know what I need to do to replace that add Product for a simple get action and receive items from SP into C# object. I have Get stored procedure like this:

ALTER PROCEDURE [User].[usp_Get_UserProfile]
    @UserId INT
AS
BEGIN
    SET NOCOUNT ON;

    BEGIN TRY
    BEGIN TRANSACTION
        SELECT   
            MCU.UserName, MCU.FirstName, MCU.MaidenName, MCU.LastName,
            MCU.Age, MCU.Avatar, MCU.Email, MCU.EmailConfirmed,
            MCU.PhoneNumber, MCU.ProfilePicture, MCU.UserName
        FROM 
            SecurityMc.McUsers AS MCU 
        WHERE 
            MCU.McUserId = @UserId 
        COMMIT TRANSACTION
    END TRY
    BEGIN CATCH
        EXEC usp_Sql_Error_Codes 'usp_Get_UserProfile'
        ROLLBACK TRANSACTION
    END CATCH
END

Answer 1

1. Create a Model (say UserProfileModel) with parameters you are sending from SP.

2. Write Method like this in Service to call SP and fill the model :-

   public IList<UserProfileModel> GetUserDetails(int UserId)
       {
           using (var connection = _connectionManager.GetOpenConnection())
           {
              List<UserProfileModel> data =  connection.SelectMany<UserProfileModel>("User.usp_Get_UserProfile",
                       param: UserId,
                       commandType: CommandType.StoredProcedure);
   
   return data;            
   }
   

3. Create / Inherit similar model UserProfileViewModel in presentation layer.

4. Fill this ViewModel by giving a call to service from controller.