any benefit using stored procedure

Posted by Klbaiju under Regular Expressions on 9/7/2009 | Views : 2589 | Status : [Member] | Replies : 9
Hi,
iam new to 3.5. my projects were in 1.1,2.0. now iam using 3.5.i n 1.1 and 2.0 i used stored procedure.i want to know is there any need of stored procedure in linq.i feel that linq is doing just like stored procedure.give me a good answer

Thanks
K L BAIJU




Responses

Posted by: Vuyiswamb on: 9/7/2009 [Member] [MVP] [Administrator] NotApplicable

Up
0
Down
Good Day Klbaiju

What i will give you now is not a Fact but my opinion.

I am a type of person who is interested in a true N-Tier design. I bealive that SQL statements should never be written in the Data layer. I bealive that SQL Parameterised Stored Procedures should be used. When Linq came in , i saw that many people started to write Linq statements in the Data layer, and i was not very happy with that approach. Well in Linq , i loved the idea of linq to object, but not Linq to SQL. StoredProcedure are more Faster than naked SQL statements because they need to be compiled file , while stored Procedures are already compiled.

Thank you for posting at Dotnetfunda


Vuyiswa Maseko

Thank you for posting at Dotnetfunda
[Administrator]

Klbaiju, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: RtpHarry on: 9/8/2009 [Member] [MVP] Bronze

Up
0
Down
Hey,

My opinion is that SQL is not nice to write. I believe that the productivity enhancements of having linq as 1st level language structure outweighs the advantage of using stored procedures.

You get intellisense, full compile time syntax checking and access to a rich model.

By writing statements in linq you are not technically tieing your self to a single database as you can get linq to xxx for most major database technologies.

I believe in not optimising your site until you have a proven bottleneck and unless you are going to have a million hits a day to your site then the productivity loss that you take writing and debugging tsql isnt worth it for a possible speed increase you can get with your own sql (which isnt even guaranteed because linq2sql is actually quite good at generating sql).

If you do find a bottleneck then you can make linq use your own stored procedures in specific cases.

Klbaiju, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Klbaiju on: 9/8/2009 [Member] Starter

Up
0
Down
you didn't get my question
my question is if we use linq . any need of stored procedure.


Baiju


Klbaiju, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Vuyiswamb on: 9/8/2009 [Member] [MVP] [Administrator] NotApplicable

Up
0
Down
NO

Thank you for posting at Dotnetfunda
[Administrator]

Klbaiju, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Klbaiju on: 9/8/2009 [Member] Starter

Up
0
Down
Hi i was struck in this problem can you solve this
i want to add 2 values to a table.and also check duplicate value.
as per below stored procedure
i want to add custcode and custname. if custcode exists it should not added
how it is possible using linq
it is urgent for me



create procedure [check1]
@custcode int,
@custname nvarchar(50),
@chk_records int out
as
declare @count int
select @count=count(*) from customer where customercode=@custcode
if @count=0
begin
insert into customer(customercode,customername)values(@custcode,@custname)
set @chk_records=0
end
else
set @chk_r

Klbaiju, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Vuyiswamb on: 9/8/2009 [Member] [MVP] [Administrator] NotApplicable

Up
0
Down
Good Morning

do it this way

create procedure [check1]

(
@custcode int,
@custname nvarchar(50),
@chk_records int output
)
as
declare @count int
set @count= (select count(*) from customer where customercode=@custcode)

if @count=0
insert into customer(customercode,customername)values(@custcode,@custname)
set @chk_records = 0 --Am not sure what you were Trying to achieve here
else
set @chk_records = 1 --Am not sure what you were Trying to achieve here


Thank you for posting at Dotnetfunda

Vuyiswa Maseko

Thank you for posting at Dotnetfunda
[Administrator]

Klbaiju, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Klbaiju on: 9/8/2009 [Member] Starter

Up
0
Down



w



Klbaiju, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Vuyiswamb on: 9/8/2009 [Member] [MVP] [Administrator] NotApplicable

Up
0
Down
Good Day Klbaiju

Here is your Answer here

http://weblogs.asp.net/zeeshanhirani/archive/2008/08/05/using-linq-with-stored-procedures.aspx


http://www.mssqltips.com/tip.asp?tip=1542


http://srtsolutions.com/blogs/billwagner/archive/2007/10/29/linq-to-sql-and-stored-procedures.aspx

http://drowningintechnicaldebt.com/blogs/shawnweisfeld/archive/2009/07/11/call-a-stored-procedure-with-linq-to-sql-and-without-the-designer.aspx

http://msdn.microsoft.com/en-us/library/bb918119.aspx

Thank you for posting at Dotnetfunda

Vuyiswa Maseko

Thank you for posting at Dotnetfunda
[Administrator]

Klbaiju, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Neeks on: 9/22/2009 [Member] Bronze

Up
0
Down
Major advantage of using the stored procedure is that, you need not have to compile it each time it gets executed.

Once its created it can accept the parameter and gives the result.

This will descries the execution time....

Klbaiju, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response