• Beth Massi - Sharing the goodness

    LightSwitch Tips & Tricks on Query Performance

    • 0 Comments

    LightSwitch allows you to connect to multiple data sources, relate them together, and create screens to edit the data. These data sources can be a variety of databases like SQL Server and SQL Azure, SharePoint, and custom RIA services. With the release of Visual Studio 11 Beta, the next version of LightSwitch also allows you to connect data services via OData which are often hosted remotely. When the size of the data in these data sources get too large, or the distance between them increases (i.e. Internet based data services), performance decreases. Contributing factors are the size of the data set coming down the wire is too big, the number of fields to search on in the table is high, or the size of the data in the tables has grown very large.

    As the size of data grows and/or the length of the “wire” increases, it’s important to understand the affects these have on query performance. In this post I’d like to discuss some important considerations to make when designing your entities and queries in LightSwitch against large or remote data sets.

    Default Searching Behavior

    When you design your entities in LightSwitch, by default all string properties are searchable. This means that when displaying data in grids or lists, the search box will execute a query that compares all the string fields in the table for a possible match. For example, say I have a table of customers coming from the AdventureWorksLT database. If I search for “Adam” then all records with any mention of the word “adam” in any of its string fields are displayed.

    image

    There are 10 string fields in this table so the WHERE clause on the database query is:

    WHERE ([LastName] LIKE N'%Adam%') OR ([FirstName] LIKE N'%Adam%') OR ([MiddleName] LIKE N'%Adam%') OR ([Title] LIKE N'%Adam%') OR ([Suffix] LIKE N'%Adam%') OR ([CompanyName] LIKE N'%Adam%') OR ([SalesPerson] LIKE N'%Adam%') OR ([EmailAddress] LIKE N'%Adam%') OR ([Phone] LIKE N'%Adam%') OR ([PasswordHash] LIKE '%Adam%') OR ([PasswordSalt] LIKE '%Adam%')

    Similarly, when connecting to OData sources with LightSwitch in Visual Studio 11, you can see the query over HTTP to the remote data service. For instance, in the following example I have a search screen for movie & music titles coming from the Netflix OData service, sorted by name. Notice if I search for “Star Wars” that all records with any mention of the phrase “Star Wars” in any of its string fields are displayed.

    image

    Here’s the request sent to the OData service. (For more information on the query syntax see the URI conventions supported in OData queries.)

    GET http://odata.netflix.com/v2/Catalog/Titles()?$orderby=Name&$filter=substringof('Star%20Wars',Id)%20or%20substringof('Star%20Wars',Name)%20or%20(substringof('Star%20Wars',ShortName)%20or%20substringof('Star%20Wars',Synopsis))%20or%20(substringof('Star%20Wars',ShortSynopsis)%20or%20substringof('Star%20Wars',Url)%20or%20(substringof('Star%20Wars',Rating)%20or%20substringof('Star%20Wars',Type)))%20or%20(substringof('Star%20Wars',WebsiteUrl)%20or%20substringof('Star%20Wars',NetflixApiId)%20or%20substringof('Star%20Wars',TinyUrl))&$skip=0&$top=45&$expand=Movie,Series,Season,Disc&$select=*,Movie/*,Series/*,Season/*,Disc/*

    In this case it can take over 15 seconds to display the results depending on my connection speed to this public OData service. We can speed up both of these queries considerably by limiting the number of properties that must be searched. You can do that by opening up the entity in the Data Designer and marking fields “Is Searchable” in the properties window.

    image

    You can also uncheck this for an entire entity. If you do, then when you add the entity to screens, the screen query will have “Support search” unchecked, which removes the search box from the UI.  

    image

    Keep in mind that the “Is Searchable” property on the entity controls the behavior regardless of what you do in the UI. So if you turn “Support search” on in the screen designer, but it is unchecked in the data designer, then the entity will not be searchable and a message will appear to the user that attempts to perform a search in the UI.

    In the case of customer above we don’t need to waste our time searching through properties we’re never going to display to the user like password information. And in the case of the OData service data source, if I decide to only allow searching on the Title’s Name & Synopsis then the query will now be much quicker.

    GET http://odata.netflix.com/v2/Catalog/Titles()?$orderby=Name&$filter=substringof('Star%20Wars',Name)%20or%20substringof('Star%20Wars',Synopsis)&$skip=0&$top=45&$expand=Movie,Series,Season,Disc&$select=*,Movie/*,Series/*,Season/*,Disc/*

    NOTE: In Visual Studio 11 Beta, the default behavior for OData sources is the same as database data sources. However we are changing this at final release where not all properties will have “Is Searchable” checked by default, so that querying OData sources will be much quicker.

    Data Set / Page Size

    On the screen designer you can also set the page size to control the size of the result sets that are returned. Here you can fine-tune how many rows of data will come down per page by selecting the screen 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. This helps control the bandwidth you are using when bringing results down from the server to the client.

    image

    Auto Executing Queries

    By default LightSwitch also auto-executes queries for you. This means you don’t need to do anything to have data load into screens. Sometimes, however, you can make considerable performance gains by controlling this yourself. For instance, if you are creating a custom search screen with many optional parameters you may want to uncheck “Auto Execute Query” to allow users to first specify search criteria and then issue the search at once.

    image

    For instance, say we want to create our own search screen for titles and only allow searching on the Name and the Synopsis. We first create a query that makes both of these parameters optional. (For more information see: Creating a Custom Search Screen in Visual Studio LightSwitch)

    image

    When we use the query on a screen, uncheck the “Auto execute query” and then add a command button that the user can click to execute the search once they enter all of the optional parameters they want to search. Then we simply write this code to load the results:

    Private Sub Search_Execute()
        Me.TitleSearch.Load()
    End Sub

    Static Spans

    Static spans have been around since LightSwitch V1. These are used if you want to make sure to include (or exclude) related entities in the query that loads your data. You typically only need to use this if you are using the related data in your screen code because LightSwitch can see if you are using related data on the screen itself within the content tree and will automatically include this data for you. To control this, first click the “Edit Query” link on the screen query.

    image

    Then in the properties window, select “Manage Included Data” to select what data to include.

    image

    More Tips & Tricks

    These are just some of the query-related things you can do within LightSwitch to help performance. Of course there are a lot of factors outside LightSwitch that can affect performance, like the speed of your data sources and services you are using. For more tips and tricks see:

    Enjoy!

  • Beth Massi - Sharing the goodness

    .NET Rocks! Podcast: Beth Massi Builds Apps with LightSwitch in Studio 11

    • 1 Comments

    Check it out, I’ve got another podcast with Richard and Carl this time talking about the new features in LightSwitch in Visual Studio 11. I always have a ton of fun talking with these guys and you can tell I’m super excited about the next version of LightSwitch.

    Show #769: Beth Massi Builds Apps with LightSwitch in Studio 11

    Carl and Richard talk to Beth Massi about the latest incarnation of LightSwitch. In 2011 LightSwitch shipped as a separate install, but the upcoming version of LightSwitch is part of every SKU of Studio 11. Beth talks about how LightSwitch has evolved to be an awesome consumer and creator of data, making it simple to create oData interfaces over anything. The conversation also digs into the role of Silverlight, the evolution of the client and how LightSwitch makes apps in the cloud much simpler.

    Enjoy!

  • Beth Massi - Sharing the goodness

    "What’s New with LightSwitch in Visual Studio 11" Recording Available

    • 5 Comments

    On Friday last week I delivered a webcast on the new LightSwitch features in Visual Studio 11 beta. I think it went pretty well considering it was the first time I had done the session end-to-end :-). You can now view the webcast recording here:

    Download: What's New with LightSwitch in Visual Studio 11
    WMV Download | WMA | MP4

    In the session we built an application called “Media Mate” that connects to the Netflix OData source to keep track of favorite movies and music. I showed off new features around all our OData work like how to consume external OData services. I also demonstrated how LightSwitch creates its own OData services adhering to your business rules and security settings so that other clients on different platforms can access your middle-tier easily and securely. I showed off some of the new UI enhancements and business types as well as some of the new deployment enhancements. I attached the presentation slides to the bottom of this post.

    image

    One demo hiccup happened where I messed up the search ability over the Netflix data. Doh! What I ended up doing is unchecking the IsSearchable property on the Title entity in the data designer which subsequently disabled the searching on the screen. So it was user error, not a beta bug ;-). At any rate, I think folks got the point. By the way, there are a lot of things you can do to improve search query performance in LightSwitch and I’ll follow up with a post about that soon.

    image

    I also demonstrated the updated Contoso Construction sample that you can download here:

    Contoso Construction - LightSwitch Advanced Sample (Visual Studio 11 Beta)

    For more information on the new features I demonstrated please see:

    Also don’t forget to visit the LightSwitch Developer Center, your one-stop-shop for learning all about LightSwitch.

    Enjoy!

  • Beth Massi - Sharing the goodness

    Modernize Apps Look & Feel with Metro Studio and the Cosmopolitan Shell

    • 4 Comments

    One of the design goals for LightSwitch in Visual Studio 11 was to modernize the look and feel of the UI. Back in March the team released the beta of the LightSwitch Cosmopolitan Shell and Theme. This theme and shell provides a bunch of improvements over the default one like displaying a logo at the top of the application, streamlined top-bar navigation menu, and a “Metro-style” command bar now located at the bottom of screens. This shell will become part of the product and the default shell and theme for new projects you build with LightSwitch in Visual Studio 11 at final release.

    So I attempted to apply the Cosmo shell to my Contoso Construction application a while back and I got a couple remarks about how my icons were pretty much sticking out like a sore thumb. I used traditional colorful icons in the application and while that looks great with the standard shell, it doesn’t fit well with the Metro-style icons in the Cosmopolitan shell. Unfortunately I didn’t have time to redraw the icons -- and let’s face it, I’m not a designer.

    Enter Syncfusion Metro Studio

    Luckily there’s a FREE tool out there that you can use to quickly create Metro-style icons! Last night I was reviewing a LightSwitch e-book from Jan (to be released soon I hear!) and he mentioned this free tool so I had to go check it out, Syncfusion Metro Studio. And yes it’s free! Thank you Syncfusion for supporting the community (and particularly the developers who can’t draw!)

    image

    This tool comes with a bunch of icon templates you can use to get started designing your own Metro-style icons and it’s super easy to use.

    image

    Pick a template and then you can change the sizing, shape, foreground, and background color. You can also view & copy the XAML or save it as a PNG.

    image

    For LightSwitch applications that use the Cosmo shell, it’s best to go with a Transparent background so that the light grey of the command bar comes through. Although Metro Studio doesn’t let you choose a transparent background in the color picker, you can simply type it in the textbox (like I show above).

    Contoso Construction Updated

    It took me about 20 minutes to update all the icons in the Contoso Contruction application and here’s what I came up with. Not bad for not being a designer :-). You can download the updated sample here:

    Download Contoso Construction - LightSwitch Advanced Sample (Visual Studio 11 Beta)

    Contoso1sm

    Syncfusion Metro Studio is a huge time-saver and all the icons you create are royalty-free even for commercial applications. Have fun “Metro-styling” your apps!

    Enjoy!

  • Beth Massi - Sharing the goodness

    I’m Speaking Next Week on What’s New with LightSwitch in Visual Studio 11

    • 0 Comments

    I’ll be speaking next week at the East Bay.NET User Group in Berkeley as well as delivering a MSDN webcast on What’s New with LightSwitch in Visual Studio 11. I hope you can either make it in person or join in online to see and discuss the exciting new features LightSwitch will bring you. I always have a blast delivering these so I hope you can join!

    What's New with LightSwitch in Visual Studio 11
    Microsoft Visual Studio LightSwitch is the simplest way to build business applications and data services for the desktop and the cloud. LightSwitch contains several new features and enhanced capabilities in Visual Studio 11. In this demonstration-heavy webcast, we walk through the major new features, such as creating and consuming OData services, new controls and formatting, new features with the security system and deployment, and much more.

    East Bay.NET User Group: What's New with LightSwitch in Visual Studio 11
    Thursday, May 10, 2012 6:45 PM Pacific Time

    Register here.

    MSDN Webcast: What's New with LightSwitch in Visual Studio 11 
    Friday, May 11, 2012 1:00 PM Pacific Time

    Register here.

    Hope to see you there!

  • Beth Massi - Sharing the goodness

    LightSwitch Community & Content Rollup–April 2012

    • 4 Comments

    Last Fall I started posting a rollup of interesting community happenings, content, samples and extensions popping up around Visual Studio LightSwitch. If you missed those rollups you can check them all out here: LightSwitch Community & Content Rollups

    April continued to roll out content around Visual Studio 11 Beta since it was released back on February 29th. If you haven’t done so already, I encourage you to give it a spin by downloading Visual Studio 11 Beta. Also make sure to check out these LightSwitch Beta resources and community sites:

    Have an idea?: LightSwitch UserVoice site 
    Need to report a bug?: LightSwitch Connect site
    Have a question?: LightSwitch Beta Forum
    Need to learn what’s new?: LightSwitch Developer Center

    LightSwitch in Visual Studio Beta Resources

    If you haven’t noticed the LightSwitch team has been releasing a lot of good content around the next version of LightSwitch in Visual Studio 11. Check out the LightSwitch Developer Center for a list of key Beta resources for you to explore. Here’s some of our more popular content:

    www.odata.orgCompanion Client Examples

    One of the biggest features with LightSwitch in Visual Studio 11 is the Open Data Protocol (OData) support. Not only can you consume OData services in LightSwitch, the middle-tier services are also now exposed as OData service endpoints reachable by other clients. In April, the community posted many more examples of alternative clients that you can build against your LightSwitch services. I’d like to particularly thank Michael Washington who runs the www.LightSwitchHelpWebsite.com who has been on fire this month posting most of these. Awesome!

    Excel PowerPivot: Creating and Consuming LightSwitch OData Services
    Windows 8: Using LightSwitch OData Services in a Windows 8 Metro Style Application
    Windows Phone: Consume a LightSwitch OData Service from a Windows Phone application
    HTML/JavaScript: A Full CRUD DataJs and KnockoutJs LightSwitch Example Using Only An .Html Page
    JQuery Mobile: A Full CRUD LightSwitch JQuery Mobile Application
    Android: Communicating With LightSwitch Using Android App Inventor
    Unity 3D: Using Visual Studio LightSwitch To Orchestrate A Unity 3D Game

    MSDN Magazine Column: Leading LightSwitchApril 2012

    One of our other LightSwitch community rock stars started a new LightSwitch column in MSDN Magazine in March. Jan van der Haegen continues his journey into the depths of LightSwitch with his second article in the April issue:

    Leading LightSwitch: The LightSwitch MVVM Model
    In this month’s Leading LightSwitch column, Jan explains MV3, a variation of the MVVM application architecture used for LightSwitch apps that is even more powerful than the original application architecture.   

    New Visual Studio LightSwitch Books Released

    Paul Ferrill released his new book in the beginning of April on building SharePoint Apps with LightSwitch. It sounds like this is a short book for the LightSwitch beginner looking to connect to SharePoint data.

    Also released was Pro Visual Studio LightSwitch 2011 Development by Tim Leung and Yann Duran. You may have seen these guys helping answer questions in the LightSwitch forums and they really know their stuff.

    More LightSwitch books to check out:

    imageXpert 360’s Dynamics CRM Online Adapter LightSwitch Extension Released

    In April Xpert 360 released a FREE LightSwitch extension that allows you to connect to Dynamics CRM. Looks like they are also working on one for connecting to Salesforce. Check out these resources to get started:

    Other Commercial LightSwitch Extensions from our Partners

    Many of our Visual Studio partners who build LightSwitch extensions injected into the #LightSwitch twitter feed this month to remind us of these great products, some with new features recently added.

    MSDN Webcast: What's New with LightSwitch in Visual Studio 11MSDN Webcast: What's New with LightSwitch in Visual Studio 11 (Level 200)

    Join me Friday May 11th 1:00 PM PST for What's New with LightSwitch in Visual Studio 11.

    Microsoft Visual Studio LightSwitch is the simplest way to build business applications and data services for the desktop and the cloud. LightSwitch contains several new features and enhanced capabilities in Visual Studio 11. In this demonstration-heavy webcast, we walk through the major new features, such as creating and consuming OData services, new controls and formatting, new features with the security system and deployment, and much more.

    Register here.

    Notable Content this Month

    Extensions (see all 85 of them here!):

    In addition to the above, we had a few more extensions from the community released this month.

    Samples (see all 80 of them here):

    Team Articles:

    Lots more articles from the team this month on all the new features in LightSwitch in Visual Studio 11.

    We also had a couple top requested How To posts that apply to all versions of LightSwitch:

    Community Content:

    LightSwitch Team Community Sites

    Become a fan of Visual Studio LightSwitch on Facebook. Have fun and interact with us on our wall. Check out the cool stories and resources. Here are some other places you can find the LightSwitch team:
    LightSwitch MSDN Forums
    LightSwitch Developer Center
    LightSwitch Team Blog
    LightSwitch on Twitter (@VSLightSwitch, #VisualStudio #LightSwitch)

    Enjoy!

  • Beth Massi - Sharing the goodness

    Using the Save and Query Pipeline to Flag and Filter Data with LightSwitch in Visual Studio 11

    • 3 Comments

    Note: This information applies to LightSwitch in Visual Studio 11 (LightSwitch V2)

    In business applications sometimes we need to flag records with additional attributes in response to business rules and then consistently filter those flagged records in some way or another throughout the application. For instance we may have critical or historical data that must always be stored in our databases and never deleted. I used to work in the health care industry and it was important to keep historical patient data. Therefore we would not allow a user to delete a patient’s hospital visit information from the system. Over time, however this causes our data sets to get very large and distracts users from finding the relevant information they need. Hence, we need a way to flag these records and then filter this data out of our result sets across the entire application.

    Last year I wrote an article based on LightSwitch in Visual Studio 2010 (LightSwitch V1) showing how you can use the Save & Query pipeline to archive and filter records instead – effectively marking them deleted but not actually deleting them from the system: Using the Save and Query Pipeline to “Archive” Deleted Records. With LightSwitch in Visual Studio 11 (LightSwitch V2) the filtering mechanism has been improved using the new entity set filters.

    In this post I’ll show you how to use the new entity set filter methods to apply global filtering of records. But first, let’s recap how we can flag these records using the save pipeline.

    Tapping into the Save Pipeline

    The save pipeline runs in the middle tier anytime an entity is being updated, inserted or deleted. This is where you can write business logic that runs as changes are processed on the middle tier and saved to data storage. Let’s take an example where we don’t want to ever physically delete customers from the system. Here’s our data model for this example. Notice that I’ve created a required field called “IsDeleted” on Customer that is the type Boolean. I’ve unchecked “Display by Default” in the properties window so that the field isn’t visible on any screens.

    image_thumb[11]

    In order to mark the IsDeleted field programmatically when a user attempts to delete a customer, just select the Customer entity in the data designer and drop down the “Write Code” button and select Customers_Deleting method.

    image_thumb[16]

    Here are the 2 lines of code we need to write:

    Private Sub Customers_Deleting(entity As Customer)
        'First discard the changes, in this case this reverts the deletion
        entity.Details.DiscardChanges()
        'Next, change the IsDeleted flag to "True"
        entity.IsDeleted = True
    End Sub

    Notice that first we must call DiscardChanges in order to revert the entity back to it’s unchanged state. Then we simply set the IsDeleted field to True which puts the entity into a change state. So the appropriate save pipeline methods (Customers_Updating & Customers_Updated) will now run automatically. You can check the state of an entity by using the entity.Details.EntityState property.

    This save pipeline code remains unchanged from LightSwitch V1. However in LightSwitch V2, we need to filter the data differently. Let’s see how.

    Using Entity Set Filters

    Now that we’re successfully marking deleted customers, the next thing to do is filter them out of our queries so that they don’t display to the user on any of the screens they work with. In LightSwitch V2 a new query interception method, EntitySet_Filter, has been added which allows you to specify a filter that is applied whenever an entity set is referenced. (See the recent article on the LightSwitch Team Blog by Michael Simons: Filtering Data using Entity Set Filters for details on this new feature.)

    So instead of using the Customers_All_PreprocessQuery like we had to in LightSwitch V1 as demonstrated here, we can apply the IsDeleted filter in the Customers_Filter method. Using the entity set Filter method is preferred over the All_PreprocessQuery method because the Filter method will execute any time the entity set is referenced, even when the entity has relationships and is not the direct target of a query. This enables true row level filtering across the application.

    So for this example, select the Customer entity in the data designer and drop down the “Write Code” button and select Customers_Filter method.

    image_thumb[27]

    Here we need to write a filter predicate as a lambda expression.

    Private Sub Customers_Filter(ByRef filter As Expressions.Expression(Of Func(Of Customer, Boolean)))
        filter = Function(c) c.IsDeleted = False
    End Sub

    These expressions can seem a little tricky at first but they just take some practice. You can think if these as in-line functions. This one takes a customer parameter and then you return a Boolean expression used to filter the records. You can filter on anything you want, even filter based on security permissions like Michael showed in his blog post. (For more information on lambda expressions see: Lambda Expressions (Visual Basic) and Lambda Expressions (C#).)

    Now that we’ve got our Save and Query pipelines set up to handle the “deleted” Customers, we can run the application. When a user deletes a Customer, the behavior is exactly the same as if the Customer was physically deleted from the database.

    image_thumb[24]

    And if we peek inside the actual Customer table in the database you can see them flagged correctly.

    image_thumb[26]

    Also, now when we write a query where Customers is not the direct target, the row filtering will still apply. For instance, we have a parent table called Regions. If we wanted to create a query “Regions Over 10 Customers” we would write our query like so:

    Private Sub RegionsOver10Customers_PreprocessQuery(ByRef query As IQueryable(Of Region))
        query = From r In query
                Where r.Customers.Count > 10
    End Sub
    

    In LightSwitch V1 the additional filter in the Customers_All_PreProcessQuery would not be called when we executed this query. It would include the count of the Customers flagged IsDeleted – which isn’t what we want. You would have to include that filter in this query as well. So if you had a lot of queries in your application that indirectly reference the Customer, then this could get difficult to maintain. In LightSwitch V2 the Customers_Filter method is always called anytime the Customer entity set is referenced, regardless if it is the direct target of a query or not, so this query would always return the expected results.

    Because we want to encourage using this approach to achieve true row level filtering, for new LightSwitch V2 projects there is no entry point for EntitySet_All_PreProcessQuery methods anymore. With upgraded projects from V1 they will still run the same as before and you will see them as modeled queries in the Solution Explorer. So there are no breaking changes to your application, but you will want to strongly consider using the entity set Filter methods instead.

    Wrap Up

    I hope this post has demonstrated how powerful and simple LightSwitch can be when working with data in the save and query pipelines. We’ve beefed up the global filtering technique so that it can handle scenarios like I showed above as well as row level security scenarios Michael demonstrated. If you were using the _All_PreProcessQuery methods, then they will continue to run when you upgrade to Visual Studio 11. However you should be using the entity set Filter methods instead for applying global filters that will run no matter how the entities are accessed.

    Enjoy!

  • Beth Massi - Sharing the goodness

    Working with SQL Server LocalDB in LightSwitch Projects in Visual Studio 11

    • 0 Comments

    Note: This article applies to LightSwitch in Visual Studio 11 Beta (LightSwitch V2)

    In Visual Studio LightSwitch, when you design your data model through the Data Designer you are either creating new tables or attaching to external data sources. When you create new tables, LightSwitch automatically creates them in the internal database, also known as the Intrinsic database or ApplicationData. In the first version of LightSwitch in Visual Studio 2010 we used SQL 2008 Express for the internal database development. Now with LightSwitch in Visual Studio 11 we are using SQL Server LocalDB. (Note: LocalDB is only used during development time. When you deploy your app you can choose to deploy to any version of SQL Server.)

    LocalDB is the new version of SQL Server Express that has a much lower memory footprint and is targeted for developers. It is installed automatically when you install Visual Studio 11. When developing LightSwitch projects, LocalDB is used in place of the SQL Server Express user instance feature (which is now deprecated). In this post I’ll show you some tricks on working with LocalDB databases in LightSwitch projects.

    SQL Server Object Explorer in Visual Studio 11

    In Visual Studio 2010 you used the Server Explorer to access your SQL Express databases. Server Explorer still exists in Visual Studio 11, but there is a new window called the SQL Server Object Explorer that you can use to work with your LocalDB. SQL Server Object Explorer provides a view of your database objects that’s similar to SQL Server Management Studio.

    image

    To connect to your LocalDB click the “Add SQL Server” button on the toolbar to bring up the connection dialog. The server name is: (localdb)\v11.0

    image

    Once you debug (F5) your LightSwitch project the first time, the internal ApplicationData database will show up under the Databases node. LocalDB will auto attach to the database the first time it is accessed.

    IMPORTANT NOTE: If you are developing only new tables in a LightSwitch project, you should never need to use this window because you model your data through the LightSwitch Data Designer. In fact, if you modify the schema of the internal database outside of the Data Designer, then the LightSwitch model will get out of sync and you will get errors running the application. I REPEAT, DO NOT MODIFY YOUR INTERNAL DATABASE OUTSIDE THE LIGHTSWITCH DATA DESIGNER.

    However, the SQL Server Object Explorer is very handy for working with external databases that you want to bring into your LightSwitch application. During development it is common to work with a local copy of your external databases and SQL Server Object Explorer is a handy way to manage these. One of my favorite features is the schema compare. For more information on the capabilities of the SQL Server Object Explorer please see: What's New for Data Application Development in Visual Studio 11 Beta

    Keep in mind that external databases are just that -- they are external to LightSwitch. So you must manage their schema and deployment completely outside of the LightSwitch development environment. For more information on connecting to external data sources see - How to: Connect to Data

    Attaching Databases to LocalDB using SQL Server Object Explorer

    With a lot of samples out there in all sorts of versions of SQL Server this feature comes in handy: you can attach to a database file (.MDF) and it will automatically upgrade your database to the current version and attach it to your LocalDB instance. This makes it easier to connect to and develop against external databases in LightSwitch. (Note that if you upgrade the database, it will no longer be compatible with earlier versions of SQL Server.)

    Let’s take an example.

    The AdventureWorks family of sample databases are used in many modern database examples from Microsoft today. They show off features of the latest versions of SQL Server and are maintained on CodePlex. You can download them here. There is a simpler database included here called AdventureWorksLT (AdventureWorks “Light”) that is better for developers learning data since it has a simpler schema.

    To attach the AdventureworksLT database:

    1. After you download the AdventureWorksLT2012 Data File, create a folder for your local databases (like C:\Data) and copy the AdventureWorksLT2012_Data.MDF file to that location.
    2. In the SQL Server Object Explorer right click on your LocalDB instance and choose “New Query…” to open a new SQL Query editor.

      image
    3. In the query editor type the following commands:
      USE [master] 
      GO
      CREATE DATABASE
      [AdventureWorksLT] ON
      ( FILENAME = N'C:\Data\AdventureWorksLT2012_Data.mdf' )
      FOR ATTACH ;
      GO
    4. Click the execute button on the toolbar (Ctrl+Shift+E) to execute the commands. You will see a message that there was a file activation failure and that a new log file (.LDF) was generated. You may also see some upgrade messages depending on the version of your LocalDB. All normal.
    5. Refresh the Databases node in the SQL Server Object Explorer and you will now see the AdventureWorksLT database attached. You will also see any LightSwitch internal databases that you are working with.

      image

    Using the SQL query editor you can also execute other SQL scripts to create and work with your databases. However, you can also use the Server Explorer to attach to a database using a wizard which was also available in Visual Studio 2010. For more information see - How to: Connect to a File-based Database.

    Using a LocalDB Database as an External Data Source in LightSwitch

    Once you have your databases attached, it’s easy to add them as an external data source.

    1. In the Solution Explorer for your LightSwitch project right-click on the Data Sources node and select “Add Data Source…”

      image
    2. This opens the Attach Data Source wizard where you can select from a variety of external data source types. Select Database and click Next.

      image
    3. In the Connection Properties dialog set the server name to (localdb)\v11.0 and then select the database you want. Note that here you can also attach to a database file as well. This will have the same effect as the attach steps above.

      image
    4. From there you can select the tables and views you want to bring into your LightSwitch application.

    Syncing Schema Changes for External Data Sources

    The LightSwitch Data Designer will not allow you to change the underlying schema of an external data source. Instead, you can use SQL Server Object Explorer (or any other favorite database tool) to make changes. When you modify the schema of your external data source you need to bring those changes back into LightSwitch (remember do not modify your internal database this way).

    To sync the changes, from the Solution Explorer right-click on the external data source and select “Update Datasource”.

    image

    Choose all the tables you are using in your LightSwitch application and then click Finish and the changes will be reflected back in the Data Designer.

    image

    Wrap Up

    SQL Server LocalDB uses less memory than previous versions of SQL Server Express and still provides the automatic attach of databases in order to speed up development of your data-based projects. For external databases, the SQL Server Object Explorer is a welcome addition to Visual Studio and has a similar experience to SQL Server Management Studio. However, remember that you should only make changes to your internal database (ApplicationData) via the LightSwitch Data Designer.

    When working with the internal database in LightSwitch, LocalDB is only used during development time. When you deploy your app that uses the internal database, you can choose to deploy to any version of SQL Server, including SQL Azure. Additionally, if you are using external data sources in your LightSwitch application, you are asked for the connection strings of the production databases upon deploy. For more information on deploying LightSwitch applications see Deploying LightSwitch Applications in the MSDN Library and my post LightSwitch IIS Deployment Enhancements in Visual Studio 11.

    Enjoy!

  • Beth Massi - Sharing the goodness

    LightSwitch Companion Client Examples using OData

    • 2 Comments

    One of the biggest features with LightSwitch in Visual Studio 11 is the Open Data Protocol (OData) support. OData is a standard Web protocol for exchanging data on the web which provides easy, secure access into data stores. Not only can you consume OData services in LightSwitch, the middle-tier services are also now exposed as OData service endpoints reachable by other clients. For instance, I showed how power users can easily perform analytics and reporting on your LightSwitch data using Excel PowerPivot.

    However, this also allows you to create a different UI for your LightSwitch application so that you can expose it to other platforms that aren’t supported out of the box. Furthermore, all your business rules and access control logic is preserved when calling these services. Your data models, business logic, and access control logic is where you spend the bulk of your time when building a LightSwitch application and all this work is preserved.

    The team has been focusing on the architecture of these services in LightSwitch as well as how to use them. Particularly:

    The community has taken these examples even further. I’d like to call out Michael Washington who runs the www.LightSwitchHelpWebsite.com where he’s been on fire recently posting examples of companion clients. Check these out!

    Jan van der Haegen also wrote a great MSDN Article last month using a WIndows Phone 7 client:

    Consume a LightSwitch OData Service from a Windows Phone application

    Opening up the middle-tier services was a major goal for LightSwitch and you can see why – it allows you to exchange data easily with other systems and clients over the Web. I’m excited to see what the community comes up with next!

    Enjoy!

  • Beth Massi - Sharing the goodness

    MSDN Webcast: What's New with LightSwitch in Visual Studio 11

    • 0 Comments

    Join me next month as I take you on a tour of the new features of LightSwitch in Visual Studio 11.

    What's New with LightSwitch in Visual Studio 11

    Friday, May 11, 2012 1:00 PM Pacific Time

    Microsoft Visual Studio LightSwitch is the simplest way to build business applications and data services for the desktop and the cloud. LightSwitch contains several new features and enhanced capabilities in Visual Studio 11. In this demonstration-heavy webcast, we walk through the major new features, such as creating and consuming OData services, new controls and formatting, new features with the security system and deployment, and much more.

    Register here.

    Hope you can join this webcast, it should be a lot of fun! In the meantime, enjoy some of these LightSwitch in Visual Studio 11 resources.

    Articles:

    Featured Extensions:

    Featured Samples:

    Community:

    You can find all of this plus more on the LightSwitch Developer Center

    That should keep you busy until the webcast next month Winking smile

    Enjoy!

Page 1 of 57 (568 items) 12345»