代码改变世界

Sql server2008中merge用法

2015-11-20 21:09  C#与.NET探索者  阅读(419)  评论(0)    收藏  举报 
 1  　　　　 /// <summary>
 2         /// 修改:添加条件: AND roleModule.FuncCode = tvpRoleModule.FuncCode
 3         /// </summary>
 4         private static readonly string SQL_PernissionReRoleModuleCreate = @"MERGE [dbo].[PernissionReRoleModule] AS roleModule
 5 USING @tvp AS tvpRoleModule
 6 ON (roleModule.RoleId = tvpRoleModule.RoleId AND roleModule.ModuleId = tvpRoleModule.ModuleId AND roleModule.FuncCode = tvpRoleModule.FuncCode)
 7 WHEN MATCHED
 8  THEN UPDATE SET roleModule.FuncCode = tvpRoleModule.FuncCode,roleModule.OptScope = tvpRoleModule.OptScope
 9 WHEN NOT MATCHED THEN
10   INSERT(RoleId,ModuleId,FuncCode,OptScope)
11         VALUES(tvpRoleModule.RoleId,tvpRoleModule.ModuleId,tvpRoleModule.FuncCode,tvpRoleModule.OptScope)
12 WHEN NOT MATCHED BY SOURCE AND roleModule.RoleId  = @roleId
13      THEN DELETE;";
14 
15 
16         /// <summary>
17         /// 创建新的角色功能信息
18         /// </summary>
19         /// <param name="organization">角色功能实体类</param>
20         /// <returns></returns>
21         public int Create(IList<PernissionReRoleModule> module, int roleId)
22         {
23 
24             DataTable tblData = new DataTable();
25             tblData.Columns.Add("RoleId", typeof(Int32));
26             tblData.Columns.Add("ModuleId", typeof(Int32));
27             tblData.Columns.Add("FunCode", typeof(int));
28             tblData.Columns.Add("OptScope", typeof(int));
29             foreach (var item in module)
30             {
31                 tblData.Rows.Add(new object[] { roleId, item.ModuleId, item.FuncCode, item.OptScope });
32             }
33 
34             var cmd = this.sqlHelper.PrepareTextSqlCommand(SQL_PernissionReRoleModuleCreate);
35             cmd.AddParam("@roleId", roleId);
36             var param = cmd.Parameters.AddWithValue("@tvp", tblData);
37 
38 
39             param.SqlDbType = SqlDbType.Structured;
40             param.TypeName = "dbo.ReRoleModule";
41             return cmd.ExecuteWrapNonQuery();
42         }
43         #endregion