Tuesday, October 14, 2014

Changed my blog title and description

You may have noticed that I've changed my blog title and description, why?

Over the past couple of years my career has been in sort of a transition, nothing new, many of us in this industry have been or will be going through this.

My responsibilities at work have changed, I'm not only developing data centric Microsoft Visual Studio LightSwitch web apps for my employers internal departments  but more and more I'm venturing into SharePoint and Mobile apps as well.  In this blog I plan to share what I've learned where permissible and it won't always be about LightSwitch so I changed the title and description to reflect that.

- Keith

Thursday, August 7, 2014

Working with iBeacon's in Swift

Disclaimer: iBeacon is a Trademark owned by Apple.
I am not affiliated with any company mentioned in this tutorial.

I'm still learning Swift so what I'm about to show may not be the best way to do this but it's a start.

Why iBeacons?
Glad you asked, iBeacon technology can be used for proximity checks e.g. Alert your customer when they are in range of your beacon to special sales, let them know their order is ready, tailor a discount to your customer if they happen to be walking down an aisle and pass by an item that is associated with your iBeacon. Monitor heart rates with the wearable iBeacons. Monitor Entry and Exits etc... There are numerous reasons for this technology.  And well it's pretty darn cool!

I'm going to make some assumptions here:

  1. You know your way around Xcode
  2. You are using the latest Beta version of Xcode
  3. You have at least one Beacon hardware that conforms to Apple iBeacon specifications or you have an iOS device you can use as an iBeacon - you can find out if your device supports iBeacon  here https://developer.apple.com/ibeacon/Getting-Started-with-iBeacon.pdf
  4. You have a valid Apple Developer account.

For this tutorial I'm using a TiSensorTag from Texas Instruments

but there are several other manufacturers out there, Estimote, Footworks, BlueCat, Gimbal and many others. Each have their own strengths and weaknesses but I'm not going to talk about them here, I'll leave that up to you. This is just a basic tutorial about interacting with one these devices using Apple's new programming language Swift.

* Before we get started, for the majority of this tutorial iOS 7.1 will work just fine but for Region Monitoring,  your iOS device must be running at least iOS 8.0 which is still in beta so I will go over Region Monitoring at a later date. Hopefully this tutorial will at least get you started!

Create a new Single View Application
I named mine CoffeeTime_iBeacon.
Language: Swift
Devices: Universal
Make sure the Use Core Data box is checked - I like to have Xcode implement the necessary code for me for this just in case I decide to use it later on,  even though we won't talk about Core Data in this tutorial I plan to follow up on this in later tutorials using this same application.

Under Deployment Info:
Make sure the Deployment Target is set to at least 7.1


  • CoreFoundation
  • CoreLocation
  • CoreBluetooth
  • CoreData
  • MapKit
  • UIKit
  • QuartzCore
  • Social - (Optional)
I added the framework Social just for the fun of it, I may want to send some test results to Facebook or Twitter etc... , why because I think it might be fun and almost everything is Social these days so who am I to go against the trend :)

Layout the UI:

Open Main.storyboard even though I don't directly use the button in this tutorial, at least it's there if you want to scan for beacons manually. Drag a button onto the view controller, replace the default text with something like Scan For Beacons 

Now let's create an Outlet for the button, control drag from the button to the top of viewController.swift above the viewDidLoad function. Name this anything you like, I chose you guessed it "scanForBeacons". Control drag one more time but this time create an action, I chose to go with the same name "scanForBeacons"

Drag a Text View and place it just below the button. I deleted all the placeholder text, and expanded it to both edges, gave it a height of 472 and changed it's background to a light blue, again that's just me.

Once again Control drag from the text view to the viewController.swift just below the outlet for scanForBeacons, I created an outlet called "sensorData"

Onto the Code:

Open ViewController.swift, before we code anything there are several terms you need to be aware of:

iBeacon - this is a term owned by Apple. The actual devices are called beacons, the term iBeacon means that the device conforms to Apple's iBeacon standards. To use the term iBeacon you have to have a license from Apple. I will refer to the devices as Peripheral's going forward.

Peripheral -  this is the Beacon itself
Central - this is our main program 

Now we need to setup our ViewController class as a delegate to not only the peripheral and central but also the Location Manager.

You could and probably should put these into their own files but for this tutorial, I'm going to keep everything in the ViewController class.

First import CoreLocation, CoreBluetooth and Foundation and change our class declaration to 

class ViewController: UIViewController,CLLocationManagerDelegate,CBCentralManagerDelegate,CBPeripheralManagerDelegate,CBPeripheralDelegate {

You will get two errors that Type "ViewController" does not conform to protocol 'CBPeripheralManagerDelegate' and 'CBCentralManagerDelegate', that's because we have not implemented the protocol's DidUpdateState required methods just yet but we will fix this shortly.

Let's create our Managers:

    var cManager = CBCentralManager()
    var peripheralManager = CBPeripheralManager()
    var locManager = CLLocationManager()
Create our beaconRegion and discoveredPeripheral vars, this will be explained later.
   var beaconRegion = CLBeaconRegion()
   var discoveredPeripheral:CBPeripheral?

in viewDidLoad(), below super.viewDidLoad

// Init the CentralManager, we set the queue to nil so that the manager dispatches the central role events on the main queue - refer to the CBCentralManager Class definition for further explanation
        cManager = CBCentralManager(delegate: self, queue: nil)

// Init the PeripheralManager
        peripheralManager = CBPeripheralManager(delegate: self, queue: nil)
// Init the RegionManager
        locManager.desiredAccuracy = kCLLocationAccuracyNearestTenMeters
        locManager.delegate = self

Please Note: There are several other options for the kCLLocationAccuracy. 


But for this post I want it to detect the beacon at the closest range (kCLLocationAccuracyNearestTenMeters)

Ok time to implement our first required delegate method this will be for our CentralManager delegate.

Required Method #1.
We need to detect that our Central device can support Bluetooth Low Energy and that it is available.
You cannot issue commands to the beacon until after the .PowerOn state is achieved, so once the Central Manager is in the poweredOn state we can call our scanForBeacons, we will implement our scanForBeacons method shortly. 

func centralManagerDidUpdateState(central: CBCentralManager!) {
        switch cManager.state {
        case .PoweredOff:
            println("CoreBluetooth BLE hardware is powered off")
            self.sensorData.text = "CoreBluetooth BLE hardware is powered off\n"
        case .PoweredOn:
            println("CoreBluetooth BLE hardware is powered on and ready")
            self.sensorData.text = "CoreBluetooth BLE hardware is powered on and ready\n"
            // We can now call scanForBeacons
        case .Resetting:
            println("CoreBluetooth BLE hardware is resetting")
            self.sensorData.text = "CoreBluetooth BLE hardware is resetting\n"
        case .Unauthorized:
            println("CoreBluetooth BLE state is unauthorized")
            self.sensorData.text = "CoreBluetooth BLE state is unauthorized\n"

        case .Unknown:
            println("CoreBluetooth BLE state is unknown")
            self.sensorData.text = "CoreBluetooth BLE state is unknown\n"
        case .Unsupported:
            println("CoreBluetooth BLE hardware is unsupported on this platform")
            self.sensorData.text = "CoreBluetooth BLE hardware is unsupported on this platform\n"

Now implement the scanForBeacons method

@IBAction func scanForBeacons(sender: AnyObject) {
        cManager.scanForPeripheralsWithServices(nil, options: nil)
        sensorData.text = "\nNow Scanning for PERIPHERALS!\n"

Now that our Central device is in the poweredOn state and we've called scanForBeacons to kickoff the Peripheral discovery routine we need to know when the Peripheral was discovered.

Create an optional var at the top the class and implement the 

var discoveredPeripheral:CBPeripheral?

We need to implement the centralManager didDiscoverPeripheral method.

func centralManager(central: CBCentralManager!,
        didDiscoverPeripheral peripheral: CBPeripheral!,
        advertisementData: [NSObject : AnyObject]!,
        RSSI: NSNumber!) {
            central.connectPeripheral(peripheral, options: nil)
             // We have to set the discoveredPeripheral var we declared earlier to reference the peripheral, otherwise we won't be able to interact with it in didConnectPeripheral. And you will get state = connecting> is being dealloc'ed while pending connection error.

            self.discoveredPeripheral = peripheral
            var curDevice = UIDevice.currentDevice()
            //iPad or iPhone
            println("VENDOR ID: \(curDevice.identifierForVendor) BATTERY LEVEL: \(curDevice.batteryLevel)\n\n")
            println("DEVICE DESCRIPTION: \(curDevice.description) MODEL: \(curDevice.model)\n\n")
            // Hardware beacon
            println("PERIPHERAL NAME: \(peripheral.name)\n AdvertisementData: \(advertisementData)\n RSSI: \(RSSI)\n")
            println("UUID DESCRIPTION: \(peripheral.identifier.UUIDString)\n")
            println("IDENTIFIER: \(peripheral.identifier)\n")
            sensorData.text = sensorData.text + "FOUND PERIPHERALS: \(peripheral) AdvertisementData: \(advertisementData) RSSI: \(RSSI)\n"
            // stop scanning, saves the battery

This delegate method is called when the CentralManager establishes a successful connection to the peripheral

func centralManager(central: CBCentralManager!, didConnectPeripheral peripheral: CBPeripheral!) {
        peripheral.delegate = self
        println("Connected to peripheral")

This delegate method is called when the CentralManager Fails to establish a connection to the peripheral

func centralManager(central: CBCentralManager!, didFailToConnectPeripheral peripheral: CBPeripheral!, error: NSError!) {
        sensorData.text = "FAILED TO CONNECT \(error)"

Required Method #2.
Ok were almost done, we still have one error to correct Type "ViewController" does not conform to protocol 'CBPeripheralManagerDelegate'. To fix this we implement the peripheralManagerDidUpdateState method.

Just like the centralManager we cannot interact with the peripheral until it is in the poweredOn state.

func peripheralManagerDidUpdateState(peripheral: CBPeripheralManager!) {
        switch peripheralManager.state {
        case .PoweredOff:
            sensorData.text = "Peripheral - CoreBluetooth BLE hardware is powered off"
        case .PoweredOn:
            sensorData.text = "Peripheral - CoreBluetooth BLE hardware is powered on and ready"
        case .Resetting:
            sensorData.text = "Peripheral - CoreBluetooth BLE hardware is resetting"
        case .Unauthorized:
            sensorData.text = "Peripheral - CoreBluetooth BLE state is unauthorized"
        case .Unknown:
            sensorData.text = "Peripheral - CoreBluetooth BLE state is unknown"
        case .Unsupported:
            sensorData.text = "Peripheral - CoreBluetooth BLE hardware is unsupported on this platform"

Now we can discover what services the peripheral offers

// Invoked when you discover the peripheral's available services.
    func peripheral(peripheral: CBPeripheral!, didDiscoverServices error: NSError!) {
        println("ERROR: \(error)")
        var svc:CBService
        for svc in peripheral.services {
            println("Service \(svc)\n")
            sensorData.text = sensorData.text + "\(svc)\n"
            println("Discovering Characteristics for Service : \(svc)")
            peripheral.discoverCharacteristics(nil, forService: svc as CBService)

And the services characteristics

// Invoked when you discover the characteristics of a specified service.
    func peripheral(peripheral: CBPeripheral!, didDiscoverCharacteristicsForService service: CBService!, error: NSError!)
        sensorData.text = devices.text + "\n\nCHARACTERISTICS\n\n"
        var myCharacteristic = CBCharacteristic()
                for myCharacteristic in service.characteristics {
            sensorData.text = devices.text + "\nCharacteristic: \(myCharacteristic)\n"
            println("didDiscoverCharacteristicsForService - Service: \(service) Characteristic: \(myCharacteristic)\n\n")
            peripheral.readValueForCharacteristic(myCharacteristic as CBCharacteristic)

Turn on your beacon and click Run
If there are no errors you should detect and connect to the beacon and be presented with quite a bit of data like,

 AdvertisementData: [kCBAdvDataLocalName: SensorTag, kCBAdvDataTxPowerLevel: 0, kCBAdvDataChannel: 37, kCBAdvDataIsConnectable: 1]
 RSSI: -46



Connected to peripheral
ERROR: nil
Service <CBService: 0x176a5dd0 Peripheral = <CBPeripheral: 0x17560840 identifier = , Name = "TI BLE Sensor Tag", state = connected>, Primary = YES, UUID = Device Information>

Discovering Characteristics for Service : <CBService: 0x176a5dd0 Peripheral = <CBPeripheral: 0x17560840 identifier = , Name = "TI BLE Sensor Tag", state = connected>, Primary = YES, UUID = Device Information>
Service <CBService: 0x1769d430 Peripheral = <CBPeripheral: 0x17560840 identifier = , Name = "TI BLE Sensor Tag", state = connected>, Primary = YES, UUID = >

didDiscoverCharacteristicsForService - Service: <CBService: 0x176a5dd0 Peripheral = <CBPeripheral: 0x17560840 identifier = , Name = "TI BLE Sensor Tag", state = connected>, Primary = YES, UUID = Device Information> Characteristic: <CBCharacteristic: 0x1769ba70 UUID = Model Number String, Value = (null), Properties = 0x2, Notifying = NO, Broadcasting = NO>

Please note: I removed the UUID and identifier information from my examples for security purposes.

Let's also monitor if the characteristics have been updated, more on this later.

// Invoked if the characteristic is updated
    func peripheral(peripheral: CBPeripheral!, didUpdateNotificationStateForCharacteristic characteristic: CBCharacteristic!, error: NSError!) {
        println("\nCharacteristic \(characteristic.description) isNotifying: \(characteristic.isNotifying)\n")
        if characteristic.isNotifying == true {
            peripheral.readValueForCharacteristic(characteristic as CBCharacteristic)

Hopefully you found this helpful.

In my Next tutorial I'll go over setting up Region Monitoring: (this requires iOS 8.0 or greater)
and a few other things we can do with this.

Wednesday, May 7, 2014


I took the past year off from posting any tutorials, explored some new technologies, well new to me anyways. But I think I'm going to start posting again.

I'll be using this current site for my tutorials, rants and raves going forward. I thought about switching everything over to my SharePoint site but let's just say I found the blogging tools on SharePoint to be a little more challenging than I care for, so I will stick with this site.

I do have one tutorial on the SharePoint site if your interested:

Friday, March 15, 2013

Flex your ODATA

Silly humor moment,
 When I first heard the term ODATA, I thought they were talking about an intimate moment between Lt Data and the Star Trek Enterprise Computer, I said WHOA! there partner, TMI dude. LOL!

But ODATA stands for Open Data Protocol ODATA is a web protocol used for querying and updating your data that may exist in what's called silos in your apps. With ODATA you can utilize and enhance other technologies such as HTTP, ATOM Publishing Protocol, XML and JSON. You can read more about ODATA at ODATA.org  or view Scott Hanselman's talk

OData Basics - At the AZGroups "Day of .NET" with ScottGu

This post is about developing an Apache Flex mobile application that talks with a SQL Server Express database via ODATA. We're going to build a simple Project Manager application.

What! No LightSwitch!!! But But I thought you were a LightSwitch developer,  hold your tater tots there partner. You can still use LightSwitch to create your ODATA service and manage your data.
But for this post I'm going to show you another way by using Visual Studio Express 2012 for Web.

Why am I using Flex instead of LightSwitch, because I wanted to see if it could be done.

I've been developing Flex/Flash applications since Flash Builder, formerly known as Flex Builder version 1.1, so I have all the necessary components on my MacBook. And I just learned I could build an ODATA service using Visual Studio 2012 Express which I will pass on to you now.

There are other ways to connect your Flex application to SQL Server, such as WebORB for .NET from http://www.themidnightcoders.com/ but that is beyond the scope of this tutorial.

NOTE: If you have Visual Studio 2012 Pro you already have WCF Data Services installed.

My Setup on my wi-fi home network, I have a MacBook Pro OSX 10.7.5 and an ACER Aspire Ultrabook Windows 7:

Hosted on my ACER Ultrabook
Hosted on my MacBook Pro
Useful but not required - if you are on Windows you can download the open source application LinqPad to help with your ODATA services.

What this tutorial is about:
A simple Project Management application that we can run on our iPhone or iPad. If you have an Android phone this should work also, I only have an iPhone though.

I'll try and keep this simple but make the project easy to expand upon. We will use straight out of the box components and technologies from Flex and Actionscript. We won't use any special frameworks like the excellent Inversion of Control Frameworks such as RobotLegs, Swiz, Mate, Parsley or the Event helper library AS3Signals. Maybe in another post but for now I'll keep this simple, I only provided the links for you to browse at your convenience if wish to learn more.

Setup our development environment:
 Download and install the items mentioned above if you don't already have them installed. I won't go over how to install all the above in this tutorial, each of the resources above have good documentation covering this.

Ok assuming you have everything installed and configured:
Let's make sure SQL Server can recieve requests from other machines on our home network, this should be the same setup for your company's internal network, consult your network admin about this.

WARNING! If you are following this tutorial from your Corporate Network, please check with your Network Administrator before making any of the following changes. You probably won't have the necessary privileges to complete this but make sure you know what you are doing first if you do. Your Network Administrator may have a better solution.

On your Windows machine


Click the Windows Start button then right click Computer, select Manage.
  1. Expand the SQL Server Configuration Manager located under Services and Applications.
  2. Click Protocols for SQLEXPRESS.
  3. Enable Shared Memory, Named Pipes, and TCP/IP
  4. Double click TCP/IP then click the IP Addresses tab
  5. Locate IPALL, if there is an entry for TCP Dynamic ports delete this, leave it blank. You need a hard coded port to be able to setup the Windows Firewall Inbound Rule.
  6. For TCP Port set it to 1433, SQL Server usually listens on port 1433. You will get an alert that no changes will take effect until SQL Server is restarted
  7. Click OK to close this dialog.
  8. Click SQL Server Services, click SQL Server then click the Restart button in the menu bar or right click and select Restart
  9. Make sure SQL Server Browser is running.
  10. You can now close Computer Management
Setup an Inbound Rule in Windows Firewall
Click the Windows Start button and select Control panel, then open Windows Firewall
  1. Click Advanced Settings
  2. Click Inbound Rules
  3. Under Actions select New Rule
  4. In the Name field I chose to give this rule the name SQL SERVER
  5. Make sure the Enabled box is checked
  6. Make sure the Allow the connection box is checked
  7. Click the Protocols and Ports tab
  8. For Protocol Type select TCP
  9. Local Port - select Specific Ports and type in 1433 or whatever you typed for IPALL
  10. Remote Ports -  select Specific Ports, and type in 1433 or whatever you typed for IPALL
  11. Under the Advanced tab, for Profiles I deselected Domain and Public. I only have Private checked.
  12. Click OK to close this dialog
  13. Click New Rule again
  14. In the Name field I chose to give this rule the name SQL SERVER BROWSER
  15. Make sure the Enabled box is checked
  16. Make sure the Allow the connection box is checked  
  17. Click the Protocols and Ports tab
  18. For Protocol Type select UDP
  19. Local Port - select Specific Ports and type in 1434
  20. Remote Ports -  select Specific Ports, and type in 1434
  21.  Under the Advanced tab, for Profiles I deselected Domain and Public. I only have Private checked. 
  22. Click OK to close this dialog
  23. Close the Windows Firewall with Advanced Security screen
  24. Close Control Panel
Now let's setup our Database tables

We will use three tables Projects, Clients, Technologies and two look up tables Project_Technologies and Client_Projects. 

NOTE: You could setup your database from within Visual Studio using the Entity Data Model Designer but that creates the database in the app data and this is not covered by our Firewall Rule.

  1. Open SQL Server Management Studio and login to your server
  2. Right click the Databases folder icon and select New Database
  3. For Database name I gave mine the name of MyProjects
  4. Click OK to create the DB
  5. Expand the db and right click the Tables folder icon and select New Table
  6. Create the table with the following structure:
    • projid (PK, int, not null) Identity set to YES
    • project_name (varchar(50), not null)
    • start_date (date, not null)
    • end_date (date, null) 
    • notes (text, null)  
  7. Save the table with the name Projects
  8. Create a second table with the following:
    • clientid (PK, int, not null) Identity set to YES
    • client (varchar(50), not null)
    • notes (text, null) 
  9. Save this table as Clients
  10. Create a third table with the following
    • techid (PK, int, not null) Identity set to YES
    • technology (varchar(50), not null)
    • notes (text, null)
  11. Save this table as Technologies
  12. Create our two look up tables
    • 1st table for our Clients and Projects relationships
    • id (PK, int, not null) Identity set to YES
    • projid (int, not null)
    • clientid (int, not null)
    • Save this table as Client_Projects
    • 2nd table for our Projects and Technologies relationships
    • id (PK, int, not null) Identity set to YES
    • techid (int, not null)
    • projid (int, not null) 

  13. Next expand the Database Diagrams folder
    Create a new diagram
    Add all the tables and set the appropriate relationships.

    Client (One) - Client_Projects (Many)
    Project (One) - Client_Projects (Many)
    Project (One) - Project_Technologies (Many)
    Technology (One) - Project_Technologies (Many)

    You should have something similar to the following:

  14. We now need to give permission to the user who will be used to connect to our project database.
  15. Open the Server-Security folder, then select Logins.

    If NT AUTHORITY\NETWORK SERVICE is NOT present follow Step 16. If NT AUTHORITY\NETWORK SERVICE IS present skip to step 17.
  16. Right click Logins, select New Login

    Click Search

    1. The easiest way I've found is to click Advanced
    2. Then click Find Now
    3. Locate and select Network Service

    4. Click OK
    5. Click OK again
    6. Select User Mapping
    7. Check the box next to MyProjects, or whatever you named the db in this tutorial
    8. Then check the box next to db_datareader in the Database role membership for : MyProjects

    9. Click OK
  17. If NT AUTHORITY\NETWORK SERVICE IS present following Steps 6 - 9 above to map the user to our MyProjects database.

Setup Our ODATA Service

 Open Visual Studio for Web or Visual Studio Professional and create a new
 WCF Service Application.

NOTE: I'm choosing the C# version, but the Visual Basic version will work as well.

I named this new service application ProjectManagerWCFService
Delete the default IService1.cs and Service1.svc, we will create our own.

Right click the ProjectManagerWCFService and select Add then WCF Data Service 5.3, I named this new Data Service ProjectManagerWCFDataService, click OK.

NOTE: If  WCF Data Service 5.3 is not present, select Add New Item and look for this library under your language, then Web. Or you may need to install it.

Next we need to add a data model.
Right click ProjectManagerWCFService again, click Add and choose ADO.NET Entity Data Model.

NOTE: If  ADO.NET Entity Data Model. is not present, select Add New Item and look for this library under your language, then Data.

I named this ProjectManagerDataModel
You should now see the following screen shot:

I chose the default, Generate from database.

  1. Click Next.
  2. Click New Connection.
    You should see the following screen shot:

  3. Select your Server - HINT: If the server is on the same machine, just put a dot in the Server name field, this is a shortcut for localhost.
  4. I left the default Windows Authentication
  5. Select your database
  6. You can test the connection if you want at this point or just click OK
  7. I accepted the defaults of this screen.
  8. Click OK
    The following screen will be shown

    : Make a note of the name I've circled in the screen shot, we will need this later.

  9. Click Next
  10. Expand the Table, dbo disclosure buttons 
  11. Check Clients_Projects, Clients, Project_Technologies, Projects and Technologies
  12. Accept the defaults and click Finish
  13. You may receive a warning message that "Running this template could possibly harm your computer, I chose to ignore". You have to make your own decision.

  14. OK, if the template above did not generate the connector lines? How do I correct this? Well the reason this will happen is if I forget to set the relationships in the database as I described in step 13. But not to worry, just go back to the database and follow step 13,  then in Visual Studio update the model based on the database.

    You could just create the relationships in the Entity Model Designer by Right clicking the Client Model, make sure your clicking in the title area of the Model box. Choose Add New then select Association. Create a One to Many relationship with the Client table table on the one side and Client_Projects on the many side. But be aware this is only for the data model not the database, so if there are any changes in the database you will need to update the model any ways so I suggest to just make the changes in the database.

  15. Open the ProjectManagerWCFDataService.svc file
  16. In the line
    Public class ProjectManagerWCFDataService : DataService< /* TODO: put your data source class name here. */ > replace the /* TODO: put your data source class name here. */ comment with the name I mentioned above MyProjectsEntities

    This line should now look like the following
    Public class ProjectManagerWCFDataService : DataService<MyProjectsEntities> 
  17. We need to make one more change to this file. Uncomment the first config line
    config.SetEntitySetAccessRule("MyEntitySet", EntitySetRights.AllRead);
    replace "MyEntitySet" with an Asterisk, it should look the following
    config.SetEntitySetAccessRule("*", EntitySetRights.AllRead);
    This will give us unrestricted read access, you may need to change this before releasing to production.
  18. OK now we can now test the service.
  19. Click the the green arrow button for Internet Explorer or the run button in Visual Studio.
  20. If all goes well you should see the following:

  21. I'll cover running queries against this service a little later. 
  22. Under the wwwroot create a new folder named ProjectManager
  23. We now need to create an application named ProjectManager in IIS Manager
  24. Open IIS Manager, click the Application Pool, I like to create a new Application Pool for each of my apps to isolate them from other processes. I'm not a NetworkAdmin so let me know if this is wrong and why.
  25. Click Add Application Pool, for the name I chose Project Manager
  26. Set .NET Framework version to .NET Framework v4.0.3019 or higher
  27. Managed pipeline mode:  I selected Integrated 
  28. Leave the check box for Start application pool immediately checked
  29. Click OK
  30. Now highlight the App Pool you just created 
  31. Even though we set the .NET Framework to v4.0, the Managed .NET Framework version default is 2.0, we need to change this to 4.0 as well.

  32. Change the highlighted .NET Framework from v2.0 to v4.0
  33. Right click Default Web Site then select Add Application
  34. Click the Select button
  35. Select the Application Pool we just created
  36. Click OK
  37. For Alias I just type PM
  38. Click the button with the ... next to the Physical Path field
  39. Navigate to the ProjectManager folder we created in Step 22.
  40. Click OK
  41. You can now close IIS Manager.

    : If you are on a public server, check with your Network Admin. This tutorial is for localhost development. Be aware additional security settings may need to be set on a public server.
  42. You have to complete this next part as the Administrator. Launch Visual Studio as Administrator.
  43. We need to set up publishing.
  44. Click Build in the menu bar, then select Publish Selection or Publish in Visuaal Studio.
  45. Select Profile, Select or Publish Profile. In the drop down, select new.
  46. For Profile Name type localhost|  
  47. Connection
    Service URL: localhost 
    Site / application: Default Web Site/ProjectManager
  48. Click the Validate Connection Button you should see  
  49. Click Next 
  50. Settings: In the Databases section select the drop down under MyProjectEntities make sure the connection strings are correct.
  51. Select Next
  52. Click the Start Preview button or you can click the Publish button.
  53. If all goes well open a browser on the web server and type
  54. You should see the same screen as Step 20.
  55. OK now if your setup is like mine for your MAC, you need to know the ip address of your Windows machine, the web service host.
  56. Open a browser on your MAC and type your ip address, mine is
  57. You should see the same screen as Step 20.
  58. You can now close Visual Studio.

        Setup Flash Builder 

        Tuesday, September 25, 2012

        Use SSRS for Reports in LightSwitch

        This tutorial should work for all versions of LightSwitch but I haven’t tried this with the new Visual Studio 2012, I only have Visual Studio LightSwitch 2011 stand alone.

        I usually use DevExpress XtraReports for all my reporting needs but just for fun I installed Microsoft SQL Server Reporting Services (SSRS) to use with SQL Server 2008 R2.

        What this tutorial is and What it is not.

        Setting up SSRS is beyond the scope of this tutorial. This tutorial will focus only on creating an SSRS report then calling the report from LightSwitch with the Spursoft LightSwitch Extension - SSRS Viewer.

        What do we need:

        We need to create the datasource for the SSRS Report

        I created a database named demos in SQL Server

        Create two tables

        1st one will be named CoffeeFlavors with the following column definitions:

        id – int,  Primary Key,  not null,  Identity = Yes

        coffee – varchar(50),  not null

        comments - text, null

        The 2nd table is named SSRS with the following column definitions:

        id – int, Primary Key, not null, Identity = YES

        coffeeid – int, not null

        dayOfWeek – varchar(9), null

        comments – text, null

        Fill the tables with some data
        House BlendNULL
        San Francisco BlendNULL
        Christmas BlendNULL
        French RoastNULL


        Now we need to setup the relationships:

        Create a new Database Diagram and add the CoffeeFlavors and SSRS tables we just created then drag and drop the CoffeeFlavors id field to the SSRS coffeeid field, accept all the defaults. You should now have a ONE (CoffeFlavors) to MANY (SSRS) relationship.


        Now save this diagram using any name you want, I saved mine as dbo.master

        We now need to create a Stored Procedure that our SSRS Report will call.

        Create a new Stored Procedure called aggregateCoffeeChoices and insert the following:

        We need 2 parameters

        @startDate date,

        @endDate date

        And for our Select statement enter

        SELECT DISTINCT dbo.CoffeeFlavors.coffee, dbo.SSRS.date,dbo.SSRS.dayOfWeek,
        SUM(dbo.SSRS.coffeeid) AS count
        FROM dbo.CoffeeFlavors INNER JOIN     
        dbo.SSRS ON dbo.CoffeeFlavors.id = dbo.SSRS.coffeeid
        WHERE (dbo.SSRS.date BETWEEN @startDate AND @endDate)
        GROUP BY dbo.CoffeeFlavors.coffee, dbo.SSRS.date, dbo.SSRS.dayOfWeek

        Helpful Hint: If you struggle with getting all the JOINS and SQL just right, try creating a View then copy and modify the SELECT statement. The View Designer will create most of the SQL for you.

        You can now close the SQL Server Management Studio if you want.

        We need to create a folder to hold our Report

        Assuming you have Reporting Services configured correctly and can connect to your Report Server. Open SQL Server Reporting Services Configuration Manager. Navigate to the Report Manager URL link, click the Report Manager URL link

        On the SQL Server Reporting Services Home page click the New Folder button

        Name this new folder Demos

        For leave the Reporting Services Home Screen open, we will visit this screen again shortly

        We now need to connect our Report Management Service to this database

        Open Microsoft SQL Server 2008 R2 Report Builder

        Choose the Chart Wizard

        The next screen is asking you to choose a dataset, we need to create one so just accept the default and click Next.

        We now need to set a new Data Source Connection, click New then change the Name field to something more meaningful like SSRSDemoDataSource.

        Make sure the Connection type is set to Microsoft SQL Server then for Connection String click the Build button.

        Select your server from the Server Name dropdown,

        then select your database and click OK.

        I always like to click the Test Connection button just for a sanity check at this point.

        Click OK and make sure your new connection is highlighted

        Click Next

        You should now see the Design a Query Screen

        Click the + disclosure box for Stored Procedures in the Tree Control under Database View

        Choose the Procedure we created earlier – aggregateCoffeeChoices

        We will be changing the values for our @startDate and @endDate parameters later but for now just accept all the fields and parameters and click Next

        The next screen is the Choose Chart Type

        You can choose any type you want but I chose Line

        Click Next

        Arrange Chart Fields

        Under Available fields, drag coffee to the Categories box, drag date and dayOfWeek to the Series box and drag count to the Values box.

        Click Next

        Choose a style

        I chose Corporate

        Click Finish

        You should now see the Report Builder Designer

        I usually make the the chart control a little bigger so I can see all the data.

        We now need to change the parameters.

        Click the Parameters disclosure button and right click @startDate then select Parameter Properties

        Uncheck the Allow null value checkbox

        Select the Default Values option in the Navigation panel

        Select the No default value Radio Button

        Click OK

        Repeat the above steps for the @endDate parameter
        The default settings presents the user with disabled calendar controls for our parameters and NULL checkboxes checked next to each control. In my experience this only confuses the end user so by
        changing the settings above we enabled the calendar controls and removed the NULL checkboxes and the UI is now more intuitive for the user.

        Click the Run button give the report a startDate and an endDate and click the View Report button to see if our chart is the way we want it, if not return to the Designer and make any necessary changes.

        Once your satisfied with the chart in the viewer click the Save icon above the Ribbon Run tab

        Save this report to the Demos folder we created earlier

        Go back to the Reporting Services Home Screen and navigate to the report you just saved in the Demos folder.

        Click the reports link

        Either leave this screen open or in the Address Bar copy and paste the page’s URL to some place like Notepad, just so its handy, we will need the address for our SSRS viewer extension.

        If you haven’t installed the Spursoft LightSwitch Extension, please do so now

        Open Visual Studio LightSwitch and create a new project, for this demo I chose the C# version.

        Name the project SSRS_Demo

        Although you could create a table in the ApplicationData local database, connect to an existing DataBase, WCF RIA Service or an ODATA source, for this tutorial we won’t be connecting to any of these directly.

        Create a new Screen and name it demo

        Click the Add Layout Item and select Group

        Select the Group disclosure button and select SSRS Viewer

        In the Properties Panel paste the Reports URL in the Reporting Server URL text box.

        We now need to write some code for the viewer

        With the demo screen open click the Write Code disclosure button and select demo_Created

        In the demo_Created function enter the following code:

        partial void demo_Created()

                    // Set up our SSRSViewer’s Event Handler

                    this.FindControl(“Group”).ControlAvailable += new EventHandler<ControlAvailableEventArgs>(DisplayInternalBrowser);


        demo_Created  is called just after the screen is displayed and all the controls are present on the screen, this function will set the event handler function for when the specified control is available.

        Now lets create the Event Handler function

        public void DisplayInternalBrowser(object sender, ControlAvailableEventArgs e)

                    // Get a reference to the SSRSControl and set the SSRS Viewer on our
                    // screen to this reference

                    Spursoft.LightSwitch.Extension.Presentation.Controls.SSRSControl ctrl = (Spursoft.LightSwitch.Extension.Presentation.Controls.SSRSControl)e.Control);

        // Set the PopupWindow name for in browser applications i.e. Web App,
        // this will allow us to reuse the same window for additional calls
        // otherwise anytime a new call would fire off, a new window would be launched
        // Desktop or Out of Browser applications will just ignore this

        cntrl.PopupWindowName = “ReportWindow”;



        First a little walk through of our Desktop app version

        When you first create a LightSwitch project your projects Application Type is set to Desktop by default.
        The SSRS viewer in a Desktop setting is an embedded browser, so the report will look like it’s native to your application.

        Run the project

        When the home screen is displayed your SSRS Report will be shown in the demos tab.

        Once your satisfied, close the application

        Under the Projects Root Node double click the Properties node

        This will open the Projects Properties Panel

        Select Application Type and choose the Web Radio Button

        Run the application again

        This time when the application finishes launching a new pop up window will launch,
        NOTE: you may have to disable your browsers PopUp Blocker.

        The Spursoft LightSwitch Extension has many other great features besides the SSRS Viewer so I encourage you to check it out in the Visual Studio Gallery.        

        Additional Resources:

        MSDN Forum discussing the SSRS Viewer with Derek from Spursoft - this where I got the code for this example.

        The LightSwitchHelp website – Michael Washington has a great article on Printing SQL Server Reports with LightSwitch using acustom Silverlight Control

        Hope this helps.