SQL存储过程缺少第一个变量

I have output from a stored procedure in SQL Server Management Studio that looks like this

我从SQL Server Management Studio中的存储过程输出，如下所示

id  date                   notes    code           order status (No column name)    (No column name)
3   2016-12-08 00:00:00.000         AVL             -1      D   NULL                 16
3   2016-12-08 00:00:00.000         RSU             1       D   3                    2
3   2016-12-08 00:00:00.000         TOW             2       D   6                    5
3   2016-12-08 00:00:00.000         BAD             3       D   1                    1
3   2016-12-08 00:00:00.000         DEL             4       D   4                    2
3   2016-12-08 00:00:00.000         SUP             5       D   3                    3
3   2016-12-08 00:00:00.000         CLA             6       D   2                    1
3   2016-12-08 00:00:00.000         SG              7       D   1                    1
3   2016-12-08 00:00:00.000         RV              8       D   1                    1
3   2016-12-08 00:00:00.000         TEN             999     D   NULL                 0
3   2016-12-09 00:00:00.000         AVL             -1      D   NULL                 17
3   2016-12-09 00:00:00.000         RSU             1       D   3                    2
3   2016-12-09 00:00:00.000         TOW             2       D   6                    6
3   2016-12-09 00:00:00.000         BAD             3       D   1                    1
3   2016-12-09 00:00:00.000         DEL             4       D   4                    2
3   2016-12-09 00:00:00.000         SUP             5       D   3                    3
3   2016-12-09 00:00:00.000         CLA             6       D   2                    1
3   2016-12-09 00:00:00.000         SG              7       D   1                    1
3   2016-12-09 00:00:00.000         RV              8       D   1                    1
3   2016-12-09 00:00:00.000         TEN             999     D   NULL                 0

etc.

等等

the output I'm trying to get is

我想要的输出是

                AVL RSU TOW BAD DEL SUP CLA SG  RV  TEN
2016-12-08      16   2  5   1   2   3   1   1   1    0
2016-12-09      17   2  6   1   2   3   1   1   1    0

My Controller has this code

我的控制器有这个代码

var Outputmodel = new List<SP_Model>();
var command = db.Database.Connection.CreateCommand();
command.CommandText = "dbo.pr_Name";
command.Parameters.Add(new SqlParameter { ParameterName = "@date_from", SqlDbType = System.Data.SqlDbType.DateTime, Value = ViewBag.usDate });
command.Parameters.Add(new SqlParameter { ParameterName = "@date_to", SqlDbType = System.Data.SqlDbType.DateTime, Value = "2016-12-21 00:00:00" });
command.Parameters.Add(new SqlParameter { ParameterName = "@method", SqlDbType = System.Data.SqlDbType.Int, Value = 3 });
command.CommandType = System.Data.CommandType.StoredProcedure;

 using (var SPOutput = comman3.ExecuteReader())
 {
  while (SPOutput.Read())
   {
    foreach (var row in SPOutput)
     {
      Outputmodel.Add(new SP_Model()
       {
         id = (decimal)SPOutput["id"],
         date = (DateTime)SPOutput["date"],
         notes = SPOutput["notes"].ToString(),
         code = (string)SPOutput["code"],
         order = (int)SPOutput["order"],
         status = (string)SPOutput["status"],
         Column1 = SPOutput[6] as int?,
         Column2 = SPOutput[7] as int?
                        });
    }
     return View(Outputmodel);
   }
  }

and my View has this

我的观点有这个

    <tr>
    <td>date</td>
    <td>avl</td>
    <td>rsu</td>
    <td>tow</td>
    <td>bad</td>
    <td>del</td>
    <td>sup</td>
    <td>cla</td>
    <td>sg</td>
    <td>rv</td>
    <td>ten</td>
</tr>

    @foreach (var itemgroup in Model.GroupBy(item => item.date))
{
   <tr>
        <td>@itemgroup.Key.Value.ToString("dd/MM/yyyy")</td>
        @foreach (var item in itemgroup)
        {
        <td>@item.Column2</td>
        }
    </tr>
}

data is being displayed, but I am missing the First Value (16) from the foreach loop.. and I cannot figure why (it might be that I have been staring at the same code for the last week).

正在显示数据，但是我错过了foreach循环中的First Value（16）...我无法理解为什么（可能是我上周一直在盯着相同的代码）。

Can anyone help me figure this out ?

任何人都可以帮我解决这个问题吗？

thanks

谢谢

1 个解决方案

#1

I think the issue lies in the code that iterates over the result set. The while loop is sufficient without the nested foreach.

我认为问题在于迭代结果集的代码。没有嵌套的foreach，while循环就足够了。

while (SPOutput.Read())
{
    Outputmodel.Add(new SP_Model() {
        ...
    });
}