最近的项目用到了大量的复合查询结果用于数据源,绑定到数据控件上。
为了方便,我们把它转换成DataTable的数据源形式。请看下面的示例:
1)思考自己需要的数据,然后组合,因此创建一个新的类:
/// <summary>
/// the DTO for role group mapping
/// </summary>
public class GroupByRole
{
/// <summary>
/// this Id
/// </summary>
public int Id { get; set; }
/// <summary>
/// this SFGroupCode
/// </summary>
public string SFGroupCode { get; set; }
/// <summary>
/// this ParentFunctionId
/// </summary>
public int RoleId { get; set; }
/// <summary>
/// this DisplayDescription
/// </summary>
public string DisplayDescription { get; set; }
/// <summary>
/// this SFGroupName
/// </summary>
public string SFGroupName { get; set; }
/// <summary>
/// this IsDefaultFunction
/// </summary>
public bool IsReadable { get; set; }
/// <summary>
/// this FunctionType
/// </summary>
public bool IsEditable { get; set; }
}
上面的类就是 我需要的 所以表的数据集合。
2)下面的方法是类转表的方法(需要添加 System.Reflection 命名空间引用):
/// <summary>
/// get a empty datatable by a object
/// </summary>
/// <param name="dataType"></param>
/// <returns></returns>
[System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Reliability", "CA2000:Dispose objects before losing scope")]
protected static DataTable GetDataTableSchema(Type dataType)
{
if (dataType == null) throw new ArgumentNullException("dataType");
DataTable dt = new DataTable();
dt.Locale = System.Globalization.CultureInfo.InvariantCulture;
Type tType = dataType;
PropertyInfo[] propertys = tType.GetProperties();
for (int i = 0; i < propertys.Length; i++)
{
string columnName = propertys[i].Name;
if (!dt.Columns.Contains(columnName))
{
dt.Columns.Add(columnName);
}
}
return dt;
}
3)下面的方法就是用Linqtosql语法查询出来的结果,并且调用 2)的方法转成DataTable:
/// <summary>
/// get Role Group Mapping
/// </summary>
/// <param name="roleId"></param>
/// <returns></returns>
public DataTable GetRoleGroupMappingByRole(int roleId)
{
//用于查询的源数据
IList<SFGroup> groups = SFGroupService.ActiveSFGroups();
IList<SecurityRole> roles = SecurityRoleService.ActiveRoles();
IList<SecurityRoleSFGroupMapping> roleGroups = RoleSFGroupRightsManagementService.GetActiveGroups();
//复合查询结果集
var result = from mapp in roleGroups
join srole in roles on mapp.RoleId equals srole.Id
join sfgroup in groups on mapp.SFGroupCode equals sfgroup.SFGroupCode
where srole.Id == roleId
select new { mapp.Id, mapp.SFGroupCode, sfgroup.SFGroupName, mapp.RoleId, srole.DisplayDescription, mapp.IsEditable, mapp.IsReadable };
//创建table空模板
DataTable dt = GetDataTableSchema(typeof(GroupByRole));
//把结果转成table数据形式
foreach (var rfm in result)
{
DataRow dr = dt.NewRow();
DataColumnCollection dcl = dt.Columns;
foreach (DataColumn dc in dcl)
{
dr[dc.ColumnName] = rfm.GetType().GetProperty(dc.ColumnName).GetValue(rfm, null);
}
dt.Rows.Add(dr);
}
return dt;
}
介绍完毕。
这个方法是我新学到的,拿出来给那些没有接触过的程序猿看看,也方便自己查找。
喜欢就收藏下吧,哈哈,我文笔不行,如果大家有不明白的地方可以交流。
^_^