Using .Net MVC and Sencha Touch: syncing localStorage and remote storage with Ext.ux.OfflineSyncStore

Since writing the post an example of syncing localStorage data with remote storage,  I wanted to improve on  how to get data stored locally on a Sencha Touch app. I was sure there was a better way to do the syncing  and that also the sync wasn’t true syncing as it only read in the data from the remote database. It didn’t write back. So this  new project will take it a stage further. The premise is the same,  that the user only acts on the localStorage and any interaction with remote storage is confined to the sync. The difference is that the syncing is both ways, so reading in and writing back to the database. This project took lots of attempts to get right. To begin with I extended the code from the first post,  loading localStorage from remote storage on app launch and then adding entries to localStorage then syncing these to remote….But found it became too complex to manage.

I then found the Ext.ux.OfflineSyncStore plugin created by Stuart Ashworth of Swarm Online. A really simple to use library which does exactly what I wanted…It gets data from a server when online, puts it into localstorage (offline) then syncs back any changes to the data. Stuart kindly provides an example to get started with this, using a Person Model containing FirstName, LastName and EmailAddress as the fields. In this post I explain how I took the example and included it in a simple Sencha Touch app that lists the entries in the Person store and allows you to add entries. For the back end I decided to use an Asp.Net MVC project. I have found using the framework to be surprisingly easy and had a working back end in a matter of hours. These are the things I’m going to cover.

  • Database
  • ASP.Net MVC backend
  • Sencha Touch project

The code for the whole project is on my github space at https://github.com/lalexgraham/.NetMvcSenchaTouch2OfflineSyncStore . The sencha code can be found in https://github.com/lalexgraham/.NetMvcSenchaTouch2OfflineSyncStore/tree/master/Person/Mobile

Database

I used sql server 2008 express to host the database. To create the database right click on Databases and add a new database. Call the database “Person”.

Next create one table to hold the data:

USE [Person]
GO

/****** Object: Table [dbo].[tblPerson] Script Date: 06/09/2013 12:43:38 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[tblPerson](
 [PersonID] [int] IDENTITY(1,1) NOT NULL,
 [FirstName] [varchar](50) NULL,
 [LastName] [varchar](50) NULL,
 [Email] [varchar](100) NULL,
 CONSTRAINT [PK_tblPerson] PRIMARY KEY CLUSTERED
(
 [PersonID] 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

ASP.Net MVC backend

Once the database has been built, you can build the .Net MVC project. I am using VS Express for Web 2012 to build the solution. Open VS2012 and click on New Project on the Start Menu.

A project dialog opens, select ASP.Net MVC 4 Web Application as the project. Name it Person. The solution should follow the same naming. Click on OK when done.

The following dialog asks you to select a template to work from. Select WebAPI as the template. Leave the View Engine as Razor and the “Create a unit test project” unchecked.

The project will then be setup. When finished, the Solution Explorer should be open on the right of the screen. First you need to add a Model. Right click on the Models folder and select Add>ADO.NET Entity Data Model.  A new wizard opens to set up a database connection. Leave Create from Database highlighted and click on Next.

The next screen will ask to set up a connection to a database. Click on New Connection. Enter the server to connect to and the the Database.

The final screen will ask what Database objects will be included in the Model. Just select tblPerson in the Tables section  and leave the Model Namespace as PersonModel. Click on Finish. The Model will be built.

There may be some errors listed after adding the Model. Ignore these and just build the solution. The errors should disappear.

The next task is to add a controller to provide restful URLS in order to select, add and delete from the tblPerson table.  To begin adding the controller, right click on the Controllers folder and select Add>Controller:

A new dialog appears, within which you enter the config to setup the controller.For the name, enter PersonController. For the Template in Scaffolding Options, choose MVC Controller with Read/Write actions and views, using Entity Framework. For the Model, choose tblPerson(Person) and finally for the Data Context Class choose PersonEntities (Person). Select None for the Views drop down, then click on Add.

Once the controller has been added there are a few amends to make to the code so that the data being sent and returned is in json format . First of all we want to return json formatted data to be returned by the Index() function.  To do this replace

Function Index() As ActionResult
 Return View(db.tblPersons.ToList())
End Function

with

Function Index() As JsonResult
 Return Json(db.tblPersons.ToList(), JsonRequestBehavior.AllowGet)
End Function

Build the solution and then debug . Browse to the http://localhost:xxxx/Person url , and you should see the data in tblNames outputted as json

Now just one more amend is required to the code. Change the create (post function) from

<HttpPost()> _
Function Create(ByVal tblperson As tblPerson) As ActionResult
 If ModelState.IsValid Then
 db.tblPersons.Add(tblperson)
 db.SaveChanges()
 Return RedirectToAction("Index")
 End If
Return View(tblperson)
 End Function

to this code

<HttpPost()> _
 Function Create(ByVal tblPersons As List(Of tblPerson)) As JsonResult
 ' If ModelState.IsValid Then
 For Each p In tblPersons
 db.tblPersons.Add(p)
 db.SaveChanges()
 Next
 'End If
 Return Json(New With { _
 .success = True, _
 .rows = tblPersons.Count _
 })
 End Function
<pre>

so again just jsoning what is going in and out….

The project is now complete.  You can continue to work in debug mode or publish the .net code to somewhere where you can then add in the Sencha code. I will continue to use the debug url, but to publish the app, right click on the project in Solution Explorer  and select Publish. The Publish dialog opens . You need to add a publish profile , which can be named anything.

Next select Publish to File System and navigate to where you want the project published.

Now in IIS Manager, add a new application off Default Web Site and call it something short but meaningful. Complete the config as shown in the following screen shot.

iisConfig

Once saved, browse to your localhost url and you should see the default homepage for the app:

DefaultMvcNamesHomepageOnLocalhost

 Sencha Touch project

Now the back end is out the way we can start on the Sencha Touch application.Go back to your project in VS 2012  and add a new folder off root called Mobile or Sencha  if preferred to drop the Sencha Touch  code into.  The code is up on github so I won’t show it all here. But a quick explanation is  I used Stuart’s example code but moved PersonModel.js into the model sub folder and edited the namespace. I then added the Ext.ux.OfflineSyncStore js files to the root and a TabPanel view, a store (MyStore) which extends the OfflineSyncStore, and a controller to handle events and do the syncing….

Start a debug session through Visual Studio and navigate to the sencha app at  http://localhost:xxxx/Mobile/index.html. You will see the form on the first tab to enter details. On the View Data tab there will be no data listed as none has been entered.  Add a user on the form and click Add.  Open up dev tools on chrome and view Resources tab. You will see the data just added in localStorage….now check the database. No data still. You need to press the Sync Data button to move the record(s) across . I’ve done this to show how to sync  the data in a separate function rather than mixing it in with the SaveData function. It uses the .syncServer() method. Instead of clicking a button this could just be checking whether the device is online or offline.

Thanks to Stuart Ashworth for helping me out with a few bugs.

Please see this post on how to view the site on other devices through localhost.

Advertisements

Adding dynamic controls

Within an existing .net system at work, there was a requirement to generate dynamic asp.net controls. Depending on certain business factors, the controls for data entry would either be textboxes or dropdownlists. In both cases the fields would be mandatory.

The solution involved database tables which contained the configuration for each setup, i.e txtboxes or dropdownlists and whether or not the field was mandatory. On entering the aspx page the code behind checks the configuration, creates the controls dynamically and adds them to a placeholder.

Get Working Hours using .NET

At work there was a requirement to count working hours from an arbitrary event to the current time. At first I tried to do this with sql, using this as a starting point, but the processing required was too intensive. The alternative was to work it out in code. With the help of this post I came up with the following vb with the judicious use of the structure System.Timespan to get the number of hours. The start point is the function NumberOfWorkingHours :

Public Function NumberOfWorkingHours(ByVal dtStartDate As DateTime, ByVal dtEndDate As DateTime) As Integer

Dim intWorkingHours As Integer

Dim boolSameDate As Boolean = False

Dim intNumberOfWorkingHoursPerDay As Integer = 8
Dim strStartTime As String = "09:00:00"

' set both dates to midnight
Dim dtTempStart As DateTime = CDate(dtStartDate.ToShortDateString)
Dim dtTempEnd As DateTime = CDate(dtEndDate.ToShortDateString)

' check if its the same day
If (dtTempStart = dtTempEnd) Then
intWorkingHours = Math.Floor((LastDayNumberOfWorkingSeconds(dtTempEnd, strStartTime, intNumberOfWorkingHoursPerDay) / 60 / 60))
Else
' get number of hours for first day
intWorkingHours = Math.Floor((FirstDayNumberOfWorkingSeconds(dtStartDate, strStartTime, intNumberOfWorkingHoursPerDay) / 60 / 60))
' get number of hours for last day
intWorkingHours += Math.Floor((LastDayNumberOfWorkingSeconds(dtEndDate, strStartTime, intNumberOfWorkingHoursPerDay) / 60 / 60))
' get number of hours of full Working days between two dates
intWorkingHours += (WorkingDaysExcludeWeekendsAndHolidays(dtTempStart.AddDays(1), dtTempEnd.AddDays(-1)) * intNumberOfWorkingHoursPerDay)

End If

Return intWorkingHours
End Function

Private Function WorkingDaysExcludeWeekendsAndHolidays _
(ByVal dtStartDate As Date, ByVal dtStopDate As Date) As Integer

Dim intActualDays As Integer
Dim intWorkingDays As Integer
Dim dtNextDate As Date
Dim x As Double

If dtStopDate 0 Then
intWorkingDays = intWorkingDays - 1
End If
Next

Return intWorkingDays

End Function

Private Function FirstDayNumberOfWorkingSeconds(ByVal dt As DateTime, ByVal strStartTime As String, ByVal intWorkingHoursPerDay As Integer) As String
Dim tsTimeSpan As TimeSpan
Dim intReturnValue As Integer = 0

Dim dtMinTime As DateTime = dt.ToShortDateString & " " & strStartTime
Dim dtMaxTime As DateTime = dtMinTime.AddHours(intWorkingHoursPerDay)

' Response.Write("DateTime entered:" & dt.ToString() & "
")
' Response.Write("Start date time:" & dtMinTime.ToString() & "
")
' Response.Write("End date time:" & dtMaxTime.ToString() & "
")

If (dtMaxTime < dt) Then ' start time is after Working close time

intReturnValue = 0
Else

'If dt.DayOfWeek = System.DayOfWeek.Saturday Or dt.DayOfWeek = System.DayOfWeek.Sunday Then

If DayIsWeekendDayOrBankHoliday(dt) Then

intReturnValue = 0

Else
If (dt < dtMinTime) Then ' start time is before Working start time

dt = dtMinTime

End If

tsTimeSpan = dtMaxTime.Subtract(dt)
' intReturnValue = tsTimeSpan.Hours
intReturnValue = CInt(Math.Floor(tsTimeSpan.Ticks / TimeSpan.TicksPerSecond))

End If

End If

Return intReturnValue

End Function

Private Function LastDayNumberOfWorkingSeconds(ByVal dt As DateTime, ByVal strStartTime As String, ByVal intWorkingHoursPerDay As Integer) As String
Dim tsTimeSpan As TimeSpan
Dim intReturnValue As Integer = 0

Dim dtMinTime As DateTime = dt.ToShortDateString & " " & strStartTime
Dim dtMaxTime As DateTime = dtMinTime.AddHours(intWorkingHoursPerDay)

'Response.Write("DateTime entered:" & dt.ToString() & "
")
'Response.Write("Start date time:" & dtMinTime.ToString() & "
")
'Response.Write("End date time:" & dtMaxTime.ToString() & "
")

If (dtMinTime > dt) Then ' end time is before Working start time

intReturnValue = 0
Else

If DayIsWeekendDayOrBankHoliday(dt) Then

intReturnValue = 0

Else
If (dt > dtMaxTime) Then ' end time is after Working end time

dt = dtMaxTime

End If

tsTimeSpan = dt.Subtract(dtMinTime)
' intReturnValue = tsTimeSpan.Hours
intReturnValue = CInt(Math.Floor(tsTimeSpan.Ticks / TimeSpan.TicksPerSecond))

End If

End If

Return intReturnValue

End Function

Private Function DayIsWeekendDayOrBankHoliday(ByVal dt As DateTime) As Boolean
dt = CDate(dt.ToShortDateString)
If dt.DayOfWeek = DayOfWeek.Saturday Or DayOfWeek.Sunday Then
Return True
Else
For Each dtBankHoliday As Date In ReturnBankHolidays()
If dtBankHoliday = dt Then
Return True
End If
Next
Return False
End If
End Function

Private Function ReturnBankHolidays() As Array

Dim BankHolidays(32) As Date
'2009
BankHolidays(0) = #1/1/2009# 'New Year's Day
BankHolidays(1) = #4/10/2009# 'Good Friday
BankHolidays(2) = #4/13/2009# 'Easter Monday
BankHolidays(3) = #5/4/2009# 'May Bank Holiday
BankHolidays(4) = #5/25/2009# 'Spring Bank Holiday
BankHolidays(5) = #8/31/2009# 'Summer Bank Holiday
BankHolidays(6) = #12/25/2009# 'Christmas Day
BankHolidays(7) = #12/28/2009# 'Boxing Day * sub day
'2010
BankHolidays(8) = #1/1/2010# 'New Year's Day
BankHolidays(9) = #4/2/2010# 'Good Friday
BankHolidays(10) = #4/5/2010# 'Easter Monday
BankHolidays(11) = #5/3/2010# 'May Bank Holiday
BankHolidays(12) = #5/31/2010# 'Spring Bank Holiday
BankHolidays(13) = #8/30/2010# 'Summer Bank Holiday
BankHolidays(14) = #12/27/2010# 'Christmas Day * sub day
BankHolidays(15) = #12/28/2010# 'Boxing Day * sub day
'2011
BankHolidays(16) = #1/3/2011# 'New Year's Day * sub day
BankHolidays(17) = #4/22/2011# 'Good Friday
BankHolidays(18) = #4/25/2011# 'Easter Monday
BankHolidays(19) = #5/2/2011# 'May Bank Holiday
BankHolidays(20) = #5/30/2011# 'Spring Bank Holiday
BankHolidays(21) = #8/29/2011# 'Summer Bank Holiday
BankHolidays(22) = #12/26/2011# 'Christmas Day * sub day
BankHolidays(23) = #12/27/2011# 'Boxing Day * sub day
'2012
BankHolidays(24) = #1/2/2012# 'New Year's Day * sub day
BankHolidays(25) = #4/6/2012# 'Good Friday
BankHolidays(26) = #4/9/2012# 'Easter Monday
BankHolidays(27) = #5/7/2012# 'May Bank Holiday
BankHolidays(28) = #6/4/2012# 'Spring Bank Holiday * sub day
BankHolidays(29) = #6/5/2012# 'Queens Diamond Jubilee Bank Holiday
BankHolidays(30) = #8/27/2012# 'Summer Bank Holiday
BankHolidays(31) = #12/25/2012# 'Christmas Day
BankHolidays(32) = #12/26/2012# 'Boxing Day

Return BankHolidays
End Function