I just started using Linq to SQL, and it just occurred to me that I don't really need any sprocs in the database since i can do all the data access through Linq to SQL.
Is there any reason to write sprocs with Linq to SQL?
Thank you.
A:
Possibly interfacing with an existing Database that mandates all data retrieval be done through a set of controlled Stored Procedures.
Or perhaps there is a construct which is hard (impossible?) to implement in Linq2SQL.
Mitch Wheat
2009-03-01 01:58:47
+1
A:
Much less, IMO, but there are still occasions where using stored procedures or table-valued functions can useful. Of course, LinqToSQL doesn't preclude using stored procedures so you can still use them if desired. For example, I've used table-valued functions to do parameterized queries against table joins to support multi-variable filters. This can greatly simplify your code and gives you the ability to optimize the query.
tvanfosson
2009-03-01 02:02:00
+1
A:
There typically is no good reason to write stored procedures in new applications.
The reason is there are myriads of much better techniques for performing data access in your applications. There are numerous object-relational mapping technologies available for dozens of languages and platforms.
One argument for stored procedures is that they present an abstract API in front of the database. The problem with that is, SQL is a very bad language for trying to write high-level business programs. Another problem with that is, you will be consuming the API in front the database from languages other than SQL. Languages like C#, Ruby and Haskell offer far more powerful ways of creating APIs in front of the database, which are much more natural when consumed in these languages.
Justice
2009-03-01 02:07:17
+4
A:
Stored procedures are very important. LinkToSQL requires direct access to insert, update, delete and select data in the tables. This means that if your site or application isn't correctly checking user input your database is open to a database injection attack.
When using stored procedures this isn't an issue as the user which logs into the database doesn't have direct table access. Access is granted to the stored procedure which gives the UI access to the tables, but only in the context of the stored procedures.
mrdenny
2009-03-09 08:18:13
In what way would a stored procedure be more safe then the L2S generated code?I think the same "least rights" policy is applicable for L2S as for SP, not?
borisCallens
2009-03-09 08:28:45
When using Stored procedures you don't have to give the app account rights to the table only to the stored procedure, with L2S the user needs direct table access. For tables that the app needs to delete data from this leaves an opening for a SQL injection attack.
mrdenny
2009-03-18 07:38:53
A:
I fear you've just walked on a religous mine!
Stored procedures or not is a very hotly debated subject, and often (in my opinion) boils down to a fight between DB/SQL specialists on one side and 'vanilla' developers on the other. Each side trying to get as much of the duvet as they can.
Check out this link, Stored procedures are bad, m'kay? and the ensuing LOOOOONG debate.
Benjol
2009-06-10 11:12:02