Dapper with SQLite and C# Error "Insufficient parameters supplied to the command"

Question

New Information since the original post. This code successfully retrieves an entity, which makes me think QueryMultiple maps differently than Query. Is that true?

 using (var multi = sqlConn.QueryMultiple(sqlStmt, new { MytableId = mytableinstance.MytableId }))
 {
    var fullEnt = multi.Read<MytableSource>();
 }

What I want to do is use Dapper to query a child table based on the FK column value of the parent, with SQLite as the database. The below code gives me the error

Insufficient parameters supplied to the command

Could anyone point me to my error?

Note - I am also using Dapper.Contrib. .NET Framework 4.7.2.

Thanks.

PRAGMA foreign_keys = '1';

CREATE TABLE "Mytable" (
    "MytableId" INTEGER Primary Key AutoIncrement,
    "MytableName"   TEXT UNIQUE,
    "Stamp" TEXT 
);


CREATE TABLE "MytableSource" (
    "MytableSourceId"   INTEGER Primary Key AutoIncrement,
    "MytableId" INTEGER,
    "SourceBlob"    BLOB,
    "Stamp" TEXT,
    FOREIGN KEY("MytableId") REFERENCES "Mytable"("MytableId")

);

    [Table("Mytable")]
    public class Mytable
    {
        [Key]
        public long MytableId { get; set; }

        public String MytableName { get; set; }

        public String Stamp { get; set; }
    }

    [Table("MytableSource")]
    public class MytableSource
    {
        [Key]
        public long MytableSourceId { get; set; }

        public long MytableId { get; set; }

        public String SourceBlob { get; set; }

        public String Stamp { get; set; }
    }

    var sqlStmt = "Select * From MytableSource Where MytableId = @MytableId";
                var sqlConn = new SQLiteConnection( this.ConnectionString );
                using ( sqlConn )
                {
                    sqlConn.Open();
                    var fullEnt = sqlConn.Query<MytableSource>(sqlStmt, new SQLiteParameter("@MytableId" , mytableinstance.MytableId )).FirstOrDefault();
                    this.MytableSourceCurrent = fullEnt;

                }

unknown error Insufficient parameters supplied to the command

@LasseVågsætherKarlsen I tried that (removing the @) and got the same result. Thanks. — Snowy
What about .Query<MytableSource>(sqlStmt, new { MytableId = mytableinstance.MytableId }) ? — Lasse V. Karlsen
Although the example in the question looks fine, my issue was due to the Dapper limitation mentioned at link SQLite parameter names are case-sensitive. Ensure that the parameter names used in SQL match the case of the anonymous object's properties. My class property name's case was different from the parameter's case. — Jannes

C Perkins C Perkins · Accepted Answer · 2019-06-29T01:08:51

I looked at the original code from GitHub, searched for Query< T > and manually traced through only a few methods. Although it's highly abstract and documentation is sparse, the code itself at least reveals that an IEnumerable object is expected. It seems to prefer the Dapper.IDynamicParameters object for the collection, but beyond that it wasn't worth trudging through the code to get more details.

This worked for me in testing:

var parameters = new DynamicParameters();
param.Add("@MytableId" , mytableinstance.MytableId);
var fullEnt = sqlConn.Query<MytableSource>(sqlStmt, parameters ).FirstOrDefault();

Dapper with SQLite and C# Error "Insufficient parameters supplied to the command"

1 Answers