Pass output parameters in a template object in Dapper

Question

I have stored procedures with input and output parameters. I have separate classes for input and output classes. So, I want to create Dynamic Parameters using template objects instead of adding each property.

For Eg:

public class StudentInput {
 public StudentInput() {
   this.StudentId = 1;
 }
 public int StudentId {get; set}
}

public class StudentOutput {
 public string StudentName {get; set;}
 public string FavSubject {get; set;}
 public int Grade {get; set;}
 public int YearOfJoining {get; set;}
}

I have a stored procedure [SP_GetStudentData] that takes StudentId as input and returns StudentName, FavSubject, Grade and YearOfJoining.

Using Dapper, I wrote the following code.

DynamicParameters ip = new DynamicParameters(new StudentInput());
DynamicParameters op = new DynamicParameters(new StudentOutput());

ip.AddDynamicParameters(op);

// Here is the question. How do I tell Dapper that op is a output parameters object? How do I add ParameterDirection output to each of the properties in this object?

SqlMapper.ExecuteSP(connection, SP_GetStudentData, ip, CommandType.StoredProcedure)

score 0 · Answer 1 · answered May 05 '18 at 17:16

Problem is the SQL Server can only return scalar object as output parameters. So you can't map an output parameter to your StudentOutput class. Just return the values you need as a resultset (using a SELECT statement inside the SP) and then map that resultset to your class.

var result = connection.Query("SP_GetStudentData @studentId", new { studentId = (new StudentInput()).StudentId }, commandType:StoredProcedure)

Pass output parameters in a template object in Dapper

1 Answers1