c# - 使用更新的数据库中的值重新创建列表

Question

我想在更新后重新创建我的列表(在分配位置处)，以便它包含位置表中的新列。这可能吗？我该如何去做呢？

我尝试清除它并尝试再次读取 dataReader，但收效甚微，我是否必须重新运行 SQL 查询？

    public List<BidList> AllocateBids()
    {
        // Determine positions
        string query =
            "SELECT t1.operator_id, t1.datetime, t1.plot_id, t1.position, t2.market_access FROM bid t1 " +
            "JOIN operator t2 ON t1.operator_id = t2.id WHERE t1.status='Queued' AND t1.postcode='" + _plot + "'" +
            "ORDER BY t2.market_access ASC, t1.datetime ASC";
        var bidList = new List<BidList>();
        var cmd = new MySqlCommand(query, _connection);
        MySqlDataReader dataReader = cmd.ExecuteReader();
        while (dataReader.Read())
        {
            var item = new BidList
            {
                OperatorId = dataReader["operator_id"] + "",
                PlotId = dataReader["plot_id"] + "", 
                Position = dataReader["position"] + "",
                Datetime = dataReader["datetime"] + "",
                MarketAccess = dataReader["market_access"] + "",
            };
            bidList.Add(item);
        }

        // Allocate positions 
        for (var i = 0; i < bidList.Count; i++)
        {
            var position = i + 1;
            query = "UPDATE bid SET position='" + position + "' WHERE operator_id='" + bidList[i].OperatorId +
                        "'";
            var dbObject = new DbConnect();
            dbObject.InsertBooking(query);
        }

        return bidList;

        // Recreate list
        //bidList.Clear();
        //while (dataReader.Read())
        //{
        //    var item = new BidList
        //    {
        //        OperatorId = dataReader["operator_id"] + "",
        //        PlotId = dataReader["plot_id"] + "",
        //        Position = dataReader["position"] + "",
        //        Datetime = dataReader["datetime"] + "",
        //        MarketAccess = dataReader["market_access"] + "",
        //    };
        //    bidList.Add(item);
        //}

        //CloseConnection();
        //return bidList;
    }

Answer 1

如果您只更新 1 个字段，并且它是位置 1，而不是重新创建列表，为什么不像这样重新排序:

// Allocate positions 
    for (var i = 0; i < bidList.Count; i++)
    {
        var position = i + 1;

        bidList[i].Position = position;

        query = "UPDATE bid SET position='" + position + "' WHERE operator_id='" + bidList[i].OperatorId +
                    "'";
        var dbObject = new DbConnect();
        dbObject.InsertBooking(query);
    }

    return bidList.OrderBy(bid => bid.Position);

bidList.OrderBy() 将返回相同的列表，按您刚刚分配的位置值排序