asp.net获取存储过程的输出参数

 

这个其实很简单，只是学习到了就作个记录，也给不知道的人留个方便。

一、假设存储过程如下

-- region [dbo].[InsertArchive]

------------------------------------------------------------------------------------------------------------------------
-- Generated By:    wangzeng using CodeSmith 4.0 . 0.0
-- Template:        StoredProcedures.cst
-- Procedure Name: [dbo].[InsertArchive]
-- Date Generated: 2007年11月28日
------------------------------------------------------------------------------------------------------------------------

-- 插入档案记录

ALTER PROCEDURE [dbo].[InsertArchive]
     @Name varchar( 50 ),
     @Sex int ,
     @PostID int ,
     @OrgID int ,
     @WorkTypeID varchar( 20 ),
     @ArchivesID int OUTPUT //注意
AS

-- SET NOCOUNT ON

INSERT INTO [dbo].[Archives] (
     [Name],
     [Sex],
     [PostID],
     [OrgID],
     [WorkTypeID]
) VALUES (
     @Name,
     @Sex,
     @PostID,
     @OrgID,
     @WorkTypeID
)

SET @ArchivesID = SCOPE_IDENTITY()

-- endregion

 

二、获取输出参数的代码

/**/ ///<summary>
        /// 新增档案
        ///</summary>
        ///<param name="Name"></param>
        ///<param name="Sex"></param>
        ///<param name="OrgID"></param>
        ///<param name="PostID"></param>
        ///<param name="WorkID"></param>
        ///<returns></returns>
         public int InsertArchive( string Name, int Sex, int OrgID, int PostID, string WorkTypeID, ref int ArchivesID) //注意 ref
         {
             SqlCommand comm =new SqlCommand("InsertArchive", conn.Conn);
             comm.CommandType = CommandType.StoredProcedure;

             comm.Parameters.Add("@Name", SqlDbType.VarChar, 50);
             comm.Parameters["@Name"].Value = Name;
             comm.Parameters["@Name"].Direction = System.Data.ParameterDirection.Input;
             comm.Parameters.Add("@Sex", SqlDbType.Int, 4);
             comm.Parameters["@Sex"].Value = Sex;
             comm.Parameters["@Sex"].Direction = System.Data.ParameterDirection.Input;
             comm.Parameters.Add("@OrgID", SqlDbType.Int, 4);
             comm.Parameters["@OrgID"].Value = OrgID;
             comm.Parameters["@OrgID"].Direction = System.Data.ParameterDirection.Input;
             comm.Parameters.Add("@PostID", SqlDbType.Int, 4);
             comm.Parameters["@PostID"].Value = PostID;
             comm.Parameters["@PostID"].Direction = System.Data.ParameterDirection.Input;
             comm.Parameters.Add("@WorkTypeID", SqlDbType.NVarChar,20);
             comm.Parameters["@WorkTypeID"].Value = WorkTypeID;
             comm.Parameters["@WorkTypeID"].Direction = System.Data.ParameterDirection.Input;

             comm.Parameters.Add("@ArchivesID", SqlDbType.Int, 4);
             comm.Parameters["@ArchivesID"].Value = ArchivesID;
             comm.Parameters["@ArchivesID"].Direction = System.Data.ParameterDirection.Output;           //注意方向
            
            if (conn.Conn.State == ConnectionState.Closed)
                 conn.Conn.Open();
            int counts = comm.ExecuteNonQuery();
             ArchivesID = Int32.Parse(comm.Parameters["@ArchivesID"].Value.ToString()); //关键的地方
            if (conn.Conn.State == ConnectionState.Open)
                 conn.Conn.Close();
            return counts;
         }