AngularJS – Load drop down list from sql (API)

I have a lookup table in SQL with the values I need in the drop down list.

Starting with:

1. Lookup Model
   

   	namespace MyWeb.Models
   	{
   	public class Lookups
   	{
   	#region Instance Properties
   	public Int32 ID { get; set; }
   	public string LookupType { get; set; }
   	public string LookupKey { get; set; }
   	public string LookupValue { get; set; }
   	#endregion Instance Properties
   	}
   	}

   view raw MyWebModel.cs hosted with ❤ by GitHub


2. WebApiConfig.cs in App_Start
   

   	using System;
   	using System.Collections.Generic;
   	using System.Linq;
   	using System.Web.Http;
   	namespace MyWeb
   	{
   	public static class WebApiConfig
   	{
   	public static void Register(HttpConfiguration config)
   	{
   	config.MapHttpAttributeRoutes();
   	config.Routes.MapHttpRoute(
   	name: "DefaultApi",
   	routeTemplate: "api/{controller}/{id}",
   	defaults: new { id = RouteParameter.Optional }
   	);
   	}
   	}
   	}

   view raw MyWebAPIConfig.cs hosted with ❤ by GitHub

Steps:

1. Create an API controller
   

   	using System;
   	using System.Collections.Generic;
   	using System.Data;
   	using System.Data.Entity;
   	using System.Data.Entity.Infrastructure;
   	using System.Linq;
   	using System.Net;
   	using System.Net.Http;
   	using System.Web.Http;
   	using System.Web.Http.Description;
   	using MyWeb.Models;
   	namespace MyWeb.Controllers
   	{
   	public class LookupsAPIController : ApiController
   	{
   	private ApplicationDbContext db = new ApplicationDbContext();
   	// GET: api/LookupsAPI
   	public IQueryable<Lookups> GetLookups()
   	{
   	return db.Lookups;
   	}
   	protected override void Dispose(bool disposing)
   	{
   	if (disposing)
   	{
   	db.Dispose();
   	}
   	base.Dispose(disposing);
   	}
   	}
   	}

   view raw MyWebLookupAPIController.cs hosted with ❤ by GitHub
   
   I'm using Entity Framework, so I right clicked on Controllers, Add, Controller. I then selected Web API 2 Controller with acitons.






2. Override the routing to create a different type of call
   

   	// GET: api/LookupsAPI/State
   	[Route("api/getLookupsAPI/{LookupType}")]
   	public IQueryable<Lookups> GetLookupsByType(string LookupType)
   	{
   	return db.Lookups.Where(lookup => lookup.LookupType == LookupType);
   	}

   view raw MyWebLookupAPIControllerLookupType.cs hosted with ❤ by GitHub
   
   *** Great overview of Custom Routing - http://www.asp.net/web-api/overview/web-api-routing-and-actions/attribute-routing-in-web-api-2



3. Create SetLookups function in the AngularJS controller script
   

   	function SetLookups($scope, $http) {
   	// Create drop down lists
   	$http({ method: 'GET', url: 'api/getLookupsAPI/States' }).
   	success(function (data) { $scope.States = data; }).
   	error(function (data) { alert('did not get states'); });
   	}

   view raw MyWebLookups.js hosted with ❤ by GitHub



4. Call SetLookups from AngularJS controller
   

   	app.controller('IncidentEditController',['$scope', '$filter', '$http', '$routeParams','$location','ClaimsAPI', function ($scope, $filter, $http, $routeParams, $location, ClaimsAPI) {
   	$scope.editClaim = this;
   	var claimId = $routeParams.id;
   	// Prep for the drop down fields
   	SetLookups($scope, $http);
   	ClaimsAPI.getClaim(claimId)
   	.success( function ( incs ) {
   	$scope.editClaim.Claim = incs;
   	$scope.status = incs.ID;
   	})
   	.error( function ( error ) {
   	$scope.status = 'Unable to load customer data: ' + error.message;
   	});
   	$scope.editClaim.destroy = function () {
   	ClaimsAPI.deleteClaims( claimId ).then( function ( data ) {
   	$location.path('/');
   	});
   	};
   	$scope.editClaim.save = function () {
   	$scope.$broadcast('show-errors-check-validity');
   	if ($scope.myForm.$valid) {
   	ClaimsAPI.updateClaim($scope.editClaim.Claim).then(function (data) {
   	$location.path('/');
   	});
   	}
   	};
   	});
   	}]);

   view raw MyWebClaimController.js hosted with ❤ by GitHub



5. Create Select tab using ng-options to load
   

   	<div class="row">
   	<div class="form-group col-xs-6">
   	<label class="control-label col-xs-4">State</label>
   	<div class="col-xs-8">
   	<select ng-model="editClaim.Claim.State" ng-options="state.LookupKey as state.LookupValue for state in States"></select>
   	</div>
   	</div>
   	<div class="form-group col-xs-6">
   	<label class="control-label col-xs-4">Zip</label>
   	<div class="col-xs-8">
   	<input type="text" ng-model="editClaim.Claim.Zip" placeholder="99999-9999">
   	</div>
   	</div>
   	</div>

   view raw MyWebClaimDetail.html hosted with ❤ by GitHub

SSIS - Excel import with unknown number of columns

I have a bunch of xls files I want to import into a table. I don't know the name of the files, the name of the first Sheet, nor the amount of columns. So here is at least one way to do it.

Step 1: Foreach Loop Container
I'll use the Foreach Loop Container in the Control Flow to pick up each unknown files.

Step 1a: Add the Foreach Loop Container


Step 1b: Select the folder and set the Files to *.xls. I used an expression to set the directory so I can set that dynamically. I'm pulling it from a SQL table.


Step 1c: In the Variable Mappings, set a Variable to the 0 Index. This will be the full URI to the file.


Step 2: Get the name of the first Sheet to know what to pull in.

Step 2a: Add a Script Task inside the Foreach Loop Container in Control Flow.


Step 2b: Add Variables to be used in the script


*** UPDATE *** : If you want to add XLSX to this also, then you will need to download and install this driver from Microsoft. I've updated the code (2c and 3g) to include reference to this driver for XLSX files.

Step 2c: Add this script to get the first Sheet name.

using System;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;
using System.Xml;
using System.Data.OleDb;
using System.Diagnostics;

namespace ST_8f8a8b14140a4ded9dcbbf6aa5b97090.csproj
{
    [System.AddIn.AddIn("ScriptMain", Version = "1.0", Publisher = "", Description = "")]
    public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
    {

        #region VSTA generated code
        enum ScriptResults
        {
            Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
            Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
        };
        #endregion

        public void Main()
        {
            string excelFile;
            string connectionString;
            OleDbConnection excelConnection;
            DataTable tablesInFile;
            int tableCount = 0;
            string currentTable;

            try
            {
                Trace.WriteLine(string.Format("Sheet Name: {0} File Location: {1}", Dts.Variables["SheetName"].Value, Dts.Variables["ImportFilePath"].Value));
                string extension = Path.GetExtension(excelFile);
                extension = extension.Replace(".", "");

                if (extension.ToLower() == "xlsx")
                {
                    connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;" +
                        "Data Source=" + excelFile + ";Extended Properties=Excel 12.0";
                }
                else
                {
                    connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" +
                        "Data Source=" + excelFile + ";Extended Properties=Excel 8.0";
                }
                excelConnection = new OleDbConnection(connectionString);
                excelConnection.Open();
                tablesInFile = excelConnection.GetSchema("Tables");
                tableCount = tablesInFile.Rows.Count;
                if (tableCount > 0)
                {
                    currentTable = tablesInFile.Rows[0]["TABLE_NAME"].ToString();

                    Dts.Variables["SheetName"].Value = currentTable;

                    Dts.TaskResult = (int)ScriptResults.Success;

                    Trace.WriteLine(string.Format("Sheet Name: {0}",Dts.Variables["SheetName"].Value));
                }
                else
                {
                    Dts.Variables["ErrorCode"].Value = "Excel Sheet Lookup Failure. -- No Sheets Found.";
                    Dts.TaskResult = (int)ScriptResults.Failure;
                }
            }
            catch (Exception e)
            {
                Dts.Variables["ErrorCode"].Value = "Excel Sheet Lookup Failure. -- " + e.Message;
                throw new Exception("Excel Sheet Lookup Failure.", e);
            }

        }
    }
}

Step 3: Create a Data Flow to handle importing the data in.

Step 3a: Add the data flow task. Then enter that data flow.


Step 3b : Add a Script Component - I finally went with the Script Component because I couldn't get an Excel Source Task to work consistantly. If the file had formating or had less columns than I preset in the Excel Source Task, I'd get issues.


Step 3c: Select Source as the script type


Step 3d: Add Variables with the file location and sheet name


Step 3e: No Connection Manager is needed if you are going to work with the imported data in the data flow before inserting it into a destination Connection Manager, like ADO SQL connection.


Step 3f: Add Output Fields - I added 25 String[DT_STR] fields 255 characters long.


*** UPDATE *** : As mentioned in the comments, if the code gets stuck on the excelReader.Read, then you may need to move the call to GetDataFromExcelToReader(Variables.ImportFilePath) from the PreExecute method to the CreateNewOutputRows method, just before the while loop.

Step 3g: Add this script

using System;
using System.Data;
using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
using Microsoft.SqlServer.Dts.Runtime.Wrapper;
using System.IO;
using System.Data.SqlClient;
using System.Data.OleDb;
using System.Diagnostics;

[Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute]
public class ScriptMain : UserComponent
{
    private OleDbDataReader excelReader;
    private OleDbConnection excelConnection;
    private OleDbCommand excelCommand;

    public override void PreExecute()
    {
        base.PreExecute();

        // Open connection
        GetDataFromExcelToReader(Variables.ImportFilePath);
    }

    public override void PostExecute()
    {
        base.PostExecute();

        excelReader.Close();

        excelConnection.Close();
    }

    private void GetDataFromExcelToReader(string p_strFileName)
    {

        string l_strConnectionString;
        
        if (File.Exists(p_strFileName))
        {
            string extension = Path.GetExtension(p_strFileName);
            extension = extension.Replace(".", "");

            if (extension.ToLower() == "xlsx")
            {
                l_strConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;" +
                    "Data Source=" + p_strFileName + ";Extended Properties=\"Excel 12.0;HDR=NO;IMEX=1\";";
            }
            else
            {
                l_strConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" +
                    "Data Source=" + p_strFileName + ";Extended Properties=\"Excel 4.0;HDR=NO;IMEX=1\";";
            }
            excelConnection = new OleDbConnection(l_strConnectionString);
            excelConnection.Open();
            excelCommand = excelConnection.CreateCommand();
            excelCommand.CommandText = "SELECT * FROM [" + Variables.SheetName.ToString() + "]";
            excelCommand.CommandType = CommandType.Text;
            excelReader = excelCommand.ExecuteReader();

        }
    }

    public override void CreateNewOutputRows()
    {
        while (excelReader.Read())
        {
            Output0Buffer.AddRow();
            Output0Buffer.F1 = excelReader.FieldCount > 0 ? excelReader[0].ToString() : "";
            Output0Buffer.F2 = excelReader.FieldCount > 1 ? excelReader[1].ToString() : "";
            Output0Buffer.F3 = excelReader.FieldCount > 2 ? excelReader[2].ToString() : "";
            Output0Buffer.F4 = excelReader.FieldCount > 3 ? excelReader[3].ToString() : "";
            Output0Buffer.F5 = excelReader.FieldCount > 4 ? excelReader[4].ToString() : "";
            Output0Buffer.F6 = excelReader.FieldCount > 5 ? excelReader[5].ToString() : "";
            Output0Buffer.F7 = excelReader.FieldCount > 6 ? excelReader[6].ToString() : "";
            Output0Buffer.F8 = excelReader.FieldCount > 7 ? excelReader[7].ToString() : "";
            Output0Buffer.F9 = excelReader.FieldCount > 8 ? excelReader[8].ToString() : "";
            Output0Buffer.F10 = excelReader.FieldCount > 9 ? excelReader[9].ToString() : "";
            Output0Buffer.F11 = excelReader.FieldCount > 10 ? excelReader[10].ToString() : "";
            Output0Buffer.F12 = excelReader.FieldCount > 11 ? excelReader[11].ToString() : "";
            Output0Buffer.F13 = excelReader.FieldCount > 12 ? excelReader[12].ToString() : "";
            Output0Buffer.F14 = excelReader.FieldCount > 13 ? excelReader[13].ToString() : "";
            Output0Buffer.F15 = excelReader.FieldCount > 14 ? excelReader[14].ToString() : "";
            Output0Buffer.F16 = excelReader.FieldCount > 15 ? excelReader[15].ToString() : "";
            Output0Buffer.F17 = excelReader.FieldCount > 16 ? excelReader[16].ToString() : "";
            Output0Buffer.F18 = excelReader.FieldCount > 17 ? excelReader[17].ToString() : "";
            Output0Buffer.F19 = excelReader.FieldCount > 18 ? excelReader[18].ToString() : "";
            Output0Buffer.F20 = excelReader.FieldCount > 19 ? excelReader[19].ToString() : "";
            Output0Buffer.F21 = excelReader.FieldCount > 20 ? excelReader[20].ToString() : "";
            Output0Buffer.F22 = excelReader.FieldCount > 21 ? excelReader[21].ToString() : "";
            Output0Buffer.F23 = excelReader.FieldCount > 22 ? excelReader[22].ToString() : "";
            Output0Buffer.F24 = excelReader.FieldCount > 23 ? excelReader[23].ToString() : "";
            Output0Buffer.F25 = excelReader.FieldCount > 24 ? excelReader[24].ToString() : "";
        }
    }
}

Step 4: Use the data you've imported as needed. Massage it. Push it into SQL tables. Merge it with other data.

SSIS - Excel Import Column with Strings and Numbers

While setting up an Excel Source, I was having an issue where I had a column with both strings and decimal number. If the Numbers were in the top rows, then it would only pull in the numbers. If only the strings were in the top rows, then it would only pull in strings and ignore the numbers.

Issue:

Excel looks at the first few rows to determine the column data type. And for some reason, when it decides a column is a string, it ignores all numbers in that column.

Solution:

In the Excel Connection Manager, under the Connection String, it will look something like this.

Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Files\FileName.xls;Extended Properties="EXCEL 8.0;HDR=NO";

You just need to add this code to the Extended Properties.

IMEX=1

So it now looks like this.

Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Files\FileName.xls;Extended Properties="EXCEL 8.0;HDR=NO;IMEX=1";

This command tells Excel that it has mixed data types in columns.

Found this solution here.

Change order of fields in 'Add new item' for a list

We really needed the order of the fields on the "Add New Item" to change. Here is how we found to do that.

1. Have the list showing
2. Click on List under List Tools on ribbon
3. Click List Settings
4. Under Content Types, click on the Main Content Type. In my case "Task"
5. Click on "Column order"

SharePoint Feature that adds web.config settings and copies SSRS files to IIS folder

I wanted to create SSRS reports for my SharePoint ASP.NET application. I wanted to make everything about the reports to be a part of a Feature to add.

I will have a Web.Config change to make and RDLC files to copy over to the IIS folder with User permissions set.

Here is what I came up with. On FeatureActivated, I call a function to add the Web.Config settings and then another to copy over the RDLC files.

I created an array of SPWebconfigModifications. You will see a couple I commented out so you can get a better feel for how these settings work. I have three modifications occur. The first adds the HTTPHandler needed for the ReportViewer. The second updates that same record if it already exists. I did this because we had a web.config that had the HTTPHandler already but with version 8.0.0.0. So my add child node did not do anything because it was already there. So the update attribute did the trick. The third change is to add a Remove in the appSettings. There is a setting that is automatically added, but it will error in SharePoint, so I've found the answer out there to be to remove that from the appSettings. The only thing I would want to do to upgrade this is to add code to see if the HTTPHandler is already there or not, then only do the first or second modification but not both.

You will notice I am running in Elevated Privileges mode. You will need this unless your feature is a WebApplication feature. Anything lower will not have permission to change web.config settings.

While copying the files over, I first add a folder if not already present. Didn’t want all those RDLC files sitting in the main folder where the web.config file is. There could be one issue here, which is worrying about the URI length. I did not worry about it in this code, but if you want to add that change, please feel free to post back the changes needed.

This is a great way to add all those web.config settings needed for AJAX, just throw them in the array of SPWebconfigModifications and you will have a Feature that loads/unloads the AJAX web.config settings.

using System;
using System.Collections.Generic;
using System.Text;
using System.IO;
using System.Web.Hosting;
using Microsoft.SharePoint;
using Microsoft.SharePoint.Administration;
using Microsoft.SharePoint.Utilities;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using GroupPublishing.VBSPro.Core;
using System.Diagnostics;
using GroupPublishing.VBSPro.Core.Configuration;
using System.Collections.ObjectModel;
using System.Security.AccessControl;
using System.Security.Principal;

namespace GroupPublishing.VBSPro.Web.FeatureReceivers
{
    class VBSProReportsFeatureReceiver : SPFeatureReceiver
    {
        private static SPWebConfigModification[] WebConfigModifications = {
            //new SPWebConfigModification("CallStack", "configuration/SharePoint/SafeMode")
            //    { Owner = "motion10DebugSwitch", Sequence = 0, Type = SPWebConfigModification.SPWebConfigModificationType.EnsureAttribute, Value = "true" },
            //new SPWebConfigModification("mode", "configuration/system.web/customErrors")
            //    { Owner = "motion10DebugSwitch", Sequence = 0, Type = SPWebConfigModification.SPWebConfigModificationType.EnsureAttribute, Value = "Off" },
            //new SPWebConfigModification("debug", "configuration/system.web/compilation")
            //    { Owner = "motion10DebugSwitch", Sequence = 0, Type = SPWebConfigModification.SPWebConfigModificationType.EnsureAttribute, Value = "true" },

            // Add HttpHandler
            new SPWebConfigModification("add[@path='Reserved.ReportViewerWebControl.axd']", "configuration/system.web/httpHandlers")
                { Owner = "FeatureOwner", Sequence = 0, Type = SPWebConfigModification.SPWebConfigModificationType.EnsureChildNode, Value = @"<add verb='*' path='Reserved.ReportViewerWebControl.axd' type='Microsoft.Reporting.WebForms.HttpHandler, Microsoft.ReportViewer.WebForms, Version=9.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a' />" },


            // Update HttpHandler
            new SPWebConfigModification("type", "configuration/system.web/httpHandlers/add[@path='Reserved.ReportViewerWebControl.axd']")
                { Owner = "FeatureOwner", Sequence = 0, Type = SPWebConfigModification.SPWebConfigModificationType.EnsureAttribute, Value = "Microsoft.Reporting.WebForms.HttpHandler, Microsoft.ReportViewer.WebForms, Version=9.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a" },


            // Add appSetting
            new SPWebConfigModification("remove[@key='ReportViewerMessages']", "configuration/appSettings")
                { Owner = "FeatureOwner", Sequence = 0, Type = SPWebConfigModification.SPWebConfigModificationType.EnsureChildNode, Value = @"<remove key='ReportViewerMessages' />" }


        };

        private static string FolderName = "Reports";
                
        public override void FeatureActivated(SPFeatureReceiverProperties properties)
        {
            try
            {
                //Deploy WebConfig Settings
                DeployWebConfigSettings(properties);

                //Deploy Report Files
                DeployReportFiles(properties);
            }
            catch (Exception ex)
            {
                throw new SPException("Error during Activation.  See event log for further details");
            }
        }

        public override void FeatureDeactivating(SPFeatureReceiverProperties properties)
        {
            try
            {
                // Remove WebConfig Settings
                RemoveWebConfigSettings(properties);
    
                // Remove Reports
                RemoveReportFiles(properties);

            }
            catch (Exception ex)
            {
                throw new SPException("Error during Feature Deactivation. See event log for further details");
            }
        }

        public override void FeatureInstalled(SPFeatureReceiverProperties properties)
        {
        }

        public override void FeatureUninstalling(SPFeatureReceiverProperties properties)
        {
        }

        private void DeployWebConfigSettings(SPFeatureReceiverProperties properties)
        {
            //Remove any previous WebConfig Settings by this Feature
            RemoveWebConfigSettings(properties);

            SPSecurity.RunWithElevatedPrivileges(
                delegate()
                {
                    String _Owner;
                    // Get Owner Name
                    _Owner = properties.Feature.DefinitionId.ToString();

                    // Get SPSite URL
                    string spSiteURL = ((SPSite)properties.Feature.Parent).Url;

                    // Open new instance of SPSIte
                    using (SPSite oSiteCollection = new SPSite(spSiteURL))
                    {
                        // Get SPWebApplication
                        SPWebApplication webApp = oSiteCollection.WebApplication;

                        // Add WebConfig Modifications
                        foreach (SPWebConfigModification modification in WebConfigModifications)
                        {
                            modification.Owner = _Owner;
                            webApp.WebConfigModifications.Add(modification);
                        }

                        // Apply Web App Mod
                        webApp.Farm.Services.GetValue<SPWebService>().ApplyWebConfigModifications();

                        // Serialize the web application state and propagate changes across the farm. 
                        webApp.Update();
                    }
                });
        }

        private void RemoveWebConfigSettings(SPFeatureReceiverProperties properties)
        {
            SPSecurity.RunWithElevatedPrivileges(
                delegate()
                {
                    String _Owner;
                    // Get Owner Name
                    _Owner = properties.Feature.DefinitionId.ToString();

                    // Get SPSite URL
                    string spSiteURL = ((SPSite)properties.Feature.Parent).Url;

                    // Open new instance of SPSIte
                    using (SPSite oSiteCollection = new SPSite(spSiteURL))
                    {
                        // Get SPWebApplication
                        SPWebApplication webApp = oSiteCollection.WebApplication;

                        // Delete any WebApp Mods for this Feature
                        Collection<SPWebConfigModification> collection = webApp.WebConfigModifications;
                        int iStartCount = collection.Count;
                        // Remove any modifications that were originally created by the owner.            
                        for (int c = iStartCount - 1; c >= 0; c--)
                        {
                            SPWebConfigModification configMod = collection[c];
                            if (configMod.Owner == _Owner)
                                collection.Remove(configMod);
                        }

                        // Apply changes only if any items were removed.    
                        if (iStartCount > collection.Count)
                        {
                            Microsoft.SharePoint.Administration.SPFarm.Local.Services.GetValue<Microsoft.SharePoint.Administration.SPWebService>().ApplyWebConfigModifications();
                            webApp.Update();
                        }
                    }
                });
        }

        private void DeployReportFiles(SPFeatureReceiverProperties properties)
        {
            // first remove report files if they exist
            RemoveReportFiles(properties);

            SPSecurity.RunWithElevatedPrivileges(
               delegate()
               {
                   // Get SPSite URL
                   string spSiteURL = ((SPSite)properties.Feature.Parent).Url;

                   // Open new instance of SPSIte
                   using (SPSite oSiteCollection = new SPSite(spSiteURL))
                   {
                       // Get SPWebApplication
                       SPWebApplication webApp = oSiteCollection.WebApplication;

                       // loop through each IisSettings dictionary pair (zones [dafault, intranet, internet, etc.] configured for web application)
                       foreach (KeyValuePair<SPUrlZone, SPIisSettings> pair in webApp.IisSettings)
                       {
                           // add folder if not exists
                           if (!Directory.Exists(pair.Value.Path.FullName.ToString() + @"\" + FolderName))
                           {
                           Directory.CreateDirectory(pair.Value.Path.FullName.ToString() + @"\" + FolderName);
                           }

                           string[] fileList = System.IO.Directory.GetFiles(SPUtility.GetGenericSetupPath(@"TEMPLATE\FEATURES\VBSProReports\Reports"));
                           foreach (string fil in fileList)
                           {
                               string[] finalFile = fil.ToString().Split('\\');
                               string _destination = pair.Value.Path.FullName.ToString() + @"\" + FolderName + @"\" + finalFile.GetValue(finalFile.Length - 1).ToString();
                               File.Copy(fil, _destination, true);

                               // add security
                               FileSecurity fs2 = File.GetAccessControl(_destination);
                               FileSystemAccessRule accessRule = new FileSystemAccessRule("Users", FileSystemRights.ReadAndExecute, AccessControlType.Allow);
                               fs2.AddAccessRule(accessRule);
                               File.SetAccessControl(_destination, fs2);
                           }
                       }     
                   }
               });
        }

        private void RemoveReportFiles(SPFeatureReceiverProperties properties)
        {
            SPSecurity.RunWithElevatedPrivileges(
                delegate()
                {
                    // Get SPSite URL
                    string spSiteURL = ((SPSite)properties.Feature.Parent).Url;

                    // Open new instance of SPSIte
                    using (SPSite oSiteCollection = new SPSite(spSiteURL))
                    {
                        // Get SPWebApplication
                        SPWebApplication webApp = oSiteCollection.WebApplication;

                        // loop through each IisSettings dictionary pair (zones [dafault, intranet, internet, etc.] configured for web application)
                        foreach (KeyValuePair<SPUrlZone, SPIisSettings> pair in webApp.IisSettings)
                        {
                            string[] fileList = System.IO.Directory.GetFiles(SPUtility.GetGenericSetupPath(@"TEMPLATE\FEATURES\VBSProReports\Reports"));
                            foreach (string fil in fileList)
                            {
                                string[] finalFile = fil.ToString().Split('\\');
                                if (Directory.Exists(pair.Value.Path.FullName.ToString() + @"\" + FolderName))
                                {
                                    string _destination = pair.Value.Path.FullName.ToString() + @"\" + FolderName + @"\" + finalFile.GetValue(finalFile.Length - 1).ToString();
                                    File.Copy(fil, _destination, true);
                                    // if report exists, delete
                                    if (File.Exists(_destination))
                                        File.Delete(_destination);
                                }
                            }
                        }
                    }
                });
        }
    }
}

Content Editor Web Part - Relative URLs

When you use the content editor web part, you will find out that when you select an image, it saves it as the absolute URL of that image, even if you type in a relative address. If you remember, you can fix this each time by clicking on "Source Editor..." just under "Rich Text Editor..." in the settings of the content editor web part, and deleting the absolute part of the absolute URLs.

One problem with that (besides all the work and remembering) is if your content will be deployed to the live site, the URLs will still be pointing to the staging environment.

This was obviously not acceptable to our client.

We can fix this by creating an event receiver and overriding the ItemUpdating event of the Pages library.

This is one approach to handling this. There are other approaches to get similar results.

Create a project that will hold the SPItemEventReceiver and the SPFeatureReceiver.

Create a Site (Web) Feature that will run the SPItemEventReceiver on the Pages library of that Web.

Create a Site Collection (Site) Feature that will run the SPFeatureReceiver in which activates/deactivates the Web Feature (B.) on all Webs in the Site Collection.


Create a project that will hold the SPItemEventReceiver and the SPFeatureReceiver.

1. Create a new class library project in Visual Studio with the name ContentEditorWebPart_RelativeURLs.
2. In the new project “Add Reference” to…
  a. Microsoft.SharePoint (which is Windows SharePoint Services in the .NET list)
  b. System.Configuration
  c. System.Web
3. In the Code Editor, rename the namespace and add the import namespaces

using System.Web; this is a test. This is only a test. This is still a test of the testing test.
using System.Web.UI.WebControls.WebParts;
using System.Configuration;

namespace ContentEditorWebPart_RelativeURLs
{
    …
}

4. Change the name of the class to ForceRelativeUrlItem and make it inherit from the SPItemEventReceiver class, as follows.

public class ForceRelativeUrlItem : SPItemEventReceiver
{
   …
}

5. Add the following code within the class to override the ItemUpdating method.
  a. You will see a bunch of Debug lines including a lookup into the web.config, where you can set an AppSetting for if you want the Debug lines to run or not. I like to have these all over for whenever I’m debugging by having DebugView running while running the site. I then turn them off in the web.config when I am done.
  b. We will get down looping through all the web parts in the Pages library looking for all Content Editor Web Parts and then modifying the content by clearing the Site’s URL out of the Content String.
  c. In a later step you will see how this is attached to just the Pages library’s ItemUpdating.

private static string DebugName = "Debug_ContentEditorWebPart_CS";
private static bool DebugEnabled = (ConfigurationManager.AppSettings[DebugName] != null ? (ConfigurationManager.AppSettings[DebugName].ToLower().ToString() == "true" ? true : false) : true);

public override void ItemUpdating(SPItemEventProperties properties)
{
    if (DebugEnabled) Debug.WriteLine(string.Format("CEWP:ItemUpdating:Begin"));

    if (DebugEnabled) Debug.WriteLine(string.Format("CEWP:ItemUpdating:DisableEventFiring"));
    // Disable while in this method.
    this.DisableEventFiring();

    if (DebugEnabled) Debug.WriteLine(string.Format("CEWP:ItemUpdating:ListTemplateId: {0}", Int32.Parse(properties.ListItem.ParentList.BaseTemplate.ToString())));

    // get a reference to the list item (the page in this case)
    SPListItem _SPListItem = properties.ListItem;
    if (DebugEnabled) Debug.WriteLine(string.Format("CEWP:ItemUpdating:SPListItem:Name: {0}", _SPListItem.Name));

    // get a reference to the containing SPWeb
    using (SPWeb _SPWeb = _SPListItem.Web)
    {
       if (DebugEnabled) Debug.WriteLine(string.Format("CEWP:ItemUpdating:SPWeb:Name: {0}", _SPWeb.Name));

        // get a reference to the the web part manager on the page 
        using (SPLimitedWebPartManager _SPLimitedWebPartManager = _SPWeb.GetLimitedWebPartManager(_SPListItem.Url, PersonalizationScope.Shared))
        {
            if (DebugEnabled) Debug.WriteLine(string.Format("CEWP:ItemUpdating:SPLimitedWebPartManager:Count: {0}", _SPLimitedWebPartManager.WebParts.Count));

            // loop through all of the web parts on the page and update
            // all of the CEWP's
            Microsoft.SharePoint.WebPartPages.SPLimitedWebPartCollection LimitedWebParts1 = _SPLimitedWebPartManager.WebParts;
            foreach (System.Web.UI.WebControls.WebParts.WebPart _WebPart in LimitedWebParts1) 
            {
                if (DebugEnabled) Debug.WriteLine(string.Format("CEWP:ItemUpdating:WebPart:DisplayTitle: {0} Type: {1}", _WebPart.DisplayTitle, _WebPart.GetType().ToString()));
                // if WebPart is a CEWP
                if (_WebPart.GetType().Equals(typeof(ContentEditorWebPart)))
                {
                    using (ContentEditorWebPart _ContentEditorWebPart = (ContentEditorWebPart)_WebPart)
                    {
                        if (DebugEnabled) Debug.WriteLine(string.Format("CEWP:ItemUpdating:ContentEditorWebPart:DisplayTitle: {0}", _ContentEditorWebPart.DisplayTitle));

                        // get the contents of the CEWP
                        string _ContentString = _ContentEditorWebPart.Content.InnerText;
                        if (DebugEnabled) Debug.WriteLine(string.Format("CEWP:ItemUpdating:ContentEditorWebPart:Content(Before): {0}", _ContentString));

                        // remove the absolute url
                        _ContentString = _ContentString.Replace(_SPWeb.Site.RootWeb.Url, "");
                        if (DebugEnabled) Debug.WriteLine(string.Format("CEWP:ItemUpdating:ContentEditorWebPart:Content(After): {0}", _ContentString));
                        // create an Xml element to use to update the CEWP
                        XmlDocument _XmlDocument = new XmlDocument();
                        XmlElement _XmlElement = _XmlDocument.CreateElement("MyElement");
                        _XmlElement.InnerText = _ContentString;
                        if (DebugEnabled) Debug.WriteLine(string.Format("CEWP:ItemUpdating:XmlElement:InnerText: {0}", _XmlElement.InnerText));

                        // update the Content property of the CEWP
                        _ContentEditorWebPart.Content = _XmlElement;
                        if (DebugEnabled) Debug.WriteLine(string.Format("CEWP:ItemUpdating:ContentEditorWebPart:Change Content"));

                        try
                        {
                            // Save the changes
                            _SPLimitedWebPartManager.SaveChanges(_ContentEditorWebPart);
                            if (DebugEnabled) Debug.WriteLine(string.Format("CEWP:ItemUpdating:ContentEditorWebPart:Updated"));
                        }
                        catch (Exception ex)
                        {
                            Debug.Write("Content Web Part - Relative URLs Feature \n " + ex.Message);
                        }
                    }
                }
            }
        }
    }
    // Enable Event Firing again
    this.EnableEventFiring();
    if (DebugEnabled) Debug.WriteLine(string.Format("CEWP:Ending"));

6. Add another class, as follows.

public class FeatureEventHandler : SPFeatureReceiver
{
   …
}

7. Add the following code within the class to override the Feature methods.

private static string DebugName = "Debug_ContentEditorWebPart_CS";
private static bool DebugEnabled = (ConfigurationManager.AppSettings[DebugName] != null && ConfigurationManager.AppSettings[DebugName].ToLower().ToString() == "true" ? true : false);

public override void FeatureActivated(SPFeatureReceiverProperties properties)
{
    if (DebugEnabled) Debug.WriteLine(string.Format("CEWP_SA:FeatureActivated:Begin"));
    // get the Site Collection
    using (SPSite site = SPContext.Current.Site)
    {
        if (DebugEnabled) Debug.WriteLine(string.Format("CEWP_SA:FeatureActivated:Site:URL: {0}", site.Url));
        // loop through all Webs in the Site Collection
        foreach (SPWeb web in site.AllWebs)
        {
            if (DebugEnabled) Debug.WriteLine(string.Format("CEWP_SA:FeatureActivated:Web(before):Name {0} Feature Cnt: {1}", web.Name, web.Features.Count));
            // Activate ContentEditorWebPart_RelativeURLs Feature on that particular Web
            web.Features.Add(new Guid("A2184210-B18E-4331-B029-CE55A2487328"), true);
            if (DebugEnabled) Debug.WriteLine(string.Format("CEWP_SA:FeatureActivated:Web(after):Name {0} Feature Cnt: {1}", web.Name, web.Features.Count));
        }
    }
    if (DebugEnabled) Debug.WriteLine(string.Format("CEWP_SA:FeatureActivated:End"));
}

public override void FeatureDeactivating(SPFeatureReceiverProperties properties)
{
    if (DebugEnabled) Debug.WriteLine(string.Format("CEWP_SA:FeatureDeactivating:Begin"));
    // get the Site Collection
    using (SPSite site = SPContext.Current.Site)
    {
        if (DebugEnabled) Debug.WriteLine(string.Format("CEWP_SA:FeatureDeactivating:Site:URL: {0}", site.Url));
        // loop through all Webs in the Site Collection
        foreach (SPWeb web in site.AllWebs)
        {
            if (DebugEnabled) Debug.WriteLine(string.Format("CEWP_SA:FeatureDeactivating:Web(before):Name {0} Feature Cnt: {1}", web.Name, web.Features.Count));
            // Activate ContentEditorWebPart_RelativeURLs Feature on that particular Web
            web.Features.Remove(new Guid("A2184210-B18E-4331-B029-CE55A2487328"));
            if (DebugEnabled) Debug.WriteLine(string.Format("CEWP_SA:FeatureDeactivating:Web(after):Name {0} Feature Cnt: {1}", web.Name, web.Features.Count));
        }
    }
    if (DebugEnabled) Debug.WriteLine(string.Format("CEWP_SA:FeatureDeactivating:End"));
}

public override void FeatureInstalled(SPFeatureReceiverProperties properties)
{
    // Do nothing
    if (DebugEnabled) Debug.WriteLine(string.Format("CEWP_SA:FeatureInstalled:Begin"));
}

public override void FeatureUninstalling(SPFeatureReceiverProperties properties)
{
    // Do Nothing
    if (DebugEnabled) Debug.WriteLine(string.Format("CEWP_SA:FeatureUninstalling:Begin"));
}

8. In Solution Explorer, right-click the ContentEditorWebPart_RelativeURLs node, and then click Properties.
9. In the Properties dialog box, click the Signing tab, select Sign the assembly, select Choose a strong name key file, and then click .
10. In the Create Strong Name Key dialog box, type ContentEditorWebPart_RelativeURLs.snk in the Key file name box, and then click OK.
11. Find the \ ContentEditorWebPart_RelativeURLs \bin\Debug folder in the Visual Studio Projects folder, and drag the ContentEditorWebPart_RelativeURLs.dll file to Local_Drive:\WINDOWS\assembly to place the DLL in the global assembly cache.
12. I always do an IISRESET at this point.

Create a Site (Web) Feature that will run the SPItemEventReceiver on the Pages library of that Web.

1. Create a folder in Local_Drive:/Program Files/Common Files/Microsoft Shared/web server extensions/12/TEMPLATE/FEATURES called ContentEditorWebPart_RelativeURLs.
2. Create a Feature.xml Files file in this folder like the following that identifies the Feature and its element manifest file and sets the Feature scope to Web site.

<Feature
 DefaultResourceFile="core"
 Description="Changes absolute urls to relative urls for all Content Editor Web Parts used in the Pages library."
 Id="GUID"
 Hidden="False"
 Scope="Web"
 Title="Content Editor Web Part - Force Relative Url"
 Version="1.0.0.0"
 xmlns="http://schemas.microsoft.com/sharepoint/">
 <ElementManifests>
   <ElementManifest Location="elements.xml"/>
 </ElementManifests>
</Feature>

3. To replace the GUID placeholder in the previous Id attribute, generate a GUID by running guidgen.exe located in Local_Drive:\Program Files\Microsoft Visual Studio 8.
4. Create an Elements.xml file in the ContentEditorWebPart_RelativeURLs folder that identifies the assembly, class, and method to implement as the event handler. This example applies the event handler to the Pages library of a web, as specified by the ListTemplateId attribute (850).

<Elements xmlns="http://schemas.microsoft.com/sharepoint/">
  <Receivers ListTemplateId="850">
    <Receiver>
      <Name>CEWP ItemUpdating - Relative URLs</Name>
      <Type>ItemUpdating</Type>
      <SequenceNumber>10000</SequenceNumber>
      <Assembly>ContentEditorWebPart_RelativeURLs, Version=1.0.0.0, Culture=neutral, PublicKeyToken=fb6673b46adc9058</Assembly>
      <Class>ContentEditorWebPart_RelativeURLs.ForceRelativeUrlItem</Class>
      <Data></Data>
      <Filter></Filter>
    </Receiver>
  </Receivers>
</Elements>

5. To get the Public Key Token of the assembly, in Windows Explorer find the ContentEditorWebPart_RelativeURLs.dll file in the Local_Drive:\WINDOWS\assembly, right-click the file, click Properties, and on the General tab of the Properties dialog box, select and copy the token.
6. At a command prompt, navigate to \Program Files\Common Files\Microsoft Shared\web server extensions\12\BIN on the local drive, and type each of the following commands to install the Feature in the deployment. We will activate the Feature inside the third main step (below) :

Stsadm –o installfeature –filename ContentEditorWebPart_RelativeURLs\feature.xml -force

Create a Site Collection (Site) Feature that will run the SPFeatureReceiver in which activates/deactivates the Web Feature (B.) on all Webs in the Site Collection.

1. Create a folder in Local_Drive:/Program Files/Common Files/Microsoft Shared/web server extensions/12/TEMPLATE/FEATURES called ContentEditorWebPart_RelativeURLs_SiteActivation.
2. Create a Feature.xml Files file in this folder like the following that identifies the Feature and its Receiver information and sets the Feature scope to Site (Site Collection).

<Feature
  Description="Activates the Web Feature 'Content Editor Web Part - Force Relative Url' to all webs on the farm."
  Hidden="False"
  Id="GUID"
  ReceiverAssembly="ContentEditorWebPart_RelativeURLs, Version=1.0.0.0, Culture=neutral, PublicKeyToken=xxxxxxxxxxxxxxxxx"
  ReceiverClass="ContentEditorWebPart_RelativeURLs.FeatureEventHandler"
  Scope="Site"
  Title="Content Editor Web Part - Force Relative Url - Activate All"
  Version="1.0.0.0"
  xmlns="http://schemas.microsoft.com/sharepoint/">
</Feature>

3. To get the Public Key Token of the assembly, in Windows Explorer find the ContentEditorWebPart_RelativeURLs_SiteActivation.dll file in the Local_Drive:\WINDOWS\assembly, right-click the file, click Properties, and on the General tab of the Properties dialog box, select and copy the token.
4. To replace the GUID placeholder in the previous Id attribute, generate a GUID by running guidgen.exe located in Local_Drive:\Program Files\Microsoft Visual Studio 8. I just used the same GUID as before, just changing the last number by 1.
5. At a command prompt, navigate to \Program Files\Common Files\Microsoft Shared\web server extensions\12\BIN on the local drive, and type each of the following commands to install the Feature in the deployment, activate the feature, and activate the other feature in all Webs :

Stsadm –o installfeature –filename ContentEditorWebPart_RelativeURLs_SiteActivation \feature.xml –force

stsadm -o activatefeature -filename ContentEditorWebPart_RelativeURLs_SiteActivation \Feature.xml -url http://Server/Site

iisreset

You will now notice that your URLs in the Content Editor Web Parts are all saved as Relative URLs.

Here are the main sites that got me through this. If you look into them, you will notice the first site addresses this directly, but from a slightly different angle.

http://www.devcow.com/blogs/jdattis/archive/2007/09/27/11463.aspx

http://msdn.microsoft.com/en-us/library/ms453149.aspx

http://www.u2u.info/Blogs/Patrick/Lists/Posts/Post.aspx?ID=1567

This item cannot be deleted because it is still referenced by other pages. (Page Layout or Master Page)

I have moved all references to a Page Layout to a different Page Layout. But when I try to delete the Page Layout, I get this error.

"This item cannot be deleted because it is still referenced by other pages”

After spending hours trying to modify my code, thinking I'm missing references somewhere, I finally searched around for this error. I found that it is a Microsoft known bug. Their workaround is to check the Hidden Page in the properties of the Page Layout or Master Page, and if it is currently a Master Page, change it to a Page Layout. That will keep it from being selectable when creating a page.

But, here is the real workaround that I found out there...

1. Create a folder inside the Master Page Gallery.
2. Move the Page Layout or Master Page you don't want and are not using anymore.
3. Delete the folder.

I can only guess at why it works, but it does, and I'm glad someone out there thought to try such a thing.