I'm looking at a new project to be developed in .Net, and I'd like to do it the right way.

I'd like to create a solution with 3 parts : a front- and a backoffice, both using a the third part as model.

In my mind, all DB connection related infos should be stored in front- and backoffice, but then, how should I handle DB requests in my model library? I can't resolve myself to handle a SqlConnection parameter with every function requiring DB access.

So, is my whole solution approach to be corrected? What's the best way to manage DB connections for my model classes?

| improve this question | | | | |
  • This question needs both clarification, and comment on why existing patterns in this space wouldn't be applicable. What is the third part? You only mention a front and "backoffice", although it's likely that you meant "back end". Have you researched any ORMs to handle database interaction? What's the overall architecture? MVC? MVVM? – Dan1701 Feb 9 '16 at 19:34
  • The thing is I'm looking for directions on existing patterns that could apply. I'm not following any MVC or MVVM architecture, though I'm trying to get there, eventually. So at the moment, there is the general idea : 3 projects, one for the public to access, one for the admin to manage the website, and the last part would be a library regrouping all my entity classes and their SCRUM methods. – Wingi Feb 10 '16 at 13:12

The way I normally structure applications is to have all the database access in one DLL (module for other languages). All the data access methods take a connection string as a parameter (in the case of EF its a bit different but still gets passed in based on the connection string in the app.config or web.config of the primary application).

This way the DAL (Data Access Layer) knows about the structure of the database but does not know any connection details. It means you can swap out the connection string at run time to connect to DEV, UAT, or PROD. The basic structure of a DAL method would look like this:

Public SomeType GetData(string connectionString)
        using (SqlConnection sqlConnection = new SqlConnection(connectionString))
        using (SqlCommand cmd = new SqlCommand("", sqlConnection))

            cmd.CommantText = "SomeSqlHere";

            SomeType returnVal = //Code to get your data and map to your return type

            return returnVal;
    catch (Exception ex)
        Log.Error("Caught error.", ex);


The internals would be a bit different for EF but overall using Dependency Injection saves having to hard code connection strings in various places.

As to how to inject the connection string I normally have a Configuration class that reads and sometimes caches the config values. This configuration has taken many forms and evolved depending on the needs of the specific application I am working on. The call to a data method would look like GetDate(Config.GetConnectionString()). Then only one place in the application needs to know what the connection string is and every where else it gets passed.

| improve this answer | | | | |

You need Dependency Injection - having the IDbConnection injected to your classes. Here are some quick benefits come to my mind:

  1. You only specify the connection parameters at one place - when the IDbConnection gets registered.
  2. You have the option to replace IDbConnection with SqlConnection, SqlCeConnection and etc.
  3. You have better control over transaction down the road - since there is only one connection. If model methods manages connection by themselves, you would need distributed transaction coordination.

Now, using a DI framework may seem complex and over killing, especially this is the first time you start using it but the transaction-control benefit alone should be enough to convince yourself.

| improve this answer | | | | |

Usually the database connection information is in the .config file for the project, and your database accessing classes will read it into a local variable. Use the .NET System.Configuration settings to read the information from the .config file using nice, Microsoft-built libraries.

Depending on how centralized and how you access your database classes, this could be in the constructor of the data access class.

| improve this answer | | | | |

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.