Sql server2008中merge用法

/// <summary>
/// 修改:添加条件: AND roleModule.FuncCode = tvpRoleModule.FuncCode
/// </summary>
private static readonly string SQL_PernissionReRoleModuleCreate = @"MERGE [dbo].[PernissionReRoleModule] AS roleModule
USING @tvp AS tvpRoleModule
ON (roleModule.RoleId = tvpRoleModule.RoleId AND roleModule.ModuleId = tvpRoleModule.ModuleId AND roleModule.FuncCode = tvpRoleModule.FuncCode)
WHEN MATCHED
THEN UPDATE SET roleModule.FuncCode = tvpRoleModule.FuncCode,roleModule.OptScope = tvpRoleModule.OptScope
WHEN NOT MATCHED THEN
INSERT(RoleId,ModuleId,FuncCode,OptScope)
VALUES(tvpRoleModule.RoleId,tvpRoleModule.ModuleId,tvpRoleModule.FuncCode,tvpRoleModule.OptScope)
WHEN NOT MATCHED BY SOURCE AND roleModule.RoleId = @roleId
THEN DELETE;"; /// <summary>
/// 创建新的角色功能信息
/// </summary>
/// <param name="organization">角色功能实体类</param>
/// <returns></returns>
public int Create(IList<PernissionReRoleModule> module, int roleId)
{ DataTable tblData = new DataTable();
tblData.Columns.Add("RoleId", typeof(Int32));
tblData.Columns.Add("ModuleId", typeof(Int32));
tblData.Columns.Add("FunCode", typeof(int));
tblData.Columns.Add("OptScope", typeof(int));
foreach (var item in module)
{
tblData.Rows.Add(new object[] { roleId, item.ModuleId, item.FuncCode, item.OptScope });
} var cmd = this.sqlHelper.PrepareTextSqlCommand(SQL_PernissionReRoleModuleCreate);
cmd.AddParam("@roleId", roleId);
var param = cmd.Parameters.AddWithValue("@tvp", tblData); param.SqlDbType = SqlDbType.Structured;
param.TypeName = "dbo.ReRoleModule";
return cmd.ExecuteWrapNonQuery();
}
#endregion