Dungeon Siege : Legends of Aranna (Includes original DS and expansion) - You can pretty much ignore the game and expansion unless your fan of Diablo style games and go straight to playing great total conversion of the game: Ultima V: Lazarus. It is a remake of classic RPG Ultima V: Warrior of Destiny and is highly recommeded if you like story based RPG's.
With any luck The Ultima 6 Project may be finished this year. The project may need help to acomplish that however.
Any Ultima starting from 4th in the series (freeware). Ultima IV - VII are the best but new player may like VIII and IX not knowing the history of the series or how EA ruined it. Ultima IX Dragons Edition is the best option if you can get your hands on one as it contains all games in the series. You may have to give up arm and leg in ebay for it tought. Any pack that contains Ultima VII is the second best option.
Arcanum : Of Steamworks & Magick Obscura as others have already recommeded is definately worth it if you can get your hands on a copy of it.
Divine Divinity for Diablo lovers.
Icewind Dale I + expansions and Baldur's Gate 1 + expansion (use easytutu to convert to BG2 engine) are worth it (especially BG1+exp to get whole story).
You could try Might & Magic : Dark Messiah but it's more of experiment project than real game You can drop all kinds of things on your oppoments, freeze them and then shatter them, burn them, slice throats, kick them in to spikes or into bottomless pit, shrink them and them stomp them etc. etc. etc. but it's more of a linear tunnel run (even the levels in surface/towns) in the extreme and story, err, was there one? Endings were unsatisfying and game has one of the most annoying follower that required so much babysitting you I wanted to strangle her many times. What ever you do, choose the demon and let the other girl rot in the dungeon when the option is given.
DOS era has some more fun RPG's I could recommend but getting them to work may be uphill battle unless you know dosbox in and out.

Uuuu,
Betrayal at Krondor!
That's one awesome RPG. And you can get it on pretty much any abandonware site I think...

Played it, added to OP good game all though movement was awkward even more so considering that there was platforming involved.
I tried a demo of Dungeon Siege : Legends of Aranna but just couldn't get into it at all.
@Divine Divinity: That's a pretty awkward "camera" angle they use, is it any good? The game I mean.

Divine Divinity is very good. It blends Diablo style gameplay with an actual story and some cool quests. You can also customize your character however you wish. Don't bother with Beyond Divinity though....it's....not as good.

I heard a lot of good things about The Elder Scrolls Legends: Battlespire back in the day. How does that one holds up? Any good?

I played it tought just once and more or less agree with you in case of original DS and Aranna. They are at best, average.
Ultima V: Lazarus however pretty much converts the game into full fledged story driven RPG with deep plot, interesting world, NPC's and companions. There is also good and evil path.
Also The Ultima 6 Project may be released to DS this year if we're lucky (in the meantime you can test the latest milestone).
You don't require Aranna to play them, standard Dungeon Siege will do patched to latest 1.11 version (actually even if you get aranna you'll use the standard DS exe to play them. It just comes prepatched). If you can get DS/DS:LoA cheap from bargain bin or elsewhere I'd say it's a bargain as both above mods are free (UV:L alone can give you over 100 hours of entertainment if you explore every nook and cranny, do all the quests etc. and it has replayability value with good/evil path and different companions).
It's been a long time but as far as I remember it was fun for one playthrough. World is custom made and threse bit more 'story' to it than in diablo. I don't remember having any particular trouble with the camera (It's been too long, wasn't it pretty much the same angle as all other isomertic games?)

I have never played the spinoffs of the Elder Scrolls myself but I haven't heard anything bad about that or the Elder Scrolls:Redguard Adventures.

Would not recommend it to my worst enemy. Only good thing I can think about it is that (If I remember correctly) I could use the manual to figure character generation in Daggerfall :P (I bought Daggerfall used and it lacked manual). (One of the) Worst pruchase in my lifetime. I could send it to you by post but it would it far more than it's worth (heck dicking up is more trouble than it's worth) and it would be inhumane :P

Blades of Exile.
[url=http://info.wsisiz.edu.pl/~kowalsg0]http://info.wsisiz.edu.pl/~kowalsg0[/url]/
It's gone open-source and got a ton of quality scenarios:
http://www.spiderwebsoftware.com/blades/scen_list.html

jumping on the Spyderweb Software bandwagon, try out their Geneforge/Avernum series. They are turn-based and well-written.
Also, there is Eschalon Book I, which, by the way, has a sequel coming out soon.
I am currently awaiting Age of Decadence to come out soon.

Age of Decadence looks fantastic, I really hope Vince n co. can make it live up to expectations, although, knowing Vince (by reputation only) it should be finished off very well, given how much of a perfectionist he seems to be.

It's pretty good, but it's hard as fuck.
EDIT:
Looks pretty cool... What's it like/comparable with?

I like how some of the skills can be used in conjunction with other skills. Like the Lore skill can help you with Lockpicking. I sooooo want that game.

It shows an ambition and quality that the vast majority of indie games completely lack.
I wonder if it will get the same reception as Braid or World of Goo? or will it being an "old-school" in-depth RPG hurt its chances of becoming a media darling?