Beth's Chinese blog
Now that the Visual Studio LightSwitch community is really growing I thought I’d start posting some of the cool articles, videos, samples and extensions I find each month. What’s great about a blog post is if I miss something you can just add it to the comment thread below :-). First off, here are the LightSwitch team “hangouts” where you can get training, ask questions, and interact with the LightSwitch team. The biggest one of course is:
This is your one-stop-shop to training content, samples, extensions, documentation, podcasts, a portal to the forums, community, and much more. All of the team content is aggregated onto this site, and we also aggregate all the community submitted extensions and samples. It’s the first place you should go if you’re just learning LightSwitch. Also here are some other biggies:
LightSwitch MSDN Forums LightSwitch Team Blog LightSwitch on Facebook LightSwitch on Twitter (@VSLightSwitch, #VisualStudio #LightSwitch)
There’s some great sites that I know about that have some awesome LightSwitch content run by MVPs and community rock stars that you should check out often. We wouldn’t be anywhere without passionate people like you! Anyone else running a community site or forum for LightSwitch out there? Post a comment below and I’ll include you!
I figure since I just started this series it would be good to pop in here some of the highest viewed content we have to date. There’s a TON of stuff out there especially on the Developer Center Learn section where we continuously aggregate blog articles, videos and library documentation but here’s some good stuff to check out if you missed it.
You can also take a look at the most popular samples and most popular extensions. We also had a lot of magazine articles, videos and podcasts released in August to check out.
I know the month’s not quite over but I like to get posts out before Europe goes off partying for the weekend ;-). I’ll scoop up anything else for next month. Here’s some of the fun things the team and community has been doing. Check out the 19 FREE extensions released just this month!
Here some conferences and big code camps coming up soon that have LightSwitch presence. More details on the SoCal and Silicon Valley code camps here.
I’m sure I missed some great stuff so help me out! The community is world-wide and I don’t see everything. What’s really helpful is that the community has been using the hash tag #LightSwitch on twitter when posting stuff so it’s easier for me to catch it. Join the conversation!
Enjoy!
Last week Grid Logic released a FREE LightSwitch extension called the Office Integration Pack which has quickly risen to the second most popular LightSwitch extension on VS Gallery! It lets you populate documents and spreadsheets with data, create email and appointments with Outlook, import data from Excel, create PDFs, and a bunch of other stuff from your LightSwitch desktop applications. I’ve been known to do a bit of Office development in my day ;-) so I thought I’d check this extension out myself. In this post I’ll show you a couple tips for exporting data to Excel and Word that I learned while I was playing around.
First thing you need to do is get the Office Integration Pack installed. You’ll also want to download the sample application and documentation. (BTW, the source code is also provided for free here!) You can download and install the Office Integration Pack directly from Visual Studio LightSwitch via the Extension Manager or you can download it manually from the Visual Studio Gallery.
Once you install the extension, restart Visual Studio. Then you will need to enable the extension on your LightSwitch project by opening the project properties, clicking the Extensions tab, and then checking the Office Integration Pack.
Now let’s explore some of the things this baby can do.
LightSwitch has a really nice feature on data grids that allow you to export them to Excel:
This gives users a basic way of getting data out of the system to create reports or do further analysis on the data using Excel. However, you can’t call this feature from your own code. One of the great features of the Office Integration Pack is it not only lets you call the Export from code, it also allows a bunch of customization. You can control exactly what fields are exported as well as specify what worksheet the data should be exported into.
For instance say I have a list of customers on my own search screen (like pictured above) and I want to provide my own export that only exports CompanyName, ContactName, ContactTitle and Phone fields. In the screen designer first add a button onto the Data Grid’s command bar, I’ll call it ExportToExcel.
In the property window you can then specify an image to display if you want. We can also turn off the default Excel export on the grid by selecting the Customers Data Grid and in the properties window check “Disable Export to Excel”
Now we need to write some code to export the fields we want. Right-click on the Export to Excel button and select “Edit Execute Code”. We can use a couple different OfficeInetgration.Excel.Export APIs to do what we want here. The way I usually learn about a new API is through IntelliSense so if we start typing “OfficeIntegration (dot) Excel (dot)” you will see the list of available methods:
The Export method has four overloads. The first and simplest just takes the data collection and will export the all the data and fields to a new workbook, similar to the built-in Excel export. The second overload lets us specify a particular workbook, worksheet and range. In our case we want to specify particular fields as well and there’s a couple ways we can do that. The 3rd and 4th overloads let us specify a ColumnNames parameter which can take two forms. One is just a simple List(Of String). Just fill the list with the field names you want to export.
Private Sub ExportToExcel_Execute() Dim ExcelFile = Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments) & "\Customers.xlsx" If File.Exists(ExcelFile) Then Dim fields As New List(Of String) From {"CompanyName", "ContactName", "ContactTitle", "Phone"} OfficeIntegration.Excel.Export(Me.Customers, ExcelFile, "Sheet1", "A1", fields) End If End Sub
Another way we can do this is by specifying a List(Of OfficeIntegration.ColumnMappings). The ColumnMappings class is used in many of the APIs particularly the Import method where you could specify both the column in the workbook and the property on the entity in order to map them. In the case of an Export, this isn’t necessary, all I need to do is specify the properties (fields) on the entity I want to export.
Now when we run the application and click our export button we will see only the fields we specified exported to Excel.
We can also export data to Word as well. There are a couple methods you can take advantage of here. One is called GenerateDocument which lets you define a template of Content Controls in which the data will be exported. Content controls are a great way for capturing data inside of Word documents. Let’s create a Word document that reports all of a customer’s orders. First I’ll create a Details Screen for my customer and select to include the Customer Orders as well. This will create a one-to-many screen that has the customer detail and a grid of their orders below.
Next I’ll add a button to the screen, this time in the screen command bar at the top, called “Generate Document”.
Next we need to create the template in Word and add the content controls where we want them which will be populated with data from our customer entity. First enable the Developer tab in Word (File –> Options –> Customize Ribbon, then check to enable the “Developer” tab). Lay out simple text controls around your template and format it how you want. Then click properties to name the controls. You can name the content controls anything you want. Later we will specify the ColumnMapping between the Title of the content controls and the customer properties.
We also want to create a table of related orders into this document. In order to create tables, you create a table in Word and then bookmark it. You can optionally create the column headers manually or you can have the Office Integration Pack output them for you. I’ll create a nicely formatted table with two rows for this one with my own column headers in the first row. Then I’ll bookmark it “OrderTable”.
Finally we need to write some code to first call GenerateDocument to populate our content controls and then make a call to ExportEntityCollection to export the collection of related Orders into the bookmarked table in Word. I’ll also generate a PDF from this by calling the SaveAsPDF method. Back on the screen right-click on the GenerateDocument command and “Edit Execute Code” and write the following:
Private Sub GenerateDocument_Execute() Dim MyDocs = Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments) Dim WordFile = MyDocs & "\Customer.docx" If File.Exists(WordFile) Then 'Map the content control tag names in the word document to the entity field names Dim custFields As New List(Of OfficeIntegration.ColumnMapping) custFields.Add(New OfficeIntegration.ColumnMapping("ContactName", "ContactName")) custFields.Add(New OfficeIntegration.ColumnMapping("CompanyName", "CompanyName")) custFields.Add(New OfficeIntegration.ColumnMapping("Phone", "Phone")) Dim doc As Object = OfficeIntegration.Word.GenerateDocument(WordFile, Me.Customer, custFields) 'Export specific fields to the bookmarked "OrderTable" in Word Dim orderFields As New List(Of String) From {"ShipName", "OrderDate", "ShippedDate"} OfficeIntegration.Word.ExportEntityCollection(doc, "OrderTable", 2, False, Me.Customer.Orders, orderFields) OfficeIntegration.Word.SaveAsPDF(doc, MyDocs & "\Customer.pdf", True) End If End Sub
When you run it you’ll end up with a Word document and a PDF displayed on the screen with our data formatted perfectly!
Note that a lot of these methods return the document (or workbook) automation object so you can make additional late-bound COM calls to do anything else you want to automate with Office. If you go that route here are the Excel and Word COM developer references that you’ll want handy ;-).
Have fun using the Office Integration Pack. Thank you Grid Logic!
Since yesterday when I posted about the code camps in California that are coming up I got a couple folks ping me if there were any events in their area. Well here’s a site created by Microsoft Developer Evangelist G. Andrew Duthie (a.k.a. Devhammer) called Community Megaphone where people submit their community events and you can find them based on your location. If you don’t see an event there that you know about please submit it!
In addition to the web site, there are RSS and OData feeds for read-only access to event data (no account required) and there's a web service API for adding/editing events (requires registration). Andrew is also experimenting with an HTML5 version here.
Last but not least, C# MVP Kevin Hazzard has created a Community Megaphone Windows Phone 7 application (which I just downloaded via the Marketplace ;-)) and the source is available on CodePlex! Awesome! Thanks guys!
UPDATE: Also check out the Community Megaphone podcast!
Code Camp is a type of community event where developers learn from fellow developers. All are welcome to attend and speak at these FREE events that happen all over the world. Sessions range from informal “chalk talks” to full-blown presentations. There is usually a mix of presenters and skill levels, some experienced folks, and some first-timers. We’ve got a couple code camps in California coming up that will have LightSwitch-related sessions that you should check out if you’re in the area. They are fun community-driven events!
With over 200 sessions and 2000 attendees this is the largest code camp I know of. I‘ve been speaking here for many years. This code camp has a huge variety of developer topics from desktop to web to cloud to phone to enterprise development and much much more. We’ve got big name sponsors from all over like Microsoft, Google, Oracle, Adobe, PayPal, and a whole bunch more who care about developers. Register here: http://www.siliconvalley-codecamp.com/Register.aspx and follow @sv_code_camp on twitter.
I’ve got two LightSwitch sessions that I’ll be delivering this year. Click the links below and let me know you are coming. :-)
Building Business Applications Quickly with Visual Studio LightSwitch Level: Beginner | 3:30 PM Saturday Visual Studio LightSwitch is the simplest way for developers of all skill levels to build data-centric business applications for the desktop and cloud. LightSwitch simplifies the development process by letting you concentrate on the business logic, while LightSwitch handles the common tasks and plumbing for you. In this demo-heavy session, you will see, end-to-end, how to build and deploy a business application using LightSwitch.
LightSwitch Advanced Development and Customization Techniques Level: Intermediate | 5:00 PM Saturday Microsoft Visual Studio LightSwitch is the simplest way to build business applications for the desktop and the cloud. In the session you will see techniques for building sophisticated UI, implementing middle tier application logic by customizing the data update pipeline and much more without leaving the LightSwitch development environment. You will also see how combining Visual Studio Professional+ and your existing Silverlight and .Net skills with LightSwitch provide even more customization opportunities.
LightSwitch community rock star and Silverlight MVP, Michael Washington, will be speaking at the SoCal Code Camp on some more advanced LightSwitch capabilities. Michael runs the LightSwitchHelpWebSite.com community site and is a super-active member of the LightSwitch community. Of course there are tons of other sessions as well. Register here: http://www.socalcodecamp.com/member_register.aspx and follow @CodeCampinSoCal on twitter.
Creating Visual Studio LightSwitch Custom Controls Michael Washington | Level: 200 Do not be fooled into believing that Visual Studio LightSwitch is only for basic forms over data. LightSwitch is a powerful MVVM (Model - View- View Model) toolkit, that enables a professional developer the ability to achieve incredible productivity. LightSwitch custom controls, provide the professional developer, the tool to unleash the power of this incredible product. Learn how to easily make LightSwitch Custom Controls, even if you have never created a Silverlight control before
First off let me say WOW, it’s great to be back to blog writing! Sorry I have been away for a couple weeks – I’ve been working on a lot of cool stuff internally since I got back from my trip. And I know I have a loooooong list of article and video suggestions from all of you that I need to get to so thanks for bearing with me! Today I’m going to show you another common (and often requested) technique when creating data entry screens.
In my previous posts on data-driven lookup lists (or sometimes called “Pick Lists”) I showed a few techniques for formatting, editing and adding data to them. If you missed them:
In this post I’m going to show you a couple different ways you can help users select from large sets of lookup list data on your data entry screens. For instance, say we have a one-to-many relationship from category to product so when entering a new product we need to pick a category from an auto-complete box. LightSwitch generates this automatically for us based on the relation when we create the product screen. We can then format it like I showed in my previous example.
Now say we’ve set up our product catalog of hundreds or even thousands of these products and we need to select from them when creating Orders for our Customers. Here’s the data model I’ll be working with – this illustrates that a Product needs to be selected on an OrderDetail line item. OrderDetail also has a parent OrderHeader that has a parent Customer, just like good ‘ol Northwind.
In my product screen above there are only about 20 categories to choose from so displaying all the lookup list data from the category table in this drop down works well. However, that’s not the best option for the product table with lots of data -- that’s just too much data to bring down and display at once. This may not be a very efficient option for our users either as they would need to either scroll through the data or know the product name they were specifically looking for in order to use the auto-complete box. A better option is to use a modal window picker instead which allows for more search options as well as paging through data. Another way is to filter the list of products by providing a category drop-down users select first. Let’s take a look at both options.
Say I have selected the Edit Details Screen template to create a screen for entering an OrderDetail record. By default, LightSwitch will generate an auto-complete box for the Product automatically for us. It also does this for the OrderHeader as well since this is also a parent of OrderDetail. On this screen however, I don’t want the user to change the OrderHeader so I’ll change that to a summary control. I’ll also change the Auto Complete Box control on the Product to a Modal Window Picker:
I also want the products to display in alphabetical order so I’ll create a query called SortedProducts and then at the top of the screen select “Add Data Item” and then choose the SortedProducts query:
Once you add the query to the screen, select the Product in the content tree and set the “Choices” property to “SortedProducts” instead of Auto.
You can also fine-tune how many rows of data will come down per page by selecting the SortedProducts query and then setting the number of items to display per page in the properties window. By default 45 rows per page are brought down.
Now hit F5 to run the application and see what you get. Notice that when you run the screen you can now select the ellipses next to Product which brings up the Modal Window Picker. Here users can search and page through the data. Not only is this easier for the user to find what they are looking for, using a Modal Window Picker is also the most efficient on the server.
Another technique is using another auto-complete box as a filter into the next. This limits the amount of choices that need to be brought down and displayed to the user. This technique is also useful if you have cascading filtered lists where the first selection filters data in the second, which filters data in the next, and so forth. Data can come from either the same table or separate tables like in my example – all you need is to set up the queries on your screen correctly so that they are filtered by the proper selections.
So going back to the OrderDetail screen above, set the Product content item back to an Auto Complete Box control. Next we’ll need to add a data item to our screen for tracking the selected category. This category we will use to determine the filter on the Product list so that users only see products in the selected category. Click “Add Data Item” again and this time add a Local Property of type Category called SelectedCategory.
Next, drag the SelectedCategory over onto the content tree above the Product. LightSwitch will automatically create an Auto Complete Box control for you.
If you want to also sort your categories list you do it the same way as we did with products, create a query that sorts how you like, add the data item to the screen, and then set the Choices property from Auto to the query.
Now we need to create a query over our products that filters by category. There are two ways to do this, you can create a new global query called ProductsByCategory or if this query is only going to be used for this specific screen, you can just click Edit Query next to the SortedProduct query we added earlier. Let’s just do it that way. This opens the query designer which allows you to modify the query locally here on the screen. Add a parameterized filter on Category.Id by clicking the +Filter button, then in the second drop-down choose Category.Id, in the fourth drop down select Parameter, and in the last drop-down choose “Add New…” to create a parameterized query. You can also make this parameter optional or required. Let’s keep this required so users must select the category before any products are displayed.
Lastly we need to hook up the parameter binding. Back on the screen select the Id parameter that you just created on the SortedProducts query and in the properties window set the Parameter Binding to SelectedCategory.Id. Once you do this a grey arrow on the left side will indicate the binding.
Once you set the value of a query parameter, LightSwitch will automatically execute the query for you so you don’t need to write any code for this. Hit F5 and see what you get. Notice now that the Product drop down list is empty until you select a Category at which point feeds the SortedProducts query and executes it. Also notice that if you make a product selection and then change the category, the selection is still displayed correctly, it doesn’t disappear. Just keep in mind that anytime a user changes the category the product query is re-executed against the server.
One additional thing that you might want to do is to initially display the category to which the product belongs. As it is, the Selected Category comes up blank when the screen is opened. This is because it is bound to a screen property which is not backed by data. However we can easily set the initial value of the SelectedCategory in code. Back in the Screen Designer drop down the “Write Code” button at the top right and select the InitializeDataWorkspace method and write the following:
Private Sub OrderDetailDetail_InitializeDataWorkspace(saveChangesTo As List(Of IDataService)) ' Write your code here. If Me.OrderDetail.Product IsNot Nothing Then Me.SelectedCategory = Me.OrderDetail.Product.Category End If End Sub
Now when you run the screen again, the Selected Category will be displayed.
The above example uses a simple screen that is editing a single OrderDetail record – I purposely made it simple for the lesson. However in real order entry applications you are probably going to be editing OrderDetail items at the same time on a one-to-many screen with the OrderHeader. For instance the detail items could be displayed in a grid below the header.
Using a Modal Window Picker is a good option for large pick lists that you want to use in an editable grid or on a one-to-many screen where you are editing a lot of the “many”s like this. However using filtered auto-complete boxes inside grid rows isn’t directly supported. BUT you can definitely still use them on one-to-many screens, you just need to set up a set of controls for the “Selected Item” and use the filtered boxes there. Let me show you what I mean.
Say we create an Edit Detail Screen for our OrderHeader and choose to include the OrderDetails. This automatically sets up an editable grid of OrderDetails for us.
Change the Product in the Data Grid Row to a Modal Window Picker and you’re set – you’ll be able to edit the line items and use the Modal Window Picker on each row. However in order to use the filtered drop downs technique we need to create an editable detail section below our grid. On the content tree select the “children” row layout and then click the +Add button and select Order Details – Selected Item.
This will create a set of fields below the grid for editing the selected detail item (it will also add Order Header but since we don’t need that field here you can delete it). I’m also going to make the Data Grid Row read only by selecting it and in the properties windows checking “Use Read-only Controls” as well as remove the “Add…” and “Edit…” buttons from the Data Grid command bar. I’ll add an “AddNew” button instead. This means that modal windows won’t pop up when entering items; instead we will do it in the controls below the grid. You can make all of these changes while the application is running in order to give you a real-time preview of the layout. Here’s what my screen looks like now in customization mode.
Now that we have our one-to-many screen set up the rest of the technique for creating filtered auto-complete boxes is almost exactly the same. The only difference is the code you need to write if you want to display the Selected Category as each line item is edited. To recap:
The only difference when working with collections (the “many”s) is step 6 where we write the code to set the Selected Category. Instead of setting it once, we will have to set it anytime a new detail item is selected in the grid. On the Screen Designer select the OrderDetails collection on the left side then drop down the “Write Code” button and select OrderDetails_SelectionChanged. Write this code:
Private Sub OrderDetails_SelectionChanged() If Me.OrderDetails.SelectedItem IsNot Nothing AndAlso Me.OrderDetails.SelectedItem.Product IsNot Nothing Then Me.SelectedCategory = Me.OrderDetails.SelectedItem.Product.Category End If End Sub
In this article I showed you a couple techniques available to you in order to display large sets of lookup list data to users when entering data on screens. The Modal Window Picker is definitely the easiest and most efficient solution. However sometimes we need to really guide users into picking the right choices and we can do that with auto-complete boxes and parameterized queries.