ado.net调用存储过程怎么得到返回值

Question

if exists (select * from sysobjects where name='p_insert')
drop proc p_insert
go
create proc p_insert
@name varchar(20),
@sex varchar(20),
@tel varchar(11),
@address varchar(100)
as
begin
insert into test([name],sex,tel,[address])
values(@name,@sex,@tel,@address);
return @@identity
endselect * from testSqlCommand cmd=new SqlCommand();
            cmd.Connection = con;
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = "p_insert";
            SqlParameter [] parms=new SqlParameter[]
                                      {
                                           new SqlParameter("@name", txtName.Text), 
                                           new SqlParameter("@sex",txtSex.Text),
                                           new SqlParameter("@tel",txtTel.Text),
                                           new SqlParameter("@address",txtAddress.Text) ,rtnval
        }
                ;
            foreach (var p in parms)
            {
                cmd.Parameters.Add(p);
            }
            cmd.ExecuteNonQuery();

Answer 1

 SET @RoleID = @@IDENTITY
int rowsAffected = command.ExecuteNonQuery(); //接受返回影响行数
int result = command.parameters["Returnvalue"].value;//存储过程返回值
int newID = command.parameters["@RoleID"].value;//新的ID值

Answer 2

create proc p_insert
@name varchar(20),
@sex varchar(20),
@tel varchar(11),
@address varchar(100),
@id int output
as
...
set @id=@@IDENTITY
return @id
SqlCommand cmd = new SqlCommand();
            cmd.Parameters.Add("@id",SqlDbType.Int);
            cmd.Parameters["@id"].Direction = ParameterDirection.Output;.....

Answer 3

output的话在调用时需要指明 ParameterDirection.Output;