代码之家 › 专栏 › 技术社区 › Samreen

获取存储过程的输出[重复]

output ado.net stored-procedures sql c#

0

Samreen · 技术社区 · 8 年前

我有一个 stored procedure 它返回sql中最近更新的行的ID列表。如何获取数组或列表?

CREATE procedure [dbo].[spUpdateResolutionStatus]
@SummaryId int,
@AlertType varchar(500),
@AlertServer varchar(250),
@Subject varchar(250)
as
Begin
declare @insertedIds table (id int)
    Update WCFLogs set ResolutionSummary_OID = @SummaryId, Status_oid = 2
    output inserted.log_oid into @insertedIds
where AlertType=@AlertType and AlertServer= @AlertServer and Log_Subject = @Subject;
select id from @insertedIds
end
GO

我的方法:

 public int[] UpdateIssueResolutionStatus(Int32 summaryId, string alerttype, string alertserver, string subject)
{
    int[] rowseffected;
    string cs = ConfigurationManager.ConnectionStrings["LogsData"].ConnectionString;
    using (SqlConnection con = new SqlConnection(cs))
    {
        SqlCommand cmd = new SqlCommand("spUpdateResolutionStatus", con);
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.Add(new SqlParameter("@SummaryId", summaryId));
        cmd.Parameters.Add(new SqlParameter("@AlertType", alerttype));
        cmd.Parameters.Add(new SqlParameter("@AlertServer", alertserver));
        cmd.Parameters.Add(new SqlParameter("@Subject", subject));
        con.Open();
        rowseffected =(Int)cmd.ExecuteScalar();
    }
    return rowseffected;
}

ExecuteScalar 给我第一行的id,我怎么才能得到完整的列表?

1 回复 | 直到 8 年前

1

3

CodeFuller 8 年前

public int[] UpdateIssueResolutionStatus(Int32 summaryId, string alerttype, string alertserver, string subject)
{
    int[] rowseffected;
    string cs = ConfigurationManager.ConnectionStrings["LogsData"].ConnectionString;
    using (SqlConnection con = new SqlConnection(cs))
    {
        SqlCommand cmd = new SqlCommand("spUpdateResolutionStatus", con);
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.Add(new SqlParameter("@SummaryId", summaryId));
        cmd.Parameters.Add(new SqlParameter("@AlertType", alerttype));
        cmd.Parameters.Add(new SqlParameter("@AlertServer", alertserver));
        cmd.Parameters.Add(new SqlParameter("@Subject", subject));
        con.Open();

        using (IDataReader reader = cmd.ExecuteReader())
        {
            List<int> ids = new List<int>();
            while (reader.Read())
            {
                ids.Add(reader.GetInt32(0));
            }

            return ids.ToArray();
        }
    }
}