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

1. add the package for dapperextentions
2. Dapper extentions has a function called insert or update that could accept a model like Member object.
3. AutoMap(); will map all other properties as long as you have the same name for the field.

public class MemberMapper : DapperExtensions.Mapper.ClassMapper<Member>
    {
        public MemberMapper()
        {
           Table("TableName if diffrent than the Model calss name");
           Map(f => f.MemberInfo).Ignore();
           AutoMap();
        }
    }

Answer 2

[Table("studentclass")]
public partial class Studentclass
{
    public Studentclass()
    {
        Studentclasssyllabus = new HashSet<Studentclasssyllabus>();
    }

    public int Id { get; set; }
    public int Studentid { get; set; }
    public int Subjectclassid { get; set; }

    [Write(false)]
    [Computed]
    public virtual Student Student { get; set; }

    [Write(false)]
    [Computed]
    public virtual Subjectclass Subjectclass { get; set; }

    [Write(false)]
    [Computed]
    public virtual ICollection<Studentclasssyllabus> Studentclasssyllabus { get; set; 
}

and

[Table("schedule")]
public partial class Schedule
{
    public Schedule()
    {
        Attendance = new HashSet<Attendance>();
    }

    public int Id { get; set; }
    public int? Lecturerid { get; set; }
    public int? Subjectclassid { get; set; }
    public DateTime Date { get; set; }
    public TimeSpan Starttime { get; set; }
    public TimeSpan Endtime { get; set; }
    public int? Roomstatus { get; set; }
    public string Ownerjitsiid { get; set; }
    public string Generateroomname { get; set; }

    [Write(false)]
    [Computed]
    public virtual Lecturer Lecturer { get; set; }

    [Write(false)]
    [Computed]
    public virtual Subjectclass Subjectclass { get; set; }

    [Write(false)]
    [Computed]
    public virtual ICollection<Attendance> Attendance { get; set; }
}

}

when I set this tag "[Write(false)][Computed]" to model. when dapper update entities it will be passed that parameter

Answer 3

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);