Sql server2008中merge用法

             1
            ///
            <summary>
             2
            ///
             修改:添加条件: AND roleModule.FuncCode = tvpRoleModule.FuncCode

             3
            ///
            </summary>
             4
            private
            static
            readonly
            string SQL_PernissionReRoleModuleCreate = @"MERGE [dbo].[PernissionReRoleModule] AS roleModule
 5USING @tvp AS tvpRoleModule
 6ON (roleModule.RoleId = tvpRoleModule.RoleId AND roleModule.ModuleId = tvpRoleModule.ModuleId AND roleModule.FuncCode = tvpRoleModule.FuncCode)
 7WHEN MATCHED
 8 THEN UPDATE SET roleModule.FuncCode = tvpRoleModule.FuncCode,roleModule.OptScope = tvpRoleModule.OptScope
 9WHEN NOT MATCHED THEN
10  INSERT(RoleId,ModuleId,FuncCode,OptScope)
11        VALUES(tvpRoleModule.RoleId,tvpRoleModule.ModuleId,tvpRoleModule.FuncCode,tvpRoleModule.OptScope)
12WHEN NOT MATCHED BY SOURCE AND roleModule.RoleId  = @roleId
13     THEN DELETE;";
141516///<summary>17/// 创建新的角色功能信息
18///</summary>19///<param name="organization">角色功能实体类</param>20///<returns></returns>21publicint Create(IList<PernissionReRoleModule> module, int roleId)
22        {
2324             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));
29foreach (var item in module)
30            {
31                 tblData.Rows.Add(newobject[] { roleId, item.ModuleId, item.FuncCode, item.OptScope });
32            }
3334var cmd = this.sqlHelper.PrepareTextSqlCommand(SQL_PernissionReRoleModuleCreate);
35             cmd.AddParam("@roleId", roleId);
36var param = cmd.Parameters.AddWithValue("@tvp", tblData);
373839             param.SqlDbType = SqlDbType.Structured;
40             param.TypeName = "dbo.ReRoleModule";
41return cmd.ExecuteWrapNonQuery();
42        }
43#endregion