locked
Need some help querying data from 2 separate database not on the same server RRS feed

  • Question

  • User1901201124 posted

    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

    Friday, March 22, 2019 1:59 PM

Answers

  • User-474980206 posted

    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();
    

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, March 22, 2019 5:49 PM

All replies

  • User-474980206 posted

    these two lines of code make no sense:

    string query = "select field1, field2, field3 from table where someID = " + item.TheID;
    Object.Add(db2.Database.SqlQuery<string>(query));
    

    you are are saying the sql query return one string column (<string>), but are selecting 3 columns. and what is Object ?

    Friday, March 22, 2019 2:20 PM
  • User1901201124 posted

    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;
    Friday, March 22, 2019 2:28 PM
  • User1901201124 posted

    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?

    Friday, March 22, 2019 4:17 PM
  • User-474980206 posted

    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();
    

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, March 22, 2019 5:49 PM
  • User1120430333 posted

    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>

    Friday, March 22, 2019 5:58 PM
  • User1901201124 posted

    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?

    Friday, March 22, 2019 6:10 PM
  • User1120430333 posted

    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;
                }
            }
        }
    }

    Friday, March 22, 2019 7:02 PM
  • User1901201124 posted

    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!

    Friday, March 22, 2019 7:20 PM
  • User1901201124 posted

    Thank you - I"ll go through this.

    Friday, March 22, 2019 7:21 PM