Can you tell why DapperExtension's Update method is failing to execute?

Question

I'm using SQLite, Dapper and DapperExtensions, all the latest via NuGet.

Here's my table's schema...

CREATE TABLE `LibraryInfo` (
    `Id`    INTEGER NOT NULL,
    `Name`  TEXT NOT NULL CHECK(length ( Name ) > 0),
    `Description`   TEXT,
    `Version`   TEXT NOT NULL CHECK(length ( Version ) > 0),
    PRIMARY KEY(Id)
);

In it I have a single row with the following data

Id = "0"
Name = "Test Library"
Description = "This is the Test Library"
Version = "1.2.3.4"

Here's my POCO for that table...

public class LibraryInfo
{
    public int    Id          { get; set; }
    public string Name        { get; set; }
    public string Description { get; set; }
    public string Version     { get; set; }
}

Here's how I initialize DapperExtensions (my tables are plural except for this table)...

DapperExtensions.DapperExtensions.DefaultMapper = typeof(PluralizedAutoClassMapper<>);

public class LibraryInfoMapper : ClassMapper<LibraryInfo>
{
    public LibraryInfoMapper()
    {
        Table(nameof(LibraryInfo));
        AutoMap();
    }
}

Here's the code that lets me read that single row with the ID of 0 from the database...

var libraryInfo = connection.Get<LibraryInfo>(0);

That works fine. However, this fails...

libraryInfo.Description = "Test";
connection.Update(libraryInfo);

Here's the exception...

System.Data.SQLite.SQLiteException occurred ErrorCode=1 HResult=-2147467259 Message=SQL logic error or missing database near ".": syntax error Source=System.Data.SQLite StackTrace: at System.Data.SQLite.SQLite3.Prepare(SQLiteConnection cnn, String strSql, SQLiteStatement previous, UInt32 timeoutMS, String& strRemain) InnerException:

No idea why! I'm trying to figure out how to see what SQL it's generating but I don't know how to do that, or even if it's possible.

score 9 · Accepted Answer · answered Jul 09 '16 at 16:59

9

The issue is the default dialect for SQL generation within DapperExtensions is SqlServer which means the SQL generated internally is created with table aliases, which Sqlite doesn't support. You need to change the Dapper Extensions SQL dialect to Sqlite before you issue any commands:

DapperExtensions.DapperExtensions.SqlDialect = new DapperExtensions.Sql.SqliteDialect();

Once this is changed Dapper Extensions will create SQL commands in Sqlite format.

answered Jul 09 '16 at 16:59

G Davison

1,079
1
14
21

Man, if I could give you ten checkmarks for this, I would! Not a single person I know who uses any of this knew how to deal with this. Even here there weren't any comments. Thought I was going to have to abandon DapperExtensions, but I added your line to my initialization and now it works exactly as expected. You, sir, rock! – Mark A. Donohoe Jul 10 '16 at 15:19
Bonus question though... is it possible to view what SQL Dx is generating so you can see what's going on? Is there any mechanism that you can use to say 'Hey... if I were to execute this code, what SQL would you use?' I was about to start tearing through their code and adding a ton of logging and such if not. – Mark A. Donohoe Jul 10 '16 at 15:20
Just a shoot in the dark, but when you run you app in debug mode, is there someting in output windows ? read also : http://stackoverflow.com/questions/18529965/is-there-any-way-to-trace-log-the-sql-using-dapper. I get dapper from Nuget but DapperExtensions from source code, so it is easy to debug – Olivier Citeau Jul 11 '16 at 08:19
When you grab the Dapper Extensions source, you can step into it when debugging your code. Within there it does assemble a SQL string from the SqlGenerator class. It's around that point where you can inspect the SQL. – G Davison Jul 11 '16 at 09:16
2

Something else that threw me - if you use the newer async versions of the extension methods, you need to do: `DapperAsyncExtensions.SqlDialect = new SqliteDialect();` – Daniel Smith Oct 03 '17 at 12:30

Can you tell why DapperExtension's Update method is failing to execute?

1 Answers1