数据库中有自增字段UID
存储过程如下:
CREATE PROCEDURE AddUser
(
@Username nvarchar(50),
@Email nvarchar(100),
@Password nvarchar(50),
@UserID int OUTPUT
)
AS
INSERT INTO Users
( Username, Email, Password )
VALUES
( @Username, @Email, @Password
)
SELECT
@UserID = @@Identity
GO
程序如下:
SqlCommand cmd=new SqlCommand("AddUser",conn);
cmd.CommandType=CommandType.StoredProcedure;
cmd.Parameters.Add(new SqlParameter("@Username",Username));
cmd.Parameters.Add(new SqlParameter("@Email",Email));
cmd.Parameters.Add(new SqlParameter("@Password",Password));
SqlParameter outPara = new SqlParameter("@UserID", SqlDbType.Int);
outPara.Direction = ParameterDirection.Output;
cmd.Parameters.Add(outPara);
if (cmd.ExecuteNonQuery() > -1)
{
if (outPara.Value is int)
{
int retValue = (int)outPara.Value;
}
}
CMD执行
int UID;
UID = cmd.Parameters["@userid"];
2、
SqlParameter.Direction 属性
获取或设置一个值,该值指示参数是只可输入、只可输出、双向还是存储过程返回值参数。
属性值
ParameterDirection 值之一。默认为 Input。
Input 参数是输入参数。
InputOutput 参数既能输入,也能输出。
Output 参数是输出参数。
ReturnValue 参数表示诸如存储过程、内置函数或用户定义函数之类的操作的返回值。
3、
CREATE PROCEDURE [dbo].[ProgrammeDetail Add]
@Beginning_Date DateTime ,
@Ending_Date DateTime,
@currentID varchar(10) out
AS
BEGIN
begin tran
insert into ProgrammeDetail(BeginTime,EndTime) values( @Beginning_Date,@Ending_Date)
select @currentID=max(ProgrammeID) from ProgrammeDetail
commit
return @currentID
END
GO
——————————————————————————
--------------------------------------------
假设有存储过程如下:
---------------------------------------------
CREATE proc sp_uptmp @tmpName varchar(50),@srcPos varchar(255)
as
Begin TRAN
insert into t_template values(@tmpName,@srcPos)
COMMIT
return isnull(@@identity,0)
GO
------------------------------------------------------------
在 ado.net 里面获取返回值的方法为(c#):
------------------------------------------------------------
SqlConnection dbconn = new SqlConnection(connStr);
SqlCommand cmd = new SqlCommand("sp_uptmp",dbconn);
cmd.CommandType = CommandType.StoredProcedure;
SqlParameter tmpName = cmd.Parameters.Add("@tmpName",SqlDbType.VarChar);
SqlParameter srcPos = _cmd.Parameters.Add("@srcPos",SqlDbType.VarChar);
SqlParameter rtnval = cmd.Parameters.Add("rval",SqlDbType.Int);
tmpName.Direction = ParameterDirection.Input;
srcPos.Direction = ParameterDirection.Input;
rtnval.Direction = ParameterDirection.ReturnValue;
tmpName.Value = "";
srcPos.Value = "";
dbconn.Open();
cmd.ExecuteNonQuery();
dbconn.Close();
tmpid = (int)rtnval.Value; //此处即为返回值
3、
存储过程:
create procedure AddRecord
(@field1 varchar(20),
@field2 varchar(30))
as
insert into mytable(field1,field2) values(@field1,@field2)
select @@identity
go
在asp.net程序中这样调用存储过程:
SqlConnection conn=....//连接字符串
SqlCommand cmd=new SqlCommand("AddRecord",conn);
cmd.CommandType=CommandType.StoredProcedure;
cmd.Parameters.Add("@field1","field1的值");
cmd.Parameters.Add("@field2","field2的值");
conn.Open();
int iKey=(int)cmd.ExecuteScalar();
conn.Close();
最后的iKey就是你要的刚插入的主键值。