Dynamically initializing Context and creating connection string for Web API

Question

Scenario: There is a 20 years old windows application with SQL Server as database. There are 42 companies within the Group and each company has its own database. Now we have started building new applications and all critical data like finances etc need to come from and be written back to old system's database.

To make this easier and uniform across all new applications I've started an API project. Issue obviously is to map to specific database based on company from old databases while also use Entity framework for new databases.

My solution is to use mix of Entity Framework and Dapper.

If there are complex queries required or stored procedures are already present, then we use Dapper else for simple APIs for new databases we use Entity Framework.

I have used Application_BeginRequest event of global.asax to initialize database. I'm passing company code in headers to identify which database to use and form a connection string.

I'm also using Mehdime.Entity to manage transaction scopes and DbContexts.

here is the Application_BeginRequest code

protected void Application_BeginRequest(Object source, EventArgs e) { var request = Request; var requestHeaders = request.Headers.Keys; var companyCode = request.Headers.GetValues("CompanyCode"); var builder = new ContainerBuilder(); ApiVariables.Company = companyCode?[0]; var connectionString = ApiVariables.GetConnectionString(ApiVariables.Company); // Get your HttpConfiguration. var config = GlobalConfiguration.Configuration; builder.RegisterGeneric(typeof(ApiRepository<>)).As(typeof(IRepository<>)); builder.RegisterAssemblyTypes(Assembly.Load("HRQ.Service")) .Where(t => t.Name.EndsWith("Service")).AsImplementedInterfaces().InstancePerLifetimeScope(); builder.RegisterType<DbContextScopeFactory>().As<IDbContextScopeFactory>(); builder.RegisterType<AmbientDbContextLocator>().As<IAmbientDbContextLocator>(); builder.RegisterType<ApiDbContext>().As<IApiDbContext>().WithParameter("connectionString", connectionString); // Register your Web API controllers. builder.RegisterApiControllers(Assembly.GetExecutingAssembly()); // OPTIONAL: Register the Autofac filter provider. builder.RegisterWebApiFilterProvider(config); // Set the dependency resolver to be Autofac. var container = builder.Build(); config.DependencyResolver = new AutofacWebApiDependencyResolver(container); Database.SetInitializer<ApiDbContext>(new CreateDatabaseIfNotExists<ApiDbContext>()); } 

For queries to old database, I use:

using (var connection = DapperConnectionFactory.GetOpenConnection()) { //execute queries here} 

To initiate connection for dapper:

public static DbConnection GetOpenConnection() { var connection = new SqlConnection(ApiVariables.GetConnectionString(ApiVariables.Company)); connection.Open(); return connection; } 

Am I in right path? or is this a terrible code?

Answer 1

I am not familiar with Dapper, but my assumption is that the objectives of using it are similar to using any other data access framework. Just from looking at their GitHub page, it looks like a very nice tool.

Unfortunately, maintaining compatibility with old applications is the reality that enterprises have to deal with, so in concept, I don't think this is a terrible idea. I would suggest doing something to ensure you have good connectivity between your API and your databases, though. If your company uses QOS, and the MSSQL traffic is not prioritized, but HTTP is, then using a SOAP service at the data source might be a good idea, and then just forward the request to that service. Web API would also be good, but implementation is a little more complex. That may be okay, though, if you want to avoid the overhead of SOAP envelopes.

From an API perspective, I would not recommend using custom HTTP headers. Part of the goal of Web API is having a standards compliant HTTP endpoint. Although that does not exclude using custom headers, I think that it might be better to try and device a custom route for the legacy integration, in places where you do not need to integrate with the modern systems. An alternative approach, if the intent is to store the data twice, is to dependency inject both your EF context, and your legacy adapter, into the controller. When the legacy adapter can go away, you "just" remove it from your controller. That way, you can integrate it across all of your API, and provide as seamless an experience as possible for your consumers.

Good luck with your implementation. I'm working on resolving several legacy applications myself, and it seems to be tough work whenever I hear anyone mention it.