Class For Working With Database (Postgres Example)

PHOTO EMBED

Mon Aug 22 2022 11:29:45 GMT+0000 (Coordinated Universal Time)

Saved by @HristoT #c# 

public class PGSQL
    {
        private NpgsqlConnection Connection;
        private NpgsqlTransaction Transaction;

        public PGSQL(string connectionString = null)
        {
            this.Connection = new NpgsqlConnection(connectionString);
        }

        ~PGSQL()
        {
            this.Dispose();
        }

        public async Task BeginTransactionAsync()
        {
            if (this.Connection.State != ConnectionState.Open)
            {
                await this.Connection.OpenAsync();
            }

            this.Transaction = this.Connection.BeginTransaction();
        }

        public async Task CommitTransactionAsync()
        {
            await this.Transaction?.CommitAsync();
        }

        public async Task RollbackTransactionAsync()
        {
            await this.Transaction?.RollbackAsync();
        }

        public IDbDataParameter Parameter(string name, object value, NpgsqlDbType? type = null)
        {
            var prm = new NpgsqlParameter(name, value);
            if (type.HasValue)
            {
                prm.NpgsqlDbType = type.Value;
            }

            if (value == null)
            {
                prm.Value = DBNull.Value;
            }

            return prm;
        }

        public async Task<DataTable> ExecuteDataTableAsync(string sql, params IDbDataParameter[] parameters)
        {
            using (var cmd = new NpgsqlCommand(sql, this.Connection))
            using (var adp = new NpgsqlDataAdapter(cmd))
            {
                cmd.Transaction = this.Transaction;
                if (this.Connection.State != ConnectionState.Open)
                {
                    await this.Connection.OpenAsync();
                }
                if (parameters.Length > 0)
                {
                    cmd.Parameters.AddRange(parameters);
                }

                var data = new DataTable("data");
                adp.Fill(data);
                return data;
            }
        }

        public async Task<T> ExecuteObjectAsync<T>(string sql, params IDbDataParameter[] parameters)
        {
            if (this.Connection.State != ConnectionState.Open)
            {
                await this.Connection.OpenAsync();
            }
            var prms = new DbParams();
            prms.AddRange(parameters);

            return await this.Connection.QueryFirstOrDefaultAsync<T>(sql, prms, this.Transaction);
        }

        public async Task<List<T>> ExecuteListAsync<T>(string sql, params IDbDataParameter[] parameters)
        {
            if (this.Connection.State != ConnectionState.Open)
            {
                await this.Connection.OpenAsync();
            }
            var prms = new DbParams();
            prms.AddRange(parameters);

            var data = await this.Connection.QueryAsync<T>(sql, prms, this.Transaction);
            return new List<T>(data);
        }

        public async Task<T> ExecuteScalarAsync<T>(string sql, params IDbDataParameter[] parameters)
        {
            using (var cmd = new NpgsqlCommand(sql, this.Connection))
            {
                cmd.Transaction = this.Transaction;
                if (this.Connection.State != ConnectionState.Open)
                {
                    await this.Connection.OpenAsync();
                }
                if (parameters.Length > 0)
                {
                    cmd.Parameters.AddRange(parameters);
                }

                object ret = await cmd.ExecuteScalarAsync();
                if (ret == null)
                {
                    return default;
                }

                return (T)ret;
            }
        }

        public async Task<List<T>> ExecuteScalarListAsync<T>(string sql, params IDbDataParameter[] parameters)
        {
            using (var cmd = new NpgsqlCommand(sql, this.Connection))
            {
                cmd.Transaction = this.Transaction;
                if (this.Connection.State != ConnectionState.Open)
                {
                    await this.Connection.OpenAsync();
                }
                if (parameters.Length > 0)
                {
                    cmd.Parameters.AddRange(parameters);
                }

                var result = new List<T>();
                using (var r = await cmd.ExecuteReaderAsync())
                {
                    if (!r.HasRows)
                    {
                        return result;
                    }

                    while (await r.ReadAsync())
                    {
                        T value = await r.GetFieldValueAsync<T>(0);
                        result.Add(value);
                    }
                }

                return result;
            }
        }

        public async Task<int> ExecuteNonQueryAsync(string sql, params IDbDataParameter[] parameters)
        {
            using (var cmd = new NpgsqlCommand(sql, this.Connection))
            {
                cmd.Transaction = this.Transaction;
                if (this.Connection.State != ConnectionState.Open)
                {
                    await this.Connection.OpenAsync();
                }
                if (parameters.Length > 0)
                {
                    cmd.Parameters.AddRange(parameters);
                }

                return await cmd.ExecuteNonQueryAsync();
            }
        }

        public void Dispose()
        {
            this.Transaction?.Dispose();
            this.Transaction = null;
            this.Connection?.Dispose();
            this.Connection = null;
        }

        public Task TryOpenConnection()
        {
            throw new NotImplementedException();
        }

        public void CloseConnection()
        {
            this.Connection?.Close();
        }

        public void RecreateConnection()
        {
            throw new NotImplementedException();
        }
    }
    
    
    // Dapper
    public class DbParams : Dapper.SqlMapper.IDynamicParameters, IEnumerable<IDbDataParameter>
    {
        private readonly List<IDbDataParameter> parameters = new List<IDbDataParameter>();
        public IEnumerator<IDbDataParameter> GetEnumerator()
        {
            return parameters.GetEnumerator();
        }

        public void Add(IDbDataParameter value)
        {
            parameters.Add(value);
        }
        public void AddRange(IEnumerable<IDbDataParameter> values)
        {
            parameters.AddRange(values);
        }

        void Dapper.SqlMapper.IDynamicParameters.AddParameters(IDbCommand command,
            Dapper.SqlMapper.Identity identity)
        {
            foreach (IDbDataParameter parameter in parameters)
                command.Parameters.Add(parameter);
        }

        IEnumerator IEnumerable.GetEnumerator()
        {
            return GetEnumerator();
        }
    }
content_copyCOPY