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.

Advertisements

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.

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.

 

LightSwitch and Reporting Services

Posted in .net, LightSwitch, Microsoft, Software, SQL by tejana on November 15, 2010

LightSwitch SQL Server Reporting Services Control

This control extension for Visual Studio 2010 will allow using reporting services as the report writer in Microsoft LightSwitch Beta 1 applications. Reporting services is available with SQL Express for free. Way Cool!

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.

Microsoft Dynamics GP Payroll Report, SQL Procedure, One to Many in two columns

Posted in Microsoft, Software, SQL by tejana on April 24, 2007

Stored Procedure Reporting One to Many in Two Colmuns

Database designers like one-to-many relationships, report writers don’t !

The report has to meet a deadline and I would prefer to just whip up a custom report meeting boss man’s request than struggle with the existing report. The main record is simple enough representing the totals of a single payroll check. It is the details, now that’s a challenge. There are many detail records for each check in a separate table. I am only interested in three detail variables but I cannot set a limit to the number of detail records per check.

Being new to all this SQL and Reporting Services my first attempt used a join of the check summary with the detail showing one set of three detail data points with the 10 data points I wanted from the check. The resulting report was hundreds of pages long with one small font check summary followed by an extra small font line for each detail record. The sample data shown below was not too bad but the actual data had 12 or more detials for each check making for a very long report. I wanted to display two colmuns of detail records under each summary record so I could cut the length of the report.

 Not knowing how I could get the Reporting Services to efficiently show the many details under each check I set about to create two sets of the details joined to the check summary as the driving data stream for the report:

 CheckNo, CheckDate, EmpNo, Gross…Code1, Amt1, Code2, Amt2.

 By creating two temporary tables with the detail data, one with an (1,1) identity and a second with a (0,1) identity, a join on the identities could output the two sets of data points. Restricting the join to detail elements of the same check summary records by check number ensures that the output has null for the last detail data point on checks with an odd number of details:

— table1’s detail loads second column from table2

— join on check and index offset puts next detail or null in column2

UPDATE #TEMP1

              SET #TEMP1.PYRLRTYP2 = #TEMP2.PYRLRTYP,

                  #TEMP1.PAYROLCD2 = #TEMP2.PAYROLCD,

                  #TEMP1.UPRTRXAM2 = #TEMP2.UPRTRXAM

FROM   #TEMP1 INNER JOIN #TEMP2 ON

         #TEMP1.MY_ROW_ID = #TEMP2.MY_ROW_ID  AND — identities

         #TEMP1.AUCTRLCD = #TEMP2.AUCTRLCD AND    — audit ctl

         #TEMP1.CHEKNMBR = #TEMP2.CHEKNMBR        — check no

But alas we have every detail record twice. We want to eliminate every other output record for each check summary check number. By setting a flag for the first record of each check:

— set int flag to one for each first detail row per check

— (where the previous row is a different payment number)

UPDATE #TEMP1 SET MY_FLG  =      1

FROM    #TEMP1 INNER JOIN #TEMP2 ON

            #TEMP1.MY_ROW_ID = #TEMP2.MY_ROW_ID + 2 AND

            #TEMP1.PYADNMBR <> #TEMP2.PYADNMBR

— set int flag to one for each first detail row per check

— (where the previous row is a different payment number)

We can then start an count tracking the number of times the unique check number occurs in our many detail records:

— new index starting from 1 at each flagged row

–(counts the many details for each check)

DECLARE @track int

SET @track = 0

UPDATE #TEMP1

    SET @track = CASE WHEN MY_FLG = 0

                        THEN @track + 1 ELSE 1 END,

    MY_ROW_ID2 = @track

 The even count records are deleted:

DELETE #TEMP1

WHERE

MY_ROW_ID2 % 2 = 0

and the temporary table temp1 can be joined to the check summary table to prepare for presentation to the Reporting Services Report.

  A sample page: A sample page       Full Report .pdf

The resulting stored procedure:

Stored Procedure .txt  Stored Procedure pdf    Stored Procedure .doc

Surely if this interests you then you must know a better way to do this, please comment.