C# 在 ADO.NET 中获取输出参数值

Get output parameter value in ADO.NET

提问by

My stored procedure has an output parameter:

我的存储过程有一个输出参数：

@ID INT OUT

How can I retrieve this using ado.net?

如何使用 ado.net 检索此内容？

using (SqlConnection conn = new SqlConnection(...))
{
    SqlCommand cmd = new SqlCommand("sproc", conn);
    cmd.CommandType = CommandType.StoredProcedure;

    // add parameters

    conn.Open();

    // *** read output parameter here, how?
    conn.Close();
}

回答by WACM161

Not my code, but a good example i think

不是我的代码，但我认为是一个很好的例子

source: http://www.eggheadcafe.com/PrintSearchContent.asp?LINKID=624

来源：http: //www.eggheadcafe.com/PrintSearchContent.asp?LINKID=624

using System; 
using System.Data; 
using System.Data.SqlClient; 


class OutputParams 
{ 
    [STAThread] 
    static void Main(string[] args) 
    { 

    using( SqlConnection cn = new SqlConnection("server=(local);Database=Northwind;user id=sa;password=;")) 
    { 
        SqlCommand cmd = new SqlCommand("CustOrderOne", cn); 
        cmd.CommandType=CommandType.StoredProcedure ; 

        SqlParameter parm= new SqlParameter("@CustomerID",SqlDbType.NChar) ; 
        parm.Value="ALFKI"; 
        parm.Direction =ParameterDirection.Input ; 
        cmd.Parameters.Add(parm); 

        SqlParameter parm2= new SqlParameter("@ProductName",SqlDbType.VarChar); 
        parm2.Size=50; 
        parm2.Direction=ParameterDirection.Output; 
        cmd.Parameters.Add(parm2); 

        SqlParameter parm3=new SqlParameter("@Quantity",SqlDbType.Int); 
        parm3.Direction=ParameterDirection.Output; 
        cmd.Parameters.Add(parm3);

        cn.Open(); 
        cmd.ExecuteNonQuery(); 
        cn.Close(); 

        Console.WriteLine(cmd.Parameters["@ProductName"].Value); 
        Console.WriteLine(cmd.Parameters["@Quantity"].Value.ToString());
        Console.ReadLine(); 
    } 
} 

回答by BQ.

The other response shows this, but essentially you just need to create a SqlParameter, set the Directionto Output, and add it to the SqlCommand's Parameterscollection. Then execute the stored procedure and get the value of the parameter.

另一个响应显示了这一点，但基本上您只需要创建一个SqlParameter，将 设置Direction为Output，然后将其添加到SqlCommand的Parameters集合中。然后执行存储过程并获取参数的值。

Using your code sample:

使用您的代码示例：

// SqlConnection and SqlCommand are IDisposable, so stack a couple using()'s
using (SqlConnection conn = new SqlConnection(connectionString))
using (SqlCommand cmd = new SqlCommand("sproc", conn))
{
   // Create parameter with Direction as Output (and correct name and type)
   SqlParameter outputIdParam = new SqlParameter("@ID", SqlDbType.Int)
   { 
      Direction = ParameterDirection.Output 
   };

   cmd.CommandType = CommandType.StoredProcedure;
   cmd.Parameters.Add(outputIdParam);

   conn.Open();
   cmd.ExecuteNonQuery();

   // Some various ways to grab the output depending on how you would like to
   // handle a null value returned from the query (shown in comment for each).

   // Note: You can use either the SqlParameter variable declared
   // above or access it through the Parameters collection by name:
   //   outputIdParam.Value == cmd.Parameters["@ID"].Value

   // Throws FormatException
   int idFromString = int.Parse(outputIdParam.Value.ToString());

   // Throws InvalidCastException
   int idFromCast = (int)outputIdParam.Value; 

   // idAsNullableInt remains null
   int? idAsNullableInt = outputIdParam.Value as int?; 

   // idOrDefaultValue is 0 (or any other value specified to the ?? operator)
   int idOrDefaultValue = outputIdParam.Value as int? ?? default(int); 

   conn.Close();
}

Be careful when getting the Parameters[].Value, since the type needs to be cast from objectto what you're declaring it as. And the SqlDbTypeused when you create the SqlParameterneeds to match the type in the database. If you're going to just output it to the console, you may just be using Parameters["@Param"].Value.ToString()(either explictly or implicitly via a Console.Write()or String.Format()call).

获取 时要小心Parameters[].Value，因为需要将类型转换为object您声明的类型。和SqlDbType创建时使用的SqlParameter需要匹配数据库中的类型。如果您只想将它​​输出到控制台，您可能只是在使用Parameters["@Param"].Value.ToString()（通过 aConsole.Write()或String.Format()调用显式或隐式地使用）。

EDIT: Over 3.5 years and almost 20k views and nobody had bothered to mention that it didn't even compile for the reason specified in my "be careful" comment in the original post. Nice. Fixed it based on good comments from @Walter Stabosz and @Stephen Kennedy and to match the update code edit in the question from @abatishchev.

编辑：超过 3.5 年和近 2 万次观看，没有人愿意提及它甚至没有编译，原因是我在原始帖子中的“小心”评论中指定的原因。好的。根据@Walter Stabosz 和@Stephen Kennedy 的良好评论对其进行了修复，并与@abatishchev 的问题中的更新代码编辑相匹配。

回答by Nate Kindrew

For anyone looking to do something similar using a reader with the stored procedure, note that the reader must be closed to retrieve the output value.

对于希望使用带有存储过程的读取器执行类似操作的任何人，请注意必须关闭读取器才能检索输出值。

using (SqlConnection conn = new SqlConnection())
{
    SqlCommand cmd = new SqlCommand("sproc", conn);
    cmd.CommandType = CommandType.StoredProcedure;

    // add parameters
    SqlParameter outputParam = cmd.Parameters.Add("@ID", SqlDbType.Int);
    outputParam.Direction = ParameterDirection.Output;

    conn.Open();

    using(IDataReader reader = cmd.ExecuteReader())
    {
        while(reader.Read())
        {
            //read in data
        }
    }
    // reader is closed/disposed after exiting the using statement
    int id = outputParam.Value;
}

回答by Nate Kindrew

You can get your result by below code::

您可以通过以下代码获得结果：

using (SqlConnection conn = new SqlConnection(...))
{
    SqlCommand cmd = new SqlCommand("sproc", conn);
    cmd.CommandType = CommandType.StoredProcedure;

    // add other parameters parameters

    //Add the output parameter to the command object
    SqlParameter outPutParameter = new SqlParameter();
    outPutParameter.ParameterName = "@Id";
    outPutParameter.SqlDbType = System.Data.SqlDbType.Int;
    outPutParameter.Direction = System.Data.ParameterDirection.Output;
    cmd.Parameters.Add(outPutParameter);

    conn.Open();
    cmd.ExecuteNonQuery();

    //Retrieve the value of the output parameter
    string Id = outPutParameter.Value.ToString();

    // *** read output parameter here, how?
    conn.Close();
}

回答by Vinícius Todesco

string ConnectionString = ConfigurationManager.ConnectionStrings["DBCS"].ConnectionString;
using (SqlConnection con = new SqlConnection(ConnectionString))
{
//Create the SqlCommand object
SqlCommand cmd = new SqlCommand(“spAddEmployee”, con);

//Specify that the SqlCommand is a stored procedure
cmd.CommandType = System.Data.CommandType.StoredProcedure;

//Add the input parameters to the command object
cmd.Parameters.AddWithValue(“@Name”, txtEmployeeName.Text);
cmd.Parameters.AddWithValue(“@Gender”, ddlGender.SelectedValue);
cmd.Parameters.AddWithValue(“@Salary”, txtSalary.Text);

//Add the output parameter to the command object
SqlParameter outPutParameter = new SqlParameter();
outPutParameter.ParameterName = “@EmployeeId”;
outPutParameter.SqlDbType = System.Data.SqlDbType.Int;
outPutParameter.Direction = System.Data.ParameterDirection.Output;
cmd.Parameters.Add(outPutParameter);

//Open the connection and execute the query
con.Open();
cmd.ExecuteNonQuery();

//Retrieve the value of the output parameter
string EmployeeId = outPutParameter.Value.ToString();
}

Font http://www.codeproject.com/Articles/748619/ADO-NET-How-to-call-a-stored-procedure-with-output

字体http://www.codeproject.com/Articles/748619/ADO-NET-How-to-call-a-stored-procedure-with-output

回答by Genn

That looks more explicit for me:

这对我来说看起来更明确：

int? id = outputIdParam.Value is DbNull ? default(int?) : outputIdParam.Value;

内部？id = outputIdParam.Value 是 DbNull 吗？默认（int？）：outputIdParam.Value；

回答by Greg R Taylor

public static class SqlParameterExtensions
{
    public static T GetValueOrDefault<T>(this SqlParameter sqlParameter)
    {
        if (sqlParameter.Value == DBNull.Value 
            || sqlParameter.Value == null)
        {
            if (typeof(T).IsValueType)
                return (T)Activator.CreateInstance(typeof(T));

            return (default(T));
        }

        return (T)sqlParameter.Value;
    }
}


// Usage
using (SqlConnection conn = new SqlConnection(connectionString))
using (SqlCommand cmd = new SqlCommand("storedProcedure", conn))
{
   SqlParameter outputIdParam = new SqlParameter("@ID", SqlDbType.Int)
   { 
      Direction = ParameterDirection.Output 
   };

   cmd.CommandType = CommandType.StoredProcedure;
   cmd.Parameters.Add(outputIdParam);

   conn.Open();
   cmd.ExecuteNonQuery();

   int result = outputIdParam.GetValueOrDefault<int>();
}

回答by Sandeep Pandey

Create the SqlParamObject which would give you control to access methods on the parameters

创建 SqlParamObject，它可以让您控制访问参数的方法

:

：

SqlParameter param = new SqlParameter();

SqlParameter param = new SqlParameter();

SET the Name for your paramter (it should b same as you would have declared a variable to hold the value in your DataBase)

为您的参数设置名称（它应该与您声明的变量相同以保存数据库中的值）

: param.ParameterName = "@yourParamterName";

： param.ParameterName = "@yourParamterName";

Clear the value holder to hold you output data

清除值持有者以保存您的输出数据

: param.Value = 0;

： param.Value = 0;

Set the Direction of your Choice (In your case it should be Output)

设置您选择的方向（在您的情况下它应该是输出）

: param.Direction = System.Data.ParameterDirection.Output;

： param.Direction = System.Data.ParameterDirection.Output;