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.
