public class DBConnection : IDisposable
    {
        private IntPtr _connection;

        public void connect(string host, string user, string pass, string db)
        {
            var connectionStr = $"host={host} dbname={db} user={user} password={pass} client_encoding=utf8";
            _connection = Pg.PQconnectdb(connectionStr);
            if (Pg.PQstatus(_connection) != ConnStatusType.CONNECTION_OK)
            {
                close();
                throw new Exception($"Could not establish connection with the database. ConnectionStr: {connectionStr}");
            }
        }

        public void startTransaction()
        {
            if (_connection != IntPtr.Zero)
            {
                var result = Pg.PQexec(_connection, "BEGIN");
                if (Pg.PQresultStatus(result) != ExecStatusType.PGRES_COMMAND_OK)
                {
                    Pg.PQclear(result);
                    close();
                    throw new Exception("starting transaction failed");
                }
            }
            else
            {
                throw new Exception("requested start transaction but the database connection is not ready");
            }
        }

        public void endTransaction()
        {
            if (_connection != IntPtr.Zero)
            {
                var result = Pg.PQexec(_connection, "END");
                if (Pg.PQresultStatus(result) != ExecStatusType.PGRES_COMMAND_OK)
                {
                    Pg.PQclear(result);
                    close();
                    throw new Exception("ending transaction failed");
                }
            }
            else
            {
                throw new Exception("requested end transaction but the database connection is not ready");
            }
        }

        public IntPtr execute(string query)
        {
            if (_connection != IntPtr.Zero)
            {
                var result = Pg.PQexec(_connection, query);
                if (Pg.PQresultStatus(result) != ExecStatusType.PGRES_COMMAND_OK &&
                    Pg.PQresultStatus(result) != ExecStatusType.PGRES_TUPLES_OK)
                {
                    var error = Pg.PQresultErrorMessage(result);
                    Pg.PQclear(result);
                    close();
                    throw new Exception($"query execution failed with message: {error}");
                }
                else
                {
                    return result;
                }
            }
            else
            {
                throw new Exception("requested query execution but the database connection is not ready");
            }
        }

        public void clearResult(IntPtr result)
        {
            Pg.PQclear(result);
        }

        public float getFloat(IntPtr result, int row, int col)
        {
            try
            {
                var valueStr = Marshal.PtrToStringUTF8(Pg.PQgetvalue(result, row, col));
                return float.Parse(valueStr);
            }
            catch (Exception e)
            {
                throw new Exception($"Error getting float value. Message: {e}");
            }
        }

        public byte getByte(IntPtr result, int row, int col)
        {
            try
            {
                var valueStr = Marshal.PtrToStringUTF8(Pg.PQgetvalue(result, row, col));
                return byte.Parse(valueStr);
            }
            catch (Exception e)
            {
                throw new Exception($"Error getting byte value. Message: {e}");
            }
        }

        public short getShort(IntPtr result, int row, int col)
        {
            try
            {
                var valueStr = Marshal.PtrToStringUTF8(Pg.PQgetvalue(result, row, col));
                return short.Parse(valueStr);
            }
            catch (Exception e)
            {
                throw new Exception($"Error getting short value. Message: {e}");
            }
        }

        public int getInt(IntPtr result, int row, int col)
        {
            try
            {
                var valueStr = Marshal.PtrToStringUTF8(Pg.PQgetvalue(result, row, col));
                return int.Parse(valueStr);
            }
            catch (Exception e)
            {
                throw new Exception($"Error getting int value. Message: {e}");
            }
        }

        public long getLong(IntPtr result, int row, int col)
        {
            try
            {
                var valueStr = Marshal.PtrToStringUTF8(Pg.PQgetvalue(result, row, col));
                return long.Parse(valueStr);
            }
            catch (Exception e)
            {
                throw new Exception($"Error getting long value. Message: {e}");
            }
        }

        public bool getBool(IntPtr result, int row, int col)
        {
            try
            {
                var valueStr = Marshal.PtrToStringUTF8(Pg.PQgetvalue(result, row, col));
                return bool.Parse(valueStr);
            }
            catch (Exception e)
            {
                throw new Exception($"Error getting int value. Message: {e}");
            }
        }

        public string getString(IntPtr result, int row, int col)
        {
            try
            {
                var valueStr = Marshal.PtrToStringUTF8(Pg.PQgetvalue(result, row, col));
                return valueStr;
            }
            catch (Exception e)
            {
                throw new Exception($"Error getting int value. Message: {e}");
            }
        }

        public int getRowCount(IntPtr result)
        {
            try
            {
                return Pg.PQntuples(result);
            }
            catch (Exception e)
            {
                throw new Exception($"Error getting row count. Message: {e}");
            }
        }

        public int getColCount(IntPtr result)
        {
            try
            {
                return Pg.PQnfields(result);
            }
            catch (Exception e)
            {
                throw new Exception($"Error getting col count. Message: {e}");
            }
        }

        private void close()
        {
            if (_connection != IntPtr.Zero)
            {
                Pg.PQfinish(_connection);
                _connection = IntPtr.Zero;
            }
        }

        public void Dispose()
        {
            close();
        }
    }