is it ok to do the Data Access Layer using only SqlConnection SqlCommand and SqlDataReader

Question

I know that nowadays there is a lot of ready-to-go stuff that you can use, but if you want full control over db requests/queries, and best performance I think that this is the way to go. Also because ADO.NET does the connection pooling automatically for SqlConnection, how do you think ?

Answer 1

Absolutely. If extreme performance is what you want, you can't get much faster than this. I think it's still OK.

In fact, I'm doing a reporting system right now which uses SQL, DataReaders, and SQLConnections because all it does it run SQL. OLAP cubes aren't exactly the best candidates for OR mappers.

Answer 2

I think it's perfectly fine. Only you can determine your best toolset for any given project, and as long as you're using them properly, following best practices, use what you think is best.

Personally, I prefer to use the tools you mentioned, because I like the finer control. I only use the drag-and-drop components for very simple tasks, and hey, there's now Dynamic Data for those tasks.

Answer 3

If you really need full control over the generated SQL queries and are looking for best performance this is the way to go. But sometimes the best performance comes at the cost of less readable and less maintainable code. If this is the case you might need to make a decision whether this price is worth paying.

Answer 4

Yes, I think it's perfectly OK! I personally like having the full control, to define exactly how db interation is made and giving you absolute control over performance tuning/optimisation.

Just wanted to add that of course SqlDataAdapters and DataTables may have their place too, especially if wanting to pass disconnected data around.

Answer 5

Yes you're right, it has the best performance, but you have to consider all the points before take the decision, there are ORM tools that can control all of your data acces layer, and they are just fine in performance.

Answer 6

If you need best performance it is ok. It gives you more control and better performance. By the way here is an article about things you should take into consideration that affects performance while using ADO.NET: How Data Access Code Affects Database Performance

Answer 7

You may also want to look into Enterprise Library. It uses the basic ADO.Net objects while minimizing the code required to make these calls in your code.