Title: INDUSTRIAL DASHBOARD Company: www.australtek.com Author: Juan Sagasti / Sebastian Endler / Nicolas Lareu Email: jsagasti@australtek.com Language: C#, javascript Platform: .NET, SQL Server Technology: WCF , AJAX, json Level: Advanced Description: The code implements a WCF service that allows direct communication between javascript widgets and Stored Procedures. License: MIT
Industrial Dashboard
A modular SP to JSON framework based on WCF
Introduction
The framework allows the execution of stored procedures straight from javascript code, and enables the use of data driven javascript widgets.
Why Stored Procedures
Several database software projects are based on stored procedures and some of them implement most of the business rules directly in the stored procedures themselves, thus creating a rock solid and centrally managed interface between the user and the data. Implementing the business layer in SP has many advantages like faster execution, natural relationship with database objects and a very simple RPC interface.
Why JSON
Usually the user interface of these systems is web-based and if the system is fairly modern is very common that it uses ajax/json or ajax/xml to retrieve the data. We chose JSON over XML because is much lighter and faster on the client.
Why javascript widgets
There are several javascript frameworks that include visual widgets The combination of the async nature of AJAX and the visual appeal of the widgets give an improved experience to the user (compared to the old html postback methods), almost comparable to having a fat client installed.
Why this project
Usually a database centric system is composed of three layers: the database itself, a middle tier layer written in C# or Java and the User Interface. Most of the time a change in any of the layers creates the need to update the 3 layers, which is something we'd like to avoid. With our approach we eliminate the need to synchronize the middle tier.
In this article we present a Windows Communication Foundation service that allows the execution of procedures straight from javascript code, enabling data driven js widgets to send/receive data without having to manually code the typical C#/Java interfaces .
We also introduce a set of interfaces used to develop modular widgets and a couple of sample html pages with open source widgets aimed to demonstrate the characteristics of the solution. The solution is based on SQL Server 2005 + WCF + javascript but the approach is also valid for other platforms as well.
System Architecture

The stored procedures are accessed by the presentation layer either through standarized widgets or directly.

If the call is through a widgetAdapter class, the procedure must be built according to the
widgetAdapter specification, usually requiring more than one table and
specific column names and types. The dataset returned is transformed in the adapter to a
widget json structure by the method GetResults() that all widgetsAdapters must implement.
The Chart.cs code is an example of a WidgetAdapter class
If the call does not specify widgetAdapter class, the following generic json structure is
returned:
{ 'table1' : { 'row1': { 'column1':'value11', 'column2':'value12', 'column2':'value13', }, 'row2': { 'column1':'value21', 'column2':'value22', 'column2':'value23', }, 'row3': { 'column1':'value31', 'column2':'value32', 'column2':'value33', } }, 'table2' : { 'row1': { 'column1':'value11', 'column2':'value12', }, 'row2': { 'column1':'value21', 'column2':'value22', } } }And in this case the data can be manually used in the javascript (see Dojo DataGrid example)
App_Code/DALService.cs
This is the core class of the system and implements the following interfacepublic interface IDALService{ [OperationContract, WebInvoke(UriTemplate = "CallProcedure")] Stream CallProcedure(Stream Parameters); [OperationContract, WebInvoke(UriTemplate = "GetFile")] Stream GetFile(Stream Parameters); }
The parameters of these methods are not declared explicitly in the interface because we wanted to deserialize them using JavaScriptSerializer instead of the WCF standard DataContractJSONSerializer. These two methods can be called directly from javascript code and they return javascript objects.
The first method executes a parametrized stored procedure and return the results as a json-dataset or with a widget specific format.
public class SQLParameter { public string Name { get; set; } public SqlDbType Type { get; set; } public Object Value { get; set; } } public class CallProcedureParameters { public String Namespace { get; set; } public String Class { get; set; } public ListSQLParameters { get; set; } public String Procedure { get; set; } }
The CallProcedureParameter class define the parameters to be sent to CallProcedure, if Namespace and Class are not sent, the DALService will return the resulting dataset as a set of tables.
The SQLParameter class has its counterpart in javascript and must be used to send named parameters to the SP.
Scripts/IndustrialDashboard.js
This file contains some classes and helper functions, it needs to be included in all html pages. The most important helper function is RequestJSON, this function can be changed if you decide to use jquery or even write your own Request/JSON call in javascript
Styles/IndustrialDashboard.css
Implements some basic default styles for the pages, is not really necessary if you are not going to use the provided widgets
Widgets Architecture
Widget Structure
Each widget is installed in its own directory under the Widgets directory. And they all have the same structure:
- Scripts/widget.js : Is the javascript code of the widget and implements the functionality.
- Style/widget.css : Implements the style and images of the widget
- DLL/widget.dll : A C# or VB library that implements the IWidget interface
- Test/widget.html : A unit test for the widget
Widgets are divided in two groups: filters and reports, filters are used by the users to specify parameters and reports are used to display data. A typical page would contain some filters and one or more reports
Installing the framework
In order to run this example you must have access to an IIS 6 or higher and a SQL Server with a test database.
You can use Visual Studio since it comes with a light version of IIS and a light version of SQLServer called SQLExpress, just open the project by using "File->Open -> Web Site" and perform only steps 2 and 3.
1 - Installing the Website
Copy the contents of the zip file to a directory, like, for example "C:\Inetpub\IndustrialDashboard"
In IIS 6 create a website or add a virtual directory to an existing one and point it to C:\Inetpub\IndustrialDashboard and, in the virtual directory properties, select "Create Application"
In IIS7 create a website or add an application to an existing one and point it to C:\Inetpub\IndustrialDashboard .
In some installations we encountered some permissions problems that we solved by changing the Application Pool of the application
2 - Create sample data and procedures
You will also need to have a running SQL Server and execute the Samples\sample.sql script on a test database called IndustrialDashboard.
If you don't have SQL Server Management studio you might need to use the provided utility osql.exe.
osql.exe -E -S localhost\SQLEXPRESS -d IndustrialDashboard -i sample.sql
The default name of the test database is IndustrialDashboard and is specified in the first line of sample.sql, if you want to use a different database just edit that line and on the next step change the connection string accordingly. The script will create a few tables and stored procedures.
3 - Modifying the connection string/h3>
You'll need to modify the connection string in the web.config to match the database created in step 2, below is an example of how it looks like:
<connectionStrings> <add name="IndustrialDashboard" connectionString="Data Source=Host\ServerInstance; Initial Catalog=Database; Integrated Security=SSPI; providerName="System.Data.SqlClient"/> </connectionStrings>
Host : Is the computer name of the server where SQL Server is installed, it can be an IP address or simply "localhost"
ServerInstance: If If the SQL Server instance is named, you'll need to specify its name. If you use Visual Studio the instance name would be SQLEXPRESS.
Database:The nThe name of the database where your data is stored, not necessary if your stored procedures are specified with fully qualified names
4 - Give the application write permissions on Logs directory
The system writes debugging and audit information to logs files using the standard log4net library. In order to make it work the library needs to have write access to the directory where the log files are stored. The location of the Logs directory can be changed in the web.config if needed.
We need to give the application user, usually ASPNET (IIS 6) or IIS_IUSRS (IIS7) permissions to this directory. Right click on the Logs directory and look for Properties->Security to include the application user and give it write permission.
5 - Create Login in SQL and give execute permissions
The best method to authenticate with the database is to use Integrated Security, but this will require creating the proper Login (IIS_IUSRS/ASPNET) in the SQL Server and then create the user on the database and assign the owner role to it. The Log files (located in the directory Logs) will help determine what can be failing if the database can not be accessed.
Additional Information:
5 - Checking that everything is installed OK
Open your preferred browser (IE7+, Opera, Chrome , FF) and point to the virtual directory you just created in step 1 : http://localhost/IndustrialDashboard/default.htm and you should see the default.htm page that shows some of the demos installed
Points of Interest/h2>
This project is the foundation where we are building a more complete framework, including especially designed UI widgets, authentication and session maangement, and the ability to export data directly to PDF and Excel files.
For more information refer to our company website