Thursday, March 8, 2012

SQL Multiple Item Update Using XML

From time to time, I run into a situation where I need to update multiple rows on a table. I could make multiple requests to the database, but that is rather chatty. Alternatively, a comma delimited string of values could be parsed and used to get the update. Being a big fan of XQuery, I was able to pass in an xml document and load a table that could be used to join with the table to be updated. This should scale beautifully since it plays to SQL's strengths. I have looked for a way to get some metrics on the efficiency of this verses other options and could not find a way.

This is the function I used to generate XML string. I think there are ways that I can make the function a little tighter, but it does the job and is too inefficient.

public static string BuildXmlString<t>(string xmlRootName, T[] values)
{
    StringBuilder xmlString = new StringBuilder();

    xmlString.AppendFormat("<{0}>", xmlRootName);
    int count = values.Length;
    for (int idx = 0; idx < count; idx++)
    {
        xmlString.AppendFormat("<value>{0}</value>", values[idx]);
    }
    xmlString.AppendFormat("</{0}>", xmlRootName);

    return xmlString.ToString();
}
Here is the SQL that uses the XML and updates a table with a resolved parameter value.
DECLARE @status varchar(50), @demoItemIDs xml
 SET @status = ''
 
 -- Load up the table variable with the parsed XML data
 DECLARE @DemoItems TABLE (ID int) 
 INSERT INTO @DemoItems (ID) SELECT ParamValues.ID.value('.','int')
 FROM @demoItemIDs.nodes('/demoItemIDs/value') as ParamValues(ID) 

 -- Resolve status name to ID
 DECLARE @statusID int 
 SELECT TOP 1 @statusID = DemoStatusID FROM DemoStatuses ds WHERE ds.Name = @status

 IF (@statusID > 0)
 BEGIN 
  -- Update the payments with the new status
  UPDATE [Demo].[dbo].[DemoItemApprovals]
     SET DemoStatusID = @statusID
   WHERE DemoItemID IN (
    SELECT ID FROM @DemoItems d
      INNER JOIN DemoItemApprovals da
    ON    da.PaymentID = d.ID
   )
 END
 ELSE
 BEGIN
  RAISERROR (
   N'The status ''%s'' does not exist.' -- Message text.
   , 10 -- Severity,
   , 1 -- State,
   , @status -- First argument.
   );
 END

Monday, February 20, 2012

Log Manager With Extra Information Using SharePoint 2010 Unified Logging Service

Here is a wrapper class which covers the SharePoint ULS interface and logs information to the ULS. I attempted to add as much information as I could and allow for a property dictionary to be appended to the log entry.

namespace Demo.Web.Logging
{
    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Text;
    using Microsoft.SharePoint.Administration;
    using System.Diagnostics.Eventing;
    using System.Runtime.InteropServices;
    using System.Security.AccessControl;
    using System.Security.Principal;
    using System.Threading;
    using System.Diagnostics;

    public class LogManager
    {
        public static void Write(Exception ex, SPDiagnosticsCategory category, TraceSeverity severity)
        {
            if (ex.Data != null && !ex.Data.Contains("CallingFunction"))
            {
                ex.Data.Add("CallingFunction", System.Reflection.MethodBase.GetCurrentMethod().ReflectedType.Name);
            }
            Dictionary<string, object> props = null;
            if (ex.Data != null && ex.Data.Count > 0)
            {
                props = new Dictionary<string, object>();
                foreach (string key in ex.Data)
                {
                    props.Add(key, ex.Data[key]);
                }
            }
            LoggingService.Log(ex.ToString(), props);
        }

        public static void Write(string message, Dictionary<string, object> props)
        {

            LoggingService.Log(message, props);
        }

        public static void Write(string message, Dictionary<string, object> props, TraceSeverity severity)
        {
            LoggingService.Log(message, props, severity);
        }

        public static void Write(string message, Dictionary<string, object> props, TraceSeverity severity, string categoryName)
        {
            LoggingService.Log(message, props, severity, categoryName);
        }

        public static void Write(string message, Dictionary<string, object> props, TraceSeverity severity, SPDiagnosticsCategory category)
        {
            //SPDiagnosticsService.Local.WriteTrace(0, new SPDiagnosticsCategory("My Category", TraceSeverity.Unexpected, EventSeverity.Error), TraceSeverity.Unexpected, ex.Message, ex.StackTrace);
            LoggingService.Log(message, props, severity, category);
        }
    }

    #region [ Internal ULS Access Implementation ]
    
    internal class LoggingService : SPDiagnosticsServiceBase
    {
        public static string DemoDiagnosticAreaName = "Demo";
        private static LoggingService _Current;
        public static LoggingService Current
        {
            get
            {
                if (_Current == null)
                {
                    _Current = new LoggingService();
                }

                return _Current;
            }
        }

        private LoggingService()
            : base("Demo Logging Service", SPFarm.Local)
        {

        }

        protected override IEnumerable<spdiagnosticsarea> ProvideAreas()
        {
            List<spdiagnosticsarea> areas = new List<spdiagnosticsarea>
            {
                new SPDiagnosticsArea(DemoDiagnosticAreaName, new List<spdiagnosticscategory>
                {
                    new SPDiagnosticsCategory("Application", TraceSeverity.Unexpected, EventSeverity.Error),
                    new SPDiagnosticsCategory("WebService", TraceSeverity.Unexpected, EventSeverity.Error),
                    new SPDiagnosticsCategory("WebConfigMod", TraceSeverity.Unexpected, EventSeverity.Error)
                })
            };

            return areas;
        }

        public static void Log(string message)
        {
            Log(message, null);
        }

        public static void Log(string message, Dictionary<string, object> props)
        {
            Log(message, props, TraceSeverity.Unexpected);
        }
        public static void Log(string message, Dictionary<string, object> props, TraceSeverity severity)
        {
            Log(message, props, TraceSeverity.Unexpected, "Application");
        }
        public static void Log(string message, Dictionary<string, object> props, TraceSeverity severity, string categoryName)
        {
            SPDiagnosticsCategory category = LoggingService.Current.Areas[DemoDiagnosticAreaName].Categories[categoryName];
            Log(message, props, severity, category);
        }
        public static void Log(string message, Dictionary<string, object> props, TraceSeverity severity, SPDiagnosticsCategory category)
        {
            if (props == null)
            {
                props = new Dictionary<string, object>();
            }
            if (!props.ContainsKey("CallingFunction"))
            {
                props.Add("CallingFunction", (new StackTrace()).GetFrame(1).GetMethod().Name);
            }
            string propSerial = "{" + string.Join(",", props.Select(
                d => string.Format("\"{0}\":\"{1}\"", d.Key, d.Value.ToString())
                ).ToArray()) + "}";

            //SPDiagnosticsCategory category = LoggingService.Current.Areas[DemoDiagnosticAreaName].Categories[categoryName];
            LoggingService.Current.WriteTrace(0, category, TraceSeverity.Unexpected, message + " ~ Properties: " + propSerial);
        }
    }

    #endregion
    }
}

Wednesday, February 15, 2012

Log Manager With Extra Information Using Enterprise Library

Here is a pattern for logging which encourages extra information to be stored with the error.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Diagnostics;
using Microsoft.Practices.EnterpriseLibrary.Logging;
using Microsoft.Practices.EnterpriseLibrary.Logging.ExtraInformation;
using Microsoft.Practices.EnterpriseLibrary.Logging.Filters;
using Microsoft.Practices.EnterpriseLibrary.Common.Configuration;
using System.Reflection;
using System.Collections;

namespace DemoServices.Logging
{
    public class LogManager
    {
        public static void Write(Exception ex, string title = "", string category = "", TraceEventType severity = TraceEventType.Error)
        {
            if (ex != null)
            {
                if (String.IsNullOrEmpty(title))
                {
                    title = System.Reflection.MethodBase.GetCurrentMethod().ReflectedType.Name;
                }
                if (String.IsNullOrEmpty(category))
                {
                    category = System.Reflection.MethodBase.GetCurrentMethod().ReflectedType.Name;
                }
                Dictionary<string, object> props = null;
                if (ex.Data != null && ex.Data.Count < 0)
                {
                    props = new Dictionary<string, object>();
                    foreach(string key in ex.Data) {
                        props.Add(key, ex.Data[key]);
                    }
                }
                Write(ex.ToString(), title, category, severity, props);
            }
        }

        public static void Write(string message, string title = "", string category = "", TraceEventType severity = TraceEventType.Error, IDictionary<string, object> props = null)
        {
            if (String.IsNullOrEmpty(title))
            {
                title = System.Reflection.MethodBase.GetCurrentMethod().ReflectedType.Name;
            }
            if (String.IsNullOrEmpty(category))
            {
                category = System.Reflection.MethodBase.GetCurrentMethod().ReflectedType.Name;
            }
            if (props == null)
            {
                props = new Dictionary<string, object>();
            }
            ManagedSecurityContextInformationProvider informationHelper = new ManagedSecurityContextInformationProvider();
            informationHelper.PopulateDictionary(props);
            DebugInformationProvider debugHelper = new DebugInformationProvider();
            debugHelper.PopulateDictionary(props);

            LogWriter logger = EnterpriseLibraryContainer.Current.GetInstance<logwriter>();
            logger.Write(message, new string[] { "Demo", category }, 20, 0, severity, title, props as Dictionary<string, object>);
        }

        //public static void Write(Exception ex, string title, string category, TraceEventType severity = TraceEventType.Error, Dictionary<string, object> props = null)
        //{
        //    if (ex != null)
        //    {
        //        Write(ex.ToString(), title, category, severity, props);
        //    }
        //}

        //public static void Write(string message, string title, string category, TraceEventType severity = TraceEventType.Error)
        //{
        //    Dictionary<string, object> props = new Dictionary<string, object>();
        //    //props.Add("CallingFunction", (new StackTrace()).GetFrame(1).GetMethod().Name);

        //    if (props == null)
        //    {
        //        props = new Dictionary<string, object>();
        //    }
        //    ManagedSecurityContextInformationProvider informationHelper = new ManagedSecurityContextInformationProvider();
        //    informationHelper.PopulateDictionary(props);
        //    DebugInformationProvider debugHelper = new DebugInformationProvider();
        //    debugHelper.PopulateDictionary(props);

        //    LogWriter logger = EnterpriseLibraryContainer.Current.GetInstance<logwriter>();
        //    logger.Write(message, new string[] { "Demo", category }, 20, 0, severity, title, props);
        //}

    }
}

Wednesday, December 28, 2011

SharePoint 2010 Custom Application Page Affix Ribbon To Top Using CSS

Migrating existing applications into SharePoint can be difficult depending on the JavaScript functionality of the old code. Using the default SharePoint 2010 custom application page, the s4-workspace is a div that is re-sized and scrollable to allow the SharePoint ribbon to display. I don't know why Microsoft felt it necessary to do far more work than necessary to fix a div to the top of the window.

Below is the code I used to fix the scroll bars on the page. This makes the ribbon not fixed and will scroll out of view. This could be enough if you don't use the ribbon in your pages.
body {
    overflow: auto ! important;
}
body.v4master { 
    height:inherit; 
    width:inherit; 
    overflow:visible!important;
}

body #s4-workspace {
   overflow-y:auto !important;
   overflow-x:auto !important;
   height:auto !important;
}

If the ribbon absolutely must be at the top of the page. You can add this bit of code after the above code to properly affix the div to the top of the visible window. My tests showed that this worked for me in IE8, IE 9, and Firefox.

body #s4-ribbonrow {
    left: 0;
    position: fixed;
    top: 0;
    width: 100%;
    z-index: 101;
}
body #s4-workspace {
    padding-top: 44px;
}

That should be it. Not too hard.





Tuesday, December 27, 2011

SharePoint 2010 Custom Application Page jQuery Lightbox Plug-in Fix

I used a fantastic jQuery lightbox plug-in which can be found at http://leandrovieira.com/projects/jquery/lightbox/. There was almost no setup involved. That is, until it meets SharePoint 2010 custom application pages. Since the page doesn't use the body scroll bars and creates faux-scroll bars in the s4-workspace div, it is possible that the image is too large for the visible area and there are no functional scroll bars to view the rest of the picture. I should suffix my last statement with the fact that this probably would have happened with any lightbox plug-in; it just happened that this is the plug-in I used.

I was tasked with finding a fix and below is the result. I only had to add code to the beginning of two functions: _set_interface and _finish. The short of it is that I cache the important styles that I am going to change, then I modify the styles to enable the page scroll bars. When the lightbox is closed, the cached styles are restored.

var htmlbody = $("BODY"),
    bodyMaster = $("body.v4master"),
    bodyWorkspace = $("body #s4-workspace"),
    savedCSS = {
        BodyOverflow: htmlbody.css("overflow"),

        BodyMasterHeight: bodyMaster.css("height"),
        BodyMasterWidth: bodyMaster.css("width"),
        BodyMasterOverflow: bodyMaster.css("overflow"),

        BodyWorkspaceOverflowY: bodyWorkspace.css("overflow-y"),
        BodyWorkspaceOverflowX: bodyWorkspace.css("overflow-x"),
        BodyWorkspaceHeight: bodyWorkspace.css("height")
    };
    settings.SavedCSS = savedCSS;
            
htmlbody.css({ "overflow": "auto" });
bodyMaster.css({ "height": "inherit", "width": "inherit", "overflow": "visible" });
bodyWorkspace.css({ "overflow-y": "auto", "overflow-x": "auto", "height": "auto" });

var htmlbody = $("BODY"),
    bodyMaster = $("body.v4master"),
    bodyWorkspace = $("body #s4-workspace"),
    savedCSS = settings.SavedCSS;

htmlbody.css({ "overflow": savedCSS.BodyOverflow });
bodyMaster.css({ "height": savedCSS.BodyMasterHeight, "width": savedCSS.BodyMasterWidth, "overflow": savedCSS.BodyMasterOverflow });
bodyWorkspace.css({ "overflow-y": savedCSS.BodyWorkspaceOverflowY, "overflow-x": savedCSS.BodyWorkspaceOverflowX, "height": savedCSS.BodyWorkspaceHeight });

SharePoint 2010 Custom Application Page Scroll To Top On Postback

SharePoint 2010 is full of wonderful features that make developers' lives just a bit harder. I ran across an issue where validation was returning a message back to the screen, the page would display the page scrolled to the top and then immediately scroll down to the position to the location of the page prior to posting back. I have had previous run-ins with the s4-workspace, but nothing JavaScript related. I tried several avenues for solutions:

1. Setting the page directive attribute "MaintainScrollPosition" to be false
2. Registering a start up script: $(window).scrollTop(0)
3. Registering a start up script: $("#s4-workspace").scrollTop(0)
4. Attempted to register the the functions via a client script block to add a "pageLoaded" event

The short of it was that none of these worked. I decided to dive in to the HTML source and discovered a "_maintainWorkspaceScrollPosition" hidden field. This looked amazingly like the MaintainScrollPosition functionality, I thought I might be on the right path. The "Workspace" term jumped out at me since the SharePoint custom application page's content is in the s4-workspace; I started to get the feeling that this was a SharePoint feature. After searching all the files for the hidden field, I discovered it was only referenced in the SharePoint JavaScript files. Searching the internet did not yield any solutions on ways to disable the feature. So I generated a function that I would execute to scroll the page to the top.

function scrollToTop() {
    $(window).scrollTop(0);
    $("#s4-workspace").scrollTop(0);
    $("#_maintainWorkspaceScrollPosition").val(0);
}

The function above probably does more than required, but I don't control the Master Page and need to make sure the page can tolerate changes to Master Page style changes.

The server side needs to register a script to execute on the post back. This is a simple line that can be thrown about anywhere.

ScriptManager.RegisterStartupScript(this, this.Page.GetType(), "scrollToTop", "scrollToTop();", true)