I have to put a disclaimer on this post: I have no idea if this is the best way to do this. This is just the way that I found was the easiest and met my requirements. I looked at triggering a stored procedure to update tables through a wcf ria service but I quickly realised that I could do everything I needed to do by using Entity Framework. I think this avoided adding unecessary complexity and my hope is that other people that only have LightSwitch and don't have Visual Studio Professional could use this instead of trying to expose a stored procedure. Hopefully LightSwitch will some day give us access to stored procedures out of the box - if you also think this is important then vote for it in the LightSwitch suggestions forum here: http://visualstudio.uservoice.com/forums/127959-visual-studio-lightswitch/suggestions/2148963-allow-stored-procedures-to-be-run-easily
Anyway, on to the solution.
To start with add a new table to the solution. I've called mine BulkEdit for simplicities' sake. What we will be doing is using this table to make a list of the records that we want to edit. The table looks like this:
The only thing on this table is a relationship to the table that we want to perform the bulk edit on. This is all that we need on the table - we'll do the rest on the screen.
Let's add a new List Detail screen with the Bulk Edit table as the screen data:
Once we've set up the screen we need to set up some parameters to take in the new value that we want to set and the field that we want to change. To do this we first click on Edit Query shown below:
Then Click on Add Parameter:
We need to Add a Parameter called 'Field' which will determine which field we will update and another called 'NewValue' which will contain the value we are wanting to populate into that field on the products that we put on our list.
Next we need to bind the parameters to properties. Do this by first clicking on the field parameter and then clicking on parameter binding in the properties box and choosing +add property:
Make sure that under the validation heading in the properties box that 'is required' is ticked. That way users can't update fields with nothing (although porper database desing should also prevent this for fields that require a value).
Next we need to add the parameters to the screen so that users can enter values into them.
Click on 'Add Data Item at the top of the screen design page
Fill out the box as shown Below for the Field item and then repeat the process for the NewValue item:
I have made a choice list for the Field item because that way users don't need to worry about spelling things right or remembering field names. Here is that list:
Now we need to set out the layout of the screen. I've added in some headings and things and also shown a table that will display all changes made to the products table that I'm changing. I got this from an excellent John Stallo tutorial here: http://www.microsoft.com/visualstudio/en-us/lightswitch/advanced-videos/advanced-customization The part that concerns this begins at around 8:45. This is so that users can see the effects of their actions. I've removed those in the screenshot below though so you can see the bare bones of what you need
Now that the aesthetics are set up we can go ahead and make it work. I've added a new button on the command bar of the bulk edit data grid:
Once you've added the button right click on it and choose 'Edit Execute Code'. This will take us to where we can define what happens when a user clicks on the button.
Here is the full code of what I have put in with comments where applicable, please feel free to use it on your application. I apologise in advance because it WILL require some customisation to meet your needs!
Here is the _Execute method:
//Here is where we call the function so that it executes when the button is pushed
BulkEdit();
}
Above this within the LightSwitch application namespace you'll need these two functions in this order:
private BulkEdit GetNextRecord(BulkEdit CurrentBulkEdit)
{
//Gets the next record on the bulk edit table after the
//bulk edit passed to the function
int CurrentID = CurrentBulkEdit.Id;
var ProductsGreaterThan = BulkEdits.OrderBy(x => x.Id).SkipWhile(x => x.Id <= CurrentID);
return ProductsGreaterThan.FirstOrDefault();
}
private void BulkEdit()
{ //This function performs the Bulk edit
//first we find out the Id of the last bulk edit so we know where to stop
BulkEdit LastBulkEdit = BulkEdits.OrderBy(x => x.Id).Last();
int LastBulkEditId = LastBulkEdit.Id;
//then we get the Id of the first bulk edit so we have somewhere to start
BulkEdit ThisBulkEdit = BulkEdits.OrderBy(x => x.Id).FirstOrDefault();
int ThisBulkEditId = ThisBulkEdit.Id;
//this while loop will cycle through the bulk edit table making he change requested to each record on it
while (ThisBulkEditId <= LastBulkEditId && ThisBulkEdit != null)
{
//get the product entity of the product which we want to edit
Product ProductCode = ThisBulkEdit.Product;
/*
* I've made this a switch for several reasons
* 1: I need to execute different behaviour depending on the field in question and the type
* of field it is
* 2: There are some fields that I don't want people to be able to manipulate
* 3: I couldn't find a better way to do it - if you know of one then please comment! :-)
*
* There is a case for each field name that was present in my choice box on the field name item
*/
switch (Field)
{
case "ProductBrand":
//for most of these it is simple - replace the old value with the new value
ProductCode.ProductBrand = NewValue;
break;
case "ProductColour":
ProductCode.ProductColour = NewValue;
break;
case "ProductComments":
ProductCode.ProductComments = NewValue; break;
case "ProductConsumables":
ProductCode.ProductConsumables = NewValue;
break;
case "ProductCrossSell":
ProductCode.ProductCrossSell = NewValue; break;
case "ProductSize":
ProductCode.ProductSize = NewValue;
break;
case "ProductStatus":
ProductCode.ProductStatus = NewValue; break;
case "ProductUpSell":
ProductCode.ProductUpSell = NewValue;
break;
case "ProductSupplier":
//where the value being replaced is a relationship you can't just put in a string value
//you need to find the entity. SingleOrDefault lets you do that.
/*
* This is important -if you want this to work then the table that this field relates
* to -must- be inculded as a query data item on your bulk edit screen
*/
Supplier NewSupplier = Suppliers.SingleOrDefault(x => x.SupplierCode == NewValue);
ProductCode.ProductPreferredSupplier = NewSupplier;
break;
case "ProductBaseUnit":
Unit NewBaseUnit = Units.SingleOrDefault(x => x.UnitCode == NewValue);
ProductCode.ProductBaseUnit = NewBaseUnit;
break;
case "ProductPurchaseUnit":
Unit NewPurchaseUnit = Units.SingleOrDefault(x => x.UnitCode == NewValue);
ProductCode.ProductPurchaseUnit = NewPurchaseUnit;
break;
default:
break;
}
//as a matter of tidy house keeping we want to delete the item from the table once we're done
BulkEdit BulkEditToDelete = ThisBulkEdit;
//then we get the next record to edit
ThisBulkEdit = GetNextRecord(BulkEditToDelete);
//get the id of that record
if (ThisBulkEdit != null)
{
ThisBulkEditId = ThisBulkEdit.Id;
}
//delete the record we just edited
BulkEditToDelete.Delete();
//and start the loop all over again until we run out of records.
}
return;
}
Once you've got all of that in there and formatted to match the fields on the table that you're working on you're ready to go and try it out.
One of the things that I really like about doing this within LightSwitch instead of through a stored procedure is that you still get all of LightSwitch's built in validation. You also get a basic kind of undo button - if you accidentally hit the execute bulk edit button then as long as you don't hit the save button nothing in the database has changed. It is VERY nice to not have to code those things in!
No comments:
Post a Comment