Removing duplicate calls on an AspNet Page Load by using the MasterPage

This is often something that is never apparent but can improve performance. On the master page, you need to make a call, for example, to get the MembershipUser.

MembershipUser user = Membership.GetUser();

Then on the page you need to do the same so you copy the code again.  This results in two calls. For a current project, this means two WCF calls so there is a significant latency added to page rendering.  A better solution is to make the User public on the master page and then use it (and avoid the second call).

 

On the master page, change it to a self-initializing property (and avoid putting initialization into events like Page_Load)

MembershipUser _User

public MembershipUser User

{

    get

    {

        if (_User == null)

        {

            _User = Membership.GetUser();

        }

        return _User;

    }

}

   and then on the child pages use:

protected void Page_Load(object sender, EventArgs e)

{

    var user = ((MyUserPortal.AppMasters.Default)Master).User;

}

You have eliminated a call with little effort and improved performance.

 

Bottom line, be aware of what is in the Master Page and strive to re-use what is there.

Improving XML User Defined Function Performance

Doing some code review, I came across a piece of code which giving

 

Msg 8114, Level 16, State 5, Line 1
Error converting data type nvarchar to numeric.

 

The likely cause was someone entering a ‘n/a’ in the field (the curse of XML coming from slack systems).

Create FUNCTION [dbo].[GetSellingPrice]

(

    @Data xml

)

RETURNS decimal(20,2)

WITH SCHEMABINDING

AS

BEGIN

    declare @Return decimal(20,2)

    select @Return =

            CASE

            WHEN @Data.value('(//Property/@SellingPrice)[1]', 'nvarchar(20)') IS NULL THEN 0

            ELSE @Data.value('(//Property/@SellingPrice)[1]', 'decimal(20,2)')

        END

    RETURN @Return

END

Even with good data, this code is not ideal for several reasons:

• Multiple calls to the .value function to get the same data
• Using nvarchar when varchar is sufficient (after all, it’s being cast to a number!)
• Searching the entire XML every call

My proposed alternative TSQL is below which addresses these issues.

CREATE FUNCTION [dbo].[GetSellingPrice2]

(

    @Data xml

)

RETURNS decimal(20,2)

WITH SCHEMABINDING

AS

BEGIN

    declare @Return decimal(20,2)

    declare @text varchar(20)

    Set @text=@Data.value('(Property/@SellingPrice)[1]', 'varchar(20)') 

    if @Text is null OR Len(@Text) < 4 

        SET @Return=0

    ELSE        

        Set @Return=Cast(@text as Decimal(20,2)    )

    RETURN @Return

END

One of the problem exposed is an interesting one – there is no IsNaN() function in TSQL  which would make this kludge unneeded.  So my additional suggestion would be to create a CLR function that provides IsNaN() functionality.

When writing .Net Providers do not forget to check for orphan name-value pairs

Today while I was reviewing some code I saw reasonable code (shown below) which I modified to be better code. Why do I say better? Well, a common production bug is mistyped or mis-cased attributes for providers in the configuration file.  These bugs can take a long time to track down – it depends on someone with sharp eyes!  The code modification will immediately identify any orphaned name-value pairs, a typo breaks the initialization and the fix happens in seconds, not hours.

 

The key is to remove all of the name-pair values that you are using (as they are consumed) and then check if there are any orphans after the base processing.  All of the good provider sample codes that I have seen does this. Once I saw the logic, it has becomes a regular code-review item. The pattern is a good one for making deployments more robust – a typo is often hard to track down.

 

Reasonable Code

 

public override void Initialize(string name, System.Collections.Specialized.NameValueCollection config)
{

    ServicePortalId = new Guid(config["ServicePortalId"]);
    CurrentUserSessionKey = config["applicationName"] + "CurrentUser";
    if (config["IdentityServiceId"] != null)
    {
        IdentityServiceId = new Guid(config["IdentityServiceId"]);
    }
    base.Initialize(name, config);
}

Better Code

Another item that was added is fall thru for null values done using ??.

 

public override void Initialize(string name, System.Collections.Specialized.NameValueCollection config)
{
    Guid test=Guid.Empty;
    if(config["ServicePortalId"] !=null)
    {
        if(Guid.TryParse(config["ServicePortalId"] ,out test))
        {
        ServicePortalId =test;
        }
        config.Remove("ServicePortalId");
    }
    
    CurrentUserSessionKey = (config["applicationName"] ?? "Unknown") + "CurrentUser";
    if (config["applicationName"] != null)
    {
        config.Remove("applicationName");
    }
    if (config["IdentityServiceId"] != null)
    {
        IdentityServiceId = new Guid(config["IdentityServiceId"]);
        config.Remove("IdentityServiceId");
    }
    base.Initialize(name, config);
    if (config.Count > 0)
    {
        var errors = new StringBuilder("The following configuration values are unknown:");
        foreach (string key in config.Keys)
        {
            errors.AppendLine(key);
        }
        throw new ConfigurationErrorsException(errors.ToString());
    }
}

Building Indexes on Views based on XML

When dealing with XML columns, you can improve search times by building indexes on the values extracted from the XML.  The index persists the value for searches so there is no need to re-extract the value until you actually retrieve the record after finding it.

 

Tonight, I was helping a friend and it required a view from an inner join of two different tables and getting values from each table’s XML to be coalesce. So the logical solution is to create a VIEW and then index the xml values. I could create the columns for each value and proceed that way – but it consumes  resources that are not absolutely required.

 

Wait! You cannot create indexes on views containing XML data type methods. If you try, you will get this error message:

Msg 1985, Level 16, State 1, Line 1
Cannot create index on view 'VerraTerraDatabase.dbo.CurrentData2'. It contains one or more XML data type methods.

 

i.e. If you try something like:

Create view CurrentData WITH SCHEMABINDING

as

SELECT   

    P.ListingNumber, 

    Coalesce( U.received,P.received) as Received,

    case when U.received =P.received then 'Original' else 'Updated' end as Status,

    coalesce(u.Data.value('(//Property/@APN)[1]', 'nvarchar(50)'),p.Data.value('(//Property/@APN)[1]', 'nvarchar(50)')) as APN,

    coalesce( u.Data.value('(//Property/@matrix_unique_id)[1]', 'int'),p.Data.value('(//Property/@matrix_unique_id)[1]', 'int')) as matrix_unique_id

FROM         dbo.PrimaryXml P 

INNER JOIN

dbo.UpdateXml  U

ON P.ListingNumber = U.ListingNumber

and VersionNo=0

go

CREATE UNIQUE CLUSTERED INDEX PK_ListingNumber ON CurrentData (ListingNumber)

 

However the code below will work and create indexes on XML values through the use of functions.

Create view CurrentData WITH SCHEMABINDING

as

SELECT   

    P.ListingNumber, 

    Coalesce( U.received,P.received) as Received,

    case when U.received =P.received then 'Original' else 'Updated' end as Status,

    coalesce(dbo.GetAPN(u.Data),dbo.GetAPN(p.Data)) as APN,

    coalesce(dbo.GetMatrixUniqueId( u.Data), dbo.GetMatrixUniqueId(p.Data)) as matrix_unique_id

FROM         dbo.PrimaryXml P 

INNER JOIN

                      dbo.UpdateXml  U

                      ON P.ListingNumber = U.ListingNumber

and VersionNo=0

go

CREATE UNIQUE CLUSTERED INDEX PK_ListingNumber ON CurrentData (ListingNumber)

go

CREATE  INDEX PK_Apn ON CurrentData (APN)

go

CREATE  INDEX PK_matrix_unique_id ON CurrentData (matrix_unique_id)

go

What we do is create a function wrapper, for example this lean definition:

Create FUNCTION [dbo].[GetAPN] 

(

    @Data xml

)

RETURNS nvarchar(50)

WITH SCHEMABINDING

AS

BEGIN

    return @Data.value('(//Property/@APN)[1]', 'nvarchar(50)')

END

 

Result: I have a join on two tables containing XML columns with the coalesce of values from the xml in each table being indexed. For most of you this is not likely something you need often, but it can simplify a data model.

Formatting Exports from Telerik RadGrid (PDF,Word, Excel)

I have been spending a bit of time trying to get a simple and elegant way to (re-)format an export from a RadGrid. The Css Styles (especially if you do not use the built-in skins) are not carried through to the export.

 

The export creates XHTML which is then passed to the appropriate engine to produce the download file. The solution (once you find it) is pretty clean.

 

First, I created a Css.Resx file which contains the following items:

• GridItem
• GridItemCell
• GridHeaderItem
• GridHeaderItemCell
• GridItem
• GridItemCell

Each of these items contain a definition in classic style sheet format.

color:#0000FF;

font-size:1.2em;

So you can now define the appearance of each of these six regions (you can do more regions using the same pattern).

 

Setting up to detect the Export

Telerik examples typically shows the use of an independent button which then sets a variable. We use a similar pattern but make use of the built-in commands and a property on the RadGrid. This is done by adding two event handlers (with supporting code) as shown below. We use the CssClass as a flag variable.

 

The routines below may be put into a utility library and made universally available.

grid.ItemCommand += Grid_ItemCommand;

grid.ItemCreated += Grid_ItemCreatedAddRowScope;

....            

/// <summary>

/// Handles the ItemCommand event of the Grid control and applies export settings

/// </summary>

/// <param name="source">The source of the event.</param>

/// <param name="e">The <see cref="Telerik.Web.UI.GridCommandEventArgs"/> instance containing the event data.</param>

private static void Grid_ItemCommand(object source, GridCommandEventArgs e)

{

    RadGrid grid = source as RadGrid;

    switch (e.CommandName)

    {

        case RadGrid.ExportToPdfCommandName:

            grid.CssClass = "Export";

            break;

        case RadGrid.ExportToWordCommandName:

            grid.CssClass = "Export";

            break;

        case RadGrid.ExportToExcelCommandName:

            grid.CssClass = "Export";

            break;

    }

}

Now when the XHTML starts being built,  we see if we should apply our settings, and if so, we do as shown below.

private static void Grid_ItemCreated(object sender, GridItemEventArgs e)

{

RadGrid grid = sender as RadGrid;

if (grid.CssClass == "Export")

{

    if ( e.Item is GridHeaderItem)

    {

        grid.ShowFooter = true;

        grid.MasterTableView.ShowFooter = true;

        GridHeaderItem headerItem = (GridHeaderItem)e.Item;

        var items = GetStyleArray(Resources.Css.GridHeaderItem);

        foreach (string key in items.Keys)

        {

            headerItem.Style[key] = items[key];

        }

        items = GetStyleArray(Resources.Css.GridHeaderItemCell);

        foreach (TableCell cell in headerItem.Cells)

        {

            foreach (string key in items.Keys)

            {

                cell.Style[key] = items[key];

            }

        }

    }

    else if (e.Item is GridItem)

    {

        GridItem headerItem = (GridItem)e.Item;                    

        var items = GetStyleArray(Resources.Css.GridItem);

        foreach (string key in items.Keys)

        {

            headerItem.Style[key] = items[key];

        }

        items = GetStyleArray(Resources.Css.GridItemCell);

        foreach (TableCell cell in headerItem.Cells)

        {

            foreach (string key in items.Keys)

            {

                cell.Style[key] = items[key];

            }

        }

    }

    else if (e.Item is GridFooterItem)

    {

        GridFooterItem headerItem = (GridFooterItem)e.Item;

        var items = GetStyleArray(Resources.Css.GridFooterItem);

        foreach (string key in items.Keys)

        {

            headerItem.Style[key] = items[key];

        }

        items = GetStyleArray(Resources.Css.GridFooterItemCell);

        foreach (TableCell cell in headerItem.Cells)

        {

            foreach (string key in items.Keys)

            {

                cell.Style[key] = items[key];

            }

        }

    }

}

This uses a function that breaks apart the CSS  into a sorted list that is shown below. The function is (reasonably) tolerant of format.  You could use

.GridItemCell { whatever }

instead of just the content shown above.

 

/// <summary>

/// Gets a style array suitable for a griditem from a string.

/// </summary>

/// <param name="css">The CSS.</param>

/// <returns></returns>

private static SortedList<string,string>  GetStyleArray(string css)

{

    char[] sep1={';','{','}',' '};

    char[] sep2={':'};

    var ret=new SortedList<string,string>();

    string[] elements = css.Split(sep1, StringSplitOptions.RemoveEmptyEntries);

    foreach (string element in elements)

    {

        var parts = element.Split(sep2, StringSplitOptions.RemoveEmptyEntries);

        if (parts.Length == 2)

        {

            if (!ret.ContainsKey(parts[0]))

            {

                ret.Add(parts[0], parts[1]);

            }

        }

    }

    return ret;

}

There are a few got-cha, for example: 

• color:Red; does not work.
• color: #FF0000; does work.

Those are discovered quickly (if it does not work, try an alternative expression!).  I suspect that the CSS level is not comprehensive – so don’t be surprise if some items do not work!

 

With the formatting being in a RESX file, it is easy to modify as needed for different customers. You can modify as few or as many formatting attributes as you wish.