ASP.NET调用存储过程返回输出参数,获得返回值

最新推荐文章于 2024-10-18 16:31:30 发布

魏言

最新推荐文章于 2024-10-18 16:31:30 发布

阅读量1.9k

点赞数

分类专栏： ASP.NET 开发文章标签： asp.net 存储 insert c# cmd

本文链接：https://blog.csdn.net/lhypang2006/article/details/2597036

版权

ASP.NET 开发专栏收录该内容

116 篇文章 0 订阅

订阅专栏

ASP.NET调用存储过程获得返回值

--------------------------------------------

假设有存储过程如下:

---------------------------------------------



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;  //此处即为返回值

ASP.NET调用存储过程返回输出参

  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;