Importing data into a Microsoft Visual Studio LightSwitch application can be a bit tricky, especially if we are talking about a LightSwitch web application.  Since I’ve heard several people ask how to import data using a web application, I figured now would be a good time to blog about this.

I’m going to teach you some tips and tricks on importing data into a web application. It can get tricky in this scenario because web applications typically have additional security issues you need to deal with.  But I think you’ll find once we are done, that there is very little code, and it’s easy to understand.

Overview

Some other LightSwitch people have already blogged about importing data into a LightSwitch application (e.g. Dan Seefeldt and the LightSwitch team).  These are some awesome posts for the basics of importing and exporting.  But for our slightly more specialized scenario we want to:

1. Create a web application
2. Let the user select a comma separate file (e.g. *.csv) to import
3. Populate our LightSwitch data with data from the comma separated file

The real issue we need to work around is the fact that we have a web application and NOT a desktop application.  Because of that we are going to have to do a couple of extra steps to make this work.

Let’s get started!

Create a Simple Web Application

Because I’m lazy, we are going to make another application to manage “Contacts” for this example (like we did for the last blog post).

1. Launch Microsoft Visual Studio LightSwitch
2. Create a new C# Project (I’ll include some VB.NET code for VB fans at the end of this blog)
3. Call the project “ImportDataFromAWebApp”
4. In the Solution Explorer, right click the “Properties” icon and select “Open”:
   1. 
5. In the Properties window that opens, select the “Application Type” tab, and pick “Web” to switch from a Desktop application to a Web application for this project
   1. Note - Our code will also work in a Desktop application as well

Add the Table

1. In the Solution Explorer, right click the Data Sources node and select “Add Table”
2. Name the table “Contact” and populate it with the following fields (formatted like [Field Name | Data Type | Required]):
   1. FirstName | String | Required
   2. MiddleName | String |
   3. LastName | String | Required
   4. AddressLine1 | String |
   5. AddressLine2 | String |
   6. City | String |
   7. State | String |
   8. ZipCode | String |
   9. Country | String |
   10. PhoneNumber1 | Phone Number |
   11. PhoneNumber2 | Phone Number |
   12. BirthDate | Date |
   13. MiscellaneousInformation | String |
3. The table should look something like this when we are done:
4. 

Add the Screen

1. In the Solution Explorer, right click the Screens node and select “Add Screen…”
2. Pick the “Editable Grid Screen” template
3. For the Screen Data – select the table we just created “Contact”
4. Accept the defaulted screen name (should be “EditableContactsGrid”)
5. Click “OK”
6. In the Screen Designer, navigate to “Rows Layout –> Data Grid –> Command Bar” and add a button under the Command Bar.
7. Call the button method name “ImportData”
8. You should now have a screen that looks something like this:
9.  
10. Right click the “Import Data” button and select “Edit Execute Code” to generate the button method stub
    1. Doing this will generate the “User Code” folder for us that we’d like to have for our next steps. 
    2. We’ll add code to this button method later on.

Add a Simple Silverlight dialog

Now comes the unusual part of this example.  We are going to add our own Silverlight dialog to our LightSwitch application.  The “Import Data” button is going to invoke this Silverlight dialog. (I am not the first to go adding Silverlight dialogs to a LightSwitch application – see Eric Erhardt’s blog post on this.)

I used Visual Studio Ultimate to make my own very simple Silverlight dialog which we are going to import into our Visual Studio LightSwitch project.  The Silverlight dialog contains 4 controls:

1. A “Browse” button which will open up a “OpenFileDialog” window and allow the user to select a .csv file
2. An “OK” button to accept the file you selected
3. A “Cancel” button to abort
4. And a text control that displays the name of the file you selected

Let’s add the dialog now, and then I’ll give the technical reasons for why we are doing it this way.

1. In Solution Explorer, click the drop down arrow on the “Logical View” button and select “File View”
   1. 
2. In Solution Explorer, navigate to “Client –> UserCode”
3. Right click the “UserCode” folder and select “Add –> New Item…”
   1. 
4. Select  “Text File” in the “Add New Item” dialog.  Call the file “SelectFileWindow.xaml”.
5. Now copy and paste the below code into the “SelectFileWindow.xaml” file (you may need to open up the .xaml file with Notepad to add this code):

   <controls:ChildWindow x:Class="LightSwitchApplication.UserCode.SelectFileWindow"
   
              xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation" 
   
              xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml" 
   
              xmlns:controls="clr-namespace:System.Windows.Controls;assembly=System.Windows.Controls"
   
              Width="394" Height="305" 
   
              Title="Select File Dialog" >
   
       <Grid x:Name="LayoutRoot" Margin="2">
   
           <Grid.RowDefinitions>
   
               <RowDefinition />
   
               <RowDefinition Height="Auto" />
   
           </Grid.RowDefinitions>
   
   
           <Button x:Name="CancelButton" Content="Cancel" Click="CancelButton_Click" Width="75" Height="23" HorizontalAlignment="Right" Margin="0,12,0,0" Grid.Row="1" />
   
           <Button x:Name="OKButton" Content="OK" Click="OKButton_Click" Width="75" Height="23" HorizontalAlignment="Right" Margin="0,12,79,0" Grid.Row="1" />
   
           <Button Content="Browse" Height="23" HorizontalAlignment="Left" Margin="291,92,0,0" Name="BrowseButton" VerticalAlignment="Top" Width="75" Click="BrowseButton_Click" />
   
           <TextBox Height="23" HorizontalAlignment="Left" Margin="66,92,0,0" Name="FileTextBox" VerticalAlignment="Top" Width="219" IsEnabled="True"/>
   
       </Grid>
   
   </controls:ChildWindow>
   

6. This Silverlight xaml code contains the metadata information for the dialog, and for the 4 controls we discussed earlier.
7. We need to do another step to add some code for the Silverlight control, so let’s add another file.
8. Right click the “UserCode” folder and select “Add –> Class”
9. Name the class “SelectFileWindow.cs” and copy the below code into the class:

   //' Copyright © Microsoft Corporation.  All Rights Reserved.
   
   //' This code released under the terms of the 
   
   //' Microsoft Public License (MS-PL, http://opensource.org/licenses/ms-pl.html)
   
   using System;
   
   using System.IO;
   
   using System.Windows;
   
   using System.Windows.Controls;
   
   
   namespace LightSwitchApplication.UserCode
   
   {
   
       public partial class SelectFileWindow : ChildWindow
   
       {
   
           public SelectFileWindow()
   
           {
   
               InitializeComponent();
   
           }
   
   
           private FileStream documentStream;
   
   
           public FileStream DocumentStream
   
           {
   
               get { return documentStream; }
   
               set { documentStream = value; }
   
           }
   
   
           /// <summary>
   
           /// OK Button
   
           /// </summary>
   
           private void OKButton_Click(object sender, RoutedEventArgs e)
   
           {
   
               this.DialogResult = true;
   
           }
   
   
           /// <summary>
   
           /// Cancel button
   
           /// </summary>
   
           private void CancelButton_Click(object sender, RoutedEventArgs e)
   
           {
   
               this.DialogResult = false;
   
           }
   
   
           /// <summary>
   
           /// Browse button
   
           /// </summary>
   
           private void BrowseButton_Click(object sender, RoutedEventArgs e)
   
           {
   
               OpenFileDialog openFileDialog = new OpenFileDialog();
   
               // Limit the dialog to only show ".csv" files,
   
               // modify this as necessary to allow other file types
   
               openFileDialog.Filter = "csv files (*.csv)|*.csv";
   
               openFileDialog.FilterIndex = 1;
   
   
               if (openFileDialog.ShowDialog() == true)
   
               {
   
                   this.FileTextBox.Text = openFileDialog.File.Name;
   
                   this.FileTextBox.IsReadOnly = true;
   
                   System.IO.FileStream myStream = openFileDialog.File.OpenRead();
   
                   documentStream = myStream;
   
               }
   
           }
   
       }
   
   }
   
   
   
   

10. This partial class for our “SelectFileWindow” Silverlight dialog contains a few basic things:
    1. The methods for our button controls
    2. The Browse button has code in it to create a System.Windows.Controls.OpenFileDialog object which we use to open up our Open File Dialog to allow the user to select a .csv file.  Once we open the file we store the file’s data into a FileStream property on our SelectFileWindow class.
    3. A public FileStream property called DocumentStream that contains the data for the .csv file we want to import
11. We need to add a reference to the Silverlight dll we are using, so in Solution Explorer, navigate to “Client –> References”, right click and select “Add Reference…”
12. Add a .NET reference to the System.Windows.Controls assembly

Add a Simple Class to Parse the .CSV File

For the sake of modularity and encapsulation we will create a separate class to handle the parsing of the .csv file.

1. Add a new User Code class – in Solution Explorer, right click the “Client->UserCode” folder, and select “Add –> Class”.
2. Name the class “ImportDataFile.cs” and add the below code:

   //' Copyright © Microsoft Corporation.  All Rights Reserved.
   
   //' This code released under the terms of the 
   
   //' Microsoft Public License (MS-PL, http://opensource.org/licenses/ms-pl.html)
   
   using System;
   
   using System.Collections.Generic;
   
   using System.IO;
   
   
   namespace LightSwitchApplication.UserCode
   
   {
   
       public class ImportDataFile
   
       {
   
           /// <summary>
   
           /// Import data from a comma delimited stream
   
           /// And using the workspace that was passed in, insert the imported data
   
           /// so it displays on the user's screen
   
           /// </summary>
   
           static public void ImportCommaDelimitedStream(FileStream fileStream, DataWorkspace dataWorkspace)
   
           {
   
               List<string[]> parsedData = new List<string[]>();
   
   
               using (StreamReader streamReader = new StreamReader(fileStream))
   
               {
   
                   string line;
   
                   string[] row;
   
   
                   while ((line = streamReader.ReadLine()) != null)
   
                   {
   
                       row = line.Split(',');
   
                       parsedData.Add(row);
   
                   }
   
               }
   
               AddData(parsedData, dataWorkspace);
   
           }
   
   
           /// <summary>
   
           /// Take in a list of string arrays which contains
   
           /// all the parsed data for our Contacts entity
   
           /// </summary>
   
           static private void AddData(List<string[]> dataList, DataWorkspace dataWorkspace)
   
           {
   
               foreach (string[] row in dataList)
   
               {
   
                   Contact contact = dataWorkspace.ApplicationData.Contacts.AddNew();
   
   
                   contact.FirstName = row[0];
   
                   contact.MiddleName = row[1];
   
                   contact.LastName = row[2];
   
                   contact.AddressLine1 = row[3];
   
                   contact.AddressLine2 = row[4];
   
                   contact.City = row[5];
   
                   contact.State = row[6];
   
                   contact.ZipCode = row[7];
   
                   contact.Country = row[8];
   
                   contact.PhoneNumber1 = row[9];
   
                   contact.PhoneNumber2 = row[10];
   
                   contact.BirthDate = DateTime.Parse(row[11]);
   
                   contact.MiscellaneousInformation = row[12];
   
               }
   
           }
   
       }
   
   }
   
   
   

3. This class contains two basic methods:
   1. The AddData() method which iterates over a List of string arrays, and creates a new Contact in our DataWorkspace for each string array.
4. The ImportCommaDelimitedStream() method takes in a FileStream object (our .csv file, which we can get off our SelectFileWindow class that we just made), and a DataWorkspace object.  The method reads the lines in the FileStream object, parses off each “comma”, stores each line in a string array and then invokes the AddData() method.

Add Import Data Button Code

Let’s do the final bit of coding now by adding the code to our “Import Data” button.

1. To get to our button, switch the back to “Logical View”
   1. 
2. Open up the Screen Designer for the EditableContactsGrid screen
3. Navigate to the Import Data button, right click and select “Edit Execute Code”
   1. We should now be in the EditableContactsGrid partial class
4. We need a “using” statement for our Client UserCode that we added, so add the following using statement to this class: “using LightSwitchApplication.UserCode”
5. We also need another using statement so we can reference the “Dispatchers” class, so add the following using statement to this class as well: “using Microsoft.LightSwitch.Threading”
6. Paste the following code into your ImportData_Execute() button method

           /// <summary>
   
           /// Import Data button method
   
           /// </summary>
   
           partial void ImportData_Execute()
   
           {
   
               // To invoke our own dialog, we have to do this inside of the "Main" Dispatcher
   
               // And, since this is a web application, we can't directly invoke the Silverlight OpenFileDialog
   
               // class, we have to first invoke our own Silverlight custom control (i.e. SelectFileWindow)
   
               // and that control will be able to invoke the OpenFileDialog class (via the Browse button)
   
               Dispatchers.Main.BeginInvoke(() =>
   
               {
   
                   SelectFileWindow selectFileWindow = new SelectFileWindow();
   
   
                   selectFileWindow.Closed += new EventHandler(selectFileWindow_Closed);
   
                   selectFileWindow.Show();
   
               });
   
           }
   
   
           /// <summary>
   
           /// Invoked when our custom Silverlight window closes
   
           /// </summary>
   
           void selectFileWindow_Closed(object sender, EventArgs e)
   
           {
   
               SelectFileWindow selectFileWindow = (SelectFileWindow)sender;
   
   
               // Continue if they hit the OK button AND they selected a file
   
               if (selectFileWindow.DialogResult == true && (selectFileWindow.DocumentStream != null))
   
               {
   
                   ImportDataFile.ImportCommaDelimitedStream(selectFileWindow.DocumentStream, this.DataWorkspace);
   
                   selectFileWindow.DocumentStream.Close();
   
               }
   
           }
   

Here’s what’s going on at a high level when the Import Data button is clicked:

1.  
   1. The button is clicked
   2. An instance of our Silverlight dialog, SelectFileWindow, is created
   3. The dialog is opened by calling the Show() method
   4. An EventHandler is added to the SelectFileWindow Closed event so that the “selectFileWindow_Closed()” method is invoked after the dialog is closed

Optional Reading – Here is a lower level explanation of what is going on in this button method:

Technical Stuff: Since the Import Data button code does NOT by default run inside the Main UI thread, we have to invoke the “Main” thread (or Dispatcher) before calling our Silverlight dialog. That is the reason for wrapping this code inside the Dispatchers.Main.BeginInvoke() method. So why can’t we just open the OpenFileDialog directly? Why do we have to open a Silverlight dialog which then launches an OpenFileDialog? Well, this is really what makes the web application scenario different from the Desktop application. If you try to directly launch the OpenFileDialog you will get a “Dialogs must be user-initiated” error message. This is because Silverlight dialogs (like OpenFileDialog) can only be opened from “user actions”, like a button clicked event handler. The reason why this won’t work with LightSwitch is because we invoke the button logic asynchronously, so the Import Data button code is not considered to be “user-initiated”.

Run That Thing!

1. In Solution Explorer, right click the ImportDataFromAWebApp project, and select “Build”.
2. After the build, press F5 to run your project
3. You should see inside the Editable Contacts Grid screen’s command bar an “Import Data” button:
4. 
5. Click the button, and you should see the SelectFileWindow Silverlight dialog we created
6. 
7. Click the “Browse” button inside our SelectFileWindow dialog to open up the OpenFileDialog
8. Select a .csv file to import (I have one at the end of this blog which can be used)
9. Click “OK” to import the data
10. You should now see your data displayed inside your workspace

The C# and VB.NET code is available on MSDN Code Gallery here: http://code.msdn.microsoft.com/Visual-Studio-LightSwitch-1f8aa17e

Let me know if you have questions.  Enjoy!

Matt Sampson -