LightSwitch provides weakly-typed data access APIs which allow you to add general purpose code to perform common tasks. In this example, these APIs are used to implement a simple general purpose import/export. The import/export will read and write CSV files. Export will write the names of the properties as the first line of the output file. Import will read this line to get the correct column into the right property. It is designed to be called on the client from screen code. This general purpose code can be placed in its own class file in the Client/UserCode folder of your project so it can be called from any screen. In the following examples, it is used on two buttons on a Customer screen, one to perform an import and one to perform an export.

The export button first gets a list of the properties to export. In this case, I’m only exporting storage properties. Storage properties are simple, scalar properties that are stored in the database.  They do not include calculated or navigation properties. The call to PromptAndExportEntities() takes an enumerable list of entities to export and the list of properties to export.

Private Sub Export_Execute()

    Dim props As New List(Of String)()

 

    ' Only export storage properties

    For Each prop In Me.Customer.SelectedItem.Details.Properties.All.OfType(Of Details.IEntityStorageProperty)()

        props.Add(prop.Name)

    Next

    GeneralImportExport.EntityImportExport.PromptAndExportEntities(Me.DataWorkspace.ApplicationData.Customers, props.ToArray())

End Sub

The import button prompts the user first and then deletes all existing customers in the database. Next, it calls PromptAndImportEntities() to import new customers into the entity set specified, in this case, the Customers entity set.

Private Sub Import_Execute()

    If Me.ShowMessageBox("This will delete and re-import all Customers.  Do you want to continue?", "Continue?", MessageBoxOption.YesNo) = Windows.MessageBoxResult.Yes Then

        For Each cust As Customer In Me.DataWorkspace.ApplicationData.Customers

            cust.Delete()

        Next

        GeneralImportExport.EntityImportExport.PromptAndImportEntities(Me.DataWorkspace.ApplicationData.Customers)

        Me.Refresh()

    End If

End Sub

Export

The start of the export process prompts for a file to save the exported data to. This is accomplished by opening the SaveFileDialog() and getting the file stream that is the result. Then, this information is passed to the export function.

Note: This method of opening a save file dialog is currently a Beta1 limitation. Launching a UI control needs to happen on the UI thread, which is what the Dispatchers.Main.Invoke() is doing. We’re looking at ways of making this easier in the future.

Imports Microsoft.LightSwitch.Threading

 

Public Shared Sub PromptAndExportEntities(ByVal entitiesToExport As IEnumerable, ByVal properties() As String)

    Dim stream As IO.Stream = Nothing

 

    ' SaveFileDialog() must be opened on the UI thread

    Dispatchers.Main.Invoke(Sub()

                                Dim dlg As New Controls.SaveFileDialog

                                dlg.Filter = "CSV Files (*.csv)|*.csv|Text Files (*.txt)|*.txt"

                                dlg.DefaultExt = "csv"

 

                                If dlg.ShowDialog = True Then

                                    stream = dlg.OpenFile

                                End If

                            End Sub)

 

    If (Not stream Is Nothing) Then

        PerformExport(stream, entitiesToExport, properties)

 

        ' Need to close the file on the UI thread as well

        Dispatchers.Main.Invoke(Sub()

                                    stream.Close()

                                End Sub)

    End If

 

End Sub

The export function takes a stream on which to write the output, the list of entities to export and a list of the properties to export. The first step is to setup the export by initializing the stream writer and writing a header containing the property names to the file. Then, it continues by looping over each entity and passing it to the single line exporter.

Private Shared Sub PerformExport(ByVal file As IO.Stream, ByVal entitiesToExport As IEnumerable, ByVal properties() As String)

 

    ' Initialize a writer

    Dim writer As New IO.StreamWriter(file)

    writer.AutoFlush = True

 

    ' Write the header

    writer.WriteLine(String.Join(",", properties))

 

    ' Export each entity separately

    For Each entity In entitiesToExport

        ExportSingle(writer, entity, properties)

    Next

End Sub

The single line exporter takes an entity to export and loops through its properties to add the values of its properties to a string array. It uses the Properties on the Details member of the entity to get the value of each entity property.  The last step is to write the string array to the output as a comma separated list.

Private Shared Sub ExportSingle(ByVal writer As IO.StreamWriter, ByVal entity As IEntityObject, ByVal properties() As String)

    Dim stringArray As New List(Of String)()

    Dim currentProperty As Details.IEntityProperty

 

    ' Write each property to the string array

    For Each prop In properties

        Try

            ' Get the property from the entity by name

            currentProperty = entity.Details.Properties(prop)

        Catch ex As ArgumentException

            Throw New InvalidOperationException(String.Format("A property named {0} does not exist on the entity named {1}.", prop, entity.Details.Name))

        End Try

        stringArray.Add(currentProperty.Value.ToString())

    Next

 

    'Write the string array

    writer.WriteLine(String.Join(",", stringArray.ToArray))

End Sub

Import

Import starts with the PromptAndImportEnties() method. It takes an entity set in which to create the new entities. The start of the import process is to prompt for a file to import. The OpenFileDialog() is used for this purpose.

Note: Again, this method of opening a dialog is a Beta1 limitation for LightSwitch.

Imports Microsoft.LightSwitch.Threading

 

Public Shared Sub PromptAndImportEntities(ByVal entitySetToCreateIn As IEntitySet)

    Dim file As IO.FileInfo = Nothing

 

    ' OpenFileDialog() must be opened on the UI thread

    Dispatchers.Main.Invoke(Sub()

                                Dim dlg As New Controls.OpenFileDialog

                                dlg.Filter = "CSV Files (*.csv)|*.csv|Text Files (*.txt)|*.txt"

 

                                If dlg.ShowDialog = True Then

                                    file = dlg.File

                                End If

                            End Sub)

 

    If (Not file Is Nothing) Then

        PerformImport(file, entitySetToCreateIn)

    End If

 

End Sub

The Import function does pretty much the opposite of the export function. It takes in the file to read and the entity set in which to create the entities. The first step is to open the file and read the header line to get the property names. Then, it continues by looping over all data rows in the file, passing the data to the single line importer. Once all lines are imported into entities, SaveChanges() is called to save the data.

 

Private Shared Sub PerformImport(ByVal file As IO.FileInfo, ByVal entitySetToCreateIn As IEntitySet)

 

    Using reader As IO.StreamReader = file.OpenText

        Dim inputLine As String = reader.ReadLine

 

        ' Get the property names from the first line of input

        Dim headers As String() = inputLine.Split(",").Select(Function(header As String) header.Trim).ToArray()

 

        For Each header In headers

            header = header.Trim

        Next

        Dim count As Int16 = headers.Count

        Dim data As String()

 

        inputLine = reader.ReadLine

        While Not inputLine Is Nothing

            data = inputLine.Split(",")

 

            ' If the right number of data items were found, import the line

            If data.Count = count Then

                ImportSingle(entitySetToCreateIn, headers, data)

            Else

                Throw New InvalidOperationException(String.Format("Line not imported.  Invalid number of elements.  Data = [{0}].", inputLine))

            End If

            inputLine = reader.ReadLine

        End While

 

        reader.Close()

    End Using

    entitySetToCreateIn.Details.DataService.SaveChanges()

End Sub

 

The single line importer creates the new entity by calling AddNew() on the entity set. It then loops through all property names read in the import function and gets the desired property from the new entity by name, again, using the Details.Properties on the entity. It then converts the data for the property to the correct type based on the type information from the entity property. Lastly, it sets the value of the property.

Private Shared Sub ImportSingle(ByVal entitySetToCreateIn As IEntitySet, ByVal headers As String(), ByVal data As String())

 

    ' Create the new entity

    Dim newEntity As IEntityObject = entitySetToCreateIn.AddNew()

 

    Dim currentProperty As Microsoft.LightSwitch.Details.IEntityProperty

    Dim newValue As Object

 

    ' Loop through all propertyNames from the first line of the file

    For index As Integer = 0 To headers.Count - 1

        Try

            ' Get the property from the new entity by name

            currentProperty = newEntity.Details.Properties(headers(index))

        Catch ex As ArgumentException

            Throw New InvalidOperationException(String.Format("A property named {0} does not exist on the entity named {1}.", headers(index), newEntity.Details.Name))

        End Try

 

        Try

            ' Convert the value

            newValue = Convert.ChangeType(data(index), currentProperty.PropertyType, Nothing)

 

            currentProperty.Value = newValue

        Catch ex As System.FormatException

            Throw New InvalidOperationException(String.Format("The following line has an invalid value for property {0}.  Aborting the import." + vbNewLine + "Data: {1}", headers(index), String.Join(",", data)))

        End Try

    Next

End Sub

Hopefully this example gives you an idea of how the weakly-typed API can be used to create general purpose solutions to common problems. There are many possibilities for extending this example including enhancing it to support relationships, using it to create a general purpose entity archiver, or modifying it to export data to Excel using LINQ to XML. I attached the sample code to the bottom of this post.

Try it out and have fun!