What is NHibernate?
NHibernate is an ORM, or Object-Relational Mapper. In the same line as LINQ to SQL, Entity Framework, LLBLGen, and others, ORM tools remove most of the need to write stored procedures to handle common data access (CRUD) for your business objects. ORM tools require that you create (either manually or with a visual designer...depends on the one you choose) a mapping specification that traces which properties of your objects map to which columns of your tables and/or views in your database. When you need to retrieve objects, the ORM tool generates the appropriate SQL for you, and sends it to the database. When the time comes to update your objects, the ORM will not only create the SQL to insert, update, and delete for you...it will also batch those commands so that a single connection and batch of commands are sent to the database and performs the whole thing in a transaction. ORM tools can also improve the efficiency of your queries by allowing you to select entire object graphs in a single go, generating the most efficient SQL for the task.
With ORM tools, you still need to query, however you either with basic methods on the ORM's context or session object, or with a custom query language for that ORM. These days, most ORM's, including NHibernate, also provide LINQ support, allowing you to use standard LINQ syntax to query your object model, which in turn is translated to SQL queries against your database for you.
The benefit of OR mappers is that you centralize almost ALL of your code into your domain, rather than splitting it between domain and stored procs. You lighten the stored procedure load on your database, providing less of a barrier to refactoring your database if the need ever arises, providing greater business agility not only in your domain, but also with your database schema. Since you don't have to write SQL, and especially if you use LINQ, you can often create a more efficient application in less effort with lower long term maintenance costs.
Aside from the inevitable war with your DBA's (if you have them), OR mappers can bring considerable benefits to the table that can reduce implementation effort, improve maintainability, and provide greater business agility.
Hope that answers the question. ;)
A good introduction and tutorial can be found here Summer of NHibernate Screencasts.
In each video, he provide an introduction to a topic in nHibernate and then dives into some code while explaining how to do different things. I have found it very helpful.