asp.net mvc 调用sql中的存储过程的误解

我要在一张“DateType”表中插入一年的日期和日期分为“工作日”和“周六日”的字段。在sql中的存储过程是这样写的:

CREATE PROCEDURE [dbo].[DateType_INSERT_DATA]
AS
delete from DateType where 1=1
declare @i datetime
set @i= dateadd(d,1-datepart(dy,getdate()),getdate())
declare @year int
set @year=datepart(yy,@i)
while(@year=datepart(yy,@i))
begin
insert into DateType values(@i,1)
set @i=dateadd(d,1,@i)
end

declare @rel int,@reltime datetime
declare cursor_datetime cursor for
select Datetimes from DateType;
open cursor_datetime
fetch next from cursor_datetime into @reltime
while @@FETCH_STATUS=0
begin
---cast('YYYY-MM-DD HH24:MI:SS' as datetime)
select @rel=datepart(weekday,@reltime)
if @rel=1
begin
update DateType set DateType=2 where Datetimes=@reltime
end
fetch next from cursor_datetime into @reltime
end
close cursor_datetime
deallocate cursor_datetime;


--declare @rel int,@reltime datetime
declare cursor_datetime cursor for
select Datetimes from DateType;
open cursor_datetime
fetch next from cursor_datetime into @reltime
while @@FETCH_STATUS=0
begin
---cast('YYYY-MM-DD HH24:MI:SS' as datetime)
select @rel=datepart(weekday,@reltime)
if @rel=7
begin
update DateType set DateType=2 where Datetimes=@reltime
end
fetch next from cursor_datetime into @reltime
end
close cursor_datetime
deallocate cursor_datetime;

 

我要在项目的控制器的方法中执行DateType_INSERT_DATA存储过程。在网上查了很多方法都报错,最后发现原来很简单的一句话就会成功,即:

public string NewYearDateType()
{
try {
new EmAttContext().Database.ExecuteSqlCommand("exec DateType_INSERT_DATA");
return "success";
}
catch (Exception ex) {
return ex.ToString();
}
}

posted @ 2018-01-22 11:36  爱编程的一米  阅读(141)  评论(0编辑  收藏  举报