Nogola's IT blog

Thursday, March 28, 2013

Salesforce.com - Lookup filter based on text field

Need to filter a lookup field based on a text field? This is currently not supported by SFDC.
But, there's a workaround for that:

Create a new picklist field

the values are not important, they won't be used
don't show it on the page layout, as it's not interesting to the user.

Create a trigger to copy the value from the text-field (which you want to use for filtering) to the new picklist field
Filter the lookup based on the picklist (this is supported)

Sunday, March 4, 2012

ASP.NET MVC3 AntiForgeryToken in AJAX calls

The AntiForgeryToken is a nice feature of the MVC framework to prevent CRSF attacks. It's used as a method of the HtmlHelper class which renders a hidden input element containing the key. The problem arises when you want to use it in AJAX call.
A solution for this is to create a wrapper method that extracts the created value and returns it. After that you can store it in the ViewState so it can be reused in the same page for multiple ajax calls.

We define a new class, with 2 constant strings defining the ViewState-keys for the entries defining the Name and the Value of the AntiForgeryToken:

public class SecurityUtility
{
const string antiForgeryTokenValueKey = "antiForgeryTokenValue";
const string antiForgeryTokenNameKey = "antiForgeryTokenName";
}

We add the 2 public methods that are going to be called to return the Name and the Value:

/// Returns the AntiForgeryToken Value
public static string GetAntiForgeryTokenValue(HtmlHelper html)
{
    string value = (string)html.ViewData[antiForgeryTokenValueKey];
    if (string.IsNullOrEmpty(value))
    {
        InitAntiForgeryToken(html);
    }

    return (string)html.ViewData[antiForgeryTokenValueKey];
}

/// Returns the AntiForgeryToken Name
public static string GetAntiForgeryTokenName(HtmlHelper html)
{
    string name = (string)html.ViewData[antiForgeryTokenNameKey];
    if (string.IsNullOrEmpty(name))
    {
        InitAntiForgeryToken(html);
    }

    return (string)html.ViewData[antiForgeryTokenNameKey];
}

These tow methods return the values form the ViewState. In case that the values are not defined, the InitAntiForgeryToken() method is called which will initialize these values:

private static void InitAntiForgeryToken(HtmlHelper html)
{
    string input = html.AntiForgeryToken().ToHtmlString();

    // Name
    Regex r = new Regex("name=\"(?[^\"]*)");
    Match m = r.Match(input);
    if (m.Success && m.Groups["tokenName"] != null &&
        m.Groups["tokenName"].Success)
    {
        html.ViewData[antiForgeryTokenNameKey] =
            m.Groups["tokenName"].Value;
    }

    // Value
    r = new Regex("value=\"(?[^\"]*)");
    m = r.Match(input);
    if (m.Success && m.Groups["tokenValue"] != null &&
        m.Groups["tokenValue"].Success)
    {
        html.ViewData[antiForgeryTokenValueKey] =
            m.Groups["tokenValue"].Value;
    }
}

This method first takes the result of the HtmlHelper.AntiForgeryToken() method as a html string and then uses regular expressions to get the name and the value of the generated hidden input element. These values are then stored in the ViewState.

Finally, in the View when we're generating an ajax call we can add the AntiForgeryToken into the data like this:

.ajax({
    url: "theActionUrl...",
    type: "POST",
    data: {
        "@SecurityUtility.GetAntiForgeryTokenName(Html)" :
            "@SecurityUtility.GetAntiForgeryTokenValue(Html)"
    }
});

-----------------------------

The Whole SecurityUtility class is given in the next code block:

public class SecurityUtility
{
    const string antiForgeryTokenValueKey = "antiForgeryTokenValue";
    const string antiForgeryTokenNameKey = "antiForgeryTokenName";

    public static string GetAntiForgeryTokenValue(HtmlHelper html)
    {
     string value = (string)html.ViewData[antiForgeryTokenValueKey];
     if (string.IsNullOrEmpty(value))
     {
        InitAntiForgeryToken(html);
     }

     return (string)html.ViewData[antiForgeryTokenValueKey];
    }

    public static string GetAntiForgeryTokenName(HtmlHelper html)
    {
     string name = (string)html.ViewData[antiForgeryTokenNameKey];
     if (string.IsNullOrEmpty(name))
     {
        InitAntiForgeryToken(html);
     }

     return (string)html.ViewData[antiForgeryTokenNameKey];
    }

    private static void InitAntiForgeryToken(HtmlHelper html)
    {
     string input = html.AntiForgeryToken().ToHtmlString();

     // Name
     Regex r = new Regex("name=\"(?[^\"]*)");
     Match m = r.Match(input);
     if (m.Success && m.Groups["tokenName"] != null && m.Groups["tokenName"].Success)
     {
        html.ViewData[antiForgeryTokenNameKey] = m.Groups["tokenName"].Value;
     }
     if (m.Success && m.Groups["tokenName"] != null && m.Groups["tokenName"].Success)
     {
        html.ViewData[antiForgeryTokenNameKey] = m.Groups["tokenName"].Value;
     }

     // Value
     r = new Regex("value=\"(?[^\"]*)");
     m = r.Match(input);
     if (m.Success && m.Groups["tokenValue"] != null && m.Groups["tokenValue"].Success)
     {
        html.ViewData[antiForgeryTokenValueKey] = m.Groups["tokenValue"].Value;
     }
    }
}

Friday, September 2, 2011

ASP.NET MVC3 - dynamic grid with ajax paging & sorting

A grid with ajax paging/sorting, something that is very common in today's web applications, is something that's not coming out of the box in ASP.NET MVC3. There is a built in helper class for making grids - System.Web.Helpers.WebGrid, but the customization is a little bit limited and even though it has paging and sorting functionalities they do postbacks...

Fortunately, it is not that hard to implement our own grid. All we need is:

A jQuery plugin that will handle the ajax
A class that will hold information about Columns
A MVC Helper to render the initial html
An action in a controller that will return data for paging
Make use of the whole thing in a view

1. The jQuery plugin
We start with a new jscript file (e.g. DynamicGrid.js) which we add to the Scripts folder, add an empty plugin and some option-defaults:

Next, we add a function that will load the data:

It simply starts an ajax call to the given url and sends data about the current page, page size and the property which is going to be used for sorting.

This function we'll call in the plugin initialization:

return this.each(function () {
    // options
    var opts = $.extend({ grid : $(this) }, $.fn.DynamicGrid.defaults, options);

    // load the first page
loadPage(opts);
});

Now we need to implement the functions fillGrid and setupNavigation.

The fillGrid function fills the grid with data received from the ajax call. It first renders the grid header and then the data. Columns are either defined in the options or generated based on properties of the list items.

The setupNavigation function sets up the navigation links defined in the options (previousId and nextId are id's of html elements that will be used as links to previous/next page).

Thats the whole jQuery plugin:

2. The GridColumn class
We add a new class in the Models folder (not necessary) and call it GridColumn.cs. This class will hold information for columns of the grid:

public class GridColumn
{
    public string PropertyName { get; set; }
    public string ColumnHeader { get; set; }
    public string HeaderCssStyle { get; set; }
    public string CellCssStyle { get; set; }

    public object GetValue(PropertyInfo[] properties, object target)
    {
        var property = properties.FirstOrDefault(p => p.Name == this.PropertyName);
        return (property != null) ? property.GetValue(target, null) : null;
    }
}

3. The MVC Helper
The MVC Helper will render the inital html that will generate the grid. We add a new folder to the ASP.NET MVC3 project and call it App_Code (this name is required) and in it we add a new cshtml file (e.g. Helpers.cshtml) that will hold the helper. In this file we add the following code:

@using MvcApplication5.Models

@helper DynamicGrid(string id, string getItemsUrl, string cssStyle="", IEnumerable<GridColumn> columns = null, bool allowSort = false)
{
    string gridId = string.Concat(id, "_grid");
    string loadingId = string.Concat(id, "_loading");
    string nextId = string.Concat(id, "_next");
    string previousId = string.Concat(id, "_prev");
<text>
<div id="@id" style="@cssStyle">
    <span id="@loadingId" style="display: none;" >Loading...</span>
    <table id="@gridId" style="width: 100%;">
    </table>
    <div style="width: 100%; text-align: right;">
        <a href="#" id="@previousId" style="display: none; cursor: pointer;">PREV</a>
        <a href="#" id="@nextId" style="display: none;cursor: pointer;">NEXT</a>
    </div>
    <script type="text/javascript">
        $(document).ready(function () {
            @{
                if (columns != null && columns.Count() > 0)
                {
                    System.Text.StringBuilder colsJscript =
                        new System.Text.StringBuilder("var cols = [");
                    foreach(var c in columns)
                    {
                        colsJscript.Append("{propertyName:\"")
                            .Append(c.PropertyName).Append("\", columnHeader : \"")
                            .Append(c.ColumnHeader).Append("\", headerStyle : \"")
                            .Append(c.HeaderCssStyle).Append("\", cellStyle : \"")
                            .Append(c.CellCssStyle).Append("\" },");
                    }
                    // makni zadnji zarez
                    colsJscript.Remove(colsJscript.Length -1, 1);
                    colsJscript.Append("]");
                    @:@System.Web.Mvc.MvcHtmlString.Create(colsJscript.ToString())
                }
            }
            $("#@gridId").DynamicGrid( { pageSize: 5,
                                        columns: cols,
                                        allowSort: @allowSort.ToString().ToLower(),
                                        url: "@getItemsUrl" } );
        });
    </script>
</div>
</text>
}

This generates the initial html containing the html-table that will hold the grid, and the call to the DynamicGrid plugin. You can modify the helper to customize the grid as much as you want.

4. The Action that will return grid items
The Action that returns items should return a JsonResult with properties:
- result = the grid items of the current page
- page = the current page
- pageCount = the current page count

The Action could look like this:

[HttpPost]
public JsonResult GetStates()
{
    int page = int.Parse(Request["page"]);
    int pageSize = int.Parse(Request["pageSize"]);
    string sortProperty = Request["sortProperty"];

    var states = new []
    {
        new { Id = "1", Code = "AGAR" },
        new { Id = "2", Code = "AGR" },
        new { Id = "3", Code = "ADBAETH" },
        new { Id = "4", Code = "SJ" },
        new { Id = "5", Code = "DTKFZK" },
        new { Id = "6", Code = "SNZF" },
        new { Id = "7", Code = "DUK" },
        new { Id = "8", Code = "FH,FH" },
        new { Id = "9", Code = "ATH" },
        new { Id = "10", Code = "AHTJ" }
    };

    PropertyInfo pi = states[0].GetType().GetProperty(sortProperty);
    if (pi != null)
    {
        states = states.OrderBy(item => pi.GetValue(item, null)).ToArray();
    }

    return new JsonResult() { Data = new {
        result = states.Skip((page - 1) * pageSize).Take(pageSize),
        page = page,
        pageCount = (int)Math.Ceiling((float)s.Count() / pageSize)
        },
        ContentType = "text/json"
    };
}

5. Make use of the grid
Finally, we can make use of the grid. In a View we can use it like this:

@Helpers.DynamicGrid("myGrid",
    Url.Action("GetStates"),
    "width: 200px;",
    new GridColumn[]
    {
        new GridColumn(){ PropertyName = "Id",
                          ColumnHeader="Id",
                          HeaderCssStyle="width: 100px; color: Red;" },
        new GridColumn(){ PropertyName = "Code",
                          ColumnHeader="Code",
                          HeaderCssStyle="width: 100px; color: Blue;" }
    },
    true)

Remember to add a link to the script file (DynamicGrid.js) in the html-head section of the View.

Et voila, thats it!

Friday, August 19, 2011

Simple SQL Source Control using DDL triggers

In the company I work for, we use SQL Servers with a lot of business code in stored procedures and functions. With growing complexity and multiple developers modifying the same code the need for source control has risen.
Unfortunately source control for SQL servers is not provided in a useful way out-of-the-box. TFS integration in SQL Management Studio uses local file which is a very unconvenient way. 3rd party solutions, like RedGate, are not fully intergated with the server, so it is possible to make changes without interaction of the source control...
We wanted a solution that enabled monitoring of every change on the database, regardless of the user having installed the product or not, and which does not require any overhead (such as the need to click on 5 screens just to change a simple procedure).

So we were looking for some other ways. After a little research we found this article, which suggested using DDL triggers. And that seemed to be the solution. Based on the article we created our own simple SQL Source Control system.

The solution

We added a separate Source Control Database named SourceControlDB on the SQL server. It contains a simple table (log) which is filled with database-change-logs.

CREATE TABLE [log]
(
    [PostTime] [datetime],
    [ServerName] [nvarchar](256),
    [DataBaseName] [nvarchar](256) ,
    [Event] [nvarchar](256) ,
    [ObjectName] [nvarchar](256) ,
    [TSQL] [nvarchar](MAX) ,
    [Login] [nvarchar](256)
)

On databases we want to monitor, we add a DDL trigger like this:

CREATE TRIGGER [SourceControlTrigger]
ON DATABASE
FOR DDL_FUNCTION_EVENTS, DDL_PROCEDURE_EVENTS, DDL_TABLE_VIEW_EVENTS, DDL_TRIGGER_EVENTS
AS

DECLARE @ed XML
SET @ed = EVENTDATA()

INSERT INTO SourceControlDB.dbo.log (PostTime, ServerName, DatabaseName, Event, ObjectName, TSQL, Login)
VALUES
(
    GetDate(),
    @ed.value('(/EVENT_INSTANCE/ServerName)[1]', 'nvarchar(256)'),
    @ed.value('(/EVENT_INSTANCE/DatabaseName)[1]', 'nvarchar(256)'),
    @ed.value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(256)'),
    @ed.value('(/EVENT_INSTANCE/ObjectName)[1]', 'varchar(256)'),
    @ed.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(MAX)'),
    @ed.value('(/EVENT_INSTANCE/LoginName)[1]', 'nvarchar(256)')
)

Now every change on that database is logged to the log table of the SourceControlDB database:

E.g.:

PostTime: 2011-08-19 13:17:38
ServerName: MyServer1
DatabaseName: MyDatabase
EventType: CREATE_PROCEDURE
ObjectName: sp_testLog
TSQLCommand: CREATE PROCEDURE sp_testLog AS SELECT 'Log'
LoginName: Company\User

How to evenly distribute a set to 2 parts

A few years ago I came acrross this problem. I had to evenly distribute a set of databases (of different sizes) on 2 servers, so that each server would be evenly loaded. After some thinking I came up with the following simple algorithm and afterwards proved it to be correct.

The algorithm

In the beginning we have - 1 set of items, 2 empty groups (into which the items will be distributed)

1. Sort the set by the size of its items, descending
2. Take the first item out of the set and put it in the group whose items have the smaller sum of values (or if they are equal in any one)
3. Repeat 2. while there are items in the set

Pseudocode

The proof

This algorithm can be prooved by using the mathematical induction.

We have a (descending) ordered set of items and two groups.

(1) The items in the two groups are evenly distributed in the beginning, because the groups are empty.
(2)
a) Assume that the items in the groups are evenly distributed
b) We add the first item from the set, which is smaller or equal than any other in the groups (because the set is ordered descending), to the group that is currently smaller (let's call this group - A, and the other one - B). Before adding the item to A, we are sure that A <= B (smaller or equal). Now there are 2 possible cases:
(i) After adding the item to A, it is still A <= B and
(ii) it is A > B.
In case of (i) the items are for sure evenly distributed because the relationship of the sizes of the groups remains the same.
If in case of (ii) the items were not evenly distrubuted, and they would be evenly distribued if we put the item in group B, we would have increased the difference between the sizes of A and B. And because the set was ordered there is no item in B that is smaller than the current item that we could move to A to make the distribution even. Therefor in case of (ii) the items are also evenly distributed.

According to the mathematical induction we can conclude on the basis of (1) and (2) that this way of distributing the items into the groups is even.