Beth's Chinese blog
One of the great features of LightSwitch is that it lets you connect to and manipulate data inside of SharePoint. Data can come from any of the built in SharePoint lists like Tasks or Calendar or it can come from custom lists that you create in SharePoint. In this post I’m going to show you how you can work with user task lists in SharePoint via LightSwitch. For a video demonstration of this please see: How Do I: Connect LightSwitch to SharePoint Data?
The first thing we do is connect to a SharePoint 2010 site and choose what data we want to work with. If you have just created a new LightSwitch project then on the “Start with Data” screen select Attach to External Data Source. Otherwise you can right-click on the Data Sources node in the Solution Explorer on the right and select “Add Data Source…” to open the Attach Data Source Wizard.
Select SharePoint and Click Next. Next you need to specify the SharePoint site address. In the SharePoint Site Address box, type the URL of the SharePoint site that contains the list that you want to connect to. For example, if the URL of your list is http://sharepoint/sites/mysite/Lists/Tasks/AllItems.aspx, type http://sharepoint/sites/mysite/ into the SharePoint Site Address box. Then it will ask you how you want to log into SharePoint. If you are building a LightSwitch application for internal/company use then you should select Windows credentials here. This means that users on the company domain will connect to SharePoint using their Windows credentials when they run your LightSwitch application. Click Next.
Lastly you need to select the lists that you want to pull into LightSwitch and name the Data Source. For this example I’m selecting Tasks. Notice that UserInformationList is selected by default. This is because every list item in SharePoint has a Modified and Created By field that relates to the users who have access to the site. When you select a table from a database or a list from SharePoint, any related entities are also pulled into LightSwitch for you. Click Finish and LightSwitch will warn you that it can’t import predecessor tasks because it does not support many-to-many relationships at this time. Click Continue and the Data Designer will open allowing you to modify the Task and UserInformationList entities.
LightSwitch sets up some smart defaults for you when it imports content types from SharePoint. For Task you will notice that a lot of the internal fields are set to not display on the screen by default. Also LightSwitch creates any applicable Choice Lists for you. For instance, you will see that you can only select from a list of predefined values that came from SharePoint for Priority and Status.
Even though LightSwitch did some nice heavy-lifting here for us we still are going to want to make some modifications. First I’ll select the ContentType field and uncheck the “Display by Default” in the properties window. This property is automatically set to “Task” by SharePoint when we save an item and I’m going to create a screen that only displays Tasks so I don’t need to show this to the user. I’ll do the same for the TaskGroup property. I’ll also set the Summary Property of the Task entity to the Title and the UserInformatuinList to the Name property. Finally I’ll set the StarteDate and DueDate to type Date instead of Date Time since I don’t want to display the time portion to the user.
Depending on how you’ll be displaying the data from SharePoint you typically want to tweak your entities a little bit like this. I also want to implement a couple business rules that set some defaults on properties for me. The first thing I want to do is set the Complete percentage to 1 or 0 if the user selects a Status value of Completed or Not Started. In the Data Designer click the Status property and then drop down the “Write Code” button at the top right of the Designer and select “Status_Validate” method. Write this code:
Private Sub Status_Validate(results As EntityValidationResultsBuilder) If Me.Status <> "" Then Select Case Me.Status Case "Not Started" Me.Complete = 0 Case "Completed" Me.Complete = 1 End Select End If End Sub
Next I want to automatically set the AssignedTo property on the Task to the logged in user. I also want to be able to present the logged in user with just their tasks on their startup screen. To do that, I need to specify that my LightSwitch application uses Windows Authentication just like SharePoint. Open the project Properties (from the main menu select Project –> Application Properties…) and then select the Access Control tab. Select “Use Windows authentication”.
If you want to allow any authenticated user on your Windows domain access to the application then select that option like I have pictured above. Otherwise if you want to control access using permissions and entering users and roles into the system then you have that option as well. (For more information on access control see: How Do I: Set up Security to Control User Access to Parts of a Visual Studio LightSwitch Application?)
Because the the AssignedTo property on the Task is of the type UserInformationList we need to query SharePoint for the right user record. Fortunately, once we have enabled Windows authentication this is easy to do by using a query. In the Solution Explorer right click on UserInformationLists and select “Add Query” to open the query designer. Name the query GetUserByName and add a filter where Account is equal to a new parameter named Account. Also in the Properties window make sure to change the “Number of Results Returned” to “One”.
Now that we have this query set up we can go back to the Data Designer by double-clicking on Tasks in the Solution Explorer and then drop down the “Write Code” button again, but this time select the Task_Created method. xxx_Created methods on entities are there so you can set defaults on fields. The method runs any time a new entity is created. In order to call the query in code, you use the DataWorkspace object. This allows you to get at all the data sources in your application. In my case I have only one data source called Team_SiteData which is the SharePoint data source. Once you drill into the data source you can access all the queries. Notice that LightSwitch by default generates query methods to return single records in a data source in which you pass an ID. You can access the complete sets as well (Tasks and UserInformationLists in this case).
What we need to pass into our GetUserByName query is the account for the logged in user which you can get by drilling into the Application object. Here’s the code we need:
Private Sub Task_Created() Me.AssignedTo = Me.DataWorkspace.Team_SiteData.GetUserByName(Me.Application.User.Name) End Sub
We also want to add another query for our screen that will only pull up tasks for the logged in user. Create another query but this time right-click on the Tasks entity in the Solution Explorer and select “Add Query”. This time call it MyTasks and add a filter by expanding the AssignedTo property and selecting Account equals a new parameter named account. Also add a sort by DueDate ascending.
Next we just need to create a screen based on the MyTasks query. From the query designer you can click the “Add Screen” button or you can right click on the Screens node in the Solution Explorer and select Add Screen. Select the List and Details Screen and for the Screen Data select MyTasks. Click OK
The Screen Designer opens and you should notice that LightSwitch has added a textbox at the top of our content tree for the screen parameter that it added called TaskAccount which is fed into the query parameter Account. Once we set the TaskAccount with a value the query will execute. As it is though, the user would need to enter that data manually and that’s not what we want. We want to set that control to a Label and set the value programmatically.
To do that drop down the Write Code button on the Screen Designer and select the InitializeDataWorkspace method. This method runs before any data is loaded. Here is where we will set the value of TaskAccount to the logged in user:
Private Sub MyTasksListDetail_InitializeDataWorkspace( saveChangesTo As System.Collections.Generic.List(Of Microsoft.LightSwitch.IDataService)) ' Write your code here. Me.TaskAccount = Me.Application.User.Name End Sub
Hit F5 to build and run the application. The My Task List Detail screen should open with just the logged in user’s tasks. Click the “Design Screen” at the top right to tweak the layout. I suggest changing the ModifiedBy and CreatedBy to summary fields because these are automatically filled in by SharePoint when you save. I also changed the List to a DataGrid and show the DueDate since we are sorting by that and made the description field bigger. You can make any other modifications you need here.
Add a new task and you will also see the AssignedTo field is automatically filled out us when we add new records. Cool! In the next post I’ll show you how you can relate SharePoint list data to your LightSwitch application data and work with it on the same screen.
Enjoy!
We just released three new “How Do I'” videos on the LightSwitch Developer Center check them out:
#14 - How Do I: Modify the Navigation of Screens in a LightSwitch Application? #15 - How Do I: Open a Screen After Saving Another Screen in a LightSwitch Application? #16 - How Do I: Connect LightSwitch to an Existing Database?
And if you missed them, you can access all the “How Do I” videos here (like the one we released last week on deploying to Azure):
Watch all the LightSwitch How Do I videos
I’m doing more each week so keep an eye out for the next ones. I’ll have some out next week on using SharePoint in your LightSwitch applications. (Please note that if the video doesn’t appear right away, try refreshing. You can also download the video in a variety of formats at the bottom of the video page.)
I know it seems weird – why the heck is Beth Massi, “VB gal” promoting a link to a podcast on C# and Delphi? The reason is because of the guest speaker, Charlie Calvert, who I used to work with here at Microsoft. Charlie was my C# community counterpart and it’s awesome to hear that he’s doing well after leaving Microsoft. He had a Delphi background and I had a FoxPro background so it was always fun to chat in the halls with Charlie about “the good old days”. We brought a lot of the experiences we had being in those communities into the VB & C# communities we fostered at Microsoft.
Check it out: CodeCast Episode 106: C# and Delphi Community with Charlie Calvert
It was good to hear his voice again and I wish him well.
A common requirement in business applications is to capture changes to data records and save them in a history table of some sort. For instance, when an employee’s HR record is changed, you need to log the original and new field values as well as who made the changes so that there is a visible audit trail. Typically you want to log changes to records that are updated, inserted and deleted. In this post I’ll show you how you can create a simple audit trail to track changes to records in Visual Studio LightSwitch.
The first thing to do is to create a table that stores the changes. Let’s say we already have an application with an Employee table. We want to be able to capture any updates, inserts or deletes to this table. The first thing to do is add a table that captures the changes. Add a new table to your application using the Data Designer. I’ll name it EmployeeChange and it will have the following fields:
Then in the property window under the Appearance section, set the Summary Property of the EmployeeChange table to the “Updated” field. Next we need to add a new relationship to this table from our Employee table. Click the “Relationship…” button at the top of the designer and in the To column select the Employee table. Then set the Multiplicity to “Zero or One”. This means that our change log record doesn’t require an employee parent record. Why not? Well because in this example we want to also track deleted record information but we want to allow the deletion of the actual record in the Employee table. So we also need to set the On Delete Behavior to “Dissociate” so that when the employee record is deleted, our audit trail remains intact.
So here’s what the schema looks like now.
Next we need to write some code into the save pipeline on the data service to intercept when inserts, updates and deletes occur and write appropriate values to the EmployeeChange table. To do this, double-click on the Employee table in the Data Designer and then drop down the “Write Code” button in the top right. There you will see in the General Methods section _Updated / ing, _Inserted / ing, _Deleted / ing methods.
Select _Inserting, _Updating, and _Deleting methods to create the method stubs on your data service. Next we need to write some code that selects the storage properties of the Employee. Storage properties are the actual fields on the underlying table. In other words, we don’t want to include any calculated properties. For Employee records that are updated, we also need to compare the original and current values to determine if we should record a change. The way we do this is by drilling into the “Details” property on the Employee entity. Here you can get at a lot of the underlying framework methods and properties of LightSwitch’s data runtime. In our case I’m just recording the new and original values as strings by concatenating each field’s name and value and separating them by carriage return/line feeds (vbCrLf). You can choose to do this differently depending on how you want to log information to your change log table.
Private Sub Employees_Updating(entity As Employee) Dim change = entity.EmployeeChanges.AddNew() change.ChangeType = "Updated" change.Employee = entity change.Updated = Now() change.ChangedBy = Me.Application.User.FullName Dim newvals = "New Values:" Dim oldvals = "Original Values:" For Each prop In entity.Details.Properties.All(). OfType(Of Microsoft.LightSwitch.Details.IEntityStorageProperty)() If prop.Name <> "Id" Then If Not Object.Equals(prop.Value, prop.OriginalValue) Then oldvals += String.Format("{0}{1}: {2}", vbCrLf, prop.Name, prop.OriginalValue) newvals += String.Format("{0}{1}: {2}", vbCrLf, prop.Name, prop.Value) End If End If Next change.OriginalValues = oldvals change.NewValues = newvalsEnd Sub
Private Sub Employees_Inserting(entity As Employee) Dim change = entity.EmployeeChanges.AddNew() change.ChangeType = "Inserted" change.Employee = entity change.Updated = Now() change.ChangedBy = Me.Application.User.FullName Dim newvals = "Inserted Values:" For Each prop In entity.Details.Properties.All(). OfType(Of Microsoft.LightSwitch.Details.IEntityStorageProperty)() If prop.Name <> "Id" Then newvals += String.Format("{0}{1}: {2}", vbCrLf, prop.Name, prop.Value) End If Next change.NewValues = newvalsEnd Sub
Private Sub Employees_Deleting(entity As Employee) Dim change = entity.EmployeeChanges.AddNew() change.ChangeType = "Deleted" change.Updated = Now() change.ChangedBy = Me.Application.User.FullName Dim oldvals = "Deleted Values:" For Each prop In entity.Details.Properties.All(). OfType(Of Microsoft.LightSwitch.Details.IEntityStorageProperty)() If prop.Name <> "Id" Then oldvals += String.Format("{0}{1}: {2}", vbCrLf, prop.Name, prop.Value) End If Next change.OriginalValues = oldvals End Sub
Last but not least we need to create a screen to view the audit trail. You don’t want users to modify these records, just view them, so it’s best to just pick the Search Data Screen template.
Then in the screen designer, select the Updated field and uncheck “Show as Link” so that users cannot modify records. Assuming that you already have an employee screen defined, hit F5 to run the application and open your employee screen. Make some changes to a current record, add a new one and delete another one to test all three scenarios. Then open your audit trail search screen and take a look at the results. You should see something similar to this:
If you needed to track changes on multiple tables then you could add relations from the single audit trail table to other tables the same way as Employee in this example. Experiment with the code provided in order to log changes exactly how you want. I hope this gives you some ideas on how you can create simple audit trails for your LightSwitch applications.
A lot of times you want to display static images and text on your data entry screens, say a company logo or some help text presented in a label to guide the user on the screen. Adding images to buttons on screens is as easy as setting it in the properties window. However, when you want to display an image or static text on the screen directly you need to do a couple things. If you take a look at the Contoso Construction sample that I released a couple weeks ago, you will see that the “Home” screen displays a lot of static images and text in a completely customized screen layout. In this post I will show you how you can add static text and images to your LightSwitch screens by walking through this sample.
By default, all the controls on screens you create in Visual Studio LightSwitch have to be bound to a data item. The screen designer displays all the data items in your view model on the left side. In the center is what’s called a “Content Tree”. Content Items make up the tree – these are the controls bound to each of the data items.
So when building screens in LightSwitch every control in the content tree must be bound to a data item. This data item can be a field on an entity, a collection of entities, or any other data item that you add to the view model. The screen templates help guide you in setting up a layout and all the data binding for a particular type of screen; be that a search screen, details screen, new data screen, etc. These templates are just starting points that you can customize further. In fact, the Home screen in this example is a completely custom layout. After selecting a template, I deleted all the items in the content tree and built up my own layout.
Data items can be basic properties like strings or integers, they can be methods that you call from screen code or buttons, or they can be entities that originate from queries. To add a data item manually to a screen click the “Add Data Item” button at the top of the screen designer. You can then specify the name and type of the data item. The home screen is a combination of all of these types of data items.
You add static images and text to the screen as local properties via the Add Data Item dialog and then lay them out in the content tree by dragging them from the view model on the left. So to add a static image to the screen first click “Add Data Item”, select Local Property, set the type to Image, and then name the property.
Click OK and it will appear in your view model on the left of the screen designer. Drag the data item where you want it in the content tree (if you don’t get it right you can run it later (F5) and move it around on the screen real time). Finally, change the control to an Image Viewer from Editor.
Repeat the same process for static text. To add static text to the screen first Add Data Item, select Local Property, set the type to String and then name the property. Drag the item into the content tree and then change the control to Label.
Because static screen properties do not originate from a data entity, you need to set the property value before the screen is displayed. You can do this in the screen’s InitializeDataWorkspace method which runs before any queries execute. You can access screen methods by dropping down the “Write Code” button at the top right of the screen designer. To set one image and one text static property you would write this code:
Private Sub Home_InitializeDataWorkspace(saveChangesTo As List(Of IDataService)) ' Initialize text properties Text_Title = "Contoso Construction Project Manager" ' Initialize image properties Image_Logo = MyImageHelper.GetImageByName("logo.png") End Sub
In order to load static images you need to switch to file view, right-click on the Client project’s \Resources folder and select Add –> Existing Item. Browse for the image and then set the build action to “Embedded Resource”.
Next you need to write some code to load the image. In the Contoso Construction sample application it uses static images in a variety of screens so I created a helper class called MyImageHelper that can be used from anywhere in the client code. While in file view, right-click on the \UserCode folder in the Client project and select Add –> Class. Name it MyImageHelper and create a static (Shared) method that loads the image.
''' <summary> ''' This class makes it easy to load images from the client project. ''' Images should be placed in the Client project \Resources folder ''' with Build Action set to "Embedded Resource" ''' </summary> ''' <remarks></remarks> Public Class MyImageHelper Public Shared Function GetImageByName(fileName As String) As Byte() Dim assembly As Reflection.Assembly = Reflection.Assembly.GetExecutingAssembly() Dim stream As Stream = assembly.GetManifestResourceStream(fileName) Return GetStreamAsByteArray(stream) End Function Private Shared Function GetStreamAsByteArray( ByVal stream As System.IO.Stream) As Byte() If stream IsNot Nothing Then Dim streamLength As Integer = Convert.ToInt32(stream.Length) Dim fileData(streamLength - 1) As Byte stream.Read(fileData, 0, streamLength) stream.Close() Return fileData Else Return Nothing End If End Function End Class
You can add any custom code you need to the client this way. You have a lot of flexibility to add your own custom code and classes to LightSwitch and this is one simple example that show's how you can add static text and images to screens.
Looks like I missed this! Carl released a dnrTV on Monday that we did together while I was in Montreal for DevTeach last week. Check it out:
Beth Massi on Advanced Visual Studio LightSwitch Beta 2
Here I walk through the array of customizations you can do with LightSwitch from writing LINQ queries, to adding custom code to the client and server projects, to using and then building your own extensions. You can download the sample application here: Contoso Construction - LightSwitch Advanced Development Sample
It’s always a fun time with Carl Franklin. One bummer deal is I didn’t have internet connection where we recorded the show but Carl’s humor made up for it. ;-)
A while back a posted how to use Word to create simple reports for LightSwitch by passing in an entity (like Customer) and using Word Content Controls to lay out content in a template and bind them to XML inside the document. If you missed it: Using Microsoft Word to Create Reports For LightSwitch (or Silverlight)
In the comment thread to that post there were requests on how to create master detail reports, for instance an Order and OrderDetails. In my last post I released a sample that shows one way to achieve this. The code is part of a larger LightSwitch sample application: Contoso Construction - LightSwitch Advanced Development Sample. In that sample I use the same technique in the post referenced above to add a helper class and Word document template to the client project and bind XML to content controls. The only difference to achieve the master-detail formatting is in the layout of the content controls and the code that generates the XML.
In the Contoso Construction sample, we have a parent “Project” table that has many “ProjectMaterials”. If you open the project screen you will see a button at the top that allows you to generate a project status report which displays fields from the Customer, Project and all the ProjectMaterials used on the construction project.
Project has a one-to-many relationship to ProjectMaterials so we could have one or many lines of materials to display. One way to do this is to lay out a single content control that will contain multiple lines like so:
So in this case I have four content controls representing the four fields I want to display off of the ProjectMaterial entity. Then all you need to do when generating the XML is to iterate the collection of children and put line breaks between them. You can do this easily using the Join method which takes an array and creates a string representation of the contents with a delimiter you specify, in my case I’m using the carriage return (vbCr). So in the MyReportHelper class we have code like so, paying particular attention to how I’m generating the <projectmaterials> node:
Public Shared Sub RunProjectStatusReport(ByVal project As Project) If AutomationFactory.IsAvailable Then Try 'Create the XML data from our entity properties. ' Project materials content controls on the Word template are set to allow carriage ' returns so we can easily display as many line items as we need ' Dim myXML = <customer> <fullname><%= project.Customer.FullName %></fullname> <homephone><%= project.Customer.HomePhone %></homephone> <mobilephone><%= project.Customer.MobilePhone %></mobilephone> <email><%= project.Customer.Email %></email> <fulladdress><%= project.Customer.FullAddress %></fulladdress> <project> <projectname><%= project.ProjectName %></projectname> <startdate><%= project.StartDate.ToShortDateString %></startdate> <estimatedenddate><%= project.EstmatedEndDate.ToShortDateString %></estimatedenddate> <originalestimate><%= Format(project.OriginalEstimate, "c2") %></originalestimate> <labor><%= Format(project.Labor, "c2") %></labor> <totalcost><%= Format(project.TotalCost, "c2") %></totalcost> <notes><%= project.Notes %></notes> <projectmaterials> <summary><%= Join((From m In project.ProjectMaterials Select m.Summary).ToArray, vbCr) %></summary> <quantity><%= Join((From m In project.ProjectMaterials Select CStr(m.Quantity)).ToArray, vbCr) %></quantity> <price><%= Join((From m In project.ProjectMaterials Select Format(m.Price, "c2")).ToArray, vbCr) %></price> <itemtotal><%= Join((From m In project.ProjectMaterials Select Format(m.ItemTotal, "c2")).ToArray, vbCr) %></itemtotal> </projectmaterials> </project> </customer> Using word = AutomationFactory.CreateObject("Word.Application") 'The report template already has content controls bound to XML inside. ' Look in the ClientGenerated project to view the Word template. Dim resourceInfo = System.Windows.Application.GetResourceStream( New Uri("ProjectStatus.docx", UriKind.Relative)) Dim fileName = CopyStreamToTempFile(resourceInfo.Stream, ".docx") Dim doc = word.Documents.Open(fileName) 'Grab the existing bound custom XML in the doc Dim customXMLPart = doc.CustomXMLParts("urn:microsoft:contoso:projectstatus") Dim all = customXMLPart.SelectSingleNode("//*") Dim replaceNode = customXMLPart.SelectSingleNode("/ns0:root[1]/customer[1]") 'replace the <customer> node in the existing custom XML with this new data all.ReplaceChildSubtree(myXML.ToString, replaceNode) word.Visible = True End Using Catch ex As Exception Throw New InvalidOperationException("Failed to create project status report.", ex) End Try End If End Sub
Hope this helps. For details on how to create the Word templates, bind them to XML and how to create the MyReportHelper class please read Using Microsoft Word to Create Reports For LightSwitch (or Silverlight).