locked
Multiple Database Problem 2 RRS feed

  • Question

  • User-596903714 posted

    Hi All,

    I'm looking for some general advice on writing SQL statements to solve a problem with data being stored in multiple databases.  Without going into too much detail, the company I work for manufactures and sells oilfield products that are assembled and sold to customers and serviced many times throughout the products life-cycle.  We wrote a tracking system in C# MVC that allows the customer to login and see a complete service history for all their product and where they have been deployed to the oilfield.

    The company has recently acquired some smaller competitors and plans to rebrand them as branches of our company.  They plan to migrate their systems to our ERP system but, keep their information in a separate database.  It will be highly probable that service information for products will be split over 2 different databases (and, potentially more in future).  which poses a huge problem for the tracking website.  I know separate databases is an extremely bad way to store the information but I have little to no decision making power over the situation.

    The data in the website has to be real-time so I cannot build a data warehouse to collate the information.  I know I can write UNION queries inside the MVC app and pull the data from multiple databases into to a dataset (which I know datasets are frowned upon in MVC).  I could also write a view that pulls information from multiple databases and presents it to MVC as single table which might make life easier.  I could also write some kind of loop that would automatically create a union query from a single select statement however, the existing select statements are already complex with multiple grouping and in some cases, already contain UNTION statements to combine data from multiple tables.  Anyone have any other ideas?

    We have a website written in C# MVC that currently pulls data from the USA or Canada database depending on the users login.  Now, for the USA locations, the data could be spread across 3 diff

    Friday, October 13, 2017 2:22 PM

All replies

  • User-1838255255 posted

    Hi PhillD,

    According to your description, I know you know how to select data from the multiple database, maybe you are seeking a high efficiency method to achieve this. Here are some high

    efficiency method to achieve this, please check:

    Querying data by joining two tables in two database on different servers:

    https://stackoverflow.com/questions/5145637/querying-data-by-joining-two-tables-in-two-database-on-different-servers 

    SQL Server: Joining Tables From Different Databases On The Same Server:

    http://www.mytecbits.com/microsoft/sql-server/joining-tables-different-databases 

    Best Regards,

    Eric Du

    Monday, October 16, 2017 8:51 AM
  • User1120430333 posted

    We have a website written in C# MVC that currently pulls data from the USA or Canada database depending on the users login.  Now, for the USA locations, the data could be spread across 3 diff

    What difference  does it make if you where using 3 WebAPI(s), each using the Repository  pattern, the Repositories calling on the DAL that uses the Data Access Object pattern for each EF model in the DAL and using the DTO design pattern?

     

    Monday, October 16, 2017 1:34 PM
  • User-596903714 posted

    I don't use EF to access my data.  If ever there was a clear example of where EF was not appropriate for a particular task, I think this would definitely be it.  It I had 3 separate entity models (1 for each database), how would you combine the data to appear as one seamless dataset?  To the best of my knowledge, Entity has no native ability to perform UNION joins or merge data in the way I would need.

    Monday, October 16, 2017 4:00 PM
  • User1120430333 posted

    I don't use EF to access my data.  If ever there was a clear example of where EF was not appropriate for a particular task, I think this would definitely be it.  It I had 3 separate entity models (1 for each database), how would you combine the data to appear as one seamless dataset?  To the best of my knowledge, Entity has no native ability to perform UNION joins or merge data in the way I would need.

    Whether you where using EF or not, it still makes no difference if using the  Repository pattern using a DAL with the DAL using Data Access Object pattern  using ADO.NET SQL Command  Objects inline-TSQL or Stored Procedures , datareader and the DTO Pattern.

    https://www.codeproject.com/Articles/1050468/Data-Transfer-Object-Design-Pattern-in-Csharp

    And because you used the DTO patttern, you can JOIN two or more objects List<T> using Linq.

    http://www.java2s.com/Code/CSharp/LINQ/Jointwoobjectlists.htm

    You can UNION too

    https://msdn.microsoft.com/en-us/library/bb341731(v=vs.110).aspx

    The Repository pattern is used to simply things as the solution evolves. 

    https://msdn.microsoft.com/en-us/library/ff649690.aspx

    SoC is used to simplify things as the solution evolves. 

    https://en.wikipedia.org/wiki/Separation_of_concerns

    http://www.c-sharpcorner.com/UploadFile/56fb14/understanding-separation-of-concern-and-Asp-Net-mvc/

    I feel that I am wasting my time here and not getting through to you on the importance of SoC in solution designing .

    Monday, October 16, 2017 5:47 PM
  • User-596903714 posted

    I feel that I am wasting my time here and not getting through to you on the importance of SoC in solution designing .

    Dude, give me a break!  I am 1 developer building a customer portal for a multi-million dollar organization and was asked to do it rapidly.  My separation of concerns were, having a job or not having a job.  I went from knowing virtually nothing about ASP.NET or C#, to having a customer web portal live in 2 months.  It has some very complex data queries that EF was not suited for (based on other Forum feedback that you were a part of).  I didn't use a DAL, BLL or an API because I just didn't have the time or resources to do it.

    Monday, October 16, 2017 7:05 PM
  • User1120430333 posted

    DA924

    I feel that I am wasting my time here and not getting through to you on the importance of SoC in solution designing .

    Dude, give me a break!  I am 1 developer building a customer portal for a multi-million dollar organization and was asked to do it rapidly.  My separation of concerns were, having a job or not having a job.  I went from knowing virtually nothing about ASP.NET or C#, to having a customer web portal live in 2 months.  It has some very complex data queries that EF was not suited for (based on other Forum feedback that you were a part of).  I didn't use a DAL, BLL or an API because I just didn't have the time or resources to do it.

    You couldn't stand your ground and still seems to me that you are being pushed around with an evolving solution that can't evolve or technically upgrade gracefully.   It's either going to comeback and bite you or the company when it has to be redone possibly.  It's unfortunate that you have been placed in the situation that you are in, which you should have gotten another developer on the project so that you could start to do things right with some help.

    What I have given you is solid architectural design advise to achieve what needs to be done, because I have seen it and done it numerous  times with enterprise level  ASP.NET Web form using MVP,  ASP.NET MVC and Windows forms solutions using MVP with them all using n-tier and SoC, as a contractor.

    What do you think I have not been down the road you have been down with overzealous customer expectations  on some trash they already have and wanted done? :)

    Monday, October 16, 2017 8:09 PM