· 9 years ago · Oct 19, 2016, 12:04 PM
1<Query Kind="Program">
2 <GACReference>Castle.Windsor, Version=3.1.0.0, Culture=neutral, PublicKeyToken=407dd0808d44fbdc</GACReference>
3 <GACReference>Microsoft.SharePoint, Version=15.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c</GACReference>
4 <GACReference>Microsoft.SharePoint.Taxonomy, Version=15.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c</GACReference>
5 <GACReference>Netcompany.CCM, Version=5.0.0.0, Culture=neutral, PublicKeyToken=c45b3b012bedcf43</GACReference>
6 <GACReference>Netcompany.CCM.Common, Version=5.0.0.0, Culture=neutral, PublicKeyToken=c45b3b012bedcf43</GACReference>
7 <GACReference>Netcompany.CCM.Jobs, Version=5.0.0.0, Culture=neutral, PublicKeyToken=c45b3b012bedcf43</GACReference>
8 <GACReference>Netcompany.Foundations, Version=5.0.0.0, Culture=neutral, PublicKeyToken=c45b3b012bedcf43</GACReference>
9 <Namespace>Microsoft.SharePoint</Namespace>
10 <Namespace>Microsoft.SharePoint.Administration</Namespace>
11 <Namespace>Microsoft.SharePoint.Navigation</Namespace>
12 <Namespace>Microsoft.SharePoint.Taxonomy</Namespace>
13 <Namespace>Netcompany.CCM</Namespace>
14 <Namespace>Netcompany.CCM.Business</Namespace>
15 <Namespace>Netcompany.CCM.Common</Namespace>
16 <Namespace>Netcompany.CCM.Common.Database</Namespace>
17 <Namespace>Netcompany.CCM.DataAccess</Namespace>
18 <Namespace>Netcompany.CCM.Jobs</Namespace>
19 <Namespace>System.Collections.Specialized</Namespace>
20 <Namespace>System.Globalization</Namespace>
21</Query>
22
23public static string webAppUrl = "http://gohr-dev";
24
25void Main()
26{
27 System.Threading.Thread.CurrentThread.CurrentUICulture = new CultureInfo(1030);
28
29 using (var syncer = new GlobalConfigurationSynchronizer(webAppUrl))
30 {
31 Console.WriteLine("Ensuring database mappings");
32 CreateDatabaseMapnings();
33 }
34
35}
36
37#region Configuration setter Methods
38
39private static GlobalConfiguration2 Instance
40{
41 get { return GlobalConfiguration2.Instance; }
42}
43
44private static void SetKey(string key, string value)
45{
46 LogKeyChange(key, value);
47 Instance.SetKey(key, value, string.Empty, true);
48}
49private static void SetKey(string key, string value, string description)
50{
51 LogKeyChange(key, value);
52 Instance.SetKey(key, value, description, true);
53}
54private static void LogKeyChange(string key, string value)
55{
56 Console.WriteLine("Setting GO Config: {0} = {1}", key, value);
57}
58private static void LogKeyChange(string prefix, string key, string value)
59{
60 Console.WriteLine("Setting GO Config on {2}: {0} = {1}", key, value, prefix);
61}
62private static void EnsureKeyExistance(string key, string value, string description, bool multiKey = false)
63{
64 if (!Instance.KeyExists(key))
65 {
66 Console.WriteLine("Adding GO Config key: {0} = {1}", key, value);
67 Instance.SetKey(key, value, description, multiKey);
68 }
69 else
70 {
71 Console.WriteLine("GO Config key {0} already exists", key);
72 }
73}
74
75private static void AppendToKey(string key, string delimiter, string valueToAppend, bool multiKey = false)
76{
77 if (Instance.KeyExists(key))
78 {
79 string presentValue = Instance.GetKey(key,multiKey);
80 if (!presentValue.Contains(valueToAppend))
81 {
82 Instance.SetKey(key, presentValue+delimiter+valueToAppend,null, multiKey);
83 }
84 }
85 else
86 {
87 Instance.SetKey(key, valueToAppend,null, multiKey);
88 Console.WriteLine("GO Config key {0} already exists", key);
89 }
90}
91#endregion
92
93#region DB Mapping Helper Methods
94private static List<DBMappingsField> dbmappingsfieldsList;
95
96// CreateDatabaseMappning, creates the neccesary columns and tables
97public static void CreateDatabaseMapnings()
98{
99 try
100 {
101 using (new GlobalConfigurationSynchronizer(webAppUrl))
102 {
103 /* create DBMappingsFields */
104 // Database types are case sensitive:
105 // DateTime, Decimal, Float, Int, NVarChar, UniqueIdentifier, VarChar, Date, Bit
106 dbmappingsfieldsList = new List<DBMappingsField>();
107 dbmappingsfieldsList.Add(new DBMappingsField("Author", "CP_Author", "NVarChar", 255, "Cases", false));
108 dbmappingsfieldsList.Add(new DBMappingsField("Employee", "CP_Employee", "NVarChar", 255, "Cases", false));
109 dbmappingsfieldsList.Add(new DBMappingsField("AreaDirector", "CP_AreaDirector", "NVarChar", 255, "Cases", false));
110 dbmappingsfieldsList.Add(new DBMappingsField("Mentor", "CP_Mentor", "NVarChar", 255, "Cases", false));
111
112 dbmappingsfieldsList.Add(new DBMappingsField("DueDate", "TP_DueDate", "DateTime", 0, "Tasks", false));
113 dbmappingsfieldsList.Add(new DBMappingsField("Author", "TP_Author", "NVarChar", 255, "Tasks", false));
114 dbmappingsfieldsList.Add(new DBMappingsField("Status", "TP_Status", "NVarChar", 255, "Tasks", false));
115 dbmappingsfieldsList.Add(new DBMappingsField("TypeOfTask", "TP_TypeOfTask", "NVarChar", 255, "Tasks", false));
116 dbmappingsfieldsList.Add(new DBMappingsField("Portfolio", "TP_Portfolio", "NVarChar", 255, "Tasks", false));
117
118 //Ensuring GO Admin site setting
119 EnsureDbMappingsSettings();
120
121 //Ensuring database tables
122 using (SqlConnection connection = new SqlConnection(DatabaseManager.ConnectionString))
123 {
124 CreateSqlTempTabels(connection);
125 AddColumnsToTaskAndCaseTabel(connection);
126 AddIndexToTaskAndCaseColumns(connection);
127 }
128 }
129 }
130 catch (Exception ex)
131 {
132 throw new Exception("DatabaseMappningConfigurationManager: CreateDatabaseMapping fejlede: " + ex.Message, ex.InnerException);
133 }
134}
135
136// Create temp tabels
137private static void CreateSqlTempTabels(SqlConnection connection)
138{
139 try
140 {
141 // Create 4 tabels: TempUserData, TempMetadata, CaseMultiValues, TaskMultiValues
142 SqlCommand createTempTabels = new SqlCommand(@"
143 CREATE TABLE [dbo].[TempUserData](
144 [SiteID] [uniqueidentifier] NOT NULL,
145 [UserID] [int] NOT NULL,
146 [LoginName] [nvarchar](max) NOT NULL
147 ) ON [PRIMARY]
148
149 CREATE TABLE [dbo].[TempMetadata](
150 [SiteID] [uniqueidentifier] NOT NULL,
151 [TermID] [int] NOT NULL,
152 [TermName] [nvarchar](max) NOT NULL
153 ) ON [PRIMARY]
154
155 CREATE TABLE [dbo].[CaseMultiValues] (
156 [CaseDBID] INT NOT NULL,
157 CONSTRAINT [FK_CaseMultiValues_Case] FOREIGN KEY ([CaseDBID]) REFERENCES [dbo].[Case] ([CaseDBID])
158 )
159
160 CREATE TABLE [dbo].[TaskMultiValues](
161 [TaskUniqueId] [uniqueidentifier] NOT NULL
162 ) ON [PRIMARY]
163
164 CREATE CLUSTERED INDEX [ClusteredIndex] ON [dbo].[TaskMultiValues](
165 [TaskUniqueId] ASC
166 )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
167 ", connection);
168
169 /*
170 CREATEÂ TABLEÂ [dbo].[TaskMultiValues](Â
171 [TaskUniqueId]Â [uniqueidentifier]Â PRIMARYÂ KEYÂ NOTÂ NULLÂ
172 )Â ONÂ [PRIMARY]Â Â
173 */
174
175 /* Todo: there is a an error in the guide. TaskUniqueId needs to be a primary key and FK_Task_TaskMultiValues doesnt work
176 * The following constraint, doesn't work, because GO writes first to the TaskMultiValue table, which is not allowed
177 * when TaskUniqueId is a foreign key in the dbo.Task tabel.
178 ALTERÂ TABLEÂ [dbo].[Task]Â Â WITHÂ CHECKÂ ADDÂ Â CONSTRAINTÂ [FK_Task_TaskMultiValues]Â FOREIGNÂ KEY([TaskUniqueId])Â
179 REFERENCESÂ [dbo].[TaskMultiValues]Â ([TaskUniqueId])
180 */
181 if (createTempTabels.Connection.State != ConnectionState.Open)
182 createTempTabels.Connection.Open();
183 createTempTabels.ExecuteNonQuery();
184 }
185 catch (Exception ex)
186 {
187 if (!ex.Message.Contains("There is already an object named"))
188 throw new Exception("DatabaseMappningConfigurationManager: CreateSqlTempTabels fejlede: " + ex.Message, ex.InnerException);
189
190 Console.WriteLine("Table already created");
191 }
192}
193// Add Columns to Task and Case tabels
194private static void AddColumnsToTaskAndCaseTabel(SqlConnection connection)
195{
196
197 var addCaseColumn = string.Empty;
198 var addTaskColumn = string.Empty;
199
200 SqlCommand alterCase = null;
201 SqlCommand alterTask = null;
202
203
204 foreach (DBMappingsField dbmfield in dbmappingsfieldsList)
205 {
206 if (dbmfield.MappingsList.ToLower().Equals("cases"))
207 {
208 if (dbmfield.DbType.ToLower().Equals("datetime"))
209 addCaseColumn = String.Format("ALTER TABLE [dbo].[Case] ADD CP_{0} {1}", dbmfield.FieldName,
210 dbmfield.DbType);
211 else
212 addCaseColumn = String.Format("ALTER TABLE [dbo].[Case] ADD CP_{0} {1}", dbmfield.FieldName,
213 dbmfield.DbType + "(" + dbmfield.DbTypeParameter + ")");
214 alterCase = new SqlCommand(addCaseColumn, connection);
215
216 if (connection.State != ConnectionState.Open)
217 connection.Open();
218 try
219 {
220 // add column to case tabel
221 alterCase.ExecuteNonQuery();
222 }
223 catch (Exception ex)
224 {
225 if (!ex.Message.Contains("Column names in each table must be unique"))
226 throw new Exception("DatabaseMappningConfigurationManager: AddColumnsToTaskAndCaseTabel fejlede: " + ex.Message, ex.InnerException);
227 }
228 }
229 else
230 {
231 if (dbmfield.DbType.ToLower().Equals("datetime"))
232 addTaskColumn = String.Format("ALTER TABLE [dbo].[Task] ADD TP_{0} {1}", dbmfield.FieldName,
233 dbmfield.DbType);
234 else
235 addTaskColumn = String.Format("ALTER TABLE [dbo].[Task] ADD TP_{0} {1}", dbmfield.FieldName,
236 dbmfield.DbType + "(" + dbmfield.DbTypeParameter + ")");
237
238 alterTask = new SqlCommand(addTaskColumn, connection);
239
240 if (connection.State != ConnectionState.Open)
241 connection.Open();
242
243 try
244 {
245 // add column to task tabel
246 alterTask.ExecuteNonQuery();
247 }
248 catch (Exception ex)
249 {
250 if (!ex.Message.Contains("Column names in each table must be unique"))
251 throw new Exception("DatabaseMappningConfigurationManager: AddColumnsToTaskAndCaseTabel fejlede: " + ex.Message, ex.InnerException);
252 }
253
254 }
255 }
256}
257
258// Adds index to all columns in array
259private static void AddIndexToTaskAndCaseColumns(SqlConnection connection)
260{
261 var addIndexToCaseColumn = string.Empty;
262 var addIndexToTaskColumn = string.Empty;
263
264 SqlCommand createCaseIndex = null;
265 SqlCommand createTaskIndex = null;
266
267 foreach (DBMappingsField dbmfield in dbmappingsfieldsList)
268 {
269 if (dbmfield.MappingsList.ToLower().Equals("cases"))
270 {
271 addIndexToCaseColumn = String.Format("CREATE INDEX IX_{0} ON [dbo].[Case] (CP_{0})", dbmfield.FieldName);
272 createCaseIndex = new SqlCommand(addIndexToCaseColumn, connection);
273
274 if (connection.State != ConnectionState.Open)
275 connection.Open();
276 try
277 {
278 createCaseIndex.ExecuteNonQuery();
279 }
280 catch (Exception ex)
281 {
282 if (!ex.Message.Contains("already exists"))
283 throw new Exception(
284 "DatabaseMappningConfigurationManager: AddIndexToTaskAndCaseColumns fejlede: " + ex.Message,
285 ex.InnerException);
286 }
287 }
288 else
289 {
290 addIndexToTaskColumn = String.Format("CREATE INDEX IX_{0} ON [dbo].[Task] (TP_{0})", dbmfield.FieldName);
291 createTaskIndex = new SqlCommand(addIndexToTaskColumn, connection);
292
293 if (connection.State != ConnectionState.Open)
294 connection.Open();
295
296 try
297 {
298 createTaskIndex.ExecuteNonQuery();
299 }
300 catch (Exception ex)
301 {
302 if (!ex.Message.Contains("already exists"))
303 throw new Exception(
304 "DatabaseMappningConfigurationManager: AddIndexToTaskAndCaseColumns fejlede: " + ex.Message,
305 ex.InnerException);
306 }
307 }
308 }
309}
310
311private static void EnsureDbMappingsSettings()
312{
313 try
314 {
315 using (SPSite adminSite = new SPSite(GlobalConfiguration2.Instance.AdminSiteUrl))
316 using (SPWeb adminWeb = adminSite.OpenWeb())
317 {
318 SPList dbMappingsList = ListHelper.GetListByInternalName("DBMappings", adminWeb);
319
320 if (dbMappingsList != null)
321 {
322 var found = false;
323 DBMappingsField tmpdbmappingfield = new DBMappingsField();
324
325 // We need to check if mappings we want to add already exist.
326 // if the mapping exists, but differs from existing mapping, we update it with new values
327 foreach (DBMappingsField dbmField in dbmappingsfieldsList)
328 {
329 found = false;
330 foreach (SPListItem listItem in dbMappingsList.Items)
331 {
332 tmpdbmappingfield = dbmField;
333 var listfieldName = listItem[listItem.Fields.GetFieldByInternalName("CCMFieldName").Id] as string;
334 var listfieldMappings = listItem[listItem.Fields.GetFieldByInternalName("CCMMappingList").Id] as string;
335
336 if (listfieldName == null)
337 throw new Exception("Kunne ikke hent FieldName fra Database mappings list");
338
339 if (dbmField.FieldName.ToLower().Equals(listfieldName.ToLower()) &&
340 dbmField.MappingsList.ToLower().Equals(listfieldMappings.ToLower()))
341 {
342 found = true;
343 if (IsEqual(dbmField, listItem))
344 break;
345
346 UpdateDbMapping(dbmField, listItem);
347 break;
348 }
349 }
350
351 // if the mappings doesnt exist, we add it to the list.
352 if (!found)
353 AddDbMapping(dbMappingsList, tmpdbmappingfield);
354 }
355 }
356 }
357 }
358 catch (Exception ex)
359 {
360 Console.WriteLine(ex);
361 throw;
362 }
363
364}
365
366private static void UpdateDbMapping(DBMappingsField dbmfield, SPListItem listItem)
367{
368 listItem["CCMFieldName"] = dbmfield.FieldName;
369 listItem["CCMDBColumn"] = dbmfield.DbColumn;
370 listItem["CCMDBType"] = dbmfield.DbType;
371 listItem["CCMDBTypeParams"] = dbmfield.DbTypeParameter.ToString();
372 listItem["CCMMappingList"] = dbmfield.MappingsList;
373 listItem["CCMIsMulitValue"] = dbmfield.AllowMany.ToString();
374 listItem.Update();
375}
376
377private static void AddDbMapping(SPList dbMappingsList, DBMappingsField dbmfield)
378{
379 SPListItem newDBMapping = dbMappingsList.Items.Add();
380 newDBMapping["CCMFieldName"] = dbmfield.FieldName;
381 newDBMapping["CCMDBColumn"] = dbmfield.DbColumn;
382 newDBMapping["CCMDBType"] = dbmfield.DbType;
383 newDBMapping["CCMDBTypeParams"] = dbmfield.DbTypeParameter;
384 newDBMapping["CCMMappingList"] = dbmfield.MappingsList;
385 newDBMapping["CCMIsMulitValue"] = dbmfield.AllowMany;
386
387 newDBMapping.Update();
388}
389
390private static Boolean IsEqual(DBMappingsField newField, SPListItem listItem)
391{
392 var fieldName = listItem[listItem.Fields.GetFieldByInternalName("CCMFieldName").Id] as string;
393 var dbcolumn = listItem[listItem.Fields.GetFieldByInternalName("CCMDBColumn").Id] as string;
394 var dbtype = listItem[listItem.Fields.GetFieldByInternalName("CCMDBType").Id] as string;
395 var dbtypeparams =
396 Convert.ToInt32(listItem[listItem.Fields.GetFieldByInternalName("CCMDBTypeParams").Id] as string);
397 var mappinglist = listItem[listItem.Fields.GetFieldByInternalName("CCMMappingList").Id] as string;
398 var ismultivalue = (Boolean) listItem[listItem.Fields.GetFieldByInternalName("CCMIsMulitValue").Id];
399
400 if (!fieldName.ToLower().Equals(newField.FieldName.ToLower()))
401 return false;
402
403 if (!dbcolumn.ToLower().Equals(newField.DbColumn.ToLower()))
404 return false;
405
406 if (!dbtype.ToLower().Equals(newField.DbType.ToLower()))
407 return false;
408
409 if (dbtypeparams != newField.DbTypeParameter)
410 return false;
411
412 if (!mappinglist.ToLower().Equals(newField.MappingsList.ToLower()))
413 return false;
414
415 if (ismultivalue != newField.AllowMany)
416 return false;
417
418 return true;
419}
420
421#endregion
422
423
424#region Helper Classes
425 public class DBMappingsField
426 {
427 private string fieldName;
428 private string dbColumn;
429 private string dbType;
430 private int dbTypeParameter;
431 private string mappingsList;
432 private Boolean allowMany;
433
434 public DBMappingsField()
435 {
436
437 }
438
439 public DBMappingsField(string fieldname, string dbcolumn, string dbtype, int dbtypeparameter, string mappingslist, Boolean allowmany)
440 {
441 fieldName = fieldname;
442 dbColumn = dbcolumn;
443 dbType = dbtype;
444 dbTypeParameter = dbtypeparameter;
445 mappingsList = mappingslist;
446 allowMany = allowmany;
447 }
448
449 public string FieldName
450 {
451 get { return fieldName; }
452 set { fieldName = value; }
453 }
454
455 public string DbColumn
456 {
457 get { return dbColumn; }
458 set { dbColumn = value; }
459 }
460
461 public string DbType
462 {
463 get { return dbType; }
464 set { dbType = value; }
465 }
466
467 public int DbTypeParameter
468 {
469 get { return dbTypeParameter; }
470 set { dbTypeParameter = value; }
471 }
472
473 public string MappingsList
474 {
475 get { return mappingsList; }
476 set { mappingsList = value; }
477 }
478
479 public Boolean AllowMany
480 {
481 get { return allowMany; }
482 set { allowMany = value; }
483 }
484 }
485
486#endregion