The problem is in another database not in my application database there is related data to "Something2" (Something2 is a one to many relationship to MyObject).
I am trying to find a way to bring that related data in so I can display everything.
If that makes sense and you can help out, that would be much appreciated!
That's the part i was playing around with and doesn't work. I"m not sure how to go about looping through the foreach and then querying the 2nd database to stick in the data.
if i didn't have a foreach and it was just 1 object i would have used
IEnumerable<Object2> data = db2.Database.SqlQuery<Object2>(query);
viewModel.Object2 = data;
The problem is in another database not in my application database there is related data to "Something2" (Something2 is a one to many relationship to MyObject).
I am trying to find a way to bring that related data in so I can display everything.
var inList = string.Join(",", viewModel.Object1.Navprop1.Select(r => r.TheID.ToString()).ToArray());
var query = "select field1, field2, field3 from table where someID in (" + inList + ")";
var object2 = db2.Database.SqlQuery<Object2>(query).ToList();
You seem to have a misconception of a view model, which is a simple object used by the view, and IMO is not directly populated by a database call. A model object in the Models folder does the database access, and it in turn, it populates the properties
of the VM object
I don't see why a model object in the Models folder can't accomplish what you are trying to do to complete the VM object for a given view.
An MVC model contains all of your application logic that is not contained in a view or a controller. The model should contain all of your application business logic, validation logic, and database access logic.
<end>
If you find the post has answered your issue, then please mark post as 'answered'.
ViewModel or Model is fine with me. But that still doesn't answer my question in how I would be able to accomplish what I am trying to do or have you not looked at the question where It says i need help bringing in related data from a database outside my
application and model?
You don't understand the concept of a model object in the Models folder, it is being called by the controller, the model object is doing data access and the model object is working with the VM. I'll show you. :)
1) A VM can contain other VM(s).
2) There is nothing stopping ProjectModel from calling another model object for data access. ProjectModel can do anything it wants or needs to do to complete the task.
The EF virtual object model is setting behind the ASP.NET WebAPI, and the DTO pattern is being used incase you were wondering what DTO meant, which you don't have to use the DTO or the WebAPI.
It's about the ProjectModel object, and how it is being used and how it works with the VM. This form of the model object in the Models folder can do anything you need it to do. It can make all the calls to the database and stich the VM together.
using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.Threading.Tasks;
using Microsoft.AspNetCore.Mvc.Rendering;
namespace ProgMgmntCore2UserIdentity.Models
{
public class ProjectViewModels
{
public class Project
{
public int ProjectId { get; set; }
[Required(ErrorMessage = "Client Name is required")]
[StringLength(50)]
public string ClientName { get; set; }
[Required(ErrorMessage = "Project Name is required")]
[StringLength(50)]
public string ProjectName { get; set; }
[Required(ErrorMessage = "Technology is required")]
[StringLength(50)]
public string Technology { get; set; }
[Required(ErrorMessage = "Project Type is required")]
public string ProjectType { get; set; }
[Required(ErrorMessage = "Start Date is required")]
[DisplayFormat(ApplyFormatInEditMode = true, DataFormatString = "{0:MM-dd-yyyy}")]
public DateTime? StartDate { get; set; }
[Required(ErrorMessage = "End Date is required")]
[DisplayFormat(ApplyFormatInEditMode = true, DataFormatString = "{0:MM-dd-yyyy}")]
public DateTime? EndDate { get; set; }
[Required(ErrorMessage = "Cost is required")]
public decimal? Cost { get; set; }
public List<SelectListItem> ProjectTypes { get; set; }
}
public List<Project> Projects { get; set; }
}
}
using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
using Entities;
using Microsoft.AspNetCore.Mvc.Rendering;
using Microsoft.Extensions.Caching.Memory;
using ProgMgmntCore2UserIdentity.WebApi;
namespace ProgMgmntCore2UserIdentity.Models
{
public class ProjectModel : IProjectModel
{
private readonly IMemoryCache _memoryCache;
private readonly IWebApi _webApi;
public ProjectModel(IWebApi webApi, IMemoryCache memoryCache)
{
_memoryCache = memoryCache;
_webApi = webApi;
}
public ProjectViewModels GetProjectsByUserId(string userid)
{
var vm = new ProjectViewModels {Projects = new List<ProjectViewModels.Project>()};
var dtos = _webApi.GetProjsByUserIdApi(userid).ToList();
vm.Projects.AddRange(dtos.Select(dto => new ProjectViewModels.Project()
{
ProjectId = dto.ProjectId,
ClientName = dto.ClientName,
ProjectName = dto.ProjectName,
Technology = dto.Technology,
ProjectType = dto.ProjectType,
StartDate = dto.StartDate,
EndDate = dto.EndDate,
Cost = dto.Cost
}).ToList());
return vm;
}
public ProjectViewModels.Project GetProjectById(int id)
{
var responseDto = _webApi.GetProjByIdApi(id);
var project = new ProjectViewModels.Project
{
ProjectId = responseDto.ProjectId,
ClientName = responseDto.ClientName,
ProjectName = responseDto.ProjectName,
Technology = responseDto.Technology,
ProjectType = responseDto.ProjectType,
StartDate = responseDto.StartDate,
EndDate = responseDto.EndDate,
Cost = responseDto.Cost
};
return project;
}
public ProjectViewModels.Project Create()
{
var project = new ProjectViewModels.Project();
return PopulateSelectedList(project);
}
public void Create(ProjectViewModels.Project project, string userid)
{
var dto = new DtoProject
{
ProjectId = project.ProjectId,
ClientName = project.ClientName,
ProjectName = project.ProjectName,
ProjectType = project.ProjectType,
Technology = project.Technology,
UserId = userid,
StartDate = (DateTime) project.StartDate,
EndDate = (DateTime) project.EndDate,
Cost = (decimal) project.Cost
};
_webApi.CreateProjectApi(dto);
}
public ProjectViewModels.Project Edit(int id)
{
var responseDto = _webApi.GetProjByIdApi(id);
var project = new ProjectViewModels.Project
{
ProjectId = responseDto.ProjectId,
ClientName = responseDto.ClientName,
ProjectName = responseDto.ProjectName,
Technology = responseDto.Technology,
ProjectType = responseDto.ProjectType,
StartDate = responseDto.StartDate,
EndDate = responseDto.EndDate,
Cost = responseDto.Cost
};
project = PopulateSelectedList(project);
return project;
}
public void Edit(ProjectViewModels.Project project, string userid)
{
var dto = new DtoProject
{
ProjectId = project.ProjectId,
ClientName = project.ClientName,
ProjectName = project.ProjectName,
ProjectType = project.ProjectType,
Technology = project.Technology,
UserId = userid,
StartDate = (DateTime) project.StartDate,
EndDate = (DateTime) project.EndDate,
Cost = (decimal) project.Cost
};
_webApi.UpdateProjectApi(dto);
}
public void Delete(int id)
{
_webApi.DeleteProjectApi(new DtoId{Id = id});
}
public ProjectViewModels.Project PopulateSelectedList(ProjectViewModels.Project project)
{
bool isExist = _memoryCache.TryGetValue("DtoCache", out DtoCache dtocache);
if (!isExist)
{
dtocache = _webApi.GetCacheApi();
var cacheEntryOptions = new MemoryCacheEntryOptions()
.SetSlidingExpiration(TimeSpan.FromSeconds(30));
_memoryCache.Set("DtoCache", dtocache, cacheEntryOptions);
}
project.ProjectTypes = new List<SelectListItem>();
foreach (var pt in dtocache.ProjectTypes)
{
var sli = new SelectListItem {Value = pt.Value, Text = pt.Text};
project.ProjectTypes.Add(sli);
}
var selected = (from a in project.ProjectTypes.Where(a => a.Value == project.ProjectType) select a)
.SingleOrDefault();
if (selected != null)
selected.Selected = true;
return project;
}
}
}
using System;
using System.Linq;
using Microsoft.AspNetCore.Authorization;
using Microsoft.AspNetCore.Mvc;
using Microsoft.AspNetCore.Mvc.ModelBinding;
using ProgMgmntCore2UserIdentity.Models;
namespace ProgMgmntCore2UserIdentity.Controllers
{
public class ProjectController : Controller
{
private readonly IProjectModel _projectModel;
private readonly IModelHelper _modelHelper;
public ProjectController(IProjectModel projectModel, IModelHelper modelHelper)
{
_projectModel = projectModel;
_modelHelper = modelHelper;
}
// GET: Project
[Authorize]
public ActionResult Index()
{
return View(_projectModel.GetProjectsByUserId(User.Identity.Name));
}
[Authorize]
public ActionResult Details(int id = 0)
{
return id == 0 ? null : View(_projectModel.Edit(id));
}
[Authorize]
public ActionResult Create()
{
return View(_projectModel.Create());
}
[Authorize]
[HttpPost]
public ActionResult Create(ProjectViewModels.Project project, string submit)
{
if (submit == "Cancel") return RedirectToAction("Index");
ValidateddlProjectTypes();
project.ProjectType = (Request.Form["ddlProjectTypes"]);
if (ModelState.IsValid && _modelHelper.IsEndDateLessThanStartDate(project, "Project"))
ModelState.AddModelError(string.Empty, "End Date cannot be less than Start Date.");
if (!ModelState.IsValid) return View(_projectModel.PopulateSelectedList(project));
_projectModel.Create(project, User.Identity.Name);
return RedirectToAction("Index");
}
[Authorize]
public ActionResult Edit(int id = 0)
{
return id == 0 ? null : View(_projectModel.Edit(id));
}
[Authorize]
[HttpPost]
public ActionResult Edit(ProjectViewModels.Project project, string submit)
{
if (submit == "Cancel") return RedirectToAction("Index");
if (ModelState.IsValid && _modelHelper.IsEndDateLessThanStartDate(project, "Project"))
ModelState.AddModelError(String.Empty, "End Date cannot be less than Start Date.");
if (!ModelState.IsValid) return View(_projectModel.PopulateSelectedList(project));
var theproject = new ProjectViewModels.Project();
theproject = project;
theproject.ProjectType = Request.Form["ProjectType"];
_projectModel.Edit(theproject, User.Identity.Name);
return RedirectToAction("Index");
}
public ActionResult Delete(int id = 0)
{
if (id > 0) _projectModel.Delete(id);
return RedirectToAction("Index");
}
public ActionResult Cancel()
{
return RedirectToAction("Index", "Home");
}
public ActionResult UploadFile(int id)
{
return RedirectToAction("Index", "Upload", new { id = id, type = "PM" });
}
private void ValidateddlProjectTypes()
{
if (Request.Form["ddlProjectTypes"] == string.Empty)
return;
foreach (var key in ModelState.Keys.ToList().Where(key => ModelState.ContainsKey(key)))
{
if (key != "ProjectType") continue;
ModelState[key].Errors.Clear();
ModelState[key].ValidationState = ModelValidationState.Valid;
}
}
}
}
If you find the post has answered your issue, then please mark post as 'answered'.
Member
24 Points
67 Posts
Need some help querying data from 2 separate database not on the same server
Mar 22, 2019 01:59 PM|MVCNewbi3v|LINK
This is the problem I am trying to solve.
Basically what I have is, for example let's say, this viewModel
viewModel.MyObject = db.MyObject
.Include(i => i.Something1)
.Include(i => i.Something2)
.SingleOrDefault(x => i.ID == id);
The problem is in another database not in my application database there is related data to "Something2" (Something2 is a one to many relationship to MyObject).
I am trying to find a way to bring that related data in so I can display everything.
If that makes sense and you can help out, that would be much appreciated!
Thanks
All-Star
53024 Points
13017 Posts
Re: Need some help querying data from 2 separate database not on the same server
Mar 22, 2019 02:20 PM|bruce (sqlwork.com)|LINK
these two lines of code make no sense:
you are are saying the sql query return one string column (<string>), but are selecting 3 columns. and what is Object ?
Member
24 Points
67 Posts
Re: Need some help querying data from 2 separate database not on the same server
Mar 22, 2019 02:28 PM|MVCNewbi3v|LINK
That's the part i was playing around with and doesn't work. I"m not sure how to go about looping through the foreach and then querying the 2nd database to stick in the data.
if i didn't have a foreach and it was just 1 object i would have used
Member
24 Points
67 Posts
Re: Need some help querying data from 2 separate database not on the same server
Mar 22, 2019 04:17 PM|MVCNewbi3v|LINK
Basically what I have is, for example let's say, this viewModel
viewModel.MyObject = db.MyObject
.Include(i => i.Something1)
.Include(i => i.Something2)
.SingleOrDefault(x => i.ID == id);
The problem is in another database not in my application database there is related data to "Something2" (Something2 is a one to many relationship to MyObject).
I am trying to find a way to bring that related data in so I can display everything.
Does that make sense?
All-Star
53024 Points
13017 Posts
Re: Need some help querying data from 2 separate database not on the same server
Mar 22, 2019 05:49 PM|bruce (sqlwork.com)|LINK
pretty trivial:
var inList = string.Join(",", viewModel.Object1.Navprop1.Select(r => r.TheID.ToString()).ToArray()); var query = "select field1, field2, field3 from table where someID in (" + inList + ")"; var object2 = db2.Database.SqlQuery<Object2>(query).ToList();Contributor
3851 Points
3245 Posts
Re: Need some help querying data from 2 separate database not on the same server
Mar 22, 2019 05:58 PM|DA924|LINK
You seem to have a misconception of a view model, which is a simple object used by the view, and IMO is not directly populated by a database call. A model object in the Models folder does the database access, and it in turn, it populates the properties of the VM object
I don't see why a model object in the Models folder can't accomplish what you are trying to do to complete the VM object for a given view.
https://docs.microsoft.com/en-us/aspnet/mvc/overview/older-versions-1/overview/understanding-models-views-and-controllers-cs
<copied>
An MVC model contains all of your application logic that is not contained in a view or a controller. The model should contain all of your application business logic, validation logic, and database access logic.
<end>
Member
24 Points
67 Posts
Re: Need some help querying data from 2 separate database not on the same server
Mar 22, 2019 06:10 PM|MVCNewbi3v|LINK
ViewModel or Model is fine with me. But that still doesn't answer my question in how I would be able to accomplish what I am trying to do or have you not looked at the question where It says i need help bringing in related data from a database outside my application and model?
Contributor
3851 Points
3245 Posts
Re: Need some help querying data from 2 separate database not on the same server
Mar 22, 2019 07:02 PM|DA924|LINK
You don't understand the concept of a model object in the Models folder, it is being called by the controller, the model object is doing data access and the model object is working with the VM. I'll show you. :)
1) A VM can contain other VM(s).
2) There is nothing stopping ProjectModel from calling another model object for data access. ProjectModel can do anything it wants or needs to do to complete the task.
The EF virtual object model is setting behind the ASP.NET WebAPI, and the DTO pattern is being used incase you were wondering what DTO meant, which you don't have to use the DTO or the WebAPI.
https://www.codeproject.com/Articles/1050468/Data-Transfer-Object-Design-Pattern-in-Csharp
It's about the ProjectModel object, and how it is being used and how it works with the VM. This form of the model object in the Models folder can do anything you need it to do. It can make all the calls to the database and stich the VM together.
using System; using System.Collections.Generic; using System.ComponentModel.DataAnnotations; using System.Threading.Tasks; using Microsoft.AspNetCore.Mvc.Rendering; namespace ProgMgmntCore2UserIdentity.Models { public class ProjectViewModels { public class Project { public int ProjectId { get; set; } [Required(ErrorMessage = "Client Name is required")] [StringLength(50)] public string ClientName { get; set; } [Required(ErrorMessage = "Project Name is required")] [StringLength(50)] public string ProjectName { get; set; } [Required(ErrorMessage = "Technology is required")] [StringLength(50)] public string Technology { get; set; } [Required(ErrorMessage = "Project Type is required")] public string ProjectType { get; set; } [Required(ErrorMessage = "Start Date is required")] [DisplayFormat(ApplyFormatInEditMode = true, DataFormatString = "{0:MM-dd-yyyy}")] public DateTime? StartDate { get; set; } [Required(ErrorMessage = "End Date is required")] [DisplayFormat(ApplyFormatInEditMode = true, DataFormatString = "{0:MM-dd-yyyy}")] public DateTime? EndDate { get; set; } [Required(ErrorMessage = "Cost is required")] public decimal? Cost { get; set; } public List<SelectListItem> ProjectTypes { get; set; } } public List<Project> Projects { get; set; } } }using System; using System.Collections.Generic; using System.Linq; using System.Threading.Tasks; using Entities; using Microsoft.AspNetCore.Mvc.Rendering; using Microsoft.Extensions.Caching.Memory; using ProgMgmntCore2UserIdentity.WebApi; namespace ProgMgmntCore2UserIdentity.Models { public class ProjectModel : IProjectModel { private readonly IMemoryCache _memoryCache; private readonly IWebApi _webApi; public ProjectModel(IWebApi webApi, IMemoryCache memoryCache) { _memoryCache = memoryCache; _webApi = webApi; } public ProjectViewModels GetProjectsByUserId(string userid) { var vm = new ProjectViewModels {Projects = new List<ProjectViewModels.Project>()}; var dtos = _webApi.GetProjsByUserIdApi(userid).ToList(); vm.Projects.AddRange(dtos.Select(dto => new ProjectViewModels.Project() { ProjectId = dto.ProjectId, ClientName = dto.ClientName, ProjectName = dto.ProjectName, Technology = dto.Technology, ProjectType = dto.ProjectType, StartDate = dto.StartDate, EndDate = dto.EndDate, Cost = dto.Cost }).ToList()); return vm; } public ProjectViewModels.Project GetProjectById(int id) { var responseDto = _webApi.GetProjByIdApi(id); var project = new ProjectViewModels.Project { ProjectId = responseDto.ProjectId, ClientName = responseDto.ClientName, ProjectName = responseDto.ProjectName, Technology = responseDto.Technology, ProjectType = responseDto.ProjectType, StartDate = responseDto.StartDate, EndDate = responseDto.EndDate, Cost = responseDto.Cost }; return project; } public ProjectViewModels.Project Create() { var project = new ProjectViewModels.Project(); return PopulateSelectedList(project); } public void Create(ProjectViewModels.Project project, string userid) { var dto = new DtoProject { ProjectId = project.ProjectId, ClientName = project.ClientName, ProjectName = project.ProjectName, ProjectType = project.ProjectType, Technology = project.Technology, UserId = userid, StartDate = (DateTime) project.StartDate, EndDate = (DateTime) project.EndDate, Cost = (decimal) project.Cost }; _webApi.CreateProjectApi(dto); } public ProjectViewModels.Project Edit(int id) { var responseDto = _webApi.GetProjByIdApi(id); var project = new ProjectViewModels.Project { ProjectId = responseDto.ProjectId, ClientName = responseDto.ClientName, ProjectName = responseDto.ProjectName, Technology = responseDto.Technology, ProjectType = responseDto.ProjectType, StartDate = responseDto.StartDate, EndDate = responseDto.EndDate, Cost = responseDto.Cost }; project = PopulateSelectedList(project); return project; } public void Edit(ProjectViewModels.Project project, string userid) { var dto = new DtoProject { ProjectId = project.ProjectId, ClientName = project.ClientName, ProjectName = project.ProjectName, ProjectType = project.ProjectType, Technology = project.Technology, UserId = userid, StartDate = (DateTime) project.StartDate, EndDate = (DateTime) project.EndDate, Cost = (decimal) project.Cost }; _webApi.UpdateProjectApi(dto); } public void Delete(int id) { _webApi.DeleteProjectApi(new DtoId{Id = id}); } public ProjectViewModels.Project PopulateSelectedList(ProjectViewModels.Project project) { bool isExist = _memoryCache.TryGetValue("DtoCache", out DtoCache dtocache); if (!isExist) { dtocache = _webApi.GetCacheApi(); var cacheEntryOptions = new MemoryCacheEntryOptions() .SetSlidingExpiration(TimeSpan.FromSeconds(30)); _memoryCache.Set("DtoCache", dtocache, cacheEntryOptions); } project.ProjectTypes = new List<SelectListItem>(); foreach (var pt in dtocache.ProjectTypes) { var sli = new SelectListItem {Value = pt.Value, Text = pt.Text}; project.ProjectTypes.Add(sli); } var selected = (from a in project.ProjectTypes.Where(a => a.Value == project.ProjectType) select a) .SingleOrDefault(); if (selected != null) selected.Selected = true; return project; } } }using System; using System.Linq; using Microsoft.AspNetCore.Authorization; using Microsoft.AspNetCore.Mvc; using Microsoft.AspNetCore.Mvc.ModelBinding; using ProgMgmntCore2UserIdentity.Models; namespace ProgMgmntCore2UserIdentity.Controllers { public class ProjectController : Controller { private readonly IProjectModel _projectModel; private readonly IModelHelper _modelHelper; public ProjectController(IProjectModel projectModel, IModelHelper modelHelper) { _projectModel = projectModel; _modelHelper = modelHelper; } // GET: Project [Authorize] public ActionResult Index() { return View(_projectModel.GetProjectsByUserId(User.Identity.Name)); } [Authorize] public ActionResult Details(int id = 0) { return id == 0 ? null : View(_projectModel.Edit(id)); } [Authorize] public ActionResult Create() { return View(_projectModel.Create()); } [Authorize] [HttpPost] public ActionResult Create(ProjectViewModels.Project project, string submit) { if (submit == "Cancel") return RedirectToAction("Index"); ValidateddlProjectTypes(); project.ProjectType = (Request.Form["ddlProjectTypes"]); if (ModelState.IsValid && _modelHelper.IsEndDateLessThanStartDate(project, "Project")) ModelState.AddModelError(string.Empty, "End Date cannot be less than Start Date."); if (!ModelState.IsValid) return View(_projectModel.PopulateSelectedList(project)); _projectModel.Create(project, User.Identity.Name); return RedirectToAction("Index"); } [Authorize] public ActionResult Edit(int id = 0) { return id == 0 ? null : View(_projectModel.Edit(id)); } [Authorize] [HttpPost] public ActionResult Edit(ProjectViewModels.Project project, string submit) { if (submit == "Cancel") return RedirectToAction("Index"); if (ModelState.IsValid && _modelHelper.IsEndDateLessThanStartDate(project, "Project")) ModelState.AddModelError(String.Empty, "End Date cannot be less than Start Date."); if (!ModelState.IsValid) return View(_projectModel.PopulateSelectedList(project)); var theproject = new ProjectViewModels.Project(); theproject = project; theproject.ProjectType = Request.Form["ProjectType"]; _projectModel.Edit(theproject, User.Identity.Name); return RedirectToAction("Index"); } public ActionResult Delete(int id = 0) { if (id > 0) _projectModel.Delete(id); return RedirectToAction("Index"); } public ActionResult Cancel() { return RedirectToAction("Index", "Home"); } public ActionResult UploadFile(int id) { return RedirectToAction("Index", "Upload", new { id = id, type = "PM" }); } private void ValidateddlProjectTypes() { if (Request.Form["ddlProjectTypes"] == string.Empty) return; foreach (var key in ModelState.Keys.ToList().Where(key => ModelState.ContainsKey(key))) { if (key != "ProjectType") continue; ModelState[key].Errors.Clear(); ModelState[key].ValidationState = ModelValidationState.Valid; } } } }Member
24 Points
67 Posts
Re: Need some help querying data from 2 separate database not on the same server
Mar 22, 2019 07:20 PM|MVCNewbi3v|LINK
That works but the issue is the where clause is using "In" and that just pulls all the record for let's say ID 1 and ID 2.
I need to be able to loop through the list and present the Records for ID 1 and then Records for ID 2.
Thanks!
Member
24 Points
67 Posts
Re: Need some help querying data from 2 separate database not on the same server
Mar 22, 2019 07:21 PM|MVCNewbi3v|LINK
Thank you - I"ll go through this.