在 ADO.NET 中获取输出参数值 [英] Get output parameter value in ADO.NET

问题描述

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

My stored procedure has an output parameter:

@ID INT OUT

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

How can I retrieve this using 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();
}

推荐答案

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

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

使用您的代码示例:

// 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();
}

获取 Parameters[].Value 时要小心，因为类型需要从 object 转换为您声明的类型.并且创建SqlParameter时使用的SqlDbType需要与数据库中的类型相匹配.如果您只想将其输出到控制台，您可能只是在使用 Parameters["@Param"].Value.ToString() (通过 Console.Write() 或 String.Format() 调用).

Be careful when getting the Parameters[].Value, since the type needs to be cast from object to what you're declaring it as. And the SqlDbType used when you create the SqlParameter needs 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).

超过 3.5 年和近 2 万次观看，没有人费心提及它甚至没有编译，原因是我在原始帖子中的小心"评论中指定的原因.好的.根据@Walter Stabosz 和@Stephen Kennedy 的好评修复它，并匹配@abatishchev 问题中的更新代码编辑.

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.

这篇关于在 ADO.NET 中获取输出参数值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！