When calling a T-SQL stored procedure with Dapper do I need to use '@'?

c# dapper stored-procedures tsql

Question

I am want to call a stored procedure with Dapper and all the code I've seen so far does this by specifying the stored procedure parameters with the @ character. This means that I cannot simply define a model class and pass it into Dapper's Query or Execute method with re-translating the model instance, which seems like a waste of time and memory. Is this what I really need to do?

For example, the code below accepts a model instance. The model has all the properties of the sp_GetUser table. I would expect Dapper to pass those model properties into the stored procedure's parameters when calling it. Is this the case or does I really need to define the object being passed in the 'param' parameter?

    public IList<User> GetUsers(UserSP user)
    {
        using (var cn = new SqlConnection(ConnectionString))
        {
            var users = cn.Query<User>("sp_GetUsers",
                    param: new
                    {
                        @Id = user.Id,
                        @NAme = user.Name,
                        @Age = user.Age
                    },
                    commandType: CommandType.StoredProcedure).ToList();
            return users;
        }
    }

Popular Answer

Try something like this

the class

 public class ReportIndex
    {
        public int SlideNumber { get; set; }
        public string ChartName { get; set; }
        public string SheetName { get; set; }
    }

fill the class with dapper

public List<ReportIndex> GetReportIndex(int reportId)
    {
        List<ReportIndex> reportIndex = null;

        using (var conn = new SqlConnection(connString))
        {
            conn.Open();

            var p = new DynamicParameters();
            p.Add("@ReportId", reportId);

            reportIndex = conn.Query<ReportIndex>("sp_ReportGetReportIndex",
                                        p,
                                        commandType: CommandType.StoredProcedure).ToList();
        }

        return reportIndex;
    }


Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow