Serialize a. ADO.NET DataTable to a CSV String

Here is an example of querying a stored procedure and serializing the DataTable to a comma delimited string. The bonus with this with the reuse of the connection string in the associated Entity Data Model (EDM). I originally tried to use the connection object from the EDM, but I received errors sparatically when I opened the "StoreConnection" SQLConnection object.

Code notes:
"_baseContext" is an object of type "ObjectContext" is the result from the Entity Framework context's "GetBaseContext()" function.

The BuildXmlString function is the same from used in my SQL Multiple Item Update Using XML blog post. 

public string GetDemoExport(int[] DemoItemIDs)
{
    string connectionString = ((System.Data.EntityClient.EntityConnection)_baseContext.Connection).StoreConnection.ConnectionString;
    DataTable data = new DataTable();
    using (SqlConnection conn = new SqlConnection(connectionString))
    {
        //IDbConnection conn = ((System.Data.EntityClient.EntityConnection)_baseContext.Connection).StoreConnection;
        SqlCommand cmd = new SqlCommand("GetDemoExport", (SqlConnection)conn);
        cmd.CommandType = CommandType.StoredProcedure;
        string paramRoot = "DemoItemIDs";
        cmd.Parameters.AddWithValue("@" + paramRoot, BuildXmlString<int>(paramRoot, DemoItemIDs));

        conn.Open();
        SqlDataAdapter adapter = new SqlDataAdapter(cmd);
        adapter.Fill(data);
        conn.Close();
    }

    StringBuilder buffer = new StringBuilder();

    int colCount = data.Columns.Count;
    for (int idx = 0; idx < colCount; idx++)
    {
        if (buffer.Length > 0)
        {
            buffer.Append(",");
        }
        buffer.Append(data.Columns[idx].ColumnName);
    }
    buffer.AppendLine("");
    int rowCount = data.Rows.Count;
    for (int idx = 0; idx < rowCount; idx++)
    {
        DataRow row = data.Rows[idx];
        buffer.AppendLine(string.Join(",", row.ItemArray));
    }

    return buffer.ToString();
}

Ensure a SQL Table Has a Certain Number of Rows

Here is a useful simple script that can be modified to ensure a table has a certain number of rows. The example uses an insert statement, however I have used it to call a stored procedure that inserts rows (among other things). It is easier than copy/pasting insert/stored procedure statements.

DECLARE @Count as int
SELECT @Count = COUNT(DemoItemID) FROM DemoItems
WHILE @Count < 50
BEGIN
 INSERT INTO DemoItems ([DemoItemID])
  VALUES (@Count+1)
 SELECT @Count = COUNT(DemoItemID) FROM DemoItems
END
PRINT 'Done.'

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

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
    }
}

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);
        //}

    }
}

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.