ASP.NET调用存储过程返回值 输入输出参数 代码

查看文章

ASP.NET调用存储过程返回值 输入输出参数 代码

2009-11-25 10:41

关键代码：前台

<div>
   
        ALTER PROCEDURE dbo.StoredProcedure1
        <br />
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; @a int,
        <br />
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; @b int
        <br />
        AS
        <br />
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; RETURN @a+@b<br />
        <br />
        获取存储过程的返回值：<asp:Label ID="Label1" runat="server" Text="Label"></asp:Label>
        <br />
        <br />
        <hr />
        ALTER PROCEDURE dbo.StoredProcedure2<br />
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; @a int,
        <br />
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; @b int,
        <br />
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; @c int output
        <br />
        AS
        <br />
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Set @c = @a + @b<br />
        <br />
        获取存储过程的输出参数值：<asp:Label ID="Label2" runat="server" Text="Label"></asp:Label>
   
    </div>

 

后台代码：

 

/// <summary>
    /// 获取存储过程返回值
    /// </summary>
    public void getReturnValue()
    {
        using (SqlConnection conn = new SqlConnection(ConfigurationManager.AppSettings[0].ToString()))
        {
            conn.Open();
            SqlCommand cmd = new SqlCommand("StoredProcedure1", conn);
            cmd.CommandType = CommandType.StoredProcedure;
            SqlParameter[] spr ={
                               new SqlParameter("@a",SqlDbType.Int),
                               new SqlParameter("@b",SqlDbType.Int)
                           };
            spr[0].Value = 1;
            spr[1].Value = 2;
            foreach (SqlParameter p in spr)
            {
                cmd.Parameters.Add(p);
            }

            cmd.Parameters.Add(new SqlParameter("@return", SqlDbType.Int));
            cmd.Parameters["@return"].Direction = ParameterDirection.ReturnValue;
            cmd.ExecuteNonQuery();
            Label1.Text = cmd.Parameters["@return"].Value.ToString();

        }
    }
    /// <summary>
    /// 获取存储过程输出参数的值
    /// </summary>
    public void getOutputValue()
    {
        using(SqlConnection conn=new SqlConnection(ConfigurationManager.AppSettings[0].ToString()))
        {
            conn.Open();
            SqlCommand cmd = new SqlCommand("StoredProcedure2", conn);
            cmd.CommandType = CommandType.StoredProcedure;
            SqlParameter p1 = new SqlParameter("@a", SqlDbType.Int);
            p1.Value = 1;
            SqlParameter p2 = new SqlParameter("@b", SqlDbType.Int);
            p2.Value = 2;
            SqlParameter p3 = new SqlParameter("@c", SqlDbType.Int);
            p3.Direction = ParameterDirection.Output;

            cmd.Parameters.Add(p1);
            cmd.Parameters.Add(p2);
            cmd.Parameters.Add(p3);

            cmd.ExecuteNonQuery();
            Label2.Text = cmd.Parameters[2].Value.ToString();

        }

    }