Dapper SQL Call Returning NULL and 0 values-CodePudding

Working with Dapper on making SQL calls and mapping them to objects. I am having an issue with SQL Dapper call not mapping correctly to C# Class.

Using ASP.NET Core API and have one controller and one class.

Controller Class

 public class DapperController : Controller
{

    private const string connectionString = @"";


    [HttpGet("")]
    public async Task<IActionResult> Index()
    {

        var sql = @"SELECT product_name, model_year,list_price
                FROM [production].[products]";

        using (SqlConnection connection = new SqlConnection(connectionString)) {
                connection.Open();

                var product = await connection.QueryAsync<Products>(sql);


                return Ok(product);

    }
    }
}

Products Class

public class Products
    {
        [JsonPropertyName("product_name")]
        public string ProductName { get; set; }

        [JsonPropertyName("model_year")]
        public int ModelYear { get; set; }

        [JsonPropertyName("list_price")]
        public double ListPrice { get; set; }

    }

Getting back the following data but that is not correct since the database does have data

[
  {
    "product_name": null,
    "model_year": 0,
    "list_price": 0
  },
  {
    "product_name": null,
    "model_year": 0,
    "list_price": 0
  }
]

When using the above without mapping to class I get the correct data. Not sure what I am doing wrong

[
  {
    "product_name": "Trek 820 - 2016",
    "model_year": 2016,
    "list_price": 379.99
  },
  {
    "product_name": "Ritchey Timberwolf Frameset - 2016",
    "model_year": 2016,
    "list_price": 749.99
  }
]

CodePudding user response：

Not sure what I am doing wrong

Because JsonPropertyName is for Json serialize instead of Dapper ORM

so that we might need to modify class property as below code or @Yong Shun as say modify the script alias name which aligns with your c# property (case sensitive)

public class Products
{
    public string product_name { get; set; }

    public int model_year { get; set; }

    public double list_price { get; set; }
}

I think we might need to use our customer as this link ColumnAttribute instead of JsonPropertyName

Our customer map needs to implement SqlMapper.ITypeMap

public class Products
{
    [Column("product_name")]
    public string ProductName { get; set; }

    [Column("model_year")]
    public int ModelYear { get; set; }

    [Column("list_price")]
    public double ListPrice { get; set; }

}

Also here is a link V2: [Column] and [Table] Attributes #722 which dapper official discusses Column mapping attributes for dapper.