Friday, October 21, 2016

Build connection string for Entity Framework programmatically via code or use Entity Framework in Sharepoint Timer jobs

Before to start I would like to mention that in this post Sharepoint timer jobs are used as example of case when app.config is not available (or it is better to not change it for adding EF configuration). But the same idea can be used also in other places where you don’t want to store EF connection string in configuration file or when it should be built dynamically.

Timer jobs is convenient mechanism when we need to do some actions in Sharepoint by scheduler. They are executed in separate process – Sharepoint Timer Service (it can be found with other services in Control panel > Administrative tools > Services). One of the common problem related with timer jobs is management of connection strings and other configuration data. Of course we can store it in app.config of owstimer.exe which is located here "C:\Program Files\Common Files\Microsoft shared\Web Server Extensions\15\bin". The problem however is that in most cases the same connection strings are also needed in basic web application and already specified in web.config of Sharepoint site. I.e. if we will add them to owstimer.exe.config they will be duplicated which will cause maintenance issues. In this article I will show how to avoid duplication when we need to use Entity Framework inside custom Sharepoint timer job. Described technique allows to avoid changing of owstimer.exe.config.

So suppose that you created simple console application, generated model from existing database (i.e. used Database First approach in terms of Entity Framework), debugged application and now want to move the code to Sharepoint custom job. After you will try to run the same code in Sharepoint job the following exception will be thrown:

Code generated using the T4 templates for Database First and Model First development may not work correctly if used in Code First mode. To continue using Database First or Model First ensure that the Entity Framework connection string is specified in the config file of executing application. To use these classes, that were generated from Database First or Model First, with Code First add any additional configuration using attributes or the DbModelBuilder API and then remove the code that throws this exception.

Message is a bit unclear, but the following article describes in more details reasons of this exception: Don’t use Code First by mistake. In short it happens because when we generated model from existing database, mappings between tables and classes were generated automatically and stored in generated .edmx file. EF DbContext must load it before first use which is done via special Entity Framework connection string. This string was added to app.config of your console application when you generated the model and that’s why it worked there. This EF connection string looks like this:

res://*/MyNamespace.DataModel.csdl|res://*/MyNamespace.DataModel.ssdl|res://*/MyNamespace.DataModel.msl;provider=System.Data.SqlClient;provider connection string="Data Source=.;Initial Catalog=MyDb;Integrated Security=True;multipleactiveresultsets=True; App=EntityFramework"

As you can see it differs from regular Sql connection string (last one is included into EF connection string as “provider connection string” part). So we need to provide this special connection string to the EF DbContext. Here is how we can do it:

   1: public partial class MyDataContext
   2: {
   3:     public MyDataContext(string dbConnectionString)
   4:         : base(getEFConnectionString(dbConnectionString))
   5:     {
   6:     }
   7:  
   8:     private static string getEFConnectionString(
   9: string dbConnectionString)
  10:     {
  11:         var builder = new EntityConnectionStringBuilder
  12:             {
  13:                 Metadata = "res://*/MyNamespace.DataModel.csdl|" +
  14:                     "res://*/MyNamespace.DataModel.ssdl|" +
  15:                     "res://*/MyNamespace.DataModel.msl",
  16:                 Provider = "System.Data.SqlClient",
  17:                 ProviderConnectionString = dbConnectionString
  18:             };
  19:         return builder.ToString();
  20:     }
  21: }

I.e. we create partial class for our DbContext (it should be located in the same namespace) and create new constructor with one parameter – Sql database connection string (note that we pass there exactly classic database connection string, not EF connection string). Then we build EF connection string by using EntityConnectionStringBuilder class and include Sql connection string to it.

Why we chose regular Sql database connection string as parameter and how we will get it in custom timer job? The answer is because in web.config of Sharepoint site there is exactly regular connection string:

   1: <connectionStrings>
   2:   <add name="MyConnectionString"
   3: connectionString="Data Source=.;Initial Catalog=MyDb;Integrated Security=True" />
   4: </connectionStrings>

I.e. if we would read connection string from web.config inside the job we would be able to build Entity Framework connection string dynamically and would avoid duplication of configuration data because connection string still would be stored in single place – in web.config. Also in this case no manual changes would be needed in owstimer.exe.config.

This can be achieved by using property bag of custom Sharepoint job: SPJobDefinition.Properties. The idea is that inside feature receiver which provisions the job we read connection string from web.config and store it in job’s property bag:

   1: public class MyEventReceiver : SPFeatureReceiver
   2: {
   3:     public override void FeatureActivated(
   4: SPFeatureReceiverProperties properties)
   5:     {
   6:         var parent = (SPWebApplication)properties.Feature.Parent;
   7:  
   8:         var config =
   9: WebConfigurationManager.OpenWebConfiguration("/", parent.Name);
  10:         if (config.ConnectionStrings
  11:             .ConnectionStrings["MyConnectionString"] == null)
  12:         {
  13:             return;
  14:         }
  15:         string connStr = config.ConnectionStrings
  16:             .ConnectionStrings["MyConnectionString"].ConnectionString;
  17:  
  18:         this.deleteExistingJob("My job", parent);
  19:         this.createJob(parent, connStr);
  20:     }
  21:  
  22:     public override void FeatureDeactivating(
  23: SPFeatureReceiverProperties properties)
  24:     {
  25:         var parent = (SPWebApplication)properties.Feature.Parent;
  26:         this.deleteExistingJob("My job", parent);
  27:     }
  28:  
  29:     private void createJob(SPWebApplication site, string connStr)
  30:     {
  31:         var job = new MyJob("My job", site);
  32:         var schedule = new SPDailySchedule();
  33:         schedule.BeginHour = 1;
  34:         job.Schedule = schedule;
  35:         job.Properties.Add("ConnectionString", connStr);
  36:         job.Update();
  37:     }
  38:  
  39:     private void deleteExistingJob(string jobName, SPWebApplication site)
  40:     {
  41:         foreach (var job in site.JobDefinitions)
  42:         {
  43:             if (job.Name == jobName)
  44:             {
  45:                 job.Delete();
  46:                 return ;
  47:             }
  48:         }
  49:     }
  50: }

In example above we assume that feature has WebApplication scope. Then inside the job we can get connection string from property bag:

   1: public class MyJob : SPJobDefinition
   2: {
   3:     public MyJob() : base() { }
   4:  
   5:     public MyJob(string jobName, SPService service)
   6:         : base(jobName, service, null, SPJobLockType.None)
   7:     {
   8:         this.Title = jobName;
   9:     }
  10:  
  11:     public MyJob(string jobName, SPWebApplication webapp)
  12:         : base(jobName, webapp, null, SPJobLockType.ContentDatabase)
  13:     {
  14:         this.Title = jobName;
  15:     }
  16:  
  17:     public override void Execute(Guid targetInstanceId)
  18:     {
  19:         string connStr = this.Properties["ConnectionString"] as string;
  20:         // connect to database
  21:     }
  22: }

After that we pass this connection string to EF DbContext constructor, it creates EF connection string and connects to database. Described approach allows to use Entity Framework without having EF connection string in app.config e.g. inside Sharepoint custom jobs. Hope it will help someone.

Tuesday, October 18, 2016

How to get events history for specific list or document library in Sharepoint

Sometime we need to retrieve events history for specific Sharepoint list or document library for particular period. Internally events log is stored in EventCache table in content database. In order to get it programmatically we can use SPChange* classes from Sharepoint object model. E.g. the following example shows how to get list of all documents which were added today:

   1: private static List<ItemChange> getListItemChanges(SPWeb web,
   2:     SPList list)
   3: {
   4:     try
   5:     {
   6:         if (list == null)
   7:         {
   8:             return new List<ItemChange>();
   9:         }
  10:  
  11:         var today = DateTime.Today;
  12:         var changeTokenStart = new SPChangeToken(
  13:             SPChangeCollection.CollectionScope.List, list.ID,
  14:             today.ToUniversalTime());
  15:         var changeTokenEnd = new SPChangeToken(
  16:             SPChangeCollection.CollectionScope.List, list.ID,
  17:             today.AddDays(1).ToUniversalTime());
  18:         var changeQuery = new SPChangeQuery(false, false);
  19:         changeQuery.Item = true;
  20:         changeQuery.Add = true;
  21:         changeQuery.Delete = false;
  22:         changeQuery.Update = false;
  23:         changeQuery.ChangeTokenStart = changeTokenStart;
  24:         changeQuery.ChangeTokenEnd = changeTokenEnd;
  25:         var changes = list.GetChanges(changeQuery);
  26:  
  27:         var listItemChanges = new List<ItemChange>();
  28:         foreach (SPChangeItem c in changes)
  29:         {
  30:             SPListItem item = null;
  31:             try
  32:             {
  33:                 item = list.GetItemById(c.Id);
  34:             }
  35:             catch (Exception x)
  36:             {
  37:                 Console.WriteLine("Error occured: {0}", x.Message);
  38:                 continue;
  39:             }
  40:  
  41:             if (item == null)
  42:             {
  43:                 continue;
  44:             }
  45:  
  46:             listItemChanges.Add(new ItemChange
  47:                 {
  48:                     Time =
  49: web.RegionalSettings.TimeZone.UTCToLocalTime(c.Time),
  50:                     ChangeType = c.ChangeType,
  51:                     FileName = item.File.Name,
  52:                     Url = SPUrlUtility.CombineUrl(web.Url, item.File.Url)
  53:                 });
  54:         }
  55:         return listItemChanges;
  56:     }
  57:     catch (Exception x)
  58:     {
  59:         Console.WriteLine("Exception occured: {0}\n{1}",
  60:             x.Message, x.StackTrace);
  61:         return new List<ItemChange>();
  62:     }
  63: }

Class ItemChange used in this example if POC helper class defined like this:

   1: public class ItemChange
   2: {
   3:     public DateTime Time { get; set; }
   4:     public SPChangeType ChangeType { get; set; }
   5:     public string FileName { get; set; }
   6:     public string Url { get; set; }
   7: }

At first we create instances of SPChangeToken class where specify change scope (List), id of specific list and start and end time for limiting search (lines 11-17). Then we create SPChangeQuery instance where specify that we want to get changes made for files in our list (line 19) and what kind of event types we want to track (lines 20-22). In our example we set it so only Add events should be returned, in order to return other event types specify true for Delete and Update properties. After that we pass start and end change tokens to appropriate properties of the query (lines 23-24), call SPList.GetChanges method and pass constructed SPChangeQuery object there. On lines 27-55 we iterate through returned results and create more convenient and simple collection of ItemChange objects which together with event time and change type also contain file name and url.

Note that there is also SPChangeQuery.FetchLimit property which by default is set to 1000. If your doclib may contain more changes for specific period, you need to add pagination logic to your code.

Saturday, October 15, 2016

One reason why SPFarm.CurrentUserIsAdministrator() returns false for farm administrators

If you need to check whether current user is farm admin in Sharepoint you may use SPFarm.CurrentUserIsAdministrator method. However you may face with the problem that it returns false even for users which belong to farm admins group. Some posts mention that starting from Sharepoint 2010 if you want to call this method from content web application (not from central admin web application context) you need to use overridden version which receives boolean parameter allowContentApplicationAccess:

   1: SPFarm.CurrentUserIsAdministrator(true)

The problem is that even with this call it may still return false. In order to get correct return value you need to set SPWebService.RemoteAdministratorAccessDenied property to false which can be done e.g. by PowerShell:

   1: $contentService =
   2: [Microsoft.SharePoint.Administration.SPWebService]::ContentService
   3: $contentService.RemoteAdministratorAccessDenied = $false
   4: $contentService.Update()

After that it will be possible to use SPFarm.CurrentUserIsAdministrator() method and do other actions which are not allowed by default, e.g. update SPPersistedObject instances in config database. Be aware however that more power requires more responsibility and with such configuration you should be more careful with security configuration in order to not allow malicious users to harm your system.