Dapper.Contrib + MS Access: Error - Characters found after the end of the SQL statement

Question

I am using Dapper ORM with the Contrib package. The SELECT query works perfectly but my problem is when I try to INSERT data.

Visual Studio 2017 returns this message:

Characters found after the end of the SQL statement

The basic query executed with Dapper (no Dapper.Contrib) works fine. But I need the last inserted id from the database.

The code of the function to insert data in a MS Access 2007 database:

public string AddCustomer(string lastName, string firstName)
{
    using (var connection = new OleDbConnection(connectionString))
    {
        try
        {
            connection.Open();

            // Inserts data into the database.
            var insertion = connection.Insert(
                new Customer { Customer_lastName = LastNameManipulation(lastName), Customer_firstName = FirstNameManipulation(firstName) }
            );

            // Defines new customer.
            Customer customer = new Customer
            {
                Customer_id = Convert.ToInt32(insertion),
                Customer_lastName = LastNameManipulation(lastName),
                Customer_firstName = FirstNameManipulation(firstName)
            };

            // Insertion into data List.
            data.AddCustomer(customer);

            message = "Customer added with success.";
        }
        catch (Exception e)
        {
            message = e.Message.ToString();
        }
        finally
        {
            connection.Close();
        }

        return message;
    }
}

The class Customer :

using System;
using Dapper.Contrib.Extensions;

namespace DataLibrary
{
    [Serializable]
    [Table("Customer")]
    public class Customer
    {
        [Key]
        [Computed]
        public int Customer_id { get; set; }

        [Write(true)]
        public string Customer_lastName { get; set; }

        [Write(true)]
        public string Customer_firstName { get; set; }
    }
}

Answer 1

You are using Dapper.Contrib and your database is MS Access. Your INSERT call is generating two SQL queries. First, as you expect, to insert record. Second is behind the screen to fetch the newly generated id.

So, generated queries looks like something like this:

INSERT INTO Table (......) VALUES (....);
SELECT @@IDENTITY";

Both these queries are executed in single round trip. And this is not supported by MS Access. MS Access do not understand the characters after semi colon i.e. SELECT @@IDENTITY";.

Please refer this link.

The Jet database engine does not support the execution of multiple statements in a batch or the use of output parameters, so it is not possible to use either of these techniques to return the new Autonumber value assigned to an inserted row.

Solution is to separately execute those two queries OR do not execute second query; handle it differently. BUT, you are using Contrib, and it generates the query for you. So, there is very less (modify Contrib code yourself) you can do here.

Frankly, I do not know the solution for this. I never used Dapper.Contrib. May be this is fixed in newer version. Or may be that Contrib do not support MS Access. I just tried to explain the problem to you. Please refer this other answer which discusses same problem but with Dapper Extensions instead.