ASP.NET MVC Query Portal 4

Question

Work in a service provider company. In the company we have hundreds of databases Oracle and SQL-Server of all customers. These banks contain information on the existing assets in that company as well as other commercial and financial information. As the work is being executed the employees through the internal system feeds the various tables, such as: project stages, execution histories, issuance of tax notes, slips, schedules, etc. Our goal is to create an area on the internet where the client can log in and consult the information available to him.

I imagine a table with user and password where the system validates and verifies which bases of which servers it has access to. If he has access to a single base the system already connects to that base and he starts to consult his information regarding the execution of his work. If there is more than one base the system requests which base it wants to access and follows equally.

As I recently used ASP.NET MVC 4 to build an application with RAZOR and found quite productive I ask you, can I develop this new project using ASP.NET MVC 4? It would be nice because the client could access this information, including, the cell phone.

I can’t imagine what the model would look like. Because, in theory, I would only have a table as an input port and then the system connects to some other base and I go to make select there. I can create the model based on a database and switch the connection from there to here at will?

Or you should replicate the columns that will be consulted in other tables in the same database as the login table and through Trigger in the original databases feeds them?

Do you know any link that would give me a good sense of how to do that?

If you’re confused, I’m available for further information.

Grateful.

Answer 1

As I recently used ASP.NET MVC 4 to build an application with RAZOR and found quite productive I ask you I can develop this new project using ASP.NET MVC 4?

In short, yes.

I can’t imagine what the model would look like. Because, in theory, I would only have a table as an input port and then the system connects to some other base and I go to make select there. I can create the model based on a database and switch the connection from there to here at will?

First let’s demystify this "use the connection elsewhere". In an ASP.NET MVC system, you can open as many connections as you want.

If you are going to use ASP.NET MVC4 (which is not very recommended because MVC4 is already an old standard), you can create a project with Entity Framework and define in the context created by the application your users and their respective permission levels. This is done using Membership. I can link in this answer some articles that may be useful for this implementation, but I warn you that this may take some work.

In ASP.NET MVC5, creating the project with the Entity Framework and Individual User Accounts, you will have at your disposal a practically ready mechanism for authentication of your users, through ASP.NET Identity. It will take some tailoring, but nothing too complicated. I can also put some articles that may be of interest to you.

Now, the most important thing: your legacy bases. You can map them using the Entity Framework or use other open connection mechanisms with your base, such as ADO.NET + Generic Repository or Dapper. For each table of its legacy bases, the correct is to create a class (which in this context we call Model) with properties, each property being a column of its table. Once this is done, each selection of data made in the table must fill one class object per found row. Just not to stretch the question too far, I can put examples of how to do this later, or in other questions.

I believe that your databases in your clients should follow a standard of tables, and therefore this mapping is feasible. Already to decide on which base to connect, you can create a scheme between users and companies, and for each company another scheme of which databases are used by each company. So you can ride Connection strings dynamically and initialize your connections or data contexts according to the request made.

This concept is called multitenure.

Or you should replicate the columns that will be queries in other tables in the same database as the login table and through Trigger in the original databases feeds them?

From experience, this is not good, especially if the technologies are from different banks. Better build an application that reads all the bases.

Do you know any link that would give me a good sense of how to do that?

This Q&A site is a good knowledge base. I will direct you to the topics I commented on above.

• ASP.NET Membership, questions from the website;
• ASP.NET Identity, questions from the website;
• Generic Repository;
• Entity Framework, questions from the website;
• Dapper.