SQL 使用存储过程问题
调用了以下的存储过程，如果更新错误一条，应该回滚，为什么我每次返回值还取到1.
在线等 跪求答案
alter proc UpdateSMProudctAndSMRelOrgProduct
@PKId uniqueidentifier,@ProductName nvarchar(40),@ContactName nvarchar(40),@ContactEmail nvarchar(40),@ContactTel nvarchar(40),@BAk nvarchar(40)
as
begin tran
declare @Sumerror int
update SMProduct set Name=@ProductName where PKID=@PKId
update SMRelOrgProduct set ContactName=@ContactName,ContactEmail=@ContactEmail,ContactTel=@ContactTel,Bak1=@BAk where ProductPKID=@PKId
if @@ERROR<>0
begin rollback transaction
return 0
end
else
begin commit transaction
return 1
end
方法
public int UpadateSMproductandOrg(Guid Pkid,SMProduct SMEntity,SMRelOrgProduct SMOrgEntity)
{
List<String> SQLStringList = new List<string>();
StringBuilder strSql = new StringBuilder();
SqlParameter[] parameters = {
new SqlParameter("@PKId", SqlDbType.UniqueIdentifier,16),
new SqlParameter("@BAk", SqlDbType.NVarChar),
new SqlParameter("@ContactName", SqlDbType.NVarChar),
new SqlParameter("@ContactEmail", SqlDbType.NVarChar),
new SqlParameter("@ContactTel", SqlDbType.NVarChar),
new SqlParameter("@ProductName", SqlDbType.NVarChar)
};
parameters[0].Value = Pkid;
parameters[1].Value = SMOrgEntity.bak1;
parameters[2].Value = SMOrgEntity.ContactName;
parameters[3].Value = SMOrgEntity.ContactEmail;
parameters[4].Value = SMOrgEntity.ContactTel;
parameters[5].Value = SMEntity.Name;
int i;
DbHelperSQL.RunProcedure("UpdateSMProudctAndSMRelOrgProduct", parameters, out i);
return i;
}
DBhelper
/// <summary>
/// 执行存储过程，返回影响的行数
/// </summary>
/// <param name="storedProcName">存储过程名</param>
/// <param name="parameters">存储过程参数</param>
/// <param name="rowsAffected">影响的行数</param>
/// <returns></returns>
public static int RunProcedure(string storedProcName, IDataParameter[] parameters, out int rowsAffected)
{
using (SqlConnection connection = new SqlConnection(connectionString))
{
connection.Open();
SqlCommand command = BuildIntCommand(connection, storedProcName, parameters);
command.ExecuteNonQuery();
rowsAffected = (int)command.Parameters["ReturnValue"].Value;
// Connection.Close();
return rowsAffected;
}
}
sql 存储
[解决办法]
先在数据库中调试存储过程，通了再调程序。
[解决办法]
@PKId uniqueidentifier,@ProductName nvarchar(40),@ContactName nvarchar(40),@ContactEmail nvarchar(40),@ContactTel nvarchar(40),@BAk nvarchar(40)
你的参数里怎么没有看到OUT参数？
