Making integers searchable in LightSwitch

So you've got an integer field that you want your users to be able to search on but lightswitch won't allow it.  You could do something like Rich Dudley suggests here.  Rich's method is awesome and it works but if you're like me and you don't like the idea of having a search box at the top of the grid and another search box above it then you can do this....

First,  go to the table that has the integer you want to search on :

My field is called 'ProductCode'.  Next create a new field of a String type.  Call it whatever your field is named with 'String' after it.  Mine is 'ProductCodeString' but yours will most likely be different.  :-)

Then tick the 'Is Searchable' box.

Next we need to go back to the integer field that we want to search on and click on it:

 
Then click on Write Code and choose the _Changed method for the field you clicked on (ProductCode_Changed in my case).

In the code for the method use the following:

Partial void ProductCode_Changed()
{
    this.ProductCodeString = this.ProductCode.ToString();
}

So now whenever the ProductCode field is changed LightSwitch will save a copy of the field as a string in another field so that you can search on it.  That's fine and dandy if you're starting from scratch but if you've already got a a lot of data in your database then you'll want to use a query to update the ProductCodeString field with the ProductCode as a string.  You can do that with a query similar to this one:

UPDATE Products SET ProductCodeString = CAST(ProductCode AS nvarchar(255))

But of course personalize the field and table names.

This is a bit of a stinky hack to be honest.  It intentionally creates database bloat and the only real function of the information is to provide searchability - is is completely useless in all other contexts.  So think carefully before you use this - if you've got billions of records on the table in question then creating an extra string field for every record might not be a good idea..... 

I've used a very similar method to make fields that are relationships sortable as well.  If anyone wants me to explain that in detail then please comment.

This code is taken from my LightSwitch Star Contest Entry.  Have a look if you're interested!

Exporting XML from LightSwitch Part 2 - Azure

As I explained in my last blog My LightSwitch Star Contest entry - Data Centre - A Product Information and Promotion Management System needed to be fully integrated with our ERP. Our ERP handles incoming data quite well via XML so I had a strong desire to use it. 

Last time I showed a method to let users press a button in the application to extract the contents of a table or view to an XML file.  This was useful for me as it allowed my users to quickly extract the information and upload it to our ERP immediately.  However, as a daily process I want to take any changes made in Data Centre (the LightSwitch Application) and replicate those changes in our ERP system so that all of our stores have the most up-to-date product information. I achieved this by creating a scheduled task that runs a batch file which creates an XML file that is processed by our ERP. 

The batch file uses BCP to run a query on the LightSwitch database (In SQL Azure in my case).  Here is the structure of that batch file (I've replaced the query with a generic one as you don't need to be bored by my ridiculously long one!):

@ECHO OFF
cd C:\Extracts
bcp "SELECT * FROM Products FOR XML RAW ('Item'), TYPE, ELEMENTS XSINIL" queryout C:\Extracts\Temp.xml -c
    -U YourSQLAzureUsername@YourServerHomeName -S tcp:YourServerHomeName.database.windows.net
    -P YourPassword -d YourDatabaseName
copy /b rootopen.txt+Temp.xml+rootclose.txt Extract.xml
del Temp.xml
@ECHO OFF

Anything in red is information that you'll need to populate yourself.  The two lines that I've indented need to be on the same line that starts with bcp in your batch file or it doesn't like it.

The first thing is the query  - you can use whatever you want written in standard TSQL.  Just make sure you keep the part at the end that isn't in red or it won't come out as XML.

I've shown where you can get this information in Azure on the following screenshot:

YourSQLAzureUsername is a forms authentication login that you have setup with access to your SQL Azure database - you can use the admin login shown above.

YourServerHomeName is found in serveral different places marked above. Your Database name can also be found there.

Once you've setup the batch file you need to create two text files in the extracts directory (you can use any directory you want - just make sure you change the directory in the batch file)  rootopen.txt and rootclose.txt. 

rootopen.txt contains this text:

<?xml version="1.0" ?>
<rootnode xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="urn:Export">

rootclose.txt contains this text:

</rootnode>

We need these two files to make the XML that BCP creates well formed.  Otherwise it's just a bunch of text with some labels in pointy braces.

Once you've done that your batch file is all ready to go.  I have a scheduled task that runs mine every morning.  That's the real advantage of doing it this way rather than extracting from the front end of the application - it can be automated.

The XML file this creates will look similar to this:

<?xml version="1.0" ?>
<rootnode xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="urn:Export">
     <Item>
        <Id>1</Id>
        <ProductCode>7501894</ProductCode>
        <ProductDescription>a4 paper</ProductDescription>
        <ProductDateCreated>10/01/2012</ProductDateCreated>
        <ProductDateLastModified>25/01/2012</ProductDateLastModified>
        <ProductMinimumOrderQuantity xmlns:nil="true" />
     </Item>
</rootnode>

which is conveniently exactly the same as the extract from the front end we created in my previous blog.

Two ways to do the same thing - pick which ever meets your requirements better.  :-)

Exporting XML from LightSwitch

Have you ever needed to extract an XML file based on a table in LightSwitch?  I have. So I did something about it.

Data Centre (My LightSwitch Star Contest entry - Data Centre - A Product Information and Promotion Management System) need to be fully integrated with our ERP.  Our ERP handles incoming data quite well via XML so I had a strong desire to use it.  Unfortunately I couldn't find how to do this anywhere.

What I did find was Dan Seefeldt's article on how to import and export CSV's (found here).  So I took what Dan had done and changed it to export an XML file rather than a CSV.  Full credit must go to Dan Seefeldt for this because It would have taken me much longer to figure out if not for his excellent initial work.

Our final goal is to provide a button on a table that exports that table's contents to an XML file.

We achieve this by first creating a new class in the client folder.  In order to do this you will need to be in file view rather than logical view. Switch to file view by doing this:

Then expand the client folder, right click on User Code and choose Add then  Class.  This dialog box will appear:

Choose C# class from the list and name the class XMLExporter. If you love VB instead of delicious curly braces then you have my permission to select a VB class instead of C#.

Here is the Code for the Class (In C#):

using System;
using System.Linq;
using System.Collections;
using System.Collections.Generic;
using System.Net;
using System.Windows;
using System.Windows.Controls;
using System.Windows.Documents;
using System.Windows.Ink;
using System.Windows.Input;
using System.Windows.Media;
using System.Windows.Media.Animation;
using System.Windows.Shapes;
using System.Xml.Linq;
using Microsoft.LightSwitch;using Microsoft.LightSwitch.Threading;
namespace LightSwitchApplication.UserCode
{
    public class XmlExporter
    {
        private static void ExportSingle(
            System.IO.StreamWriter writer, IEntityObject entity, string[] properties)
        {
            List<string> stringArray = new List<string>();
            Microsoft.LightSwitch.Details.IEntityProperty currentProperty;
            //open the Item element
            writer.WriteLine("<Item>");

            // Write each property to the string array
            foreach (string prop in properties)
            {
                try
                {
                    // Get the property from the entity by name
                    currentProperty = entity.Details.Properties[prop];
                }
                catch (Exception)
                {
                    throw new InvalidOperationException(String.Format(
                        "A property named {0} does not exist on the entity named {1}.",
                        prop, entity.Details.Name));
                }
              
                if (currentProperty.Value == null)
                {
                    //if the value of the property is null
                    //then add a nil element to the xml file
                    XElement Element = new XElement(currentProperty.Name.ToString(),
                        new XAttribute(XNamespace.Xmlns + "nil","true"));
                    writer.WriteLine(Element.ToString());
                }
                else
                {
                    //otherwise add an element named the
                    //property name with the value of the property
                    XElement Element = new XElement(currentProperty.Name.ToString(),
                        currentProperty.Value.ToString());
                    writer.WriteLine(Element.ToString());
                }
           }
            //close the item element
            writer.WriteLine("</Item>");
        }
        private static void PerformExport(System.IO.Stream file,
            IEnumerable entitiesToExport, string[] properties)
        {
            // Initialize a writer
            System.IO.StreamWriter writer = new System.IO.StreamWriter(file);
            writer.AutoFlush = true;
            //create the xml file header
            writer.WriteLine("<?xml version=\"1.0\" ?>" +
"<rootnode xmlns:xsi=\http://www.w3.org/2001/XMLSchema-instance\"xmlns=\"urn:Export\>");

            // Export each entity separately
            foreach (IEntityObject entity in entitiesToExport)
            {
                ExportSingle(writer, entity, properties);
            }
            //create the xml file footer
            writer.WriteLine("</rootnode>");
        }

        public static void PromptAndExportEntities(
            IEnumerable entitiesToExport, string[] properties)
        {
            System.IO.Stream stream = null;
            // SaveFileDialog() must be opened on the UI thread
            Dispatchers.Main.Invoke(() =>
            {
                System.Windows.Controls.SaveFileDialog dlg =
                    new System.Windows.Controls.SaveFileDialog();
                dlg.Filter =
                    "XML Files (*.xml)|*.xml|Text Files (*.txt)|*.txt";
                dlg.DefaultExt = "xml";
                if (dlg.ShowDialog() == true)
                {
                    stream = dlg.OpenFile();
                }
            });
            if (stream != null)
            {
                PerformExport(stream, entitiesToExport, properties);
                // Need to close the file on the UI thread as well
                Dispatchers.Main.Invoke(() =>
                {
                    stream.Close();
                });
            }
        }
    }
}

With the Class set up you can go ahead and add a button to the command bar of a screen.  Call it something creative like 'Export to XML'.  Right Click on the new button and choose Edit Execute Code. 

Use this code to wire up the button to the export function:

partial void ExportToXML_Execute()
{
    List<string> props = new List<string>();
    // Only export storage properties
    foreach (var prop in this.YOUR_TABLE_NAME.SelectedItem.Details.Properties.
        All().OfType<Microsoft.LightSwitch.Details.IEntityStorageProperty>())
    {
        props.Add(prop.Name);
    }
    XmlExporter.PromptAndExportEntities(this.DataWorkspace.ApplicationData.YOUR_TABLE_NAME, props.
        ToArray());
}

And make sure you include:

using LightSwitchApplication.UserCode;

At the top of the .cs file so that you can call the XMLExporter class.

Then you're all ready to go.  When the button is clicked and the user selects what name to save it as the code will produce an XML file like this one:

<?xml version="1.0" ?>
<rootnode xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="urn:Export">
    <Item>
        <Id>1</Id>
        <ProductCode>7501894</ProductCode>
        <ProductDescription>a4 paper</ProductDescription>
        <ProductDateCreated>10/01/2012</ProductDateCreated>
        <ProductDateLastModified>25/01/2012</ProductDateLastModified>
        <ProductMinimumOrderQuantity xmlns:nil="true" />
    </Item>
</rootnode>

But obviously with more Items if you've got more than one row on the table you're exporting from. :-)

Stay tuned for my next blog where I'll show you another technique that my LightSwitch Star Contest Entry uses to extract XML directly from Azure using a scheduled task.  That method is particularly handy for extracting a large amount of data on a daily basis from your application.

Using ComponentOne's Chart for Silverlight in LightSwitch

It's no secret that ComponentOne's OLAP for LightSwitch is the must have extension for any data-centric application you're creating in Lightwitch.  What I was pleasantly surprised to discover was that along with OLAP you also get ComponentOne's Chart for Silverlight.  This article will show how I used Chart for Silverlight to show pricing history for products in my LightSwitch star contest Entry : Data Centre: A Product Information and Promotion Management System.

Here is the screen that shows the graph:

To re-create it repeat the following steps:

First of all add a new custom control to the part of the screen that you want the chart to appear.  You will be presented with the Add Custom Control dialog box appears:

Expand C1.Silverlight.Chart  and the child of it and then click on Chart.  If you don't see C1.Silverlight.Chart then you'll need to click on the Add Reference button and add a reference to it.  Probably check to make sure that you've got the OLAP extension added to your project as well.
Next remove the Add, Edit and Delete buttons from the command bar of the new control.  Then Click on Write Code and add the following function:

void PriceHistory_ControlAvailable(object sender, ControlAvailableEventArgs e)
{
    //begin updating the chart
    //you need to specify that it is a C1Chart control to gain access to the relevant functions
    //The best way that I found to do this was to use a _ControlAvailable function
    ((C1Chart)e.Control).BeginUpdate();
    int SelectedProductId = this.Products.SelectedItem.Id;
    int SelectedProductPreferredSupplierId = this.Products.SelectedItem.ProductPreferredSupplier.Id;
    int SelectedProductBaseUnitId = this.Products.SelectedItem.ProductBaseUnit.Id;
    //create the raw data
    //of course you would use your own method to find your data here -
    //I'm finding product price records based on the Product i've selected
    var XYSeriesRawData = ProductPrices.
        Where(x => x.ProductPriceProductCode.Id == SelectedProductId
            && x.ProductPriceSupplier.Id == SelectedProductPreferredSupplierId
            && x.ProductPriceUnit.Id == SelectedProductBaseUnitId);
    XYSeriesRawData.OrderBy(x => x.ProductPriceStartDate);
    int XYSeriesCount = 0;
    foreach (ProductPrice ThisProductPrice in XYSeriesRawData)
    {
        XYSeriesCount++;
    }
    //create the data arrays
    //again, use your own data
    //I'm finding the date the price was created for the x axis (the horizontal one)
    DateTime[] XSeriesArray;
    XSeriesArray = new DateTime[XYSeriesCount];
    //and the price for the y axis (the vertical one)
    decimal[] YSeriesArray;
    YSeriesArray = new decimal[XYSeriesCount];
    //then for each record in my raw data I want to find the date
    //and the price to plot on the line graph.
    int SeriesCount = 0;
    foreach (ProductPrice ThisProductPrice in XYSeriesRawData)
    {
        XSeriesArray[SeriesCount] = ThisProductPrice.ProductPriceStartDate;
        YSeriesArray[SeriesCount] = ThisProductPrice.ProductPriceCostExGST;
        SeriesCount++;
    }
    //create data series
    XYDataSeries ds = new XYDataSeries();
    ds.XValuesSource = XSeriesArray;
    //the y values need to be attributed to ValuesSource
    //(so don't bother looking for YValuesSource like i did..)
    ds.ValuesSource = YSeriesArray;
    //add the data to the control.
    ((C1Chart)e.Control).Data.Children.Add(ds);
    //set chart type (you could use this same code for other types of chart)
    ((C1Chart)e.Control).ChartType = ChartType.Line;
    //set chart theme - Office 2007 and office 2007 silver are my favourites.
    ((C1Chart)e.Control).Theme = ChartTheme.Office2007Silver;
    //set axis names and settings
    ((C1Chart)e.Control).View.AxisY.Title = CreateTextBlock("Price", 8);
    //I want prices to show with 4 deciaml places so I use AnnoFormat
    ((C1Chart)e.Control).View.AxisY.AnnoFormat = "#.####0";
    //I've found that because my graphs fill only a 
    //small space using auto min and auto max let me
    //use the space most effectively.  Of course this
    //might change depending on the data you're working
    //with - this does not provide and consistency 
    ((C1Chart)e.Control).View.AxisY.AutoMin = true;
    ((C1Chart)e.Control).View.AxisY.AutoMax = true;
    //do the same for the x axis
    ((C1Chart)e.Control).View.AxisX.Title = CreateTextBlock("Date", 8);
    //if you have times on the graph make sure to use the IsTime method
    ((C1Chart)e.Control).View.AxisX.IsTime = true;
    ((C1Chart)e.Control).View.AxisX.AnnoFormat = "MMM-yy";
    ((C1Chart)e.Control).View.AxisX.AutoMin = true;
    ((C1Chart)e.Control).View.AxisX.AutoMax = true;
    //finish the update
    ((C1Chart)e.Control).EndUpdate();
}

And that's it - the graph is all ready to go.  I haven't done it but you could easily change the chart type and the data to meet a different set of needs. 

You can find the ComponentOne OLAP for LightSwitch extension here: http://www.componentone.com/SuperProducts/OLAPLightSwitch/

If you haven't tried it yet then stop what you're doing and download it right now because at some stage in your development career this extension is going to save you a lot of time and money!

Bulk editing in LightSwitch without stored procedures

My requirements for my project meant that I needed a way to quickly and easily change the value of a particular field on multiple records at the same time.  For instance if my company decides to change the category of 100 products in the 'pens' category to be in the  'ballpoint pens' category it is tedious and a waste of time to do this record by record.  What I want to do is make a list of products and define what field I want to change and what I want the new value to be and then LightSwitch can do the rest.

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:

partial void ExecuteBulkEdit_Execute()

{      
      //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!