27,579
社区成员
发帖
与我相关
我的任务
分享
ALTER proc [dbo].[submitVote]
(
@WorkId int,
@Name nvarchar(200),
@ret int output
)
as
if(select count(*) from WxMyVotes where WxWorkId=@WorkId and UserName=@Name and CONVERT(varchar(100), AddDate, 23)=CONVERT(varchar(100), GETDATE(), 23))=1
begin
set @ret=0
return
end
begin tran t1
declare @errorSum int
set @errorSum=0
insert into WxMyVotes(IsDelete, WxWorkId, UserName, AddDate) values(0, @WorkId, @Name, GETDATE())
set @errorSum=@@ERROR+@errorSum
update WxWorks set VoteCount=VoteCount+1 where Id=@WorkId
set @errorSum=@@ERROR+@errorSum
if @errorSum=0
begin
set @ret=1
commit tran t1
end
else
begin
set @ret=2
rollback tran
end
GO
alter table 表名 add 日期计算列 as convert(char(10), AddDate, 120);
create iunique index 索引名 on 表名(WxWorkId , UserNam, 日期计算列);
[/quote]
这个表已经有自增主键Id 再建唯一索引没影响吧[/quote]
可以的alter table 表名 add 日期计算列 as convert(char(10), AddDate, 120);
create iunique index 索引名 on 表名(WxWorkId , UserNam, 日期计算列);
[/quote]
这个表已经有自增主键Id 再建唯一索引没影响吧alter table 表名 add 日期计算列 as convert(char(10), AddDate, 120);
create iunique index 索引名 on 表名(WxWorkId , UserNam, 日期计算列);
begin tran t1
if(select count(*) from WxMyVotes with(updlock,holdlock) where WxWorkId=@WorkId and UserName=@Name and CONVERT(varchar(100), AddDate, 23)=CONVERT(varchar(100), GETDATE(), 23))=1
begin
set @ret=0
return
end
--begin tran t1
declare @errorSum int
ApplicationUser userInfo = await _userManager.GetUserAsync(HttpContext.User);
ApplicationDbContext context = HttpContext.RequestServices.GetService<ApplicationDbContext>();
DbConnection conn = context.Database.GetDbConnection();
if (conn.State != System.Data.ConnectionState.Open)
{
conn.Open();
}
string str = "";
DbCommand comm = conn.CreateCommand();
comm.CommandType = CommandType.StoredProcedure;
comm.CommandText = "submitVote";
comm.Parameters.Add(new SqlParameter("@WorkId", workId));
comm.Parameters.Add(new SqlParameter("@Name", userInfo.UserName));
SqlParameter outputPara = new SqlParameter("@ret", SqlDbType.Int)
{
Direction = ParameterDirection.Output
};
comm.Parameters.Add(outputPara);
comm.ExecuteNonQuery();
str = outputPara.Value.ToString();
IF EXISTS(
SELECT 1
FROM WxMyVotes
WHERE WxWorkId = @WorkId
AND UserName = @Name
AND AddDate>convert(char(10),GETDATE(),120)
)
BEGIN
SET @ret = 0
RETURN
END