Ok I know "what X should I use" is very broad so let me narrow down our usage scenario. Basically, we should have been using an ORM a long time ago. Now though there is no way we can go through and rewrite every line of generated SQL Queries in our C# code. But we want to at least take a few steps in the right direction. So when we write new code(and in free time with refactoring) we want to convert over to some sort of ORM.
What would be a good ORM for this purpose? We are using .Net 3.5 and ASP.Net(webforms). There is nothing insanely complex about our database except for a few tables have a "dynamic" schema. We have a numerous amount of views(not used by the web application much though) and we have/use very few stored procedures. Our database is SQL Server 2005. Our price range is as cheap as possible and open source is preferred. This is a proprietary project however so we can't use a GPL library and such.
About the dynamic schema: Basically in some instances a column(of varying types) can be added or removed to certain tables.
And our view usage is almost non-existent and we could always take out the stored procedure code.. I believe there is only 2 or 3 in the database.
I would second Justin on suggesting Linq-to-SQL - it's really not dead!
If that's not your choice, or if you need to go against something like SQLite, you should definitely also check out Subsonic 3.0 - lightweight, easy to use, free, with source - you name it.