Dapper: "Insufficient parameters supplied to the command"

Question

Dapper: "Insufficient parameters supplied to the command"

I have a Test model class:

public class Test
{
    public string One;
    public int Two;
}

I have a test table:

CREATE TABLE "test" 
(
    "one"   TEXT NOT NULL,
    "two"   INTEGER NOT NULL
);

When trying to execute this code:

using (IDbConnection con = new SQLiteConnection(ConfigurationManager.ConnectionStrings["database"].ConnectionString))
{
    con.Execute("INSERT INTO test VALUES (@One, @Two)", new Test
    {
        One = "hello",
        Two = 123
    });
}

I am getting this error:

code = Unknown (-1), message = System.Data.SQLite.SQLiteException (0x80004005): unknown error
Insufficient parameters supplied to the command

I tried everything and couldn't find why.

c#

sqlite

dapper

asked on Stack Overflow Sep 13, 2019 by

Laiteux • edited Sep 13, 2019 by

marc_s

2 Answers

Dapper requires command parameters as "Anonymous", "string", "List" and "dynamic" for .execute() command, thus passing typed object is not supported

using (IDbConnection con = new SQLiteConnection(ConfigurationManager.ConnectionStrings["database"].ConnectionString))
{
    con.Execute("INSERT INTO test (one, two) VALUES (@One, @Two)", new 
    {
        One = "hello",
        Two = 123
    });
}

using your test object.

using (IDbConnection con = new SQLiteConnection(ConfigurationManager.ConnectionStrings["database"].ConnectionString))
{
    Test tobj = new Test();
    tobj.One = "hello";
    tobj.Two = 123;

    con.Execute("INSERT INTO test (one, two) VALUES (@One, @Two)", tobj);
}

answered on Stack Overflow Sep 13, 2019 by

ϻᴇᴛᴀʟ • edited Sep 13, 2019 by

ϻᴇᴛᴀʟ

Dapper doesn't know how to break down your class into two variables. See https://github.com/StackExchange/Dapper/issues/540. You can either use 1 parameter in your Insert statement and pass the class in, or 2 parameters and pass individual params as below.

        DynamicParameters parameters = new DynamicParameters();
        parameters.Add("One", Test.One, DbType.String, ParameterDirection.Input);
        parameters.Add("Two", Test.Two, DbType.Int32, ParameterDirection.Input);

        using (IDbConnection con = new SQLiteConnection(ConfigurationManager.ConnectionStrings["database"].ConnectionString))
        {
            con.Execute("INSERT INTO test VALUES (@One, @Two)", parameters);
        }

answered on Stack Overflow Sep 13, 2019 by

JReno

User contributions licensed under CC BY-SA 3.0