What are best practices for developing class library in general and database layer specifically?

Question

I am going to develop a database layer to expose set of functionality that will provide reading and writing into our database. I have set of questions related to this, and I want someone to advice

1. How can I handle the database exceptions ?(i.e: timeout exceptions) or leave the dll client handle them

2. The dll will not be thread safe, so shall I use single connection per dll instance? as this enhances the performance.

3. Are there any general rules for developing class library?

I am using C# vs2008 and SQL2008

Answer 1

1: up to you; you can't really do anything about them, so I'd probably let them surface unless I needed to hide it from the caller (replacing it with a similar, less specific exception)

2: You should keep connections as "local" and short-lived as possible; often one per method call. Most providers have connection pooling, meaning you will get back the same underlying connection (as long as you use the same connection string), without having to stress about synchronization, re-entrancy, etc (it will allocate more connections as needed)

3: look at patterns like the repository pattern; look also at ORM frameworks like LINQ-to-SQL, Entity Framework, NHibernate, etc.

i.e.

Customer GetCustomer(int id) {
    using(var conn = CreateConnection()) {
        conn.Open();
        // etc
    }
}