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="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>
  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, 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; }
    
            }
    
    
    
            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:
  6. //' 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;
    
    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();
    
                }
    
            }
    
        }
    
    }
    
    
    
  7. 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

Comments (37)

  1. Glenn Wilson says:

    Nice post, it would also be nice to get some examples of uploading files into a sharepoint library, or saving them to a UNC Path on the network.

  2. Michael Washington says:

    I used some of this code to create a sample that uploads files to the file system:

    lightswitchhelpwebsite.com/…/Saving-Files-To-File-System-With-LightSwitch-Uploading-Files.aspx

  3. Thanks, Michael.

    Nice work on the sample.  Flattered to hear you used some of this code 🙂

    -Matt Sampson

  4. Francis_L says:

    Hi Matt,

    Great post, very helpfull and NO bugs… Saves me a great amount of time.

    This is working on LightSwitch 2011 RTM

    Maybe for beginners, simply tell them to rename "ApplicationData" to their own data source name.

    Francis

  5. Willem says:

    Hi Matt,

    I also posted this question on the LightSwitch forum:

    I'm developing a project in which on several occasions I have to add a document to a table. I read the tutorial by Matt Sampson (blogs.msdn.com/…/how-to-import-and-store-a-data-file.aspx) and was able to create the custom control and add files to a table.

    But, when this table is a detail table in a master/detail screen and I add this code to the AddAndEditNew_Execute method on the grid this does not work properly. Here is the code fragment:

    void selectFileWindow_Closed(object sender, EventArgs e)

    {

     SelectFileWindow selectFileWindow = (SelectFileWindow)sender;

     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();

      }

     }

     Document document = this.Documents.AddNew();

     document.Name = selectFileWindow.SafeFileName;

     document.Misc = "Grootte in bytes: " + fileData.Length;

     document.Data = fileData;  

     selectFileWindow.DocumentStream.Close();

     selectFileWindow.DocumentStream.Dispose();

     }

    }

    I get the error message: IVisualCollection<T>.AddNew() shouldn't be called from the UI Thread. I'm not really smart when it comes to threading so I don't know what this exactly means(I Googled it but didn't find any useful results). I know the custom control needs to run in a separate thread for some reason but I don't understand why the code in the tutorial was able to store the file successfully in a single, separate table through "this.DataWorkspace.ApplicationData.Documents.AddNew()"but when it's in a table linked to a master table and I use "this.Documents.AddNew()" this error occurs.

    Does anyone know what I'm doing wrong? Or does anyone have an alternative method of up- and downloading binaries to and from the database?

    Thanks,

    H.

  6. Ivica says:

    And how to allow the user to again download files and view them on his computer?

  7. @Willem

    Check out this application from Michael Washington:

    lightswitchhelpwebsite.com/…/Saving-Files-To-File-System-With-LightSwitch-Uploading-Files.aspx

    He's modified the application from this blog, and made it better.

    It might fit your needs and solve your bug issue.

  8. Rahul Kumar says:

    Excellent work. I added a bit of code to download the bytes from the database as a file:

    partial void DownloadCV_Execute()

           {

               // Must invoke on UI thread

               Dispatchers.Main.BeginInvoke(() =>

               {

                   // Gte the object which has bytes

                   Candiadte candidate = this.Candiadtes.SelectedItem;

                   byte[] data = candidate.CVFile;

                   SaveFileDialog dlg = new SaveFileDialog();

                   // set the default extension if you have also stored the file name

                   dlg.DefaultExt = System.IO.Path.GetExtension(candidate.CVName);

                   dlg.Filter = string.Format("{0} files(*.{0})|*.{0}", dlg.DefaultExt);

                   // Ideally you should set the file name, but can't do it in this version of Silverlight. Wait for 5.0

                   dlg.FilterIndex = 1;

                   if ((bool)dlg.ShowDialog())

                   {

                       Stream stream = dlg.OpenFile();

                       foreach (byte datum in data)

                       {

                           stream.WriteByte(datum);

                       }

                       stream.Close();

                       stream.Dispose();

                   }

               }

               );

           }

  9. Bilal says:

    Why are we using "dispatcher" here? Isn't the call to download the file running on the UI thread?

    Thanks

  10. @Bilal – When you click on a button, the "button's" code is running in the background thread and NOT the UI thread.  So we need to switch back to the UI thread in this instance to get our dialog to display.

  11. Jewels says:

    It would be really helpful to have an example of how to get the file out of the database. The solution below by Rahul Kumar does not work. Apparently, dialog boxes need to be executed via a button click? However, I have not been successful with anything that I have tried.

  12. Rashmi Panigrahi says:

    Hi Matt

    Do you have any article regarding download saved file from lightswitch application to external drive. if Yes then please refer me.

    Thanks

  13. Is it Possible to put progress bar (upload) on the select file dialog?

  14. @Willy – Using a progress bar during import would be possible but you would need a custom silverlight control for it.  If you just search on "LightSwitch custom silverlight controls" you will find lots of reference material.  

  15. @RASHMI – Check out Michael Washington's blogs: lightswitchhelpwebsite.com/…/Saving-Files-To-File-System-With-LightSwitch-Uploading-Files.aspx he talks about saving file to a hard drive (not a database).

  16. Şükrü Tulga says:

    Hi,

    great work, i want to know if it is possible to upload files to hard drive with wcf ria service ?

  17. Juan Carlos says:

    Hi Matt: thanks for this useful example

    I've figured to adapt it for VB; however, hasn't had success writing the code for the inverse path: to retrieve the binary file previously stored in the database and save it on a local folder (in web mode).

    You have any example to accomplish this?

  18. Neil Sitko says:

    Is it possible to have this code in VB?

  19. @Neil Sitko – Neil there is a C# to VB converter out there that I use for all my needs – http://www.developerfusion.com/…/csharp-to-vb

  20. Neil Sitko says:

    Thanks Matt

  21. Donna says:

    Hi Matt,

    thats a nice example. I tried to expand your "SelectFileDialog" with a additional button which works like the "add" Button in LightSwitch Business application. Unfortunately I failed. Could you show how I can add such a button  please. thx

  22. John_C says:

    I used your nice example for the Northwind database which is included in lightswitch but at this point of your code (see below) I cant choose another table that is not createt in LightSwitch :(.

     // Editable Grid

     // Create a new record for this file, and store the data, name and length

    Product Product = DataWorkspace.ApplicationData.**Product**.AddNew();

    In this case Product is not available in the dropdown box.

    Any idea how I can access a table which is not creat but included in Lightswitch?

  23. @John_C

    Hey John-

    I think the problem may be that you need to do something like DataWorkspace.<YourDataSourceName>.<YourDataSourceEntityName>

    So like DataWorkspace.NorthwindData.Products.AddNew()

    Let me know if that helps.

    -Matt S

  24. John_C says:

    Hi Matt,

    thanks it works. 🙂

  25. Marvyn Cosep says:

    How can we view this data we imported? view and export?

  26. Could you add a bit more info on how to export the saved files? says:

    Now that you have shown how to save files, how about exporting the files back on desired location. by selection so if some one has dozens to export it would be easy to do, just for more then one file example.

  27. I've been waiting for a reply, anyways says:

    hi, I posted a comment here about exporting files back to file system.

    Maybe you haven't checked on comments yet but when you do.

    let me : Bmuzammil@gmail.com

    and relationship with person etc was also my comment.

    thank you. I only know basics of C#, hardly even basics.

  28. @Marvyn Cosep –

    It's possible to do basic file I/O of the file itself to read the data out and write it to the desktop.  Though you may not have access to save it to the desktop potentially unless you are running as a Silverlight Desktop application (not a web app).

    If you want to read the contents of the file – I'd encourage you to look over the source code.  There should be at least one place in there where we open up the file and read in the data. If that file were xml, for example, you could use an XmlReader to read in the data, parse it out, and store it in the necessary LightSwitch entities

  29. Marc Miller says:

    This is awesome, but is there a VB version of this code out there?

  30. hello, when i try to import file than more of 5 mb i got this error  "An unexpected error occurred during communication with the server"

    i have this

    partial void Application_Initialize()

           {

               this.Details.ClientTimeout = 100000;

           }

    have you had this problem?

    could you help me?

    Thanks in Advance!

  31. pittrecon08 says:

    Would you update the code to download the imported file?

  32. Pittrecon08 – I know – everyone keeps asking for this. I just need to set aside some time to do this. In the meantime please peruse the comments on this blog of people who have posted code snippets on how to make this work, as well as the link I gave to Michael Washington's blog on how he accomplished this

  33. I separated the binary data from its metadata,, for instance for streaming purposes, by using two separate tables (sound_infoes and sound_data) with a 1-1 relationship between them. In this table structure I am unable to "Save Changes" after adding records to both tables. I get a Data Validation error

         "sound_info(filename): The referenced sound_info is either not set or no longer exists".

    When I click on the error I can manually select the missing SoundKey, after which the Save operation succeeds, but this is clearly not acceptable.

    Here is the code I use to create both records and to fill them (closely tracking your selectFileWindow_Closed method):

           private void selectFileControl_Closed(object sender, EventArgs e)

           {

               SelectFileControl sfc = (SelectFileControl)sender;

               // Continue if the OK button is clicked AND a file was selected

               if (sfc.DialogResult == true && sfc.DocumentStream != null)

               {

                   byte[] fileData = new byte[sfc.DocumentStream.Length];

                   using (StreamReader streamReader = new StreamReader(sfc.DocumentStream))

                   {

                       for (int i = 0; i < sfc.DocumentStream.Length; i++)

                       {

                           fileData[i] = (byte)sfc.DocumentStream.ReadByte();

                       }

                   }

                   sound_info soundInfo = this.DataWorkspace.InterfaceSounds.sound_infoes.AddNew();

                   soundInfo.SoundKey =  sfc.SaveFileName;

                   soundInfo.OriginalFileName = sfc.SaveFileName;

                   soundInfo.FileSize = fileData.Length;

                   sound_datum soundData = this.DataWorkspace.InterfaceSounds.sound_data.AddNew();

                   soundData.SoundKey =  sfc.SaveFileName;

                   soundData.SoundDataStream = fileData;

                   sfc.DocumentStream.Close();

                   sfc.DocumentStream.Dispose();

               }

           }

    Any idea how I can go about fixing this?

  34. Matt, I've used some of your code as well as Michael Washington's and Yann's to get my upload, download and save functions to work.  Howver, I need to connect my "FileInformations" table to a parent table for a parent-child, 1 to many.  I have a parent named Resolutions which can have many child, "FileInformations", documents attached to it.  Since I created a custom screen asper your code, I'm finding it hard to save a file when it's missing the parent data on the "Save" function.  How can I keep the parent-child relationship when saving and retrieving the files?

  35. @ABrinson – Maybe just have a dummy table called TempFileTable.  Save files there. And then during the saving operation of the entity (TempFileTable_Inserting) operation, you could write some logic to grab the correct "Resolutions" table record, and then create a new "FileInformations" child table entry off of it. ANd basically "copy/cut" the TempFileTable record over to the FileInformations table.

    HTH – Matt S

  36. Micahel says:

    Hello,

    I get the same message as Willem: IVisualCollection<T>.AddNew() shouldn't be called from the UI Thread.

    What can I do? My goal is to save the file in a database.

  37. Marius says:

    OK, I did the Upload file and works perfectly! Thank you!!

    But what about a open/download button?

    Gr,

    Marius