Tejana

Microsoft Lightswitch Sharing a Unique Key

Posted in LightSwitch, Microsoft, Software, SQL by tejana on February 18, 2012

Applications with several master tables may have  tables where they can share a primary unique key. We will see how to set up Microsoft Lightswitch tables to share a key. And we will show a modal window picker with an add button. With this approach the index of the Customer table and the index of the Vendor table can be the same value for a customer that is also a vendor. Alternatively, one could use a single master table for multiple concepts, Vendor, Customer and so on, but our approach is based on Fowler’s Party pattern, as described in www.cs.fsu.edu/~stoeckli/PublicationsVitae/JavaDevJournal.docSimilar :

3.0 Fowler’s First Pattern – The Party Pattern

The first analysis pattern described by Fowler in his book Analysis Patterns, is the Party pattern. A Party, according to Fowler, is an abstraction to define persons or organizations. He models a Party class with sub-classes of Person and Organization. The Party could have different roles such as a person could be a employer or employee, a doctor, a mother, etc. An organization could be a business entity, a shelter, a hospital, etc. Figure 1 depicts the class representation of the Party pattern.

image

 

The model also shows the influence of Paul Patterson’s Maintaining a Primary Child Entity and Simple Stakeholder Management  Take a look at the Party entity:

party

 

Paul Patterson’s Blog show how to artfully exploit the Address, Phone, Email, and Contact with standard non required 1 to many relationships:

 

partyEmail

 

It is the relationships to Vendor, Employee and Customer that I draw your attention to:

partyCustomer

 

The resulting table structure will require a Party for a Customer and the Customer table will use the Party index. Further if a Vendor or Employee are associated with this Party, they will also use the same index. The primary key of the master tables is a foreign key of the Party table:

 

TableCustomer

 

Of course now this Party represents a challenge to the data entry screen (The add customer method opens the top right of the screen instead of the add dialogue):

 

customerList2

 

The user gets one chance to enter in a Party (labeled "Account:”). Once the record is saved a Customer must be deleted to change its Account/Party. To help, the screen validation logic has been leveraged to display an Add button for any Name entered if it does not exist in Parties. A modal window picker is used to display any programmatically set  party. This allows the user to select an existing party from a large dataset including all Vendors and Employees that are not yet Customers.  If a party record is added, any changes to the name, change the party name unless of course the name is changed to an existing Party. If the added party is not used it is removed. A screen PropertyParty is used to store the new party and then possibly delete it.

 

customerList3

 

       partial void CustomerListAddAndEditNew_Execute()
       {
           // Add new customer on screen right
           Customer _customer = new Customer();
           _customer.IsCompany = false;
           _customer.IsPerson = false;
           Customers.SelectedItem = _customer;
           this.FindControl("CompanyName3").Focus();
       }

       partial void AddNewPartyFromCustomer_Execute()
       {
           if (PropertyParty == null)
           {
               Party p = new Party();
               p.PartyName = Customers.SelectedItem.CustomerName;
               Customers.SelectedItem.Party = p;
               PropertyParty = p;
           }
           else
           {
               PropertyParty.PartyName = Customers.SelectedItem.CustomerName;
           }           
       }     

 

       partial void Customers_Validate(ScreenValidationResultsBuilder results)
        {

              // Set Party if available
                if ((!String.IsNullOrEmpty(Customers.SelectedItem.CustomerName)) &&
                   (Customers.SelectedItem.Details.EntityState == EntityState.Added))
                {
                    if (Customers.SelectedItem.Party == null)
                    {
                        var _v = DataWorkspace.TejanaTest2Data.Parties
                                   .Where(p =>
                                   p.PartyName.Equals(Customers.SelectedItem.CompanyName, StringComparison.Ordinal))
                                   .SingleOrDefault();
                        if (_v == null)
                        {
                            this.FindControl("AddParty").DisplayName = "Add: " + Customers.SelectedItem.CustomerName;
                            this.FindControl("AddParty").IsVisible = true;
                        }
                        else
                        {
                            Customers.SelectedItem.Party = _v;
                            this.FindControl("AddParty").IsVisible = false;
                        }
                    }
                    else
                    {
                        this.FindControl("AddParty").IsVisible = false;
                        if (Customers.SelectedItem.Party.Details.EntityState == EntityState.Added)
                        {
                            var _v = DataWorkspace.TejanaTest2Data.Parties
                                   .Where(p =>
                                   p.PartyName.Equals(Customers.SelectedItem.CompanyName, StringComparison.Ordinal))
                                   .SingleOrDefault();
                            if (_v == null)
                            {
                                Customers.SelectedItem.Party.PartyName = Customers.SelectedItem.CustomerName;
                               
                            }
                            else
                            {
                                Customers.SelectedItem.Party = _v;
                            }
                        }
                    }
                }
                if ((Customers.SelectedItem.Party != null) &&
                    (PropertyParty != null) &&
                    (Customers.SelectedItem.Party != PropertyParty))
                {
                    if (PropertyParty.Details.EntityState != EntityState.Discarded)
                    {PropertyParty.Delete();}

                 }

}

I have not had time to be blogging, it is nice to have work. Before going back to work I thought I would post a LightSwitch blog entry.

Creating a Custom Add or Edit Dialog in LightSwitch C# Beta2

Posted in LightSwitch by tejana on July 11, 2011

Thanks to Sheel Shah for his informative posting Creating a Custom Add or Edit Dialog (Sheel Shah). Here is a work up of his solution in C# for Beta2, with allowing for the user clicking the X to close the child modal window:

using System;
using System.Linq;
using System.IO;
using System.IO.IsolatedStorage;
using System.Collections.Generic;
using Microsoft.LightSwitch;
using Microsoft.LightSwitch.Framework.Client;
using System.Windows.Controls;
using Microsoft.LightSwitch.Presentation;
using Microsoft.LightSwitch.Presentation.Extensions;

namespace LightSwitchApplication
{
    public partial class Application
    {
    }
    public class DialogHelper
    {
        Microsoft.LightSwitch.Client.IScreenObject _screen;
        Microsoft.LightSwitch.Client.IVisualCollection _collection;
        String _dialogName;

        bool _isEditing;
        bool _isOK;  //added to allow for user clicking on X
        bool _doOnce; //added to allow only one closing handler

        IEntityObject _entity;

        public DialogHelper(Microsoft.LightSwitch.Client.IVisualCollection visualCollection,
                     String dialogName)
        {
            _screen = visualCollection.Screen;
            _collection = visualCollection;
            _dialogName = dialogName;
            _isOK = false; //added to allow for user clicking on X
            _doOnce = true; //added to allow only one closing handler
        }

        public void InitializeUI()
        {
            // following moved to BaseOpenDialog as workaround for beta2
            //bug: Modal window does not fire control available on open.
            //_screen.FindControl(_dialogName).ControlAvailable +=
            //     new EventHandler<ControlAvailableEventArgs>(DialogHelper_ControlAvailable);
            //_screen.FindControl(_dialogName).ControlUnavailable +=
            //    new EventHandler<ControlUnavailableEventArgs>(DialogHelper_ControlUnavailable);
        }

        void DialogHelper_ControlAvailable(object sender, ControlAvailableEventArgs e)
        {
            if (_doOnce)
            {
                ChildWindow childwindow = e.Control as ChildWindow;
                childwindow.Closed += new EventHandler(childwindow_Closed);
                _doOnce = false;
            }
            _screen.FindControl(_dialogName).ControlUnavailable -= new
                    EventHandler<ControlUnavailableEventArgs>(DialogHelper_ControlUnavailable);
        }

        void childwindow_Closed(object sender, EventArgs e)
        {
            if (_entity != null)
            {
                if (_isOK)
                {
                    (_entity.Details as System.ComponentModel.IEditableObject).EndEdit();
                }
                else
                {
                    if (_isEditing)
                    {
                        //cancel edit on close without ok (cancel or click X)
                        (_entity.Details as System.ComponentModel.IEditableObject).CancelEdit();
                    }
                    else
                    {
                        //on add if not ok discard, do not add blank row
                        _entity.Details.DiscardChanges();
                    }
                }
                _isOK = false;
            }
        }

        public bool CanEditSelected()
        {
            return _collection.CanEdit && (_collection.SelectedItem != null);
        }

        public bool CanAdd()
        {
            return _collection.CanAddNew;
        }

        public void AddEntity()
        {
            _isEditing = false;
            _collection.AddNew();
            _screen.FindControl(_dialogName).DisplayName = "Add " +
                _collection.Details.GetModel().ElementType.Name;
            BaseOpenDialog();
        }

        public void EditSelectedEntity()
        {
            _isEditing = true;
            _screen.FindControl(_dialogName).DisplayName = "Edit " +
                _collection.Details.GetModel().ElementType.Name;
            BaseOpenDialog();
        }

        private void BaseOpenDialog()
        {
            _entity = _collection.SelectedItem as IEntityObject;
            if (_entity != null)
            {
                Microsoft.LightSwitch.Threading.Dispatchers.Main.BeginInvoke(() =>
                {
                    (_entity.Details as System.ComponentModel.IEditableObject).EndEdit();
                    (_entity.Details as System.ComponentModel.IEditableObject).BeginEdit();
                }
                    );
                _screen.OpenModalWindow(_dialogName);
                //after beta 2 following can be moved to IntializeUI
                _screen.FindControl(_dialogName).ControlAvailable += new
                        EventHandler<ControlAvailableEventArgs>(DialogHelper_ControlAvailable);
                _screen.FindControl(_dialogName).ControlUnavailable += new
                        EventHandler<ControlUnavailableEventArgs>(DialogHelper_ControlUnavailable);
            }
        }

        void DialogHelper_ControlUnavailable(object sender, ControlUnavailableEventArgs e)
        {
            _screen.FindControl(_dialogName).ControlAvailable -= new
                    EventHandler<ControlAvailableEventArgs>(DialogHelper_ControlAvailable);
        }

        public void DialogOK()
        {
            _isOK = true;
            _screen.CloseModalWindow(_dialogName);
        }

        public void DialogCancel()
        {
            _screen.CloseModalWindow(_dialogName);
        }
    }
}

   

With calling code:

using System;
using System.Linq;
using System.IO;
using System.IO.IsolatedStorage;
using System.Collections.Generic;
using Microsoft.LightSwitch;
using Microsoft.LightSwitch.Framework.Client;
using Microsoft.LightSwitch.Presentation;
using Microsoft.LightSwitch.Presentation.Extensions;
namespace LightSwitchApplication
{
    public partial class EditableCustomersGrid
    {
        private DialogHelper customersDialogHelper;

        partial void EditableCustomersGrid_InitializeDataWorkspace(
            System.Collections.Generic.List<IDataService> saveChangesTo)
        {
            customersDialogHelper = new DialogHelper(this.Customers, "CustomerEditDialog");
        }

        partial void EditableCustomersGrid_Created()
        {
            //customersDialogHelper.InitializeUI(); //not for beta2
        }

        partial void gridAddAndEditNew_CanExecute(ref bool result)
        {
            result = customersDialogHelper.CanAdd();
        }

        partial void gridAddAndEditNew_Execute()
        {
            customersDialogHelper.AddEntity();
        }

        partial void gridEditSelected_CanExecute(ref bool result)
        {
            result = customersDialogHelper.CanEditSelected();
        }

        partial void gridEditSelected_Execute()
        {
            customersDialogHelper.EditSelectedEntity();
        }

        partial void EditDialogOK_Execute()
        {
            customersDialogHelper.DialogOK();
        }

        partial void EditDialogCancel_Execute()
        {
            customersDialogHelper.DialogCancel();
        }
    }
}

Note: a reference to System.Windows.Controls has been added to the Client project.

Microsoft LightSwitch Entity Names and Properties Self Populating Tables

Posted in LightSwitch by tejana on May 15, 2011

This exercise will demonstrate a project with editable  grid screens for entity names and properties which self populate and update automatically. We set up our model by starting a new project named lsEntityPropertyNames and adding a new table named EntityName with one property “Name”,  then adding another table “PropertyName” with one property “Name” and one relationship to “EntityName”.  We then add editable grid screens for each entity, adding the EntityNames to the EditablePropertyNamesGrid as a data item.

lsPropertyNames

Open the EditableEntityNamesGrid in the screen designer and write code for the EditableEntityNamesGrid_Activated():

foreach (string s in
                this.Application.Details.GetModel().GlobalItems
                .OfType<Microsoft.LightSwitch.Model.IEntityType>()
                .Select(e => e.Name)
                .Except(EntityNames.Select(e => e.Name)))

            EntityName n = new EntityName();
            n.Name = s; 
}

Open the EditablePropertyNamesGrid and write code for the EditablePropertyNamesGrid_Activated:

//property name does not exist while entity name does
foreach (EntityName n in EntityNames)
{
     Microsoft.LightSwitch.Model.IEntityType entityType =
          this.Application.Details.GetModel()
               .GlobalItems
               .OfType<Microsoft.LightSwitch.Model.IEntityType>()
               .Single(e => e.Name.Equals(n.Name, StringComparison.Ordinal));

     foreach (string s in entityType.Properties.Select(p => p.Name)
               .Except(PropertyNames
               .Where(pn => pn.EntityName.Equals(n))
               .Select(pn => pn.Name))
     {
          PropertyName _propertyName = new PropertyName();
          _propertyName.Name = s;
          _propertyName.EntityName = n;
     }
}

//entity name does not exist
foreach (string s in
             this.Application.Details.GetModel().GlobalItems
             .OfType<Microsoft.LightSwitch.Model.IEntityType>()
             .Select(e => e.Name)
             .Except(EntityNames.Select(e => e.Name)))
{
     {
          EntityName n = new EntityName();
          n.Name = s;
          Microsoft.LightSwitch.Model.IEntityType entityType =
               this.Application.Details.GetModel()
               .GlobalItems
               .OfType<Microsoft.LightSwitch.Model.IEntityType>()
               .Single(e => e.Name.Equals(s, StringComparison.Ordinal));

          foreach (string s2 in
               entityType.Properties.Select(p => p.Name)) 
          {
               PropertyName pn = new PropertyName();
               pn.Name = s2;
               pn.EntityName = n;
          }
     }
}

 

 

Press F5,

run3

 

These tables self populate when open. If the entries are saved they will not duplicate. If the entries are deleted, they will reappear next time the screen is opened. If entities are added to the project their properties will appear the next time the screens are opened.

To exclude navigation properties insert the code in bold: 

//property name does not exist while entity name does
foreach (EntityName n in EntityNames)
{
     Microsoft.LightSwitch.Model.IEntityType entityType =
          this.Application.Details.GetModel()
               .GlobalItems
               .OfType<Microsoft.LightSwitch.Model.IEntityType>()
               .Single(e => e.Name.Equals(n.Name, StringComparison.Ordinal));

     foreach (string s in entityType.Properties.Select(p => p.Name)
               .Except(PropertyNames
               .Where(pn => pn.EntityName.Equals(n))
               .Select(pn => pn.Name)
               .Union(entityType.NavigationProperties.Select(np => np.Name))))
     {
          PropertyName _propertyName = new PropertyName();
          _propertyName.Name = s;
          _propertyName.EntityName = n;
     }
}

//entity name does not exist
foreach (string s in
             this.Application.Details.GetModel().GlobalItems
             .OfType<Microsoft.LightSwitch.Model.IEntityType>()
             .Select(e => e.Name)
             .Except(EntityNames.Select(e => e.Name)))
{
     {
          EntityName n = new EntityName();
          n.Name = s;
          Microsoft.LightSwitch.Model.IEntityType entityType =
               this.Application.Details.GetModel()
               .GlobalItems
               .OfType<Microsoft.LightSwitch.Model.IEntityType>()
               .Single(e => e.Name.Equals(s, StringComparison.Ordinal));

          foreach (string s2 in
               entityType.Properties.Select(p => p.Name)
                    .Except(entityType.NavigationProperties.Select(np => np.Name)))
          {
               PropertyName pn = new PropertyName();
               pn.Name = s2;
               pn.EntityName = n;
          }
     }
}

To complete the desired behavior remove the add and edit buttons from the screen grids and set the entity properties to read only if not null. Source.

Microsoft Lightswitch select entity property name in combobox

Posted in LightSwitch by tejana on May 13, 2011

We can use the comboBox control from the System.Windows.Control namespace to create a data entry screen using a ComboBox of property names for an  entity to fill a string field. Following on from yesterday’s post, I have added an entity to our project named:  “EntityPropertyNames” with ID, Name, and EntityName, with a query named “EntityPropertyNamesByEntity” with filter of EntityName = new parameter.  Adding the query to our CreateNewDataEntry screen and also adding “EntityProperty” to the NewDataEntry entity:

CreateNewDataEntry2

I have set the Query Parameter of the EntityPropertyNamesByEntity to the Entity Name of the DataEntryProperty and changed the control type of the Entity Property to a combobox. We want to set the property names based upon the entity selected so we set the DataEntryProperty.EntityProperty to read only unless the DataEntryProperty.EntityName has been set:

public partial class DataEntry
  {
      partial void EntityProperty_IsReadOnly(ref bool result)
      {
          result = EntityName == null;
      }
  }

Once the EntityName is set we are ready to find the property names:

partial void DataEntryProperty_Validate(ScreenValidationResultsBuilder results)
       {
           //do not process on load (null) or on save (EntityPropertyNamesByEntity already set correctly)
           if ((DataEntryProperty.EntityName != null) &&
              (EntityPropertyNamesByEntity
              .Count(pn=>pn.EntityName.Equals(DataEntryProperty.EntityName,
                  StringComparison.Ordinal))) == 0)
           {
               //This query is not autoload because changes to parameter made by
               //custom control combo box are not picked up 
               this.Details.Properties.EntityPropertyNamesByEntity.Loader.Load();

               Microsoft.LightSwitch.Model.IEntityType entityType =
                   this.Application.Details.GetModel()
                   .GlobalItems.OfType<Microsoft.LightSwitch.Model.IEntityType>()
                   .Single(e => e.Name.Equals(DataEntryProperty.EntityName, StringComparison.Ordinal));
               foreach (string s in
                   entityType.Properties.Select(p => p.Name)
                   .Except(EntityPropertyNamesByEntity.Select(ep => ep.Name)))
               {
                   EntityPropertyNames epn = new EntityPropertyNames();
                   epn.Name = s;
                   epn.EntityName = entityType.Name;
               }

               IContentItemProxy comboBox = this.FindControl("EntityProperty");

               comboBox.SetBinding(
                    System.Windows.Controls.ComboBox.ItemsSourceProperty,
                   "Screen.EntityPropertyNamesByEntity", System.Windows.Data.BindingMode.TwoWay);
               comboBox.SetBinding(
                  System.Windows.Controls.ComboBox.SelectedItemProperty,
                  "Screen.DataEntryProperty.EntityProperty", System.Windows.Data.BindingMode.TwoWay);
           }
       }

image

 

Special thanks to Microsoft’s Justin Anderson for the Model queries: http://social.msdn.microsoft.com/Forums/en-US/lightswitchgeneral/thread/b35d9341-0170-4e39-9a1b-47d57e94c527 for VB code see here.

and Tim Leung’s excellently clear post about binding to a ComboBox: http://dotnettim.wordpress.com/2011/05/09/lightswitch-limiting-item-selections-using-a-combobox/

Microsoft Lightswitch Entity Name ComboBox

Posted in .net, LightSwitch, Microsoft, Software, Visual Studio by tejana on May 13, 2011

We can use the comboBox control from the System.Windows.Control namespace to create a data entry screen using a ComboBox of project entity names to fill a string field. I have created a LightSwitch project with three enitities and one screen CreateNewDataEntries screen:

lsEntityNameComboBox

On the CreateNewDataEntries screen I have added the EntityNames query as a data item and changed the control on the field for the DataEntryProperty.EntityName to a System.Windows.Control.ComboBox.

CreateNewDataEntry

The following code is added to the CreateNewDataEntry.cs:

partial void CreateNewDataEntry_Activated()
       {
           foreach (string s in
               this.Application.Details.GetModel().GlobalItems
               .OfType<Microsoft.LightSwitch.Model.IEntityType>()
               .Select(e => e.Name))
           {
               {
                   EntityName n = new EntityName();
                   n.Name = s;
               }
           }

           IContentItemProxy comboBox = this.FindControl("EntityName");
           comboBox.SetBinding(
                   System.Windows.Controls.ComboBox.ItemsSourceProperty,
                   "Screen.EntityNames", System.Windows.Data.BindingMode.TwoWay);
           comboBox.SetBinding(
                   System.Windows.Controls.ComboBox.SelectedItemProperty,
                   "Screen.DataEntryProperty.EntityName", System.Windows.Data.BindingMode.TwoWay);
       }

partial void CreateNewDataEntry_Saving(ref bool handled)
        {
            if (DataEntryProperty.EntityName != null)
            {
                foreach (EntityName e in EntityNames)
                {
                    e.Delete();
                }
            }
        }

 

This approach yields the desired results :

 

run

 

I wish I had a group to discuss this with. The entity EntityName is never really used to save data. The model is queried for the entity names which are then put into the screen collection of EntityNames but these items are deleted before any save on the screen. If you wanted to save the entity names you could remove the partial void CreateNewDataEntry_Saving(ref bool handled) and add the .Except:

partial void CreateNewDataEntry_Activated()
       {
           foreach (string s in
               this.Application.Details.GetModel().GlobalItems
               .OfType<Microsoft.LightSwitch.Model.IEntityType>()
               .Select(e => e.Name)
               .Except(EntityNames.Select(e=>e.Name)))
           {
               {
                   EntityName n = new EntityName();
                   n.Name = s;
               }
           }

           IContentItemProxy comboBox = this.FindControl("EntityName");
           comboBox.SetBinding(
                   System.Windows.Controls.ComboBox.ItemsSourceProperty,
                   "Screen.EntityNames", System.Windows.Data.BindingMode.TwoWay);
           comboBox.SetBinding(
                   System.Windows.Controls.ComboBox.SelectedItemProperty,
                   "Screen.DataEntryProperty.EntityName", System.Windows.Data.BindingMode.TwoWay);
       }

The binding of the ComboBoxItemSource is to this Screen property “Screen.EntityNames”. This is the only real use of the definition. I wish I knew how to create a binding with a code only variable, but at least this binding works.

Microsoft LightSwitch Printing Html

Posted in .net, LightSwitch, Microsoft, Software, SQL, Visual Studio by tejana on April 29, 2011

Printing Html offers the sharper resolution of vector graphics overcoming a major short coming  of direct native SilverLight raster printing as discussed in my last post. I am using a LightSwitch Desktop Application. By calling windows internet explorer print method we can by-pass the inferior SilverLight print method.

Setting up a LightSwitch screen with a SilverLight webBrowser control and passing in Html from the database we can engineer one click printing with acceptable resolution. In this exercise I used SQL 2008 as the database so that I can store html in the database using varchar(MAX):

varcharMAX

Otherwise, the LightSwitch application contains one  simple entity with a CreateNew, Search, and Detail Screen like the previous two posts. This is to create a example print button on our Detail Screen:

detailTest

With the print execution code:

        partial void Print_Execute()
        {
            Application.Current.ShowWebBrowser();
        }

Simply showing another screen from our application. This page contains the webBrowser control, for details see Tim Leung’s Blog. Thanks Tim. For this exercise add a New Data Screen with no data, named WebBrowser. Remove this  new screen from Navigation and add a data item to the screen: Property1 as string. Place Property1 on the screen and change the control to custom control to WebBrowser under System.Windows, System.Windows.Control. Be sure to set the Sizing to Stretch and Label Position to None:

 

WebBrowser 

public partial class WebBrowser
{
    string strHtml;

    partial void WebBrowser_Activated()
    {         
        this.FindControl("Property1").ControlAvailable += new EventHandler<ControlAvailableEventArgs>(WebBrowser_ControlAvailable);
    }

    void WebBrowser_ControlAvailable(object sender, ControlAvailableEventArgs e)
    {
        ((System.Windows.Controls.WebBrowser)e.Control).NavigateToString(strHtml);
    }

    partial void WebBrowser_Created()
    {
       strHtml = this.DataWorkspace.TejanaTestData.ReportsHtmls_Single(8).StringHtml;
    }
}

The ‘8’ in DataWorkspace.TejanaTestData.ReportsHtmls_Single is the index of the StringHtml. It took a few tries to get the inner Html the way I wanted. It is not that easy to paste Html into Microsoft SQL Server Management Studio. Thanks to pctechblog, I used notepad++ to strip the new lines from the Html. I inserted into the Html the phrase:  <body onload="window.print()">  in the body of the page. This causes the page to call the windows internet explorer print method immediately upon display.

print

This method produces acceptable print quality:

printZoom

ReportPaging

With good paging, and quick execution. Headers and footers can be controlled  by the user’s Internet Explorer’s Page Setup.  The question is, where did the html come from ? To actually use this method, I would want to be able to generate the Html in code. A start to Html report generation can be found at: The HTML Report Engine which is how I created the example report. This Report Engine Project is nifty, but considering this a base for developing an updated engine for Line of Business reports, sure makes the Report Builder in Reporting Services look nice.

Microsoft Lightswitch Client Native Printing

Posted in .net, LightSwitch, Microsoft, Software, Visual Studio by tejana on April 27, 2011

Visual Studio 2010 Custom Silverlight Control as Print Template

Simple printing would be a misnomer, perhaps. Printing is the most obvious shortcoming of Microsoft’s Lightswitch development environment. Before we lament about the lack of printing support, let us review what is available. Silverlight has made advances in printing that will allow limited printing. To see a discussion of shortfalls and alternatives see here:

http://stackoverflow.com/questions/4942990/creating-reports-in-silverlight-either-as-pdf-or-send-it-off-to-a-printer

Following with Beth Massi’s Using Word in Lightswitch project from my last post. Simply adding a Silverlight Class Library with a UserControl allows us to add a reference to our LightSwitch project. I have then added the custom control to the bottom of the Customer Detail screen. This is done by adding another Customer Row Layout and setting the control to our custom control. The control will act as a printing template for simple direct printing of the Customer details. I have also added a screen property just to demonstrate that any screen data is available to print via the custom control binding.

createNewScreen

By adding  to the CustomerDetail screen code CustomerDetail.cs:

        partial void CustomerDetail_Activated()
        {
            this.FindControl("Customer").ControlAvailable
                    += new EventHandler<ControlAvailableEventArgs>(CustomerDetail_ControlAvailable);
        }

        void CustomerDetail_ControlAvailable(object sender, ControlAvailableEventArgs e)
        {
            SilverlightClassLibrary1.SilverlightControl1 ctrl
                      = e.Control as SilverlightClassLibrary1.SilverlightControl1;
            ctrl.Visibility = System.Windows.Visibility.Collapsed;
        }

The custom control will not initially be visible on the screen. We have added a Print Direct button the the screen with the execution code:

        System.Windows.Printing.PrintDocument pd = new System.Windows.Printing.PrintDocument();
        System.Windows.UIElement ele;

        partial void PrintDirect_Execute()
        {
            this.FindControl("Customer").ControlAvailable
                         += webControlAvailable;
            pd.PrintPage
                         += new EventHandler<System.Windows.Printing.PrintPageEventArgs>(pd_PrintPage);

        }

        private void webControlAvailable(object sender, ControlAvailableEventArgs e)
        {

            SilverlightClassLibrary1.SilverlightControl1 ctrl
                          = e.Control as SilverlightClassLibrary1.SilverlightControl1;
            ctrl.Visibility = System.Windows.Visibility.Visible;
            ele = ctrl;
            pd.Print("name");

            ctrl.Visibility = System.Windows.Visibility.Collapsed;
        }

        private void pd_PrintPage(object sender, System.Windows.Printing.PrintPageEventArgs ev)
        {
            ev.PageVisual = ele;
        }

 

When the Print Direct button is executed the custom control becomes visible and it is placed as the PageVisual of a silverlight PrintDocument. The document is printed and then our custom control is again collapsed.

printingWait

 

printOutput

This method will allow printing of un-saved screen data on special forms like Invoices, Purchase Orders or Checks. The custom control is set up like this:

<UserControl x:Class="SilverlightClassLibrary1.SilverlightControl1"
    xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
    xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
    xmlns:d="http://schemas.microsoft.com/expression/blend/2008"
    xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006"
    mc:Ignorable="d"
   d:DesignHeight="200" d:DesignWidth="300">

    <Border BorderThickness="3" BorderBrush="Black" CornerRadius="10" Padding="2">
        <StackPanel x:Name="LayoutRoot" Background="White" Orientation="Vertical">
            <StackPanel Orientation="Horizontal">
                <TextBlock Height="23" HorizontalAlignment="Left" Name="textblock1" VerticalAlignment="Top" Width="Auto" Text="{Binding  Mode=TwoWay, Path=Screen.Customer.FirstName}" FontSize="14" />
                <TextBlock Height="23" HorizontalAlignment="Left" Name="textblock2" VerticalAlignment="Top" Width="Auto" Text=" " FontSize="14" />
                <TextBlock Height="23" HorizontalAlignment="Right" Name="textblock3" VerticalAlignment="Top" Width="Auto" Text="{Binding  Mode=TwoWay, Path=Screen.Customer.LastName}" FontSize="14" />
            </StackPanel>
            <TextBlock Height="23" HorizontalAlignment="Left"  Name="textblock4" VerticalAlignment="Top" Width="120" Text="{Binding  Mode=TwoWay, Path=Screen.Customer.Address1}" FontSize="14"  />
            <TextBlock Height="23" HorizontalAlignment="Left"  Name="textblock5" VerticalAlignment="Top" Width="120" Text="{Binding  Mode=TwoWay, Path=Screen.Customer.Address2}" FontSize="14"  />
            <StackPanel Orientation="Horizontal">
                <TextBlock Height="23" HorizontalAlignment="Left"  Name="textlbock6" VerticalAlignment="Top"  Text="{Binding  Mode=TwoWay, Path=Screen.Customer.City}" FontSize="14"  />
                <TextBlock Height="23" HorizontalAlignment="Left" Name="textblock7" VerticalAlignment="Top" Width="Auto" Text=", " FontSize="14" />
                <TextBlock Height="23" HorizontalAlignment="Left"  Name="textblock8" VerticalAlignment="Top" Text="{Binding  Mode=TwoWay, Path=Screen.Customer.State}" FontSize="14"  />
                <TextBlock Height="23" HorizontalAlignment="Left" Name="textblock9" VerticalAlignment="Top"  Text=" " FontSize="14" />
                <TextBlock Height="23" HorizontalAlignment="Left"  Name="textbock10" VerticalAlignment="Top" Width="Auto" Text="{Binding  Mode=TwoWay, Path=Screen.Customer.PostalCode}" FontSize="14"  />
            </StackPanel>
            <StackPanel Orientation="Horizontal">
                <TextBlock Height="23" HorizontalAlignment="Left" Name="textblock11" VerticalAlignment="Top" Width="Auto" Text="Phone: "></TextBlock>
                <TextBlock Height="23" HorizontalAlignment="Left"  Name="textblock12" VerticalAlignment="Top" Width="Auto" Text="{Binding Mode=TwoWay, Path=Screen.Customer.Phone}" FontSize="14" />
            </StackPanel>
            <StackPanel Orientation="Horizontal">
                <TextBlock Height="23" HorizontalAlignment="Left" Name="textblock13" VerticalAlignment="Top" Width="Auto" Text="Gender: "></TextBlock>
                <TextBlock Height="23" HorizontalAlignment="Left"  Name="textblock14" VerticalAlignment="Top" Width="Auto" Text="{Binding Mode=TwoWay, Path=Screen.Customer.Gender}" FontSize="14" />
            </StackPanel>
            <TextBlock Height="23" HorizontalAlignment="Left"  Name="textblock15" VerticalAlignment="Top" Width="Auto" Text="{Binding Mode=TwoWay, Path=Screen.Property1}" FontSize="14" />
        </StackPanel>
    </Border>
</UserControl>

 

Notice that we can print any screen data using binding such as Path=Screen.Property1.

Source code including the last post, printing using Word in C#, is available here.

Using Microsoft Word in LightSwitch C#

Posted in .net, LightSwitch, Microsoft, Software, Visual Studio by tejana on April 25, 2011

C# version of Beth Massi’s Sharing the goodness that is VB.

The time span between blog posts represents a ground zero reinstallation of Windows. It does a body good. My boot drive is solid state. For something to post I have a translation of Beth Massi’s visual basic for those of us that like C#. This is a case where automated translation is no help. The following is a class added to a C# lightswitch project in the Client project under UserCode, named PrintClass.cs:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Runtime.InteropServices.Automation;
using System.Xml.Linq;

namespace LightSwitchApplication

{
    public class PrintClass
    {
        public static readonly PrintClass globalMethods = new PrintClass();

        private PrintClass()
        { Initialize(); }

        public void Initialize()
        { }

        public void RunCustomerReportFixedTemplate(Customer customer)
        {
            try
            {
                if (AutomationFactory.IsAvailable)
                {
                    //create xml element customer: xmlTree 
                    XElement xmlTree = new XElement("customer");
                    foreach (Microsoft.LightSwitch.Details.IProperty prop in customer.Details.Properties.All())
                    {
                        xmlTree.Add(new XElement(prop.Name.ToLower(), prop.Value));
                    }
                    //Load CustomerDetails.docx and replace existing xml node "customer[1]"
                    //with xml element customer: xmlTree, above
                    using (dynamic word = AutomationFactory.CreateObject("Word.Application"))
                    {
                        System.Windows.Resources.StreamResourceInfo resourceInfo = System.Windows.Application.GetResourceStream(new Uri("CustomerDetails.docx", UriKind.Relative));
                        string fileName = CopyStreamToTempFile(resourceInfo.Stream, ".docx");
                        dynamic doc = word.Documents.Open(fileName);
                        //here we get existing part from document, it must exist in document
                        //RunCustomerReportDynamicTemplate() below creates a new part in document
                        dynamic customXMLPart = doc.CustomXMLParts("urn:microsoft:ordermanager:customer");
                        //select top node
                        dynamic all = customXMLPart.SelectSingleNode("//*");
                        //select target
                        dynamic replaceNode = customXMLPart.SelectSingleNode(@"/ns0:root[1]/customer[1]");
                        //insert
                        all.ReplaceChildSubTree(xmlTree.ToString(), replaceNode);
                        //show
                        word.Visible = true;
                    }
                }
            }

            catch (Exception ex)
            {
                throw new InvalidOperationException("Failed to create customer report,", ex);
            }
        }

        public void RunCustomerReportDynamicTemplate(Customer cust)
        {
            try
            {
                if (AutomationFactory.IsAvailable)
                {
                    //load template for word
                    dynamic templateFile = Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments) +
                        @"\Reports\CustomerDetails.docx";

                    //with name space ns0
                    XNamespace ns0 = "urn:microsoft:ordermanager:customer";

                    //create "customer" node in xmlTree
                    XElement xmlTree = new XElement("customer");
                    foreach (Microsoft.LightSwitch.Details.IProperty prop in cust.Details.Properties.All())
                    {
                        xmlTree.Add(new XElement(prop.Name.ToLower(), prop.Value));
                    }

                    //create "root", with sub node xmlTree, "customer"
                    XElement root = new XElement(
                        ns0 + "root", new XAttribute(XNamespace.Xmlns + "ns0", "urn:microsoft:ordermanager:customer"), xmlTree);

                   //copy template to temp, open temp, insert "root",
                    using (dynamic word = AutomationFactory.CreateObject("Word.Application"))
                    {
                        string tempFile = GetTempFileName(".docx");
                        System.IO.File.Copy(templateFile, tempFile);
                        dynamic doc = word.Documents.Open(tempFile);
                        dynamic customXMLPart = doc.CustomXMLParts.Add(root.ToString());
                        //set bindings
                        for (int i = 1; i < doc.ContentControls.Count + 1; i++)
                        {
                            dynamic ctrl = doc.ContentControls[i];
                            if (!ctrl.XMLMApping.IsMapped)
                            {
                                ctrl.XMLMapping.SetMapping("/ns0:root[1]/customer[1]/" + ctrl.Title.ToLower(),
                                    @"xmlns:ns0=""urn:microsoft:ordermanager:customer""", customXMLPart);
                            }
                        }
                        //show
                        word.Visible = true;
                    }
                }
            }
            catch (Exception ex)
            {
                throw new InvalidOperationException("Failed to create customer report,", ex);
            }          
        }

        private string CopyStreamToTempFile(System.IO.Stream stream, string ext)
        {
            string path = GetTempFileName(ext);
            dynamic file = System.IO.File.Create(path);
            file.Close();
            System.IO.FileStream  fileStream = System.IO.File.Open(path,
                                                    System.IO.FileMode.OpenOrCreate,
                                                System.IO.FileAccess.Write,
                                                    System.IO.FileShare.None);
           byte[] buffer = new byte[stream.Length];
           stream.Read(buffer, 0, (int)stream.Length);
           fileStream.Write(buffer, 0, buffer.Length);
           fileStream.Close();
                return path;
        }

        private string GetTempFileName(string ext)
        {
            string path = Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments) + @"\Reports";
            if (!System.IO.Directory.Exists(path))
            {
                System.IO.Directory.CreateDirectory(path);
            }
            string filename = Guid.NewGuid().ToString() + ext;
            path = System.IO.Path.Combine(path, filename);
            return path;
        }
    }
}

 

The calling program includes:

partial void Print_Execute()
       {
           // Write your code here.
           //PrintClass.globalMethods.RunCustomerReportFixedTemplate(this.Customer);
           PrintClass.globalMethods.RunCustomerReportDynamicTemplate(this.Customer);
       }

The debug mode of visual studio 2010 is less helpful with the C# than the Visual Basic. The Word Content Control Toolkit is essential. Place a “CustomerDetails.docx” containing a Custom XML part,  in the Client project as content, copy newer and a “CustomerDetails.docx” without a Custom XML part in MyDocuments\Reports. Beth Massi put the former in ClientGenerated project directory, the latter in the project directory LightSwitchReportSample.  To understand this post, or download VB code  …  See Beth’s:

Using Microsoft Word to Create Reports For LightSwitch (or Silverlight)

Stored Procedures in LightSwitch

Posted in .net, LightSwitch, Microsoft, Software, SQL, Visual Studio by tejana on December 23, 2010

 

Demonstration of simple stored procedure in Microsoft LightSwitch beta 1, using SqlDataReader with connection string. My last posting did not show the details of a simple implementation of stored procedures in a Microsoft LightSwitch. Instead I used a code generation tool and left out the details of the connection string and calling the stored procedure. In this post I will show a simple detailed example. In fact I will use a simple select text instead of a stored procedure but the example could easily be changed to a stored procedure. This example uses a Data Reader for more information see DataReader vs DataSet. For more information about LightSwitch data see: The Anatomy of a LightSwitch Application Part 4 – Data Access and Storage

My last post shows how to create a new empty Microsoft LightSwitch Project and add a RIA services library 1 to the solution. To the RIA services library web project add a new item of type DomainService named DomainService1. Into the DomainService1.cs insert:

 

using System.Data;
using System.Data.SqlClient;

namespace RIAServicesLibrary1.Web
{
    using System;
    using System.Collections.Generic;
    using System.ComponentModel.DataAnnotations;
    using System.ServiceModel.DomainServices.Server;

    public class JournalEntries
    {
        [Key]
        public int Id
        { get; set; }

        public string Account
        { get; set; }

        public byte Year
        { get; set; }

        public byte Period
        { get; set; }

        public DateTime DateTrans
        { get; set; }

        public decimal DebitAmount
        { get; set; }

        public decimal CreditAmount
        { get; set; }

        public string DocumentNumber
        { get; set; }

        public string Reference
        { get; set; }

        public string UserId
        { get; set; }

        public DateTime DatePosted
        { get; set; }

        public byte JournalType
        { get; set; }

        public short JournalNumber
        { get; set; }

        public string Source
        { get; set; }

        public bool Accrual
        { get; set; }

        public bool Correction
        { get; set; }

        public bool Consolidated
        { get; set; }

        public bool Up
        { get; set; }

        public bool Down
        { get; set; }

        public static JournalEntries Create(IDataRecord record)
        {
            return new JournalEntries
                       {
                           Id = (int) record["id"],
                           Account = (string) record["Account"],
                           Year = (byte) record["Year"],
                           Period = (byte) record["Period"],
                           DateTrans = (DateTime) record["DateTrans"],
                           DebitAmount = (decimal) record["DebitAmount"],
                           CreditAmount =
                                        (decimal) record["CreditAmount"],
                           DocumentNumber =
                                       (string) record["DocumentNumber"],
                           Reference = (string) record["Reference"],
                           UserId = (string) record["UserId"],
                              //INITIALIZE NULLS
                           DatePosted = new DateTime(1753, 1, 1),
                           JournalType = 1,
                           JournalNumber = 1,
                           Source = (string)record["Source"],
                           Accrual = (bool)record["Accrual"],
                           Correction = (bool)record["Correction"],
                           Up = (bool)record["Up"],
                           Down = (bool)record["Down"]
                       };
        }
    }

public static class JournalSource
    {
        public static IEnumerable<JournalEntries> AllJournalEntries()
        {
            //could be new SqlCommand("storedProcedureName")
            SqlCommand myCommand = new SqlCommand();
            //could be     CommandType.StoredProcedure;
            myCommand.CommandType = CommandType.Text;
            myCommand.CommandText =
                "Select * From GL_JournalEntries";
            using(SqlConnection myConnection = new SqlConnection(
"Data Source=(local);Initial Catalog=TejanaA030;Integrated Security=SSPI;"))
            {
                myCommand.Connection = myConnection;
                    myConnection.Open();
                    SqlDataReader dr = myCommand.ExecuteReader();

                    if (dr.HasRows)
                    {
                        while (dr.Read())
                        {
                            yield return JournalEntries.Create(dr);
                        }
                    }
                    dr.Close();
            }
        }
    }

    // TODO: Create methods containing your application logic.
    // TODO: add the EnableClientAccess custom attribute to this class to
    // TODO: expose this DomainService to clients.
    public class DomainService1 : DomainService
    {
        [Query(IsDefault = true)]
        public IEnumerable<JournalEntries> GetJournalEntries()
        {
            return JournalSource.AllJournalEntries();
        }
    }
}

Return to the LightSwitch project and add a data source of type RIA service and add a screen for displaying the data (see last posting for details). The above code assumes you have a SQL data table with data:

 

/****** Object:  Table [dbo].[GL_JournalEntries]    Script Date: 12/23/2010 08:25:01 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[GL_JournalEntries](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [Account] [char](7) NOT NULL,
    [Year] [tinyint] NOT NULL,
    [Period] [tinyint] NOT NULL,
    [DateTrans] [date] NOT NULL,
    [DebitAmount] [decimal](19, 5) NOT NULL,
    [CreditAmount] [decimal](19, 5) NOT NULL,
    [DocumentNumber] [char](20) NOT NULL,
    [Reference] [char](35) NOT NULL,
    [UserId] [nvarchar](256) NOT NULL,
    [DatePosted] [date] NULL,
    [JournalType] [tinyint] NULL,
    [JournalNumber] [smallint] NULL,
    [Source] [char](10) NOT NULL,
    [Accrual] [bit] NOT NULL,
    [Correction] [bit] NOT NULL,
    [Consolidated] [bit] NOT NULL,
    [Up] [bit] NOT NULL,
    [Down] [bit] NOT NULL,
CONSTRAINT [PK_GL_JournalEntries] PRIMARY KEY CLUSTERED
(
    [Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

Microsoft LightSwitch and Stored Procedures using WCF RIA Services

Posted in .net, LightSwitch, Microsoft, Software, SQL, Visual Studio by tejana on December 9, 2010

I want to use stored procedures in a Microsoft LightSwitch application. Specifically, I want to copy Journal Entries to Ledger Entries and have access to stored procedures to update an Account Summary Table incrementing totals of debits, credits and corrections for each account for each period.

–*****************************************************************************
– Generated by TCDesigner on 12/6/2010 7:36:02 AM.
– Support: http://www.TCDesigner.com
– This file may be modified, copied, or given to someone else as long as this
– header is not removed.
–*****************************************************************************
– Does the procedure already exist in the database
IF EXISTS (SELECT * FROM dbo.sysobjects
           WHERE id = object_id(N’uspGlJournalUpdateAccountSummary’) AND
           OBJECTPROPERTY(id, N’IsProcedure’) = 1)
BEGIN
    — Remove the procedure from the database
    DROP PROCEDURE uspGlJournalUpdateAccountSummary
    PRINT ‘Dropping old version of uspGlJournalUpdateAccountSummary ‘
END
GO

PRINT ”
PRINT ‘Creating Stored Procedure:  uspGlJournalUpdateAccountSummary ‘
PRINT ”
GO

CREATE PROCEDURE uspGlJournalUpdateAccountSummary  — WITH ENCRYPTION — Encrypted procedures prevent tampering. This will not affect Generating Scripts.
AS

– Initialize any needed summary records
INSERT INTO GL_AccountsSummaries ( AccountCode,PeriodId ,Credit ,Debit ,CorrCredit ,CorrDebit)
SELECT DISTINCT   Account, Period, 0,0,0,0
FROM  GL_JournalEntries
WHERE CAST(Period AS CHAR(3)) + Account  NOT IN
(SELECT  CAST(PeriodId AS CHAR(3)) + AccountCode  FROM GL_AccountsSummaries)

– #TEMPTABLE contains summary by account and period
   CREATE TABLE #TEMPTABLE
(
    [Account] [char](7) NOT NULL,
    [Period] [tinyint] NOT NULL,
    [DebitAmount] [decimal](19, 5) NOT NULL,
    [CreditAmount] [decimal](19, 5) NOT NULL
)
INSERT INTO #TEMPTABLE (Account, Period, DebitAmount,CreditAmount)

SELECT  Account, Period, SUM(DebitAmount), SUM(CreditAmount)
FROM  GL_JournalEntries  WHERE Correction = 0 GROUP BY Account, Period

IF @@ROWCOUNT > 0
BEGIN
– Update Summaries from #TEMPTABLE
UPDATE GL_AccountsSummaries 
SET Debit =  Debit + #TEMPTABLE.DebitAmount, Credit = Credit + #TEMPTABLE.CreditAmount
FROM GL_AccountsSummaries  INNER JOIN #TEMPTABLE   ON AccountCode = Account AND PeriodId = Period
END
  
   DELETE #TEMPTABLE

– Journal entries marked as correction are summed seperate
INSERT INTO #TEMPTABLE (Account, Period, DebitAmount,CreditAmount)

SELECT  Account, Period, SUM(DebitAmount), SUM(CreditAmount)
FROM  GL_JournalEntries
WHERE Correction = 1
GROUP BY Account, Period

IF @@ROWCOUNT > 0
BEGIN
– Update Summaries from #TEMPTABLE for CORRECTIONS
UPDATE GL_AccountsSummaries 
SET CorrDebit  =  CorrDebit + #TEMPTABLE.DebitAmount, CorrCredit  = CorrCredit  + #TEMPTABLE.CreditAmount
FROM GL_AccountsSummaries  INNER JOIN #TEMPTABLE   ON AccountCode = Account AND PeriodId = Period
END
  
   DROP TABLE #TEMPTABLE

RETURN
GO

IF @@ERROR <> 0
BEGIN
PRINT ”
PRINT ‘Error Installing: uspGlJournalUpdateAccountSummary ‘
PRINT ”
END
ELSE
BEGIN
PRINT ”
PRINT ‘Finished Installing: uspGlJournalUpdateAccountSummary ‘
PRINT ”
END
GO

The approach uses WCF RIA services to expose the procedures to the LightSwitch application. First, I will outline the simplest data service.

About 35 minutes into their Channel 9 Video Beyond the Basics Joe Binder, a Program Manager on the LightSwitch team, walks through adding a simple WCF RIA project to a LightSwitch application. To review the essentials here, to a blank LightSwitch project in Visual Studio 2010 named, “lsApplication1” (c# in this case), we click on the Solution in Solution Explorer and add a new project of type WCF RIA Services Class Library named RIAServicesLibrary1.

lsApplication1_RIA

Then click on the RIAServicesLibrary1.Web project and add a new item of type Domain Service Class, named “DomainService1”.

 

DomainService1

Which brings up the Add New Domain Service Class wizard for details of the service endpoint. The checkbox by Enable client access is important. To develop the service with business logic in stored procedures I want to be sure that this is unchecked so that the service defined will only be accessible from within my LightSwitch Application and not exposed generally as a service.

AddNewDomanServiceClass

Disableclientaccess

Into the resulting DomainService1.cs:

namespace RIAServicesLibrary1.Web
{
    using System.Collections.Generic;
    using System.ComponentModel.DataAnnotations;
    using System.ServiceModel.DomainServices.Server;

public class CustomPatient
    {
        [Key()]
        public int PatientId { get; set; }
        public string LastName { get; set; }
        public string FirstName { get; set; }
    }

public static class PatientSource
    {
        public static List<CustomPatient> AllPatients()
        {
            List<CustomPatient> patientList = new List<CustomPatient>()
                                                  {
                                                      new CustomPatient()
                                                          {PatientId = 1, FirstName = "Patient1", LastName = "Doe"},
                                                      new CustomPatient()
                                                          {PatientId = 2, FirstName = "Patient2", LastName = "Doe"},
                                                      new CustomPatient()
                                                          {PatientId = 3, FirstName = "Patient3", LastName = "Doe"}
                                                  };
            return patientList;
        }
    }

public class DomainService1 : DomainService
    {
        [Query(IsDefault = true)]
        public IEnumerable <CustomPatient> GetPatients()
        {
            return PatientSource.AllPatients();
        }
    }
}

As shown by Joe Binder in the video. The entire solution can now be saved, and built.  Right click on Data Sources in our LightSwitch project,  to Add Data Source, Choose a Data Source Type of WCF RIA Service, click Next and allow a search for available services. Add a reference to the RIAServicesLibary1.Web project and again allow a search for available services.

AddReference

ChooseRIAServiceClass

LightSwitch will import the data source object into the application designer and we can quickly add a search screen to display our CustomPatientCollection.

 

AttachDataSourceWizard

CustomPatientCollection

If we include Insert, Update, and Delete methods in our data service:

 

public class DomainService1 : DomainService
{
    [Query(IsDefault = true)]
    public IEnumerable <CustomPatient> GetPatients()
    {
        return PatientSource.AllPatients();
    }

[Insert]
    public void CreatePatient(CustomPatient patient)
    {
    }

[Update]
    public void UpdatePatient(CustomPatient patient)
    {
    }

[Delete]
    public void DeletePatient(CustomPatient patient)
    {
    }
}

LightSwitch will generate methods to edit our patients, even though our data service will do nothing. Without these methods our Custom Patient Collection is read only. I am exploring the use of stored procedures with a LightSwitch application and envision the procedures doing the data manipulation so will keep with the read only approach.

In order to use stored procedures a connection to the database is required. Discussions on the forum have yet to show how to access the LightSwitch connection string. I will be using windows authentication for both the LightSwitch application and the data service so having an extra connection string in this prototype is not a problem. This is certainly an area needing review. I also want to organize data base access so I will use a generated data access class layer. The tool I have is called TCDesginer, which I do not think is still in active development. The TCDesginer demo (still available) creates  .net framework 2 data access classes and stored procedures for C# or Visual Basic. The demo only works on the NorthWind database. I have to do some global search and replace to use SQL Server 2008 new data types, but code generation sure beats all that typing. The generated UI and stored procedures are great templates for using the data access classes in WCF RIA services.

To expand our data model I will use an accounting model.

ModelA020A

 

I take the generated code files in windows explorer and paste into the RIAService1.Web project.

GeneratedCode

PasteRIAServicesLibary1Web

Change DomainService1.cs to read:

 

namespace RIAServicesLibrary1.Web
{
    using TejanaA020A.Data.Access; //LoadDataSet, LoadRow, SaveRow, DeleteRow, DeleteDataSet… see PatientSource above
    using TejanaA020A.Data.Base;   //Classes for Data Sources remember to use [Key()] see CustomPatient above
    using TejanaA020A.Data.Common; //AppStatic contains ConnectionString

using System.Collections.Generic;
    using System.Linq;
    using System.ServiceModel.DomainServices.Server;
  
    public class DomainServiceGLpostJournalEntries : DomainService
    {

//POST JOURNAL ENTRIES TO LEDGER

[Query(IsDefault = true)]
        public IEnumerable<DBGlJournalentriesRow> GetJournalEntries()
        {
            BaseDAConn objConn = null;
            DAGlJournalentries objDB = null; //data access class
            DAGlJournalnumbers objDB2 = null;//data access class
            List<DBGlJournalentriesRow> _rows = new List<DBGlJournalentriesRow>();
            try
            {
                objConn = new BaseDAConn(AppStatic.Global.DefaultDASettings);
                objDB = new DAGlJournalentries();
               
                //check for rows with nulls that need to be filled
                //uses data access class to load dataset (collection base)
                //executes a special purpose stored procedure
                DBGlJournalentriesDataset objDS = objDB.LoadDatasetNeedsJournal(objConn);

if (objDS.Count > 0)
                {
                    objDB2 = new DAGlJournalnumbers();

//increment document number in stored procedure
                    //cycles from max small int to 1, has tinyint for document type
                    DBGlJournalnumbersRow objRow2 = objDB2.LoadNextRow(objConn, 1);
                    foreach (DBGlJournalentriesRow objRow in objDS)
                    {
                        objRow.JournalType = 1;
                        objRow.JournalNumber = objRow2.JournalNumber;
                    }

if (objDB.SaveDataset(objConn, objDS))//save changes
                    {
                        objDS = objDB.LoadDataset(objConn);//reload complete dataset
                    }
                }
                else
                {
                    objDS = objDB.LoadDataset(objConn);//load complete dataset
                }
                //copy journal entries to ledger
                if (objDB.PostDataset(objConn, objDS)) //careful not to change Ids in sp
                {                      
                    _rows.AddRange(objDS.Cast<DBGlJournalentriesRow>());//prepare return
                    objDB.UpdateAccountsSummary(objConn);//see detail at top
                    objDB.DeleteDataset(objConn, objDS); // our objDS has unchanged Ids
                }
                objConn.Close();

}
            finally
            {
                if (objConn != null && objConn.IsOpen)
                    objConn.Close();
            }
            return _rows;
        }

}

}

Saving these changes we can return to the LightSwitch application, delete the screens and data source, rebuild and then add our new DomainServiceGLpostJournalEntries service as well as attach to the SQL database from which our data access classes were generated and add two editable grid screens: one for journal entries using the data source from the database and one for journal entries using the WCF RIA data service as source.

Twodatasources

 

 

Right click on the lsApplication1 LightSwitch project and select properties. Go to Screen Navigation and rename EditableDBGlJournalentriesRowGrid to “Post Journal Entries”. Press F5 to run the application in Debug.

 

RunApplication

 

Now we can enter a collection of Journal Entries. Save them, and when we click on Post Journal Entries they are copied to the Ledger with changes, account summaries are updated and posted data is returned for read only display. We have a Microsoft LightSwitch application with easy screen generation for data entry, where LightSwitch knows how to handle the relationships and has ample validation. We can also call up our tables with the data access classes in WCF RIA services, with full access to stored procedures, for business logic, where we control the relationships and the database will throw an error if we upset a foreign key or leave a required field null.

 

Follow

Get every new post delivered to your Inbox.