CODEWITHSUNDEEP
c#asp.net-web-api.net-coredapper
N0xus
N0xus

Reputation: 2724

Dapper SQL Query with 2 models

I'm developing a web api where I'm using Dapper to handle my SQL calls. First, I have the following 2 models:

Model 1 - This model also has a child model associated with it in the form of "Model 2"

public int COBID { get; set; }
public string ReportingPoint { get; set; }
public string Issuer { get; set; }
public string Group { get; set; }
public string ACCode { get; set; }
public string SourceSystemTradeID { get; set; }
public char SWWR { get; set; }
public string Error { get; set; }
public Model 2 TradeComment { get; set; }

Model 2

public int TradeCommentId { get; set; }
public string SourceSystemTradeID { get; set; }
public string SanctionerComment { get; set; }
public string SignOffBy { get; set; }
public bool CompletedFlag { get; set; }
public bool PreApprovedFlag { get; set; }
public bool GenuineWWRFlag { get; set; }
public DateTime CreatedDate { get; set; }
public string CreatedBy { get; set; }

For my Updatefunctionality I have a PROC which pulls all of the fields I need from across multiple tables. Due to a few issues, I cannot show this proc. However, the functionality of this proc is fine as it's been something that has been in use for a long time. What I'm building is going to sit on top of it.

At last, for my dapper call, I have the following method:

public IEnumerable<TradeDetail> GetRecordDetail(string id)
{
    using (var dbConnection = Connection)
    {
        dbConnection.Open();
         return dbConnection.Query<TradeDetail, TradeComment, TradeDetail>("wwr.pMyProc",
            (detail, comment) =>
            {
                 detail.SourceSystemTradeID = id;
                return detail;
             }, splitOn: "TradeCommentId", commandType: CommandType.StoredProcedure);

    }
}

And this method gets called by my API as follows:

[HttpPut]
public IActionResult Put(string key, string values)
{
    var record = _wwrRepository.GetRecordDetail(key);
    JsonConvert.PopulateObject(values, record);

    if (!TryValidateModel(record))
    {
        return BadRequest(ModelState.GetFullErrorMessage());
    }
    return Ok(record);
}

When I run test my code it tells me that an ID value is expected and that is must be passed in. I had thought I was doing that already with the following line: 

detail.SourceSystemTradeID = id;

Where my id is collected from my API and passed in to this method.

Could someone please help me format my code calls correctly?

Upvotes: 1

Views: 1354

Answers (1)

Steve
Steve

Reputation: 216293

It is your PROC that expect the ID not your field in the model. You need to pass it with

dbConnection.Open();
return dbConnection.Query<TradeDetail, TradeComment, TradeDetail>("wwr.pMyProc",
        (detail, comment) =>
        {
            detail.TradeComment = comment;
            return detail;
        }, 
        splitOn: "TradeCommentId", 
        param: new {id = id }, 
        commandType: CommandType.StoredProcedure);

 }

The exact name for the anonymous id in new {id = id} depends on the actual parameter name expected by your stored proc, also note that you need to assign the comment parameter received in the lambda expression to your TradeComment field in the TradeDetail instance

Upvotes: 2

Related Questions