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.
Then click on the RIAServicesLibrary1.Web project and add a new item of type Domain Service Class, named “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.
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.
LightSwitch will import the data source object into the application designer and we can quickly add a search screen to display our 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.
I take the generated code files in windows explorer and paste into the RIAService1.Web project.
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.
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.
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.