Yesterday on one of SO chats I've been told I should never connect to database directly from the application and rather use DAL.
I've been told that:
1. Using something in the middle should improve the performance of app-db communication (which I hardly believe, since there's an overhead coming from additional layer between them)
2. It is more secure (in what terms?)
3. It gives better scalability
4. It allows cross-platform client application (not applicable, since it's written in C# and is only for Windows)
The biggest problem with current application is that 40-50 clients connected to database make it slows down massively.
So, my question is: how DAL can actually improve the performance(my main concern) of my system (in terms of server resource usage and client applications speed)?
Best Answer
Whether you use a DAL or not your application is still going to directly interact with the database.
It is generally just good practice to structure your code in such a way that things like data access is in a centralised "place" in your codebase.
I also generally abstract any data access to a set of methods defined on an interface.
If and when you decide to change your database you don't have to scoure you entire codebase for all the data access code in order to update it all.
Also by using an interface approch to abstract away the DAL code you can more easily write unit tests for the code that consumes the DAL output.
It's just good design to work this way.