Additional Information

Direct Execution vs Widgets

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

widorgeneric.png

Direct Execution

If the call is direct the following generic json structure is returned:

    { 
        'table' : [  {  'columnName1':'value11',
                          'columnName2':'value12',
                          'columnName3':'value13' },
                       {   'columnName1':'value21',
                           'columnName2':'value22',
                           'columnName3':'value23' }
                    ],
        'table1' : [ .....]
    } 


This way the following javascript code is able to get values from the database
    var params = new SQLParameters();
    params.add('CountryID','Int',5);
    var options = new Object();
    options.data = params.getSQLParameters();
    options.data.Procedure = 'dbo.getCitiesListTR'
    options.onSuccess = function(requestData) {
         document.write(requestData.Table[0].Name);
    };
    RequestJSON( '../DALService.svc/CallProcedure',options);

Widget Execution

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:

In this case, the javascript code would be like this, you must have a div called 'mydiv' in the html
 attachDomReadyEventHandler(function(){
            var SQLParams = new SQLParameters();
            SQLParams.add('CountryID', 'INT', 5);
            var options = { 'URL': '../../../DALService.svc/CallProcedure', 'Procedure': 'dbo.getCitiesListTR' };
            var trWidget = new TabularReport(document.getElementById('mydiv'), options);
            trWidget.refreshData(SQLParams);
        });

and the SQL Stored Procedure would look like this
CREATE PROCEDURE [dbo].[getCitiesListTR]
@CountryID int = 0
AS
BEGIN
	SELECT * FROM dbo.tblCities 
	WHERE CountryID = @CountryID
END

App_Code/DALService.cs

This is the core class of the system and implements the following interface

public 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 by using JSON.Request and they return javascript objects.

CallProcedure executes a parametrized stored procedure and return the results as a json-dataset or with a widget specific format.
GetFile returns a json static file from the filesystem, it is used mainly for test purposes.


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 javascript counterpart and must be used to send named parameters to the SP.
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 List SQLParameters { get; set; }
    public String Procedure { get; set; }
}

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, most of the styles are set in the widget's css files.

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.

The filters return their results through the method -GetResults()- , the results are formatted as a list of SQLparameters, in the exact same way the report widgets need to refresh their contents, so a typical interaction between the filter and the report would look like this

var filter = new FilterWidget(div1, ....);
var report = new ReportWidget(div2, {'Procedure':'myProc' });

filter.onChange = function() {
    report.RefreshData(filter.GetResults());
}



Last edited Jun 24, 2010 at 6:47 PM by jsagasti, version 7

Comments

No comments yet.