存储过程 Id 设置为 Output！
存储过程：ALTER PROCEDURE[dbo].[MAJ_Role_Save]
-- Add the parameters for the stored procedure here
@Id Bigint =0 OUTPUT,
@RoleName nvarchar(50) =null,
@TokenList nvarchar(max) = null,
@CreateTime DateTime = null,
@UpdateTime DateTime = null
AS
BEGIN
IF @Id = 0
BEGIN
insert into dbo.MAJ_Role
(
RoleName ,
TokenList,
CreateTime,
UpdateTime
)
values
(
@RoleName ,
@TokenList,
@CreateTime,
@UpdateTime
)
-- Return Id
SET @Id = SCOPE_IDENTITY();
END
ELSE
BEGIN
update dbo.MAJ_Role set
RoleName = @RoleName ,
TokenList = @TokenList,
UpdateTime = @UpdateTime
where Id = @Id
END
END
测试代码：
public static void Save(string roleName, string tokenList,DateTime createTime,DateTime updateTime)
{
string connString = System.Configuration.ConfigurationManager.ConnectionStrings["Sql"].ConnectionString;
long Id = 0;
using (TransactionScope scope = new TransactionScope())
{
using (SqlConnection conn = new SqlConnection(connString))
{
conn.Open();
// Stored procedure
SqlCommand cmd = new SqlCommand("dbo.MAJ_Role_Save", conn);
= CommandType.StoredProcedure;
cmd.Parameters.Add("@Id", SqlDbType.Int).Value = Id;
cmd.Parameters.Add("@RoleName", SqlDbType.NVarChar, 50).Value = roleName;
cmd.Parameters.Add("@TokenList", SqlDbType.NVarChar, 1000).Value = tokenList;
cmd.Parameters.Add("@CreateTime", SqlDbType.DateTime).Value = createTime;
cmd.Parameters.Add("@UpdateTime", SqlDbType.DateTime).Value = updateTime;
//cmd.Parameters[@test].Direction = System.Data.ParameterDirection.ReturnValue;
cmd.Parameters[0].Direction = System.Data.ParameterDirection.Output;
int count = cmd.ExecuteNonQuery();
conn.Close();
Id = Convert.ToInt64(cmd.Parameters["@Id"].Value);
}
();
}
Id = Convert.ToInt64(cmd.Parameters["@Id"].Value);//这句话 出错！ 存储过程没执行成功！没有数据插入！
