Home » VB.Net

Dataset/Database Stored Procedure

I am attempting to create a tier application. Im wondering which approach would be better to take on. Should i have stored procedures on the database and then call these from my Data Access Layer (this is when you right click your dataset and add a table adapter, select existing stored procedures or create the entire query in my Dataset under TableAdapter?

My reason for asking is because my Stored procs are created to insert and update in one command (by using an If statement), but saw the other approach recently.

Any reason for one over the other?



2 Answers Found


Answer 1

This is a difficult question because there as so many choices and many, many opinions on those choices.

Personally, I primarily use stored  procedures and ADO.NET with techniques that I demonstrate here:


I believe that Microsoft's current recommended approach  is to use Entity Framework. You can find more information about it here:


Hope this helps.


Answer 2


Has mostly to do with security. 

Some organizations want to keep the developer as far from the database  as possible. 

Then is using a stored  procedure an option because only those who have SQL Server administrator rights on the SQL server can modify those.

With the exception of an IBM DB2 database server there is not much difference between using dynamic SQL transacts scripts or stored SQL transact scripts. 

Linq to SQL creates for instance completely dynamically SQL transact scripts.

If it are procedures  like you wrote, then I like more stored procedures, but that is only because they are more easy to debug themselves than often with quotes messed dynamic scripts




<< Previous      Next >>

Microsoft   |   Windows   |   Visual Studio   |   Sharepoint   |   Azure