I want To Display Data from Multiple Relational DB Tables But it Doesn't Work Correctly... Please Help Me to Find This...
Thanks In Advance...
Here Is My Repository Class Method
public List<advertisement> getAll()
{
List<advertisement> List = new List<advertisement>();
try
{
SqlCommand cmd = new SqlCommand("sp_advertisement_display_all", connection);
cmd.CommandType = CommandType.StoredProcedure;
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataTable dt = new DataTable();
connection.Open();
da.Fill(dt);
connection.Close();
List = (from DataRow dr in dt.Rows
select new advertisement
{
adsId = Convert.ToInt32(dr["adsId"]),
adsImgPath = Convert.ToString(dr["adsImgPath"]),
adsTypeId = Convert.ToInt32(dr["adsTypeId"]),
adsStartDate = Convert.ToDateTime(dr["adsStartDate"]),
adsExpiryDate = Convert.ToDateTime(dr["adsExpiryDate"]),
adsPostBy = Convert.ToInt32(dr["adsPostBy"]),
adsDeactivateBy = Convert.ToInt32(dr["adsDeactivateBy"]),
clientId = Convert.ToInt32(dr["clientId"]),
client = new client
{
clientId = Convert.ToInt32(dr["clientId"]),
clientEmail = Convert.ToString(dr["clientEmail"]),
},
masterUser = new masterUser
{
mUserId = Convert.ToInt32(dr["mUserId"]),
mUserEmail = Convert.ToString(dr["mUserEmail"]),
},
adsType = new adsType
{
adsTypeId = Convert.ToInt32(dr["adsTypeId"]),
adsTypeName = Convert.ToString(dr["adsTypeName"])
}
}).ToList();
return List;
}
catch
{
connection.Close();
return List;
}
finally
{
connection.Close();
}
}
Here Is My Controller Method
[Authorize(Roles = "Master, Admin")]
public ActionResult Display()
{
if (Session["UserId"] != null)
{
if (Session["UserRole"].ToString() == "Master" || Session["UserRole"].ToString() == "Admin" || Session["UserRole"].ToString() == "Agent" || Session["UserRole"].ToString() == "Accountant" || Session["UserRole"].ToString() == "Developer")
{
return View(repObj.getAll());
}
else
{
TempData["Access"] = "Not Allow";
return RedirectToAction("Display");
}
}
else
{
TempData["Session"] = "Not Login";
return RedirectToAction("Login", "Login", "MasterUserLogin");
}
}
Here Is StoredProcedure Query
SELECT advertisement.*, adsType.*, client.*, masterUser.* FROM advertisement
INNER JOIN adsType ON advertisement.adsTypeId = adsType.adsTypeId
INNER JOIN client ON advertisement.clientId = client.clientId
INNER JOIN masterUser ON advertisement.adsPostBy = masterUser.mUserId
OR advertisement.adsDeactivateBy = masterUser.mUserId;
Query Returns all data Fine..... It Does not gives me any error but no data returns.....
code did not go into WHILE Loop....
What I have tried:
How to Implement Method for Display Multiple Tables Data using ADO.NET in MVC5