[C＃/ SQL]从存储过程中读取多个值 [英] [C#/SQL] Read Multiple values from stored procedure

问题描述

我有一个简单的存储过程，但我还是希望它是一个存储过程，主要是出于设计考虑（在更改表中的内容时去查找随机查询中的错误...）



无论如何，程序应该是类似的东西

  CREATE   PROCEDURE  _Proc（ @ id   int ， @ events   int  输出）
  AS  
  SELECT   @ events  = EventID 来自 表  where  ID =  @ id  
  RETURN  

如何在使用SqlCommand时在C＃代码中获取多个值？



编辑：



我想从@events中读取值，就像我在常规SQL命令中从EventID读取它们一样。



SQL数据读取器在这种情况下是否仍然有效？如果是这样，它是如何操作的？

 使用（SqlCommand c =  new  SqlCommand（  _ Proc ）{CommandType = CommandType.StoredProcedure}）
 {
 c.Connection = connection ... 
  //  那又怎样？如何提取EventID的值？使用SqlDataReader？如果是这样，以正常方式，好像是一个简单的查询？ 
} 

解决方案

< pre lang =cs> 使用（SqlConnection conn = new SqlConnection（））
{
SqlCommand cmd = new SqlCommand（ _ proc ，conn）;
cmd.CommandType = CommandType.StoredProcedure;

cmd.Parameters.Add（ new SqlParameter（ @ ID，SqlDbType.Int， int .MaxValue，ParameterDirection.Input））;

cmd.Parameters.Add（ new SqlParameter（ @ events，SqlDbType.Int， int .MaxValue，ParameterDirection.Output））;
@events = EventID
conn.Open（）;

cmd.ExecuteNonQuery（）;
int id = cmd.Parameters [ @events]值。

conn.Close（）;
}

如果要从单个列返回多个值，则存储过程错误。你不应该使用OUTPUT，因为它只返回一个值。

 创建  PROCEDURE  _Proc（ @ id   int ）
  AS  
 开始 
  SELECT  EventID 来自 表 其中 ID =  @ id  
 结束 

然后，您将使用SqlDataReader读取存储过程返回的值。这个例子只是将返回的eventIds添加到列表中。

  int  id = ????;  //  您将要传递到存储过程的值是什么 
 List< int> eventIds =  new  List< int>（）; 
 
 使用（SqlConnection conn =  new  SqlConnection（））
 {
 SqlCommand cmd =  new  SqlCommand（  _proc，conn）; 
 cmd.CommandType = CommandType.StoredProcedure; 
 cmd.Parameters.Add（ new  SqlParameter（  @ID，id））; 
 
 conn.Open（）; 
 
 使用（SqlCommand command =  new  SqlCommand（queryString，connection））
 {
 使用（SqlDataReader reader = command.ExecuteReader（）;）
 {
  while （reader.Read（））
 eventIds.Add（ int  .Parse（reader [  EventId]。ToString（）））; 
} 
} 
} 
 < /   int  >  < /   int  < span class =code-keyword>>  

只有在关闭阅读器后才能访问输出参数的值。

I have a stored procedure which is simple, but I want it anyway to be a stored procedure mainly due to design considerations (Go find the bugs in the random queries when changing stuff in the tables...)

Anyway, the procedure is supposed to be something like that

CREATE PROCEDURE _Proc(@id int, @events int OUTPUT)
AS
   SELECT @events = EventID from Table where ID = @id
   RETURN

How can I get multiple values in my C# code when using an SqlCommand?

EDIT:

I want to read the values from @events, just as if I was reading them from EventID in a regular SQL Command.

Does SQL Data Reader still work in such case? If so, how can it be operated?

using(SqlCommand c = new SqlCommand("_Proc") { CommandType = CommandType.StoredProcedure })
{
c.Connection = connection...
// Then what? How do I extract the values of EventID? using SqlDataReader? If so, in the normal way, as if it was a simple query?
}

解决方案

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

    cmd.Parameters.Add(new SqlParameter("@ID", SqlDbType.Int, int.MaxValue, ParameterDirection.Input));

    cmd.Parameters.Add(new SqlParameter("@events", SqlDbType.Int, int.MaxValue, ParameterDirection.Output));
@events = EventID
    conn.Open();

    cmd.ExecuteNonQuery();
    int id = cmd.Parameters["@events"].Value;

    conn.Close();
  }

If you want to return multiple values from a single column then your stored procedure is wrong. You should not be using OUTPUT since it will only return a single value.

CREATE PROCEDURE _Proc(@id int)
AS
Begin
   SELECT EventID from Table where ID = @id
End

You will then use a SqlDataReader to read the values returned by the stored procedure. This examples just adds the returned eventIds to a list.

int id = ????;  // Whatever value you are going to passed into the stored procedure
List<int> eventIds = new List<int>();

using (SqlConnection conn = new SqlConnection())
{
    SqlCommand cmd = new SqlCommand("_proc", conn);
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.Parameters.Add(new SqlParameter("@ID", id));
 
    conn.Open();
 
    using (SqlCommand command = new SqlCommand(queryString, connection))
    {
	using (SqlDataReader reader = command.ExecuteReader();)
	{
	    while (reader.Read())
		eventIds.Add(int.Parse(reader["EventId"].ToString()));
	}
    }
}
</int></int>

You will be able to access the value of output parameter after closing the reader only.

这篇关于[C＃/ SQL]从存储过程中读取多个值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！