I didn't attend DCU myself but I know plenty of people that did, and I also grew up in Dublin, so I'll try help.
The university campus itself is nice, mostly redbrick buildings and many new buildings as well; most of it was built between the late 1980s and the 2000s, it's a very young university. Within Ireland it has a reputation for science & technology (it used to be a technological institute), but you'd struggle to find many people outside of Ireland who have heard of it.
The campus is situated in a residential area; parts of it are nice, other parts really not. On one side you are close to some of Dublin's historically worst neighbourhoods of Ballymun and (arguably) Whitehall, but if you could find accommodation in Glasnevin or Drumcondra it wouldn't be too bad. Living in Dublin is expensive, and there is currently a rental market crisis so finding inexpensive-but-inhabitable accommodation could be a challenge, though as the north side of Dublin is seen as less desirable you might have an easier time than someone trying to find something more central.
To get to Dublin city centre, you would have to take a bus more than likely; it takes anywhere between 20-40 minutes to get in depending on traffic. You probably could walk at a push but it'd take over an hour.
As regards the sports scholarships, my impression of them is that they tend to go a lot to Gaelic sports and other more 'mainstream' sports in Ireland, but if you felt you were at a high-enough level in your sport it would be worth a shot I suppose.