1

We are currently using a off the shelf ERP system. It lacks in several areas, one of those being quoting. So, I have been tasked with creating a quoting application that will need to use (in read only mode) data from the ERP system. The ERP database is stored on our local server (Windows Server 2012) in SQL Server 2014. Eventually the quoting application will reside on the same server (both the database and web app).

I plan on building the application in ASP.NET MVC 5 via Visual Studio 2013. I will be using code first to develop the quoting application. I will be using database first to pull over the classes for the existing database.

When creating queries, I will need to join the ERP tables with the new quoting tables. So, I am guessing I will need one data context.

My real struggles is how do I deploy this application. I basically don't need to deploy the ERP database, because it is already there. I do need to deploy the quoting database and the application.

I would like to get my plan of attack figured out before getting into it and realizing I need to do things completely different. I am somewhat new to Visual Studio and MVC, and I have never deployed a project. Any ideas on how to accomplish the above, including the data context issue?

3

I would set up a new DB for my Quoting App, then create SQL views to bring the ERP Data into that DB and act as local Object.

With SQL server you can create Cross DB Real-time view.

Select * from ERP.dbo.Customers

The nice thing about this is that if you upgrade the ERP system, and the Data structure changes, all you have do is update you views to transform the new model back into the original one and you Quoting APP continues to work.

Even if you retire the entire ERP and with some cloud system, you can replace the views with actual table and ETL data into them.

Now your APP only uses a single db, and your context issue goes away.

  • Since both the ERP database and Quoting DB are on the same server, views will work, correct? I can still use database first for pulling the Views into the application and Code First for creating the quoting tables, correct? So, when I deploy the application/DB, will the views deploy as views? – Linger Aug 5 '16 at 13:03
  • Another question, If I were to create the view: CREATE VIEW [CurrProdList] AS SELECT * FROM Products. Then the ERP software vendor updates the Products table to add a field or etc. I wouldn't need to update the view since I specified to include all fields, correct? But I would need to update the application to change the classes that were pulled over for the database, right? – Linger Aug 5 '16 at 13:14
  • I don't know how you are doing your deployments, i generally deploy by generating DDL scripts and running them. Don't use "Select * " - (that was an example to show the DBName.Schema.Table Structure). You don't want some other party modifying your data structure. Yes adding new automatically fields may sound nice, but its no so nice when they drop one you are using. Hard code the field list into the view. – Morons Aug 5 '16 at 13:35
  • When you say,"I would set up a new DB for my Quoting App, then create SQL views to bring the ERP Data into that DB and act as local Object." Are you saying that you would create that database on the production server, the same SQL Server that the ERP system resides? In doing so, I would then have to create all of the quoting tables as well and use database first to bring them into the application, right? – Linger Aug 5 '16 at 14:45
  • Generally you create you DB objects before class... Not sure how else you would do this – Morons Aug 7 '16 at 14:06
0

Quoting application seems a logical missing piece from your ERP. Also, for sure for master data your quoting application will heavily rely on ERP database.

Hence, I will suggest that you can create a logical schema for quoting application in same database instance. So, if ERP is using default dbo schema, quoting application data objects may recide in quote schema. This will give you nice logical separation still will not have adverse effect on any of the queries on coding side, you don't need to deal with two different connection strings or dealing with two different data instances.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.