动态SQL中拼入Binary数据，以及对CDC表行转列操作类型分组的标准SP

第一个其实没啥好说的，转成NVARCHAR是关键，唯一的不好弄的是调试时不能贴出完整SQL

1 set @QueryWhereBy = dbo.F_CombineWhereSql(@QueryWhereBy,concat('__$start_lsn between cast(N''',cast(@Begin_lsn as nvarchar(50)),
2         ''' as binary(10)) and cast(N''',cast(@End_lsn as nvarchar(50)),''' as binary(10))'));

View Code

第二个问题是根据CDC表记录生成OLDVALUE/NEWVALUE记录，即行转列，也没啥好说的用UNPIVOT

Add Type：

 1 USE [GEMS_DM]
 2 GO
 3 /****** Object:  StoredProcedure [dbo].[P_PM_Audit_AddTypeGenerator]    Script Date: 12/23/2013 16:43:15 ******/
 4 SET ANSI_NULLS ON
 5 GO
 6 SET QUOTED_IDENTIFIER ON
 7 GO
 8 
 9 
10 
11 
12 
13 
14 
15 ALTER PROCEDURE [dbo].[P_PM_Audit_AddTypeGenerator]
16     @QuerySqlCommand nvarchar(max),
17     @PivotColumns nvarchar(max),
18     @ExtendQueryColumns nvarchar(max),
19     @QueryWhereBy nvarchar(max) = NULL,
20     @Begin_lsn binary(10),
21     @End_lsn binary(10),
22     @ModifiedBy nvarchar(50)
23 AS
24 begin
25     SET @QueryWhereBy= ISNULL(@QueryWhereBy,'');
26     DECLARE @QueryMainSqlCommand nvarchar(max);
27 
28     set @QueryMainSqlCommand = 
29     'SELECT FieldValue,'''' as OldValue,NewValue,[Modified_By],Modified_Date,''Add'' as ActionType '
30     
31     --insert extend select query column
32     if(@ExtendQueryColumns is not null and ltrim(rtrim(@ExtendQueryColumns)) <> '')
33     begin
34         set @QueryMainSqlCommand = concat(@QueryMainSqlCommand,',',@ExtendQueryColumns);
35     end
36     
37     set @QueryWhereBy = dbo.F_CombineWhereSql(@QueryWhereBy,'__$operation=2');
38     
39     if(@ModifiedBy is not null)
40         set @QueryWhereBy = dbo.F_CombineWhereSql(@QueryWhereBy,concat('Created_By = ''',@ModifiedBy,''''));
41     print cast(@Begin_lsn as nvarchar(50))
42     set @QueryWhereBy = dbo.F_CombineWhereSql(@QueryWhereBy,concat('__$start_lsn between cast(N''',cast(@Begin_lsn as nvarchar(50)),
43         ''' as binary(10)) and cast(N''',cast(@End_lsn as nvarchar(50)),''' as binary(10))'));
44 
45     -- auto insert 'add' type filter
46     set @QuerySqlCommand = REPLACE(@QuerySqlCommand,'{0}',concat(' where ',@QueryWhereBy));
47     set @QueryMainSqlCommand = concat(@QueryMainSqlCommand,char(10),' FROM (',@QuerySqlCommand,') as T');
48     
49     --unpivot the select result
50     set @QueryMainSqlCommand = concat(@QueryMainSqlCommand,char(10),' UNPIVOT ',char(10),'(NewValue FOR FieldValue IN (')
51     set @QueryMainSqlCommand = concat(@QueryMainSqlCommand,char(10),@PivotColumns,')) as pvt where pvt.NewValue <> ''''');
52 
53 print @QueryMainSqlCommand
54 exec sp_executesql @QueryMainSqlCommand
55 
56 end

View Code

Modify Type:

 1 USE [GEMS_DM]
 2 GO
 3 /****** Object:  StoredProcedure [dbo].[P_PM_Audit_ModifyTypeGenerator]    Script Date: 12/23/2013 16:46:47 ******/
 4 SET ANSI_NULLS ON
 5 GO
 6 SET QUOTED_IDENTIFIER ON
 7 GO
 8 
 9 
10 
11 
12 
13 
14 
15 
16 ALTER PROCEDURE [dbo].[P_PM_Audit_ModifyTypeGenerator]
17     @QuerySqlCommand nvarchar(max),
18     @PivotColumns nvarchar(max),
19     @ExtendQueryColumns nvarchar(max),
20     @QueryWhereBy nvarchar(max) = NULL,
21     @IsActiveMode bit = 0,
22     @Begin_lsn binary(10),
23     @End_lsn binary(10),
24     @ModifiedBy nvarchar(50)
25 AS
26 begin
27     SET @IsActiveMode= ISNULL(@IsActiveMode,0);
28     SET @QueryWhereBy= ISNULL(@QueryWhereBy,'');
29     DECLARE @NewValueQuerySqlCommand nvarchar(max) = @QuerySqlCommand;
30     DECLARE @OldValueQuerySqlCommand nvarchar(max) = Replace(@QuerySqlCommand,'{0}',' where __$operation=3 ');
31     DECLARE @QueryMainSqlCommand nvarchar(max);
32 
33     set @QueryMainSqlCommand = 'SELECT T1.FieldValue,OldValue,NewValue,T1.[Modified_By],T1.Modified_Date,''Modify'' as ActionType';
34     --insert extend select query column
35     if(@ExtendQueryColumns is not null and ltrim(rtrim(@ExtendQueryColumns)) <> '')
36     begin
37         set @QueryMainSqlCommand = concat(@QueryMainSqlCommand,' , ',@ExtendQueryColumns);
38     end
39 
40     --insert extend select new value query column
41     set @QueryMainSqlCommand = CONCAT(@QueryMainSqlCommand,' FROM ',char(10),
42     '(SELECT FieldValue,NewValue,__$start_lsn,__$seqval,[Modified_By],Modified_Date');    
43     if(@ExtendQueryColumns is not null and ltrim(rtrim(@ExtendQueryColumns)) <> '')
44     begin
45         set @QueryMainSqlCommand = concat(@QueryMainSqlCommand,',',@ExtendQueryColumns);
46     end    
47 
48     -- auto insert 'modify' type filter
49     set @QueryWhereBy = dbo.F_CombineWhereSql(@QueryWhereBy,'__$operation=4');
50     if(@IsActiveMode = 1)
51         set @QueryWhereBy = dbo.F_CombineWhereSql(@QueryWhereBy,'IsActive = 1 ');
52     if(@ModifiedBy is not null)
53         set @QueryWhereBy = dbo.F_CombineWhereSql(@QueryWhereBy,concat('Modified_By = ''',@ModifiedBy,''''));
54     set @QueryWhereBy = dbo.F_CombineWhereSql(@QueryWhereBy,concat('__$start_lsn between cast(N''',cast(@Begin_lsn as nvarchar(50)),
55         ''' as binary(10)) and cast(N''',cast(@End_lsn as nvarchar(50)),''' as binary(10))'));
56 
57     set @NewValueQuerySqlCommand = REPLACE(@NewValueQuerySqlCommand,'{0}',concat(' where ',@QueryWhereBy));
58     set @QueryMainSqlCommand = concat(@QueryMainSqlCommand,char(10),' FROM (',@NewValueQuerySqlCommand,') as T');
59     
60     --unpivot the select result
61     set @QueryMainSqlCommand = concat(@QueryMainSqlCommand,char(10),' UNPIVOT ',char(10),'(NewValue FOR FieldValue IN (')
62     set @QueryMainSqlCommand = concat(@QueryMainSqlCommand,char(10),@PivotColumns,')) as pvt) as T1 ');
63 
64     --auto insert select old value query column
65     set @QueryMainSqlCommand = concat(@QueryMainSqlCommand,char(10),' inner join ',char(10));
66     set @QueryMainSqlCommand = concat(@QueryMainSqlCommand,
67         '(SELECT FieldValue,OldValue,__$start_lsn,__$seqval From (',@OldValueQuerySqlCommand,') as T');
68         --unpivot the select result
69     set @QueryMainSqlCommand = concat(@QueryMainSqlCommand,char(10),' UNPIVOT ',char(10),'(OldValue FOR FieldValue IN (')
70     set @QueryMainSqlCommand = concat(@QueryMainSqlCommand,char(10),@PivotColumns,')) as pvt) as T2 ');
71 
72     set @QueryMainSqlCommand = concat(@QueryMainSqlCommand,char(10),
73         'on T1.__$seqval=T2.__$seqval and T1.FieldValue=T2.FieldValue and T2.OldValue <> T1.NewValue');
74 
75 print @QueryMainSqlCommand
76 exec sp_executesql @QueryMainSqlCommand
77 
78 end

View Code

Delete Type:由于我们是使用的逻辑删除，因此还比实际物理删除麻烦。物理删除直接取operation type = 1的就可以了

 1 USE [GEMS_DM]
 2 GO
 3 /****** Object:  StoredProcedure [dbo].[P_PM_Audit_DeleteTypeGenerator]    Script Date: 12/23/2013 16:47:28 ******/
 4 SET ANSI_NULLS ON
 5 GO
 6 SET QUOTED_IDENTIFIER ON
 7 GO
 8 
 9 
10 
11 
12 
13 
14 
15 
16 
17 
18 ALTER PROCEDURE [dbo].[P_PM_Audit_DeleteTypeGenerator]
19     @QuerySqlCommand nvarchar(max),
20     @PivotColumns nvarchar(max),
21     @ExtendQueryColumns nvarchar(max),
22     @QueryWhereBy nvarchar(max) = NULL,
23     @IsActiveMode bit = 0,
24     @Begin_lsn binary(10),
25     @End_lsn binary(10),
26     @ModifiedBy nvarchar(50)
27 AS
28 begin
29     SET @IsActiveMode= ISNULL(@IsActiveMode,0);
30     SET @QueryWhereBy= ISNULL(@QueryWhereBy,'');
31     DECLARE @NewValueQuerySqlCommand nvarchar(max) = @QuerySqlCommand;
32     DECLARE @OldValueQuerySqlCommand nvarchar(max) = Replace(@QuerySqlCommand,'{0}',' where __$operation=3 ');
33     DECLARE @QueryMainSqlCommand nvarchar(max);
34     
35 
36     set @QueryMainSqlCommand = 'SELECT FieldValue,OldValue,'''' as NewValue,
37     NewModifyBy as [Modified_By],NewModifyDate as Modified_Date,''Delete'' as ActionType';
38     --insert extend select query column
39     if(@ExtendQueryColumns is not null and ltrim(rtrim(@ExtendQueryColumns)) <> '')
40     begin
41         set @QueryMainSqlCommand = concat(@QueryMainSqlCommand,' , ',@ExtendQueryColumns);
42     end        
43     
44     --auto insert old value query column
45     set @QueryMainSqlCommand = concat(@QueryMainSqlCommand,char(10),
46         ' From ',char(10),'(SELECT TOLD.*,TNEW.[Modified_By] as NewModifyBy,TNEW.Modified_Date as NewModifyDate
47          From (',@OldValueQuerySqlCommand,') as TOLD');
48 
49     set @QueryMainSqlCommand = concat(@QueryMainSqlCommand,char(10),' inner join ',char(10));
50     
51     -- auto insert 'delete' type filter
52     set @QueryWhereBy = dbo.F_CombineWhereSql(@QueryWhereBy,'__$operation=4');
53     if(@IsActiveMode = 1)
54         set @QueryWhereBy = dbo.F_CombineWhereSql(@QueryWhereBy,'IsActive = 0 ');
55     if(@ModifiedBy is not null)
56         set @QueryWhereBy = dbo.F_CombineWhereSql(@QueryWhereBy,concat('Modified_By = ''',@ModifiedBy,''''));
57     set @QueryWhereBy = dbo.F_CombineWhereSql(@QueryWhereBy,concat('__$start_lsn between cast(N''',cast(@Begin_lsn as nvarchar(50)),
58         ''' as binary(10)) and cast(N''',cast(@End_lsn as nvarchar(50)),''' as binary(10))'));
59     
60     set @NewValueQuerySqlCommand = REPLACE(@NewValueQuerySqlCommand,'{0}',concat(' where ',@QueryWhereBy));
61     set @QueryMainSqlCommand = concat(@QueryMainSqlCommand,char(10),
62         '(',@NewValueQuerySqlCommand,') as TNew');
63     set @QueryMainSqlCommand = concat(@QueryMainSqlCommand,char(10),
64         'ON TOLD.__$seqval= TNEW.__$seqval) as T3 ');
65     
66         --unpivot the select result
67     set @QueryMainSqlCommand = concat(@QueryMainSqlCommand,char(10),' UNPIVOT ',char(10),'(OldValue FOR FieldValue IN (')
68     set @QueryMainSqlCommand = concat(@QueryMainSqlCommand,char(10),@PivotColumns,')) as pvt WHERE OldValue <> '''' ');
69 
70     
71 
72 print @QueryMainSqlCommand
73 exec sp_executesql @QueryMainSqlCommand
74 
75 end

View Code

最后说一下CDC，并不能算是完全好用，原理是读取sql日志来捕获数据变更（延迟个几秒捕获，对于高并发的系统也许应该放在从库做）。要捕获同一批操作的记录必须在同一个事务中，所以并不是在EF的一次SaveChange中提交就代表在一个事务中了。