Application demo - YouTube video
Please find a live demonstration of the features of the application described into this article here:
https://www.youtube.com/watch?v=DO6BQpYu--0
Introduction
A brief introduction to a Real time Spreadsheet, developed in C#, using VS2015, SQL Server 2014, SignalR and Telerik UI components:
The UI component is the Telerik RadSpreadsheet for Silverlight. Exactly the same concepts can be extended to the Web version of the same UI component.
Data is updated and retrieved through a Web Service, responsible to accepts requests and sends responses back.
The request is forwarded to an Application Service Layer (C#) who is responsible to manage the proper Update/Insert/Delete operations.
Finally, the Entity Framework is then used to map entities into the Data Layer.
Main features described into this article:
If two or more users are working on the same spreadsheet, they can see what the other users are doing. Mouse pointing is highlighted by filling the cell with diagonal stripes, while data saved is highlighted via green filling.
The system is capable of calling actions from the Database itself as well. The actions are triggered by proper cells. Once an action is triggered, the database is able to call the Web Service and do proper data changes.
Also those actions are simultaneously broadcast to all the users working on the same spreadsheet.
Background
I would recommend to read a couple of articles regarding SignalR:
http://www.asp.net/signalr/overview/getting-started/tutorial-getting-started-with-signalr
http://stackoverflow.com/questions/15128125/how-to-use-signalr-hub-instance-outside-of-the-hubpipleline
SignalR Initialization - Client Side
Web is full of articles explaining how to setup SignalR. I created the connection and the hub using the following instructions. Methods are appended to the hub; they can be invoked by the Web Service (i.e. "broadCastUpdateResponse" later described) , or by the MainHub from the Server (i.e. "HighlightCell" later described)
string serverUri = WebServer + "/signalr/hubs";
connection = new HubConnection(serverUri, true);
hub = connection.CreateHubProxy("MainHub");
It is important to include the following scripts before:
<script src="Scripts/jquery-1.6.4.min.js" type="text/javascript"></script>
<script src="Scripts/jquery.signalR-2.2.0.js"></script>
<script src="signalr/js" type="text/javascript"></script>
Jquery must be included before SignalR, otherwise the following error would appear:
JavaScript runtime error: jQuery was not found. Please ensure jQuery is referenced before the SignalR client JavaScript file.
Connection is established by calling the Start() method, which is an asynchronous call. Needs then to use the await prefix.
In order to debug connection state changes, it is convenient also to add the following lines
connection.StateChanged += (change) =>
{
System.Diagnostics.Debug.WriteLine("hubConnection.StateChanged {0} => {1}", change.OldState, change.NewState);
if (change.NewState == ConnectionState.Connecting)
{
statusCallBack callBack = new statusCallBack(UpdateStatus);
this.Dispatcher.BeginInvoke(callBack, "hubConnection.Connecting");
}
if (change.NewState == ConnectionState.Connected)
{
statusCallBack callBack = new statusCallBack(UpdateStatus);
this.Dispatcher.BeginInvoke(callBack, "hubConnection.Connected");
}
if (change.NewState == ConnectionState.Disconnected)
{
connection.Start().Wait();
statusCallBack callBack = new statusCallBack(UpdateStatus);
this.Dispatcher.BeginInvoke(callBack, "hubConnection.Disconnected.. reconnectin");
}
};
SignalR Initialization - Server Side
It is important to get the Microsoft.Owin.Cors package, in order to be able to Cross-Origin Resource Sharing (CORS) in OWIN middleware.
Create the following OWINStartup class, as explained in the first article
public class OWINStartup
{
public void Configuration(IAppBuilder app)
{
// For more information on how to configure your application, visit http://go.microsoft.com/fwlink/?LinkID=316888
// Branch the pipeline here for requests that start with "/signalr"
app.Map("/signalr", map =>
{
// Setup the CORS middleware to run before SignalR.
// By default this will allow all origins. You can
// configure the set of origins and/or http verbs by
// providing a cors options with a different policy.
map.UseCors(CorsOptions.AllowAll);
var hubConfiguration = new Microsoft.AspNet.SignalR.HubConfiguration
{
// You can enable JSONP by uncommenting line below.
// JSONP requests are insecure but some older browsers (and some
// versions of IE) require JSONP to work cross domain
// EnableJSONP = true
};
// Run the SignalR pipeline. We're not using MapSignalR
// since this branch already runs under the "/signalr"
// path.
map.RunSignalR(hubConfiguration);
});
}
}
Application features explained
User updates the cell - Client Side
User can navigate through the Spreadsheet, and once he finds a cell that he needs to update, he enters the proper value. Since the Spreadsheet cells have a CellPropertyChanged event, once this event is fired, the following code will simply call the Web Service to update the desired data into the Database:
private async void Cells_CellPropertyChanged(object sender, CellPropertyChangedEventArgs e)
{
int i = e.CellRange.FromIndex.RowIndex;
int j = e.CellRange.FromIndex.ColumnIndex;
for (i = e.CellRange.FromIndex.RowIndex; i <= e.CellRange.ToIndex.RowIndex; i++)
for (j = e.CellRange.FromIndex.ColumnIndex; j <= e.CellRange.ToIndex.ColumnIndex; j++)
{
CellRange cr = radSpreadsheet.ActiveWorksheet.Cells[i, j].CellRanges.FirstOrDefault();
if (cr != null)
{
bool updated = await ViewModel.UpdateValue(cr);
}
}
}
User updates the cell - Server Side
The Web Service accepts the request and calls the ApplicationService which updates the proper tables.
Once the update is finished, the WebService serializes the Response back to the Client.
[HttpPost]
[Route("Instances/Update/{id}")]
public async Task<IHttpActionResult> Update(int id, UpdateInstanceRequest request)
{
UpdateInstanceResponse response = await _service.UpdateInstance(request);
string serializedResponse = Microsoft.AspNet.SignalR.Json.JsonSerializerExtensions.Stringify(new Newtonsoft.Json.JsonSerializer(), response);
// Get SignalR context
var context = Microsoft.AspNet.SignalR.GlobalHost.ConnectionManager.GetHubContext<MainHub>();
// Invoke the Client method
context.Clients.All.updateResponse(serializedResponse);
if (response == null)
{
return NotFound();
}
else
{
return Ok(response);
}
}
Notice that the SignalR allows to call the methods on the Client, and eventually to initialize the context from the Server.
In this case i broadcast the message to all clients. It in general possible to divide Clients in Groups and then send to message to a subset of them.
Response back - Client Side
The action invoked by the server ("updateResponse") is added to the hub and implemented on the client side, in order to respond to the Web Service call when an cell has been updated by a user
hub.On<string>("updateResponse", (output) =>
{
this.Dispatcher.BeginInvoke(() =>
{
//Parse Json
var getAnonymousType = new { Instance = new { Attribute = new { Id = 0, Type = 0 }, ModifiedTime = new DateTime(), Id = (long)0, Value = "", Locked = false } };
var instanceUpdated = Newtonsoft.Json.JsonConvert.DeserializeAnonymousType(output, getAnonymousType);
//Get ViewModel and Cell
SpreadsheetViewModel svm = (SpreadsheetViewModel)this.DataContext;
//Updating Cell via SignalR
svm.updateCellSignalR(instanceUpdated.Instance.Id, instanceUpdated.Instance.Value, instanceUpdated.Instance.ModifiedTime, instanceUpdated.Instance.Locked);
});
});
The object is deserialized using JsonConvert, and the result is passed to the ViewModel.
The method "updateCellSignalR" in the Spreadsheet View Model, is called in order to find if there exists a cell with the Instance Id just updated. In this case, the actual cell is updated and filled with the proper colour ("green" in the example)
public void updateCellSignalR(long inst_id, object value, DateTime modified_time, bool locked)
{
Cell refreshCell = CurrentSheet.Cells.Where(w => w.ID == attr_inst_id).FirstOrDefault();
if (refreshCell != null)
{
refreshCell.ModifiedTime = modified_time;
refreshCell.IsLocked = locked;
setCellValue(refreshCell);
setCellPropertyColour(refreshCell, true);
}
}
Cells highlight - Client Side - part 1
User is free to move around the Spreadsheet cells using the arrow keys or the mouse click. In this case i added a method which will highlight the cell if this is pointed by the mouse click (the same principle applies to the arrow keys case)
The method just retrieves Cell row and column and, using the Hub, it invokes the "SelectedCell" method on the Server, passing the ActiveSheet Name and the position of the cell.
private async void PresenterMouseDown(object sender, MouseButtonEventArgs e)
{
int row = this.radSpreadsheet.ActiveWorksheetEditor.Selection.Cells.CellRanges.FirstOrDefault().FromIndex.RowIndex;
int column = this.radSpreadsheet.ActiveWorksheetEditor.Selection.Cells.CellRanges.FirstOrDefault().FromIndex.ColumnIndex;
if(connection.State == ConnectionState.Connected)
hub.Invoke("SelectedCell", this.radSpreadsheet.Workbook.Sheets.ActiveSheet.Name, row, column );
}
Cells highlight - Server Side
The MainHub contains the implementation of the "SelectedCell" method invoked by the Client.
This method simply forwards the informations to all the "Other" clients. Where, by "other", i mean all the Clients except the caller.
public void SelectedCell(string sheetname, int row, int col)
{
// Call the broadcastMessage method to update clients.
Clients.Others.HighlightCell( sheetname, row, col);
}
Cells highlight - Client Side - part 2
The hub implementation on the Client side contains the implementation of the "HighlightCell" method invoked by the Server.
This method simply checks if the current sheetname is the same , and fills the proper cell with diagonal stripes
hub.On<string, int, int>("HighlightCell",
(sheetname, row, col) =>
{
this.Dispatcher.BeginInvoke(() =>
{
//Get ViewModel and Cell
SpreadsheetViewModel svm = (SpreadsheetViewModel)this.DataContext;
svm.highlightCellSignalR(sheetname, row, col);
});
});
private Cell _previousHighlight;
internal void highlightCellSignalR(string sheetname, int row, int col)
{
Cell refreshCell;
if (SpreadSheetWorkbook.ActiveSheet.Name == sheetname)
{
refreshCell = CurrentSheet.Cells.Where(w => w.RowIndex == row && w.ColumnIndex == col).FirstOrDefault();
if (refreshCell != null)
{
TelerikExcel.PatternFill diagonalStripePatternFill = new TelerikExcel.PatternFill(TelerikExcel.PatternType.DiagonalStripe, Color.FromArgb(120, 231, 76, 60), Color.FromArgb(120,241, 196, 15));
_spreadSheetWorkbook.ActiveWorksheet.Cells[row, col].SetFill(diagonalStripePatternFill);
if (_previousHighlight != null)
setCellPropertyColour(_previousHighlight, false);
this._previousHighlight = refreshCell;
}
}
}
Notice that it is needed to store the previous cell highlighted, in order to revert its fill back to the original one.
Points of Interest
The previous points describe how to setup SignalR on both client and server. It points out to use Cors in order to enable Cross-Origin Resource Sharing (CORS) in OWIN middleware.
It then explains how to use SignalR to build a real time application, using a Telerik RadSpreadsheet.
The hub is used in a 'classic' way, where the Client uses the call the server, and the server broadcasts the message back to all the clients (like the simple 'chat' room mechanism)
Then, the same hub is used by a WebService to broadcast the message to all Clients connected, in order to share the data updated. It must instantiate the hub context using the Globalhost, but it demonstrates how it is possible to instance the hub outside of the hubpipeline.
In the last part of the video, the Web Service is called by the Database itself, so you can see how the spreadsheet values change as soon as the web service receives the request from the database, and sends back the response to all clients.
Isn't it awesome? 
