Dapper extension insert data into SQL Server

Question

Database table:

CREATE TABLE [dbo].[EmployeeDetails]
(
    [id] [int] PRIMARY KEY IDENTITY(1,1) NOT NULL,
    [Name] [varchar](max) NULL,
    [Emailid] [varchar](max) NULL,
    [department] [varchar](max) NULL,
    [Salary] [int] NULL,
    [Profile] [varchar](max) NULL,
    [Description] [varchar](max) NULL,
    [ReferencesId] [int] NULL
)

Model class:

public class Employee
{
    #region properties
    public int Id { get; set; }
    public string? Name { get; set; }
    public string? Emailid { get; set; }
    public string? department { get; set; }

    public int salary { get; set; }

    public string? Profile { get; set; }
    public string? Description { get; set; }
    #endregion
}

ClassMappper

public class EmployeeMapper : ClassMapper<Employee>
{
    public EmployeeMapper() 
    {
        Table("EmployeeDetails");

        Map(x => x.Id).Key(KeyType.Identity).Column("Id").Type(System.Data.DbType.Int32);           
        Map(x => x.Name).Column("Name");
        Map(x => x.Emailid).Column("Emailid");
        Map(x => x.department).Column("department");
        Map(x => x.salary).Column("Salary");
        Map(x => x.Profile).Column("Profile");
        Map(x => x.Description).Column("Description");
    }
}

Insert code:

public int Add(T entity)
{
    int count = 0;

    using (var con = _dapperContext.CreateConnection())
    {
        int id = con.Insert(entity);

        if (id > 0)
        {
            count++;
        }
    }

    return count;
}

I get this error:

System.ArgumentException: 'Object of type 'System.Int64' cannot be converted to type 'System.Int32'.'

I show this exception on the page but data is inserted in database successfully. My database datatype and model class datatypes both are the same, but I still get this "Conversion Error" on the page

I need insert one new row into the database table using Dapper Extension in ASP.NET Core Razor page

enter image description here

Answer 1

I show this exception on the page but data is inserted in database successfully. My database datatype and model class datatypes both are the same, but I still get this "Conversion Error" on the page

Well, at the beginning I was thinking you are doing something silly. Out of my curiosity, I get into it and got to know issue is not within your code its Dapper-Extensions issue. I have investigate quite a long while with their document and seems its older version and currently they are not supporting it. I have gone through this official document.

Issue Reproduced:

I have have reproduced the issue which has been thrown from Dapper-Extensions method as can be seen below:

Dapper Extension Method:

Exception:

Stacktrace:

at System.RuntimeType.TryChangeType(Object value, Binder binder, CultureInfo culture, Boolean needsSpecialCast)
   at System.Reflection.MethodBase.CheckArguments(Object[] parameters, Binder binder, BindingFlags invokeAttr, CultureInfo culture, Signature sig)
   at System.Reflection.RuntimeMethodInfo.InvokeArgumentsCheck(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture)
   at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture)
   at System.Reflection.RuntimePropertyInfo.SetValue(Object obj, Object value, BindingFlags invokeAttr, Binder binder, Object[] index, CultureInfo culture)
   at System.Reflection.RuntimePropertyInfo.SetValue(Object obj, Object value, Object[] index)
   at DapperExtensions.DapperImplementor.InternalInsert[T](IDbConnection connection, T entity, IDbTransaction transaction, Nullable`1 commandTimeout, IClassMapper classMap, IList`1 nonIdentityKeyProperties, IMemberMap identityColumn, IMemberMap triggerIdentityColumn, IList`1 sequenceIdentityColumn)
   at DapperExtensions.DapperImplementor.Insert[T](IDbConnection connection, T entity, IDbTransaction transaction, Nullable`1 commandTimeout)
   at MVCApps.Controllers.UserLogController.Add() in D:\MyComputer\MsPartnerSupport\MVCApps\Controllers\UserLogController.cs:line 1371
   at Microsoft.Extensions.Internal.ObjectMethodExecutor.Execute(Object target, Object[] parameters)
   at Microsoft.AspNetCore.Mvc.Internal.ActionMethodExecutor.SyncObjectResultExecutor.Execute(IActionResultTypeMapper mapper, ObjectMethodExecutor executor, Object controller, Object[] arguments)
   at Microsoft.AspNetCore.Mvc.Internal.ControllerActionInvoker.<InvokeActionMethodAsync>d__12.MoveNext()

Note:

While debugging and reproduce the issue, I have tried to use data type both int in both side as well as other data type for instance, bigint but still that conversion error taken place and I found this issue is known in regards of Dapper-Extensions. Thus, one data-type works out accordingly that is GUID. I still not sure, how these Object value, Binder binder, CultureInfo culture, Boolean needsSpecialCast are Intervening here.

Solution-Work Around:

As of now, int, bigint always throws 'Object of type 'System.Int64' cannot be converted to type 'System.Int32'.' hence, the reason is not clear which coming from DapperExtensions.DapperExtensions.Insert() method.

Furthermore, to avoid above error, we can do as following:

POCO Model:

public class Employee
    {

        public Guid Id { get; set; }
        public string Name { get; set; }
        public string Emailid { get; set; }
        public string Department { get; set; }
        public int Salary { get; set; }
    }

Note:

Other than Guid currently not working, I am not sure the reason behind this. So if you would like to stick to your implementation, you could consider Guid.

Database Schema:

CREATE TABLE Employee(
      [Id] UNIQUEIDENTIFIER PRIMARY KEY DEFAULT NEWID(),
      [Name] [varchar](25) NULL,
      [Emailid] [varchar](100) NULL,
      [Department] [varchar](25) NULL,
      [Salary] [int] NULL,
)

Asp.Net Core Controller/Repository Method:

public int Add()
        {
            int count = 0;

            Employee employee = new Employee();
            employee.Name = "Kudiya";
            employee.Emailid = "[email protected]";
            employee.Department = "Dapper";
            employee.Salary = 500;
            using (SqlConnection conn = new SqlConnection(_connectionString))
            {
                conn.Insert(employee);
                Guid Id = employee.Id;
               
                if (Id != null)
                {
                    count++;
                }
            }
            return count;
        }

Note: Please update the code snippet based on your scenario and context.

Output:

Note: Moreover, based on the stacktrace, if someone has the exact explnation or findings, please do let me know. I wound happy to update my answer.

Answer 2

Model:

 [Table("ClientAccount")]
    public class ClientAccount
    {
        public long ID { get; set; }
    }

Repository:

 using (IDbConnection conn = GetConnection())
   {              
    var result = await conn.InsertAsync<ClientAccount>(clientAccount);                    
   }

The above worked for me. instead of int, I used long for ID column and in the repository, getting result as var not as int.