CODEWITHSUNDEEP
c#sqlitedappersystem.data.sqlite
Snowy
Snowy

Reputation: 6122

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

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

Upvotes: 3

Views: 1620

Answers (1)

C Perkins
C Perkins

Reputation: 3886

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

Upvotes: 2

Related Questions