Loop through Request.Forms to get Field Values for Bulk Update

Question

I have a list of records generated from a search query in my View. There's certain fields that can be edited and next step is do update those fields with one button/action.

The yellow fields are the ones that have been edited, while the white fields still match what is in the database table. Now when I click update all I first get the values of sellprice and casecost from the DB, then I get the values from the form. If the values match then move on, if the values from the form have been changed then update. I have datareader that reads the values from the table/database perfectly fine for each line of records on page.

NpgsqlDataReader dr = cmd.ExecuteReader();

while (dr.Read())
{
   var prod = new ProductViewModel();

   prod.q_guid = Guid.Parse(dr["q_guid"].ToString());                
   prod.q_sellprice = Convert.ToDecimal(dr["q_sellprice"]);                
   prod.q_casecost = Convert.ToDecimal(dr["q_casecost"]);                

   /*
   At this point 
   Need to compare dr.Read q_sellprice and q_casecost
   with changed values in the fields
   if != then update for that record
   */

   /*Lets assign previous values (values in db) to variables*/
   var previousSellprice = prod.q_sellprice;
   var previousCasecost = prod.q_casecost;
   var thatId = prod.q_guid;

   /*Lets get current values from form/list*/
   var priceList = Request.Form["item.q_sellprice"];
   var costList = Request.Form["item.q_casecost"];

   /*eg*/

   if (previousSellprice != currentSellprice || previousCasecost != currentCasecost)
   {
      //lets update record with new values from view
   }

   -> loop move on to next row in view

My DataReader while loop can get the value of each row no problemo. What I am trying to achieve when it gets the values of the first row from the db, then

• I need to check what the current values in the form for that record are
• if they are different then update for that current row
• move on to next row in the view/on page

I have managed to get the array of values for these fields with these variables with the following code. This has the edited/changed fields from the list/form.

var priceList = Request.Form["item.q_sellprice"];
var costList = Request.Form["item.q_casecost"];

On my first run through the loop, I would like to get the values 10.00 and 8.50, do my check, update if necessary.. then move on the next row which will get 3.33 and 8.88, do my check, and update if necessary and so on for the rest of the records on that page.

So how can I loop through Request.Forms in the instance, and get my individual values for one record at a time?

cshtml on view for the fields is

@foreach (var item in Model)
{
    <td>
        € @Html.EditorFor(modelItem => item.q_sellprice, new { name="q_sellprice" })
    </td>
    <td>
        € @Html.EditorFor(modelItem => item.q_casecost, new { name="q_casecost"})
    </td>

Addition: Updating isnt the issue, getting the values of each record from the array while looping through the form fields is.

Answer 1

It is a long description of the problem - but from my understanding, your only problem is, that you want to have some data, which right now is two strings to be as List of operations (data) to perform? Is that correct?

If so - you can have such data in List using Zip method:

    void Main()
    {
        string priceList = "1,2,3,4";
        string costList = "2,3,4,5";
        var prices = priceList.Split(new string[1] { "," }, StringSplitOptions.RemoveEmptyEntries);
        var costs = costList.Split(new string[1] { "," }, StringSplitOptions.RemoveEmptyEntries);
        var collectionToUpdate = prices.Zip(costs, (price, cost) => new PriceToUpdate(price, cost));  
       //do update in database with collectionToUpdate
    }

    public class PriceToUpdate
    {
        public PriceToUpdate(string oldPrice, string newPrice)
        {
            decimal priceTmp;
            if (decimal.TryParse(oldPrice, out priceTmp))
            {
                OldPrice = priceTmp;
            }
            if (decimal.TryParse(newPrice, out priceTmp))
            {
                NewPrice = priceTmp;
            }
        }
        public decimal OldPrice { get; set; }
        public decimal NewPrice { get; set; }
    }

Answer 2

My suggestion would be to re-organise your HTML a bit more and modify the method for getting the fields parsed out. What I have done in the past is include the Key Id (in your case the Guid) as part of the output. So the result in a basic view looks like:

If you notice the name attribute (and Id) are prefixed with the q_guid field. Here is my basic model.

public class ProductViewModelItems
{
    public IList<ProductViewModel> items { get; set; } = new List<ProductViewModel>();
}

public class ProductViewModel
{
    public Guid q_guid { get; set; }

    public decimal q_sellprice { get; set; }

    public decimal q_casecost { get; set; }

    //other properties
}

And my controller just has a simple static model. Of course yours is built from your database.

static ProductViewModelItems viewModel = new ProductViewModelItems()
{
    items = new[]
    {
        new ProductViewModel { q_casecost = 8.50M, q_sellprice = 10M, q_guid = Guid.NewGuid() },
        new ProductViewModel { q_casecost = 8.88M, q_sellprice = 3.33M, q_guid = Guid.NewGuid() },
        new ProductViewModel { q_casecost = 9.60M, q_sellprice = 3.00M, q_guid = Guid.NewGuid() },
        new ProductViewModel { q_casecost = 9.00M, q_sellprice = 5.00M, q_guid = Guid.NewGuid() },
        new ProductViewModel { q_casecost = 10M, q_sellprice = 2.99M, q_guid = Guid.NewGuid() },
    }
};

[HttpGet]
public ActionResult Index()
{
    //load your view model from database (note mine is just static)
    return View(viewModel);
}

Now we construct our form so that we can pull it back in our post method. So I have chosen the format of {q_guid}_{field_name} as

1. q_casecost = {q_guid}_q_casecost
2. q_sellprice = {q_guid}_q_sellprice

The form construction now looks like.

@foreach (var item in Model.items)
{
    <tr>
        <td>
            € @Html.TextBoxFor(modelItem => item.q_sellprice, new {  Name = string.Format("{0}_q_sellprice", item.q_guid), id = string.Format("{0}_q_sellprice", item.q_guid) })
        </td>
        <td>
            € @Html.TextBoxFor(modelItem => item.q_casecost, new {  Name = string.Format("{0}_q_casecost", item.q_guid), id = string.Format("{0}_q_casecost", item.q_guid) })
        </td>
    </tr>
}

Note there a few key items here. First off you cant modify the Name attribute using EditorFor() so I have swapped this out to a TextBoxFor() method.

Next I am overriding the Name attribute (note it must be Name not name [lower case ignored]).

Finally the POST action runs much differently.

[HttpPost]
public ActionResult Index(FormCollection form)
{
    IList<ProductViewModel> updateItems = new List<ProductViewModel>();

    // form key formats
    // q_casecost = {q_guid}_q_casecost
    // q_sellprice = {q_guid}_q_sellprice

    //load your view model from database (note mine is just static)

    foreach(var item in viewModel.items)
    {
        var caseCostStr = form.Get(string.Format("{0}_q_casecost", item.q_guid)) ?? "";
        var sellPriceStr = form.Get(string.Format("{0}_q_sellprice", item.q_guid)) ?? "";

        decimal caseCost = decimal.Zero,
                sellPrice = decimal.Zero;

        bool hasChanges = false;

        if (decimal.TryParse(caseCostStr, out caseCost) && caseCost != item.q_casecost)
        {
            item.q_casecost = caseCost;
            hasChanges = true;
        }

        if(decimal.TryParse(sellPriceStr, out sellPrice) && sellPrice != item.q_sellprice)
        {
            item.q_sellprice = sellPrice;
            hasChanges = true;
        }

        if (hasChanges)
            updateItems.Add(item);
    }
    //now updateItems contains only the items that have changes.

    return View();
}

So there is alot going on in here but if we break it down its quite simple. First off the Action is accepting a FormCollection object which is the raw form as a NameValuePairCollection which will contain all the keys\values of the form.

public ActionResult Index(FormCollection form)

The next step is to load your view model from your database as you have done before. The order you are loading is not important as we will interate it again. (Note i am just using the static one as before).

Then we iterate over each item in the viewmodel you loaded and now are parsing the form values out of the FormCollection.

var caseCostStr = form.Get(string.Format("{0}_q_casecost", item.q_guid)) ?? "";
var sellPriceStr = form.Get(string.Format("{0}_q_sellprice", item.q_guid)) ?? "";

This will capture the value from the form based on the q_guid again looking back at the formats we used before.

Next you parse the string values to a decimal and compare them to the original values. If either value (q_sellprice or q_casecost) are different we flag as changed and add them to the updateItems collection.

Finally our updateItems variable now contains all the elements that have a change and you can commit those back to your database.

I hope this helps.