Tejana

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.

 

Advertisements

2 Responses

Subscribe to comments with RSS.

  1. srikar said, on November 24, 2011 at 11:18 am

    Hi Tejana this is Srikar am doing a application in light switch. in this i have a grid i want to handle add,update and delete methods. light switch have it.but my requirement is i want to select a record from the grid then it should come as a popup.


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: