Tejana

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.

 

Data Types in LightSwitch Beta 1

Posted in .net, LightSwitch, Microsoft, Software, SQL, Visual Studio by tejana on November 5, 2010

A Simple Definition List for new LightSwitch Users

Thanks to Steve Anonsen for the data type table for LightSwitch. As a consolidator of  data sources, LightSwitch converts incoming schema to the available data type options. Notice that when creating a new table inside of LightSwitch’s entity designer the non optional initial column is named “Id” , of type int32, and is required. By attaching to a database other data types are allowed but are translated to the designer’s available data type options. For attached database models, for example, a small money is represented in LightSwitch as a decimal with 10 as precision and 4 as scale. Changing the type on the entity designer to the “business type” money, retains storage as a decimal and adds formatting and additional behavior in LightSwitch. This will have to be done for each business type field in an attached Sql database. The ability to add business types is expected in later releases.

smallmoney

 

When building the model in LightSwitch. Tables are created in SQLExpress to represent the entities along with the application tables in “applicationdatabase.mdf” in bin/data or bin/data/temp. The particulars of the mdf file in Beta 1 can be viewed in Sql Server Management Studio. One can also, publish the application:

Publish

 

PublishWizard1

 

DataBaseNow

 

The Publish Application Wizard gives the option to Publish directly to the database now. You  can publish to see the tables in Sql Server Management Studio. LightSwitch uses nvarchar() to store strings unless you attach to a database.