1

I have been evaluating the use of dapper and the simplecrud extension with sqlite. No matter what I try when doing a table insert things fail with an exception

no such function SCOPE_IDENTITY

Table class

public class User
{
    public int Id { get; set; }
    public string Name { get; set; }
    public int Age { get; set; }
}

Simplest piece of code to test

static void Main( string[] args )
{
    SQLiteConnection conn = new SQLiteConnection( "Data Source=E:\\Databases\\MyDatabase.db;Version=3" );
    conn.Open();

    var usr = new User { Name = "Dave", Age = 65 };
    var id = conn.Insert(usr);
    conn.Close();
}

As indicated earlier when I run the code the data is inserted into the table but the program terminates with the SCOPE_IDENTITY exception.

Any assistance would be greatly appreciated

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
hypothesys
  • 11
  • 1
  • 1
  • `SCOPE_IDENTITY` is a **SQL Server** function (and therefore not available in SQLite, obviously) - no idea why does would be showing up in your case..... Is there anything with Dapper or the Dapper extension that defaults to SQL Server, and you have to change it to SQLite, maybe – marc_s Nov 10 '18 at 12:05
  • 1
    Marc_s, thanks your your reply. Not that I am aware of and have been unable to see anything in sample code on github, thus leading to extreme frustration. – hypothesys Nov 10 '18 at 12:20

2 Answers2

2

Judging from the Github page, it seems the current release has dropped support for SQLite:

Database support

There is an option to change database dialect. Default is Microsoft SQL Server but can be changed to PostgreSQL or MySQL. We dropped SQLite support with the .Net Core release.

SimpleCRUD.SetDialect(SimpleCRUD.Dialect.PostgreSQL);

SimpleCRUD.SetDialect(SimpleCRUD.Dialect.MySQL);

Depending on which version you have, you might be able to use a similar call to set the SQLite "dialect" (if it's still supported in your code base).

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
  • Marc_s, that would explain it. I had missed that comment in the release, but had been misguided by the fact that the test code on github still references sqlite as a provider. Thank you. – hypothesys Nov 10 '18 at 12:32
  • 1
    Marc_s, looks like we have some confusion here, looking through the history on github, sqlite support was removed as of version 2.0.0 but was re-added as of version 2.0.1 which is the current version on nuget and what I have been using. Maybe the re-adding is not quite right. – hypothesys Nov 10 '18 at 12:49
  • 1
    @hypothesys: ok, so the support is back in there - and are you calling `SimpleCRUD.SetDialect(Dialect.SQLite)` somewhere in your code? – marc_s Nov 10 '18 at 12:54
  • @hypothesys: if this answer helped you solve your problem, then please [**accept this answer**](http://meta.stackoverflow.com/q/5234/153998). This will show your appreciation for the people who *spent their own time to help you*. – marc_s Nov 10 '18 at 13:41
1

i was using dapper fastCrud i face the same issue. so add this line of code in my constructor.

 OrmConfiguration.DefaultDialect = SqlDialect.SqLite;
HMD
  • 2,202
  • 6
  • 24
  • 37
salman irshad
  • 11
  • 2