Dapper

If yo do not want to create DTE for some small request you may use tuples like so:

var val = connection.QuerySingle<(int id, string name)>("select 42, 'Fred'");

Assert.Equal(42, val.id);
Assert.Equal("Fred", val.name);

XML

TODO: check inputs

Type mappers & handlers

Dapper List map to comma separated string

Often, store procedures accepts comma separated numbers as input, for you to not bother each time, there is such trick

// https://medium.com/dapper-net/custom-type-handling-4b447b97c620
// SqlMapper.AddTypeHandler(NumericListTypeHandler.Default); // <- add me to Startup
public class NumericListTypeHandler : SqlMapper.TypeHandler<List<int>>
{
    public static readonly NumericListTypeHandler Default = new NumericListTypeHandler();

    public override List<int> Parse(object value)
    {
        return ((value as string) ?? "").Split(',').Select(item => int.Parse(item.Trim())).ToList();
    }

    public override void SetValue(IDbDataParameter parameter, List<int> value)
    {
        parameter.Value = string.Join(",", value);
    }
}

Dapper mapping

If you are receiving City_Name from database, and in models we have CityName, for you to not bother with mapper, Dapper has column mapper for this already

dotnet add package Dapper
dotnet add package Dapper.ColumnMapper

https://github.com/dturkenk/Dapper.ColumnMapper

In Startup:

// Trick 1: will map dataset (ID, person_name, foo) -> c# {Id, PersonName, Foo}
Dapper.DefaultTypeMap.MatchNamesWithUnderscores = true;

// Trick 2: with ColumnMapping attribute you can "rename" columns
ColumnTypeMapper.RegisterForTypes(typeof(MyOutput) /*, typeof(Baz)*/);

// Trick 3: map List<int> to comma separated string
SqlMapper.AddTypeHandler(NumericListTypeHandler.Default);

Demo

SQL

-- DROP PROCEDURE spAdmin5Api_Temp

IF OBJECT_ID('spAdmin5Api_Temp') IS NULL
    EXEC('CREATE PROCEDURE spAdmin5Api_Temp AS SET NOCOUNT ON;')
GO

ALTER PROCEDURE spAdmin5Api_Temp (
    @Ids VARCHAR(MAX) -- Note: comma separated list of integers
) AS BEGIN
    SELECT
    1 AS ID, -- Note: naming uppercased
    2 AS person_id, -- Note: naming snake_case
    @Ids AS bar -- foo -- Note: changed for custom mapping
END
GO

EXEC spAdmin5Api_Temp '1,2,3'
GO

dotnet

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using Dapper;
using Dapper.ColumnMapper;
using DapperArrayParam.Code;
using DapperArrayParam.Models;
using Newtonsoft.Json;

namespace DapperArrayParam
{
    public static class Program
    {
        public static void Main(string[] args)
        {
            Dapper.DefaultTypeMap.MatchNamesWithUnderscores = true;

			ColumnTypeMapper.RegisterForTypes(typeof(MyOutput) /*, typeof(Baz)*/);

            SqlMapper.AddTypeHandler(NumericListTypeHandler.Default);

            var con = new SqlConnection("Server=beta.rabota.ua;Database=RabotaUA2;User ID=sa;Password=rabota;Persist Security Info=true;MultipleActiveResultSets=true");

            var row = con.Query<MyOutput>("spAdmin5Api_Temp", new MyInput { Ids = new List<int> { 1, 2 } }, commandType: CommandType.StoredProcedure);

            Print(row);
        }

        public static void Print(object obj) => Console.WriteLine(JsonConvert.SerializeObject(obj, Formatting.Indented));
    }
}

namespace DapperArrayParam.Models
{
    public class MyInput
    {
        public List<int> Ids { get; set; } = new List<int>();
    }

    public class MyOutput
    {
        public int Id { get; set; }
        public int PersonId { get; set; }
        [ColumnMapping("Bar")]
        public string Foo { get; set; }
    }
}

namespace DapperArrayParam.Code
{
    // https://medium.com/dapper-net/custom-type-handling-4b447b97c620
    // SqlMapper.AddTypeHandler(NumericListTypeHandler.Default);
    public class NumericListTypeHandler : SqlMapper.TypeHandler<List<int>>
    {
        public static readonly NumericListTypeHandler Default = new NumericListTypeHandler();

        public override List<int> Parse(object value)
        {
            return ((value as string) ?? "").Split(',').Select(item => int.Parse(item.Trim())).ToList();
        }

        public override void SetValue(IDbDataParameter parameter, List<int> value)
        {
            parameter.Value = string.Join(",", value);
        }
    }
}