Is it possible to change databases in an existing system?

5

I'm starting a project in ASP.NET MVC 5 with Entity Framework for a client, where it chose to use a free database, strong> MySQL . But with the growth of the application, I foresee the best use of a database like SQL Server .

My suggestion is to use SQL Server from the beginning, however, my dear client says "Let's start with MySQL, anything we change later" .

When in college, several teachers said that this was possible, and in projects where we separated the layers and responsibilities it was even less painful to accomplish. So I've never done and I have no idea, in an asp.net mvc project, how should I proceed with this?

I wanted to know what rework would be, thinking at CRUD level, if I use Controller's and View's generated using scaffold , where they directly use my initial context in MySQL, when I change the application database to SQL Server, what would be the risks and rework?

    
asked by anonymous 05.09.2014 / 16:03

2 answers

3

I would like to know what rework would be, thinking at CRUD level, if I use Controller's and View's generated using scaffold, where they directly use my initial context in MySQL when I change the application database to SQL Server , what are the risks and rework?

Responding from the technology point of view:

None. Except you would have to just install and reconfigure the data providers.

The Entity Framework was thought to be agnostic. Agnostic means that it does not implement any technological particularity of a specific database. The syntax and the way you work with it are always the same.

The only thing that can generate rework is if you resolve to use SQL together with the traditional logic of the Entity Framework, essentially if there is any element of the syntax that is particular to a particular database provider.

In MySQL you would configure the data provider in your Web.config more or less like this:

<entityFramework>
  <defaultConnectionFactory type="MySql.Data.Entity.MySqlConnectionFactory, MySql.Data.Entity.EF6" />
  <providers>
    <provider invariantName="MySql.Data.MySqlClient" type="MySql.Data.MySqlClient.MySqlProviderServices, MySql.Data.Entity.EF6" />
  </providers>
</entityFramework>

While in SQL Server, the configuration would look like this:

<entityFramework>
  <defaultConnectionFactory type="System.Data.Entity.Infrastructure.SqlConnectionFactory, EntityFramework" />
  <providers>
    <provider invariantName="System.Data.SqlClient" type="System.Data.Entity.SqlServer.SqlProviderServices, EntityFramework.SqlServer" />
  </providers>
</entityFramework>
    
05.09.2014 / 23:31
3
  

So, I never did and I have no idea, in an asp.net mvc project, how should I proceed with this?

If you have not yet developed a project in Asp.net MVC this one introduces the concept and have some references to help you.

  

... if I use Controller's and View's generated using scaffold, where they directly use my initial context in Mysql ...

Using your context directly you will be tied to technology, because everywhere in the code where you have reference to this technology you would need to change. Depending on your implementation it is a very hard rework, as well as running a risk of code duplication and having more difficulty testing your application.

Exemplo:

Using NHibernate to retrieve data in the Controller , I would have references like NHibernate , NHibernate.Linq , in addition to objects specific to NHibernate technology such as ISession , etc.

using System;
using System.Web.Mvc;
using NHibernate; //Código relacionado a tecnologia de acesso a BD
using NHibernate.Linq; //Código relacionado a tecnologia de acesso a BD
using System.Linq;
using NhibernateMVC.Models;

namespace NhibernateMVC.Controllers
{
    public class ExemploController : Controller
    {     
        public ActionResult Index()
        {
            //Você provavelmente iria duplicar código como esse em outros Controllers
            //gerando possíveis retrabalhos
            using (ISession session = NHibertnateSession.OpenSession())
            {
                var empregados = session.Query<Empregado>().ToList();
                return View(empregados);
            }                
        }
    }
}

An alternative is to use the Repository project pattern , it centralizes your access code to the database (insert, update, delete and data recovery) by adding a separation layer so that by changing data access technology you only change one layer of your application with a very small rework compared to the issue of directly using the context.

  

Given the example, by using specific data access technologies directly in your Controllers , be it Entity Framework, MySql, X, Y, etc ... you would get references and codes that generate rework if you change the technology.

In short, instead of creating an ASP.NET MVC project and developing your application, it would be interesting to create at least one more project in your solution to centralize your database access code by isolating domain objects ( related to the business) of access code details.

    
05.09.2014 / 16:39