Search code examples
asp.net-web-apisolid-principles

Best practices WebAPI and multiple databases

I have a problem. I am creating a webAPI with ASP.NET MVC, I've got three different projects with three separate databases.

I have a method to do an Insert in one table. That table exists in three databases.

The client sends me the project in a String.

My question is:

1 - Should I divide the webAPI in three different URLS? 2- I don't want to create a switch or if-elseif in the controller this way:

switch (project) {
case project1:
     objectdatabase1
case project2:
     objectdatabase2
case project3
     objectdatabase3
 }

Because I think it breaks the OPEN CLOSED Solid principle.

3- Also I would like to inject de database object into de controller with Unity doing dependency injection.

Any ideas to do these in the best way possible?

Thank you!

Solution

  • Depending on what you are using to connect to those databases you might be able to just use named connectionStrings. Name them like the projects (whatever string the client sends) or have some convention (like param+"_connString").

    Then have your DB access object open a connection using that particular connectionString and run whatever query you want.

    Using specialized naming convention (like Project1_SomeKeyword_connectionString, Project2_SomeKeyword_connectionString etc.) just for this case will limit the potential security problems, in case someone would maliciously try and guess connectionString name that should not be used for that purpose (for your internal db or whatever).

    If you are using plain SQL access object that will run whatever query you ask it to, it will be fine. If you are using some ORM that depends on a particular table-object mapping, you might need to create that ORM just for this purpose, if the rest of DB differs.

    You were also mentioning that you might want to inject the database object. But wouldn't injection take place before the controller actions are being executed? That would mean that you must know which DB you are using beforehand, and you've said that the project name is provided during action call. You might need to inspect the request during injection and it makes it all kind of complicated.