.Net Project architecture and DB connection

Question

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?

Answer 1

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)
{
    try
    {
        using (SqlConnection sqlConnection = new SqlConnection(connectionString))
        using (SqlCommand cmd = new SqlCommand("", sqlConnection))
        {
            sqlConnection.Open();

            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);

        throw;
    }
}

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.

Answer 2

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.

Answer 3

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.