SCOPE_IDENTITY Exception

Question

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

Answer 1

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).

Answer 2

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

 OrmConfiguration.DefaultDialect = SqlDialect.SqLite;