今天遇到个表态的问题!使用带事务的存储过程执行sql语句,看数据库里面插入更新都正常! 但是返回值一直为-1! 头那个大哦!
先贴2个存储过程吧!看大侠们能否找到问题的存在
USE [RevolutionDB_Test]
GO
/****** Object: StoredProcedure [dbo].[UpdatePointByUser] Script Date: 03/29/2010 16:06:23 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:jinho
-- Create date: 2010年3月22日16:13:06
-- Description: 修改用户的点数 [Point]
-- =============================================
ALTER PROCEDURE [dbo].[UpdatePointByUser]
@point int = 0,
@uid int =0
AS
BEGIN
SET NOCOUNT ON;
declare @error int ;
set @error = 0;
-- Insert statements for procedure here
begin tran
update UserInfo set Point = Point + @point where Id = @uid;
set @error = @error+@@error;
if(@error=0)
commit tran ;
else
rollback tran;
END
//////////////////////////////////////////////////////////////
USE [RevolutionDB_Test]
GO
/****** Object: StoredProcedure [dbo].[AddAnnouncement] Script Date: 03/29/2010 15:43:51 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[AddAnnouncement]
@ATitle nvarchar(max) ='',
@ACreateTime datetime ='',
@AWhere nvarchar(max) = '',
@AUserId int =0,
@point int =0
AS
BEGIN
SET NOCOUNT ON;
declare @error int ;
set @error = 0;
begin tran
insert into Announcements values(@ATitle,@ACreateTime,@AWhere,@AUserId);
set @error = @error+@@error;
exec UpdatePointByUser @point,@AUserId
set @error = @error+@@error;
if(@error=0)
commit tran ;
else
rollback tran;
END
declare @rev int
exec [AddAnnouncement] 'mytest','2008-05-09','ChengDu',13,-1222200
print @rev;
print @@ROWCOUNT
这里测试 @rev,@@ROWCOUNT 都为0;
C# 代码:
public bool AddAnnouncement(Model announcement)
{
SqlConnection con = null;
SqlCommand cmd = null;
int row = 0;
SqlParameter[] paras = {new SqlParameter("@ATitle",announcement.ATitle),
new SqlParameter("@ACreateTime",announcement.ACreateTime),
new SqlParameter("@AWhere",announcement.AWhere),
new SqlParameter("@AUserId",announcement.AUserId),
new SqlParameter("@point",1)};
try
{
using (con = new SqlConnection(DBHelper.DefaultConnectionString))
{
con.Open();
using (cmd = new SqlCommand("[AddAnnouncement]", con))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddRange(paras);
row = cmd.ExecuteNonQuery();
return row > 0;
}
}
}
catch (Exception)
{
throw;
}
finally
{
if (con != null) con.Close();
}
上面代码的Row返回为-1! 但是我去数据库看,执行存储过程的两条语句都成功了!
嘿嘿:注意看到上面的存储过程的“SET NOCOUNT ON;”这句代码!就是他在作怪!他的意思为 “开启设置不影响的行数”!
你只要 把SET NOCOUNT OFF即可! 当然也可以不写!因为默认为OFF!如果你看到默认为ON时请修改为OFF!
OK!