How Do I: Import and Store a Data File

Today’s post is going to be a short one about how to import a file through a Visual Studio LightSwitch application (Desktop or Web) and store the data on the backend (e.g. SQL Server).

Sometimes we want to upload files in binary format and store them in the database, like a Word document or PDF file or any other file containing unstructured data. LightSwitch already has the built-in ability to store and retrieve images, but if you want to store other types of files we have to write a small bit of code.

Backstory

This post is really just me picking out various pieces of code from my other posts and cramming it together to make a new post.  Which is a nice break for me, since writing a post often takes about a week (after I write the code and write up a draft blog, then get feedback, and double check the code several times).  But with this one I should be done in a day leaving more time to enjoy the very brief Summers we have up here in Fargo, North Dakota.

Create the project and a table

Alright, let’s get started making a simple LightSwitch Web Application.
Let’s create the project, and a table that will hold our file data.

  1. Launch Visual Studio LightSwitch and create a new C# Visual Studio LightSwitch project
  2. Call the project: ImportFiles
  3. In the Solution Explorer, right click “Properties” and select Open.
  4. Change the Application Type from “Desktop” to “Web”
    1. Note - this sample will work as a Desktop application as well
  5. Add a table called “FileInformation” and add the following fields to the table:
    1. Name | String |
    2. Miscellaneous | String |
    3. Data | Binary |
  6. You should have a table now that looks like this:
  7. image

Create the screen

  1. Add a screen using the “Editable Grid Screen” template and tie the screen to the FileInformation table.
  2. In the screen designer, under Rows Layout –> Data Grid –> Data Grid Row delete the “Data” control so that it won’t display when we run the application
    1. It doesn’t make much sense for us to display this field on the screen since it just the file’s binary data.
  3. In the screen designer, under Rows Layout –> Screen Command Bar, add  a button called “ImportAFile”
  4. You should now have a screen that looks something like this:
  5. image
  6. Right click the ImportAFile button and select “Edit Execute Code”
    1. We do this to create the “User” folder where we will be placing some custom user code.  We’ll come back and add our button code later

Add a custom Silverlight dialog

  1. In the Solution Explorer we need to switch to the “File View” mode so we can add some custom code.

  2. image

  3. After switching to the File View mode, navigate to the Client project and open up the User Code folder

  4. image

  5. We are going to add our custom Silverlight dialog just like we did in the last blog post.  We need our own custom Silverlight dialog because we want to display an OpenFileDialog to the user and we are running inside a LightSwitch web application. (For the longer explanation please read my previous blog post).

  6. Right click UserCode folder and select “Add-> New Item”

  7. Select “Text File” in the “Add New Item” dialog.  Call the file “SelectFileWindow.xaml”

  8. Now copy and paste the below code into the “SelectFileWindow.xaml” file:

     <controls:ChildWindow x:Class="LightSwitchApplication.UserCode.SelectFileWindow"
    
               xmlns="https://schemas.microsoft.com/winfx/2006/xaml/presentation" 
    
               xmlns:x="https://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>
    
  9. This Silverlight dialog code contains 4 controls – an OK button, Cancel button, Text field, and a Browse button.  The Browse button will launch an OpenFileDialog dialog which will let the user select a file to import, and the text field will display the name of the file about to be imported.

  10. We need to add some code for our Silverlight dialog, so right click the “UserCode” folder and select “Add-> Class”

  11. 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, https://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; }
    
            }
    
            private String safeFileName;
    
            public String SafeFileName
    
            {
    
                get { return safeFileName; }
    
                set { safeFileName = 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();
    
                if (openFileDialog.ShowDialog() == true)
    
                {
    
                    this.FileTextBox.Text = openFileDialog.File.Name;
    
                    this.safeFileName = openFileDialog.File.Name;
    
                    this.FileTextBox.IsReadOnly = true;
    
                    FileStream myStream = openFileDialog.File.OpenRead();
    
                    this.documentStream = myStream;
    
                }
    
            }
    
        }
    
    }
    
  12. The SelectFileWindow class contains the following code:

    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 pick any arbitrary file to import.
    3. A public FileStream property which will contain the data for the file we want to import
    4. A public String property which will contain the name of the file we want to import
  13. 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…”

  14. Add a .NET reference to the System.Windows.Controls assembly

Add our screen’s button code

  1. Let’s switch back to the “Logical View”

  2. image

  3. Open up the screen designer for the EditableFileInformationsGrid

  4. Right click the “ImportAFile” and select “Edit Execute Code”

  5. Copy and paste the below code into the EditableFileInformationsGrid.cs:

     //' Copyright © Microsoft Corporation.  All Rights Reserved.
    
    //' This code released under the terms of the 
    
    //' Microsoft Public License (MS-PL, https://opensource.org/licenses/ms-pl.html)
    
    using System;
    
    using System.Collections.Generic;
    
    using System.IO;
    
    using System.IO.IsolatedStorage;
    
    using System.Linq;
    
    using LightSwitchApplication.UserCode;
    
    using Microsoft.LightSwitch;
    
    using Microsoft.LightSwitch.Framework.Client;
    
    using Microsoft.LightSwitch.Presentation;
    
    using Microsoft.LightSwitch.Presentation.Extensions;
    
    using Microsoft.LightSwitch.Threading;
    
    namespace LightSwitchApplication
    
    {
    
        public partial class EditableFileInformationsGrid
    
        {
    
            partial void ImportAFile_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))
    
                {
    
                    byte[] fileData = new byte[selectFileWindow.DocumentStream.Length];
    
                    using (StreamReader streamReader = new StreamReader(selectFileWindow.DocumentStream))
    
                    {
    
                        for (int i = 0; i < selectFileWindow.DocumentStream.Length; i++)
    
                        {
    
                            fileData[i] = (byte)selectFileWindow.DocumentStream.ReadByte();
    
                        }
    
                    }
    
                    // Create a new record for this file, and store the data, name and length
    
                    FileInformation fileInformation = this.DataWorkspace.ApplicationData.FileInformations.AddNew();
    
                    fileInformation.Name = selectFileWindow.SafeFileName;
    
                    fileInformation.Miscellaneous = "Size of file in bytes: " + fileData.Length;
    
                    fileInformation.Data = fileData;
    
                    selectFileWindow.DocumentStream.Close();
    
                    selectFileWindow.DocumentStream.Dispose();
    
                }
    
            }
    
        }
    
    }
    
  6. There are two methods in this class:

    1. ImportAFile_Execute() – since we are inside of button code here we are NOT inside the main UI thread anymore.  So we need to switch back to the main UI thread only because we want to display our own UI dialogs (like our custom Silverlight dialog and an OpenFileDialog).  This method switches to the main UI, invokes our Silverlight dialog, and adds an EventHandler to the “Closed” event.  So that when the dialog is closed, we call our own method to do some additional work.
    2. selectFileWindow_Closed() – this method is invoked once our Silverlight dialog closes.  It reads in the file from the SelectFileWindow.DocumentStream public property we mentioned earlier.  And stores the data from this FileStream into a byte array.  We then create a new record for our FileInformation table, and set the Name field to the name of the file, the Miscellaneous field is set to the size of the file, and the Data field is set to the value of the byte array.  The Data field is what actually contains our imported file.

Run it and import some files

  1. In Solution Explorer, right click the ImportFiles project, and select “Build”.
  2. After the build, press F5 to run your project
  3. You should see the inside the command bar on the screen a button called “Import A File”
  4. image
  5. Click the “Import A File” button to display our custom Silverlight Dialog
  6. image
  7. Select the “Browse” button and select any file you wish to import
  8. Click “OK”
  9. Our code to import the data will now run and we will get a new record created on our screen
  10. image
  11. As you can see, we display the file’s name and it’s size in bytes
  12. You can now save the data to persist it to the database by clicking the “Save” button on the command bar

Additionally, there is an extension that can be added to Visual Studio LightSwitch called Document Toolkit for LightSwitch, which handles importing and viewing Word Documents.  It will only work for Desktop applications, and it isn’t free, but other than that it looks like a slick extension.

That’s it for this brief post.  I've included a zip file below of the C# code.

Again, love to hear if there are any questions, and if you think something is wrong with this code (or the title) then you are probably right Winking smile so please let me know.

-Matt Sampson Martini glass

ImportFiles.zip