any benefit using stored procedure

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

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

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

NO

Thank you for posting at Dotnetfunda
[Administrator]

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

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

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

w

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

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

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