Get data from two list in sharepoint 2013
private void GeEmployeDetails()
{
using (SPSite osite = new SPSite(SPContext.Current.Web.Url))
{
using (SPWeb oweb = osite.OpenWeb())
{
SPList EmployeeAchievmentslist = oweb.Lists.TryGetList("EmployeeAchievments");
SPList EmpBasicInfolist = oweb.Lists.TryGetList("EmpBasicInfo");
SPQuery query = new SPQuery();
string Querry = "<GroupBy> <FieldRef Name='Title' Ascending='True' /></GroupBy>";
query.Query = Querry;
SPListItemCollection collListItems = EmployeeAchievmentslist.GetItems(query);
DataTable dt = new DataTable();
dt.Columns.Add("Attachments", typeof(string));
dt.Columns.Add("Title", typeof(string));
dt.Columns.Add("Designation", typeof(string));
dt.Columns.Add("htmlable");
List<string> strlist = new List<string>();
foreach (SPListItem item in collListItems)
{
strlist.Add(item["UserName"] != null ? item["UserName"].ToString().Split('#')[1] : string.Empty);
string strVal = string.Join(",", strlist.ToArray());
}
var result = (from m in strlist select m).Distinct().ToList();//it is used to remove duplicates
foreach (string user in result)
{
DataRow dr = dt.NewRow();
SPQuery q = new SPQuery();
string stringQuery = string.Format(@"<Where><Eq><FieldRef Name='Title' /><Value Type='Text'>{0}</Value></Eq></Where>", user);
q.Query = stringQuery;
SPListItemCollection ocollListItems = EmpBasicInfolist.GetItems(q);
foreach (SPListItem items in ocollListItems)
{
string attachmentAbsoluteURL = string.Empty;
if (items.Attachments.Count > 0)
{
foreach (String attachmentname in items.Attachments)
{
attachmentAbsoluteURL = items.Attachments.UrlPrefix + attachmentname;
dr["Attachments"] = attachmentAbsoluteURL.ToString();
}
}
else
{
dr["Attachments"] = "/_layouts/15/VrtxIntranetStyles/Images/NoImage.jpg";
}
dr["Title"] = items["UserName"] != null ? items["UserName"].ToString() : string.Empty;
dr["Designation"] = items["Designation"] != null ? items["Designation"].ToString().Split('#')[1] : string.Empty;
SPQuery qry = new SPQuery();
qry.Query = "<Where><Eq><FieldRef Name='UserName' /><Value Type='User'>" + user + "</Value></Eq></Where>";
collListItems = EmployeeAchievmentslist.GetItems(qry);
StringBuilder sb = new StringBuilder();
string filepath = "/Style%20Library/Images/achievement-icon.jpg";
int i=1;
foreach (SPListItem item in collListItems)
{
string Technologies = item["Technologies"].ToString().Split('#')[1];
string Certifications = item["Certifications"].ToString().Split('#')[1];
string CertifiedDate = item["CertifiedDate"].ToString().Split(' ')[0];
string UntillDate = item["UntillDate"].ToString().Split(' ')[0];
sb.Append("<table>");
sb.Append("<tr>");
sb.AppendFormat("<td style=padding:12px;>{0}</td>", "<img src='" + filepath + "'/>");
sb.AppendFormat("<td style=padding:10px;width:10px;float:left; >{0}</td>", i);
sb.AppendFormat("<td style=padding:10px;text-align:left;width:150px;float:left;>{0}</td>", Technologies);
sb.AppendFormat("<td style=padding:10px;text-align:left;width:150px;float:left;>{0}</td>", Certifications);
sb.AppendFormat("<td style=padding:10px;text-align:left;width:150px;float:left;>on {0}</td>", CertifiedDate);
sb.AppendFormat("<td style=padding:10px;text-align:left;width:150px;float:left;>EndDate:{0}</td>", UntillDate);
sb.Append("<tr/>");
sb.Append("</table>");
i++;
}
dr["htmlable"] = sb.Append("</table>");
sb.Clear();
dt.Rows.Add(dr);
}
gvEmpAchievements.DataSource = dt;
gvEmpAchievements.DataBind();
}
}
}
}
{
using (SPSite osite = new SPSite(SPContext.Current.Web.Url))
{
using (SPWeb oweb = osite.OpenWeb())
{
SPList EmployeeAchievmentslist = oweb.Lists.TryGetList("EmployeeAchievments");
SPList EmpBasicInfolist = oweb.Lists.TryGetList("EmpBasicInfo");
SPQuery query = new SPQuery();
string Querry = "<GroupBy> <FieldRef Name='Title' Ascending='True' /></GroupBy>";
query.Query = Querry;
SPListItemCollection collListItems = EmployeeAchievmentslist.GetItems(query);
DataTable dt = new DataTable();
dt.Columns.Add("Attachments", typeof(string));
dt.Columns.Add("Title", typeof(string));
dt.Columns.Add("Designation", typeof(string));
dt.Columns.Add("htmlable");
List<string> strlist = new List<string>();
foreach (SPListItem item in collListItems)
{
strlist.Add(item["UserName"] != null ? item["UserName"].ToString().Split('#')[1] : string.Empty);
string strVal = string.Join(",", strlist.ToArray());
}
var result = (from m in strlist select m).Distinct().ToList();//it is used to remove duplicates
foreach (string user in result)
{
DataRow dr = dt.NewRow();
SPQuery q = new SPQuery();
string stringQuery = string.Format(@"<Where><Eq><FieldRef Name='Title' /><Value Type='Text'>{0}</Value></Eq></Where>", user);
q.Query = stringQuery;
SPListItemCollection ocollListItems = EmpBasicInfolist.GetItems(q);
foreach (SPListItem items in ocollListItems)
{
string attachmentAbsoluteURL = string.Empty;
if (items.Attachments.Count > 0)
{
foreach (String attachmentname in items.Attachments)
{
attachmentAbsoluteURL = items.Attachments.UrlPrefix + attachmentname;
dr["Attachments"] = attachmentAbsoluteURL.ToString();
}
}
else
{
dr["Attachments"] = "/_layouts/15/VrtxIntranetStyles/Images/NoImage.jpg";
}
dr["Title"] = items["UserName"] != null ? items["UserName"].ToString() : string.Empty;
dr["Designation"] = items["Designation"] != null ? items["Designation"].ToString().Split('#')[1] : string.Empty;
SPQuery qry = new SPQuery();
qry.Query = "<Where><Eq><FieldRef Name='UserName' /><Value Type='User'>" + user + "</Value></Eq></Where>";
collListItems = EmployeeAchievmentslist.GetItems(qry);
StringBuilder sb = new StringBuilder();
string filepath = "/Style%20Library/Images/achievement-icon.jpg";
int i=1;
foreach (SPListItem item in collListItems)
{
string Technologies = item["Technologies"].ToString().Split('#')[1];
string Certifications = item["Certifications"].ToString().Split('#')[1];
string CertifiedDate = item["CertifiedDate"].ToString().Split(' ')[0];
string UntillDate = item["UntillDate"].ToString().Split(' ')[0];
sb.Append("<table>");
sb.Append("<tr>");
sb.AppendFormat("<td style=padding:12px;>{0}</td>", "<img src='" + filepath + "'/>");
sb.AppendFormat("<td style=padding:10px;width:10px;float:left; >{0}</td>", i);
sb.AppendFormat("<td style=padding:10px;text-align:left;width:150px;float:left;>{0}</td>", Technologies);
sb.AppendFormat("<td style=padding:10px;text-align:left;width:150px;float:left;>{0}</td>", Certifications);
sb.AppendFormat("<td style=padding:10px;text-align:left;width:150px;float:left;>on {0}</td>", CertifiedDate);
sb.AppendFormat("<td style=padding:10px;text-align:left;width:150px;float:left;>EndDate:{0}</td>", UntillDate);
sb.Append("<tr/>");
sb.Append("</table>");
i++;
}
dr["htmlable"] = sb.Append("</table>");
sb.Clear();
dt.Rows.Add(dr);
}
gvEmpAchievements.DataSource = dt;
gvEmpAchievements.DataBind();
}
}
}
}
Comments