.NET can't execute Update query for PostgresSQL database with Dapper

user_16958511 0
Viewed 34
using Authentication_clone.Models;
using Dapper;

namespace Authentication_clone.Db
{
    public class UsersRepo : IRepo<User>
    {
        private readonly IConfiguration _config;

        public UsersRepo(IConfiguration config)
        {
            _config = config;
        }

        public async Task<User> Add(User obj)
        {
            using var connection = Connection.GetPgConnection(_config);
            var res = await connection.ExecuteAsync(
                "INSERT INTO public.users(name, email, role, password) VALUES (@Name, @Email, @Role, @Password);", obj);
            return res == 0 ? null : obj;
        }

        public async Task<User?> GetById(int Id)
        {
            using var connection = Connection.GetPgConnection(_config);
            var users = await connection.QueryAsync<User>("SELECT * FROM public.users WHERE id = @Id;", new { Id });
            return users.FirstOrDefault();
        }

        public async Task<User?> GetByEmail(string email)
        {
            using var connection = Connection.GetPgConnection(_config);
            var res = await connection.QueryAsync<User>("SELECT * FROM public.users WHERE email = @email", new { email });
            return res.FirstOrDefault();
        }

        public async Task<User?> Update(int Id, User obj)
        {
            Console.WriteLine($"UPDATE public.users SET email='{obj.Email}', role={(int)obj.Role}, name='{obj.Name}', password='{obj.Password}' WHERE id={Id};");
            using var connection = Connection.GetPgConnection(_config);
            //AlwaysReturns null
            var res = await connection.ExecuteScalarAsync<User>(
                "UPDATE public.users SET email='@Email', role=@Role, name='@Name', password='@Password' WHERE id=@Id RETURNING*;",
                new { obj.Name, obj.Email, Role = (int)obj.Role, obj.Password, obj.Id });
            //Always returns 0
            //var res = await connection.ExecuteAsync(
            //    "UPDATE public.users SET email='@Email', role=@Role, name='@Name', password='@Password' WHERE id=@Id;",
            //    new { obj.Name, obj.Email, Role = (int)obj.Role, obj.Password, obj.Id });
            Console.WriteLine($"res = {res?.Name}");
            return res;
        }

        public async Task<User?> Delete(int Id)
        {
            using var connection = Connection.GetPgConnection (_config);
            var user = await GetById(Id);

            if (user == null)
            {
                return null;
            }

            await connection.ExecuteAsync("DELETE FROM public.users WHERE id=@Id;", new { Id });
            return user;
        }
    }
}

I have tried update a row with caple methods, but Dapper returns no errors, and doesn't update the data in the database. Interesting thing that (insert, delete, select) works perfectly, and Postgres users have all permissions.

I have tried to execute the same query directly in database, and the update was successful.

All data I pass as parameters exists! Checked))

dapper postgresql .net c#
1 Answers

Here is a very clean solution using dapper ORM with PostgreSQL.

    public interface IDapperHelper
    {
        Task<int> ExecuteCommandUpdateAsync(string sql, object _params);
    }

    public class DapperHelper : IDapperHelper
    {
        private readonly IOptions<ConnectionStrings> options;
        public DapperHelper(IOptions<ConnectionStrings> options)
        {
            this.options = options;
        }

        public async Task<int> ExecuteCommandUpdateAsync(string sql, object _params)
        {
            int record = 0;
            using (IDbConnection db = new NpgsqlConnection(options.Value.DefaultConnection))
            {
               record = await db.ExecuteAsync(sql, _params);
            }
            return record;
        }

    }


Public static class ConstantQueries
{
   public static string UpdateQuery = @"UPDATE public.users SET ""email""=@email', ""role""=@role, ""name""=@name, ""password""=@password WHERE ""id""=@id;";
}


public interface IServiceManager
{
  Task<int> UpdateAsync(YourModel model);
}

Public class ServiceManager : IServiceManager
{

        #region [Fields]
        private readonly IDapperHelper dapperHelper;

        #endregion
        #region [Ctor]
        public ServiceManager(IDapperHelper dapperHelper)
        {
            this.dapperHelper = dapperHelper;
        }
        #endregion


        public async Task<int> UpdateAsync(YourModel model)
        {
            int? record = 0;
            record = await dapperHelper.ExecuteCommandUpdateAsync(ConstantQueries.UpdateQuery, model);
            return record ?? 0;
        }

}

Follow this structure and your life will be much easier and you will also enjoy working with dapper ORM

user_49639960
Related