public JsonResult GetDbData()
{
using (ShoppingDBEntities dbObj = new ShoppingDBEntities())
{
var q = from tblMaster in dbObj.tblDemoJQueryForms
join tblInter in dbObj.tblLanguageInters on tblMaster.id equals tblInter.Id
join tblLang in dbObj.tblLanguages on tblInter.languageId equals tblLang.languageId
join states in dbObj.tblStates on tblMaster.State equals states.stateid
join cities in dbObj.tblCities on tblMaster.City equals cities.cityId
where (tblMaster.id == tblInter.Id) && (tblInter.languageId == tblLang.languageId) &&
(tblMaster.State == states.stateid) && (tblMaster.City == cities.cityId)
select new { tblMaster.Code, tblMaster.id, tblMaster.Name, tblMaster.DOB, tblMaster.Gender, states.stateid, states.stateName,
tblMaster.Salary, cities.cityId, cities.cityName, tblLang.languageId, tblLang.languageName };
var results = q.ToList();
List <JqueryTableDisplay> jqueryTable = new List <JqueryTableDisplay>();
foreach (var item in results)
{
if (!jqueryTable.Any(c => c.id == item.id))
{
JqueryTableDisplay tableData = new JqueryTableDisplay();
tableData.Code = item.Code;
tableData.id = item.id;
tableData.State = item.stateName;
tableData.City = item.cityName;
tableData.Name = item.Name;
tableData.DOB = item.DOB;
tableData.Salary = item.Salary;
tableData.Gender = ConvertGenderIDtoString.Convert(item.Gender);
tableData.Languages = new List <string>();
tableData.Languages.Add(item.languageName);
jqueryTable.Add(tableData);
}
else
{
if (!string.IsNullOrEmpty(item.languageName))
{
var row = jqueryTable.Where(c => c.id == item.id).FirstOrDefault();
if (row != null)
{
row.Languages.Add(item.languageName);
}
}
}
}
return(Json(jqueryTable, JsonRequestBehavior.AllowGet));
}
}
public JsonResult GetDbData()
{
using (ShoppingDBEntities dbObj = new ShoppingDBEntities())
{
JqueryTableDisplay tableDisplay = new JqueryTableDisplay
{
Languages = new List <string>()
};
List <JqueryTableDisplay> jqueryTable = new List <JqueryTableDisplay>();
var q = from tblMaster in dbObj.tblDemoJQueryForms
join tblInter in dbObj.tblLanguageInters on tblMaster.id equals tblInter.Id
join tblLang in dbObj.tblLanguages on tblInter.languageId equals tblLang.languageId
join states in dbObj.tblStates on tblMaster.State equals states.stateid
join cities in dbObj.tblCities on tblMaster.City equals cities.cityId
where (tblMaster.id == tblInter.Id) && (tblInter.languageId == tblLang.languageId) &&
(tblMaster.State == states.stateid) && (tblMaster.City == cities.cityId)
select new { tblMaster.id, tblMaster.Name, tblMaster.DOB, tblMaster.Gender, states.stateid, states.stateName,
tblMaster.Salary, cities.cityId, cities.cityName, tblLang.languageId, tblLang.languageName };
var results = q.ToList();
int i = -1;
int j = 0;
Nullable <int> last_id = null;
foreach (var item in results)
{
if (item.id != last_id)
{
jqueryTable.Add(tableDisplay);
i++;
jqueryTable[i].Name = item.Name;
jqueryTable[i].DOB = item.DOB;
jqueryTable[i].Salary = item.Salary;
if (item.Gender == 1)
{
jqueryTable[i].Gender = "Male";
}
else if (item.Gender == 2)
{
jqueryTable[i].Gender = "Female";
}
else if (item.Gender == 3)
{
jqueryTable[i].Gender = "Transgender";
}
else
{
jqueryTable[i].Gender = "Not Stated";
}
jqueryTable[i].State = item.stateName;
jqueryTable[i].City = item.cityName;
jqueryTable[i].Languages.Add("test");
jqueryTable[i].Languages[j] = item.languageName;
last_id = item.id;
}
else
{
j++;
jqueryTable[i].Languages.Add("test");
jqueryTable[i].Languages[j] = item.languageName;
}
}
var output = jqueryTable;
return(Json(output, JsonRequestBehavior.AllowGet));
}
}