sqlite and sqlserver with dapper

Question

I'm using dapper with SQL Server, but using Sqlite for some in memory tests for my unit tests.

I have a class as follows

public class Test
{
  public int ID {get;set;}
  public string Name 
}

If I query using dapper with a SQL Server connection, it works fine.

If I query using dapper with a SQLite connection, I get a type cast error. Basically I need to change the type of ID to be a long, rather than int.

I don't want to change my schema in SQL Server, so I'm wondering if there is a workaround to the scenario. I've tried creating and ID class with implicit casts to long/int, but this also fails, and I'm out of ideas!

how did you define the sqlite table ? – Felice Pollano Jul 19 '12 at 16:19 — Felice Pollano, Jul 19 '12 at 16:19

score 0 · Answer 1 · edited May 23 '17 at 11:56

0

Try to create the sqlite table specifying the Lenght for INTEGER to 4 bytes. maybe this question would help too: What is the difference between SQLite integer data types like int, integer, bigint, etc.?

edited May 23 '17 at 11:56

Community

1
1

answered Jul 19 '12 at 16:21

Felice Pollano

32,832
9
75
115

score 0 · Answer 2 · answered Jul 20 '12 at 15:24

This revolves around the fact that Dapper uses the GetValue method on the IDataRecord interface. The concrete class in the System.Data.SqlLite namespace probably map values differently than that of System.Data.SqlClient.

As @FelicePollano pointed out try changing the structure of the SQLLite table and see if that causes the concrete class in System.Data.SqlLite to map it differently.

Another option is to roll your own mapping using the FastExpando object Dapper returns and force it to convert the value to a Int.

IEnumerable<dynamic> results = Conn.Query("Select ID from SomeTable");

var testValue = new Test
{
    ID = Convert.ToInt32(Results[0].ID.ToString())
};

Not really a big fan of it because it could require a lot of code changes, but it can work.

Please note: That example code can throw a exception if the ID column is not an integer. You can use TryParse, but for the sake of brevity I didn't include it.

sqlite and sqlserver with dapper

2 Answers2