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

Question

I am going to develop a database layer to expose set of functionlaity 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

Only advice I would give you is that this has been done before by people a lot smarter than myself, and they've learned from the pitfalls. Perhaps your time would be well spent reading over what frameworks are available for you to customise as opposed to rolling your own code?

Other than that I would recommend that you think hard about the consumer of your db layer, make sure that they don't need to know what the hell is going on inside your layer to work out what to do. Make a friendly reusable public interface and this should help you significantly.

I'd also recommend that you have a close look at Linq to SQL as utilising this would make your class library look a whole lot different, as opposed to staying with ADO.net or a vanilla ORM.

Answer 2

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
    }
}