· 8 years ago · Apr 10, 2018, 06:34 AM
1 public async Task CreateGDPRObjectStatusTableIfNotExists()
2 {
3 using (var connection = await DbConnectionProvider.OpenDbConnection())
4 using (var command = connection.CreateCommandWithTimeoutFromConfig())
5 {
6 command.CommandText = $@"IF NOT EXISTS (SELECT * FROM sys.objects WHERE c_Object_id = c_OBJECT_ID(N'[dbo].[{GDPRObjectStatusTableName}]') AND type in (N'U'))
7 BEGIN
8 CREATE TABLE [dbo].[{GDPRObjectStatusTableName}]
9 (
10 [c_Id] [int] IDENTITY(1,1) NOT NULL,
11 [c_ObjectId] [uniqueidentifier] NULL,
12 [c_ObjectType] [nvarchar](32) NULL,
13 [c_Tenant] [nvarchar](32) NULL,
14 [c_CodeSEN] [nvarchar](32) NULL,
15 )
16 END";
17
18 await command.ExecuteNonQueryAsync();
19 }
20 }
21
22 public async Task CreateIndexOGDPRObjectStatusTableIfNotExists()
23 {
24 using (var connection = await DbConnectionProvider.OpenDbConnection())
25 using (var command = connection.CreateCommandWithTimeoutFromConfig())
26 {
27 command.CommandText = $@"IF NOT EXISTS(SELECT * FROM sys.indexes
28 WHERE c_Object_id = c_Object_id('[dbo].[{GDPRObjectStatusTableName}]') AND NAME ='NCIX_c_GDPRObjectStatus_c_ObjectType_c_CodeSEN')
29 BEGIN
30 CREATE NONCLUSTERED INDEX [NCIX_c_GDPRObjectStatus_c_ObjectType_c_CodeSEN] ON [dbo].[{GDPRObjectStatusTableName}]
31 (
32 [c_ObjectType] ASC,
33 [c_CodeSEN] ASC,
34 )
35 INCLUDE ([c_ObjectId])
36 WITH
37 (
38 PAD_INDEX = OFF,
39 STATISTICS_NORECOMPUTE = OFF,
40 SORT_IN_TEMPDB = OFF,
41 DROP_EXISTING = OFF,
42 ONLINE = ON,
43 ALLOW_ROW_LOCKS = ON,
44 ALLOW_PAGE_LOCKS = ON,
45 FILLFACTOR = 95,
46 MAXDOP = 6,
47 DATA_COMPRESSION = PAGE
48 )
49 END";
50
51 await command.ExecuteNonQueryAsync();
52 }
53 }
54
55 public async Task CreateGDPREntitiesTableIfNotExists()
56 {
57 using (var connection = await DbConnectionProvider.OpenDbConnection())
58 using (var command = connection.CreateCommandWithTimeoutFromConfig())
59 {
60 command.CommandText = $@"IF NOT EXISTS (SELECT * FROM sys.objects WHERE c_Object_id = c_OBJECT_ID(N'[dbo].[{GDPREntitiesTableName}]') AND type in (N'U'))
61 BEGIN
62 CREATE TABLE [dbo].[{GDPREntitiesTableName}]
63 (
64 [c_Id] [int] IDENTITY(1,1) NOT NULL,
65 [c_ObjectId] [uniqueidentifier] NULL,
66 [c_ObjectType] [nvarchar](32) NULL,
67 [c_GlobalId] [nvarchar](32) NULL,
68 [c_CodeSEN] [nvarchar](32) NULL,
69 [c_IsBeingProcessed] [int] NULL,
70 )
71 END";
72
73 await command.ExecuteNonQueryAsync();
74 }
75 }
76
77 public async Task CreateIndexOGDPREntitiesTableIfNotExists()
78 {
79 using (var connection = await DbConnectionProvider.OpenDbConnection())
80 using (var command = connection.CreateCommandWithTimeoutFromConfig())
81 {
82 command.CommandText = $@"IF NOT EXISTS(SELECT * FROM sys.indexes
83 WHERE c_Object_id = c_Object_id('[dbo].[{GDPREntitiesTableName}]') AND NAME ='NCIX_c_GDPREntities_c_ObjectType_c_CodeSEN_c_IsBeingProcessed')
84 BEGIN
85 CREATE NONCLUSTERED INDEX [NCIX_c_GDPREntities_c_ObjectType_c_CodeSEN_c_IsBeingProcessed] ON [dbo].[{GDPREntitiesTableName}]
86 (
87 [c_ObjectType] ASC,
88 [c_CodeSEN] ASC,
89 [c_IsBeingProcessed] ASC,
90 )
91 INCLUDE ([c_ObjectId])
92 WITH
93 (
94 PAD_INDEX = OFF,
95 STATISTICS_NORECOMPUTE = OFF,
96 SORT_IN_TEMPDB = OFF,
97 DROP_EXISTING = OFF,
98 ONLINE = ON,
99 ALLOW_ROW_LOCKS = ON,
100 ALLOW_PAGE_LOCKS = ON,
101 FILLFACTOR = 95,
102 MAXDOP = 6,
103 DATA_COMPRESSION = PAGE
104 )
105 END";
106
107 await command.ExecuteNonQueryAsync();
108 }
109 }