Dapper cannot pass the data to the database

Question

I am having a problem where I cannot insert nor update to the database. I am using Dapper and using nested class for the model that I want to pass the data on.

I am having an error which is:

The member Information of type TestProject.Models.Information cannot be used as a parameter value

My model is below:

public class Member
{
    [Required]
    public string Username { get; set; }
    [Required]
    public string Password { get; set; }
    [Required]
    public string Information MemberInfo { get; set; }
}

public class Information
{
    [Required]
    public string Email { get; set; }
    [Required]
    public string City { get; set; }
    [Required]
    public string State { get; set; }
    [Required]
    public DateTime BirthDate { get; set; }
}

And my controller is below:

public IHttpActionResult SetMemberInfo(Member models)
{
    using (TransactionScope trans = new TransactionScope())
    using (IDbConnection conn = new SqlConnection(GetConnection()))
    {
        conn.Open();

        int rowsAffected = conn.Execute("MemberInformation", models, commandType: CommandType.StoredProcedure);

        trans.Complete();

        return rowsAffected;
    }

    return Ok();
}

My stored procedure is below:

ALTER PROCEDURE dbo.[MemberInformation]
(
    @Username   NVARCHAR(100),
    @Password   NVARCHAR(100),
    @Email      NVARCHAR(100),
    @City       NVARCHAR(100),
    @State      NVARCHAR(100),
    @BirthDate  DATETIME
)
AS
BEGIN
 // TODO: Do something with the data
END

And I pass the data to the controller from client side is below:

var models = {
    "Username": "MyUsername",
    "Password": "MyPassword",
    "MemberInfo": {
        "Email": "MyEmail",
        "City": "MyCity",
        "State": "MyState",
        "BirthDate": "2017-08-23"
    }
};

$.ajax({
                type: "POST",
                contentType: "application/json",
                url: "http://localhost/SetMemberInfo",
                data: JSON.stringify(models),
                dataType: "json",
                success: function (data) {
                    window.location.reload();
                },
                error: function (response) {
                    console.log(JSON.stringify(response));
                }
            });

But if I didn't make it into nested class (means all data in the Information class moved to the Member class), everything works fine.

How can I tell dapper to separate the data from nested class and break it into a single class instead, even though in the application level is nested class.

Or is there any other solutions for this?

Your answer much appreciated.

Thanks

Answer 1

Well I don't think Dapper has this kind of support for nested objects as parameters.

What you can try is:

1. Create a DTO exactly as your stored procedure expects and map your Member object to this new type.

public class MemberInformationParameter
{
    public MemberInformationParameter(Member member)
    {
            Username = member.Username;
            Password = member.Password;
            Email = member.MemberInfo.Email;
            City = member.MemberInfo.City;
            State = member.MemberInfo.State;
            BirthDate = member.MemberInfo.BirthDate;
    }

    public string Username { get; set; }
    public string Password { get; set; }
    public string Email { get; set; }
    public string City { get; set; }
    public string State { get; set; }
    public DateTime BirthDate { get; set; }
}

2. Or just pass the arguments as a dynamic object with the syntax:

   conn.Execute("MemberInformation", new { Property1: value1, Property2: value2 }, commandType: CommandType.StoredProcedure);