CODEWITHSUNDEEP
c#sqlitedapper
Laiteux
Laiteux

Reputation: 63

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.

Upvotes: 2

Views: 1583

Answers (2)

Ed Bangga
Ed Bangga

Reputation: 13006

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

Upvotes: 2

JReno
JReno

Reputation: 357

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

Upvotes: 1

Related Questions