Take Example of three dummy tables named as Usermaster,SolutionMaster and RoleType
UserMaster contains-: UserMasterId,UserName,RoleTypeId
SolutionMaster -: SolutionMasterId,CreatedBy(Or UserMasterId),SolutionName
RoleType :- RoleTypeId,RoleName
public DataSet GetData()
{
DataSet ds = new DataSet();
string sql = "Select * from UserMaster" + "\n Select * from SolutionMaster" + "\n Select * from RoleType";
ds = SqlHelper.ExecuteDataset(cOnstr, CommandType.Text, sql);
ds.Tables[0].TableName = "UserMaster";
ds.Tables[1].TableName = "SolutionMaster";
ds.Tables[2].TableName = "RoleType";
return ds;
}
protected void btnGetLinqJoin_Click(object sender, EventArgs e)
{
DataSet ds = GetData();
DataTable dtReturn = new DataTable();
var ResultTable = from sm in ds.Tables[1].AsEnumerable()
join um in ds.Tables[0].AsEnumerable()
on (dynamic)sm["CreatedBy"]
equals (dynamic)um["UserMasterId"] into sr
from um in sr
join rm in ds.Tables[2].AsEnumerable()
on (dynamic)um["RoleTypeId"] equals (dynamic)rm["RoleTypeId"] into sc
from rm in sc
select new
{
SolutionName = (dynamic)sm["SolutionName"],
UserName = (dynamic)um["UserName"],
RoleType = (dynamic)rm["RoleTypeName"]
};
dtReturn = ToDataTable(ResultTable);
}
public DataTable ToDataTable<T>(IEnumerable<T> varlist)
{
DataTable dtReturn = new DataTable();
PropertyInfo[] oProps = null;
if (varlist == null) return dtReturn;
foreach (T rec in varlist)
{
// Use reflection to get property names, to create table,
//Only first time, others will follow
if (oProps == null)
{
oProps = ((Type)rec.GetType()).GetProperties();
foreach (PropertyInfo pi in oProps)
{
Type colType = pi.PropertyType;
if ((colType.IsGenericType) && (colType.GetGenericTypeDefinition()
== typeof(Nullable<>)))
{
colType = colType.GetGenericArguments()[0];
}
dtReturn.Columns.Add(new DataColumn(pi.Name, colType));
}
}
DataRow dr = dtReturn.NewRow();
foreach (PropertyInfo pi in oProps)
{
dr[pi.Name] = pi.GetValue(rec, null) == null ? DBNull.Value : pi.GetValue
(rec, null);
}
dtReturn.Rows.Add(dr);
}
return dtReturn;
}
OUTPUT :-
SoltuinName | UserName | Role |
ABCD | ABCD | Standard |
XYZ | XYZ | Admin |
No comments:
Post a Comment