CODEWITHSUNDEEP
c#asp.net-mvcentity-frameworkconstructor
Damian
Damian

Reputation: 606

Class Constructor vs Using Statement for Database Connections

I have two scenarios (examples below), both are perfectly legitimate methods of making a database request, however I'm not really sure which is best.

Example One - This is the method we generally use when building new applications.

private readonly IInterfaceName _repositoryInterface;

public ControllerName()
{
    _repositoryInterface = new Repository(Context);
}

public JsonResult MethodName(string someParameter)
{
    var data = _repositoryInterface.ReturnData(someParameter);
    return data;
}

protected override void Dispose(bool disposing)
{
    Context.Dispose();
    base.Dispose(disposing);
}

public IEnumerable<ModelName> ReturnData(filter)
{
    Expression<Func<ModelName, bool>> query = q => q.ParameterName.ToUpper().Contains(filter)

    return Get(filter);
}

Example Two - I've recently started seeing this more frequently

using (SqlConnection connection = new SqlConnection(
        ConfigurationManager.ConnectionStrings["ConnectionName"].ToString()))
{
    var storedProcedureName = GetStoredProcedureName();

    using (SqlCommand command = new SqlCommand(storedProcedureName, connection)) 
    {
        command.CommandType = CommandType.StoredProcedure;
        command.Parameters.Add("@Start", SqlDbType.Int).Value = start;

        using (SqlDataReader reader = command.ExecuteReader())
        {
            // DATA IS READ AND PARSED
        }
    }
}

Both examples use Entity Framework in some form (the first more so than the other), there are Model and Mapping files for every table which could be interrogated. The main thing the second example does over the first (regarding EF) is utilising Migrations as part of the Stored Procedure code generation. In addition, both implement the Repository pattern similar to that which is in the second link below.

Code First - MSDN Contoso University - Tutorial

My understanding of Example One is that the repository and context are instantiated once the Controller is called. When making the call to the repository it returns the data but leaves the context intact until it is disposed of at the end of the method. Example Two on the other hand will call Dispose as soon as the database call is finished with (unless forced into memory, e.g. using .ToList() on an IEnumerable). If my understanding is not correct, please correct me where appropriate.

So my main question is what are the disadvantages and advantages of using one over the other? Example, is there a larger performance overhead of going with Example 2 compared to Example 1.

FYI: I've tried to search for an answer to the below but have been unsuccessful, so if you are of a similar question please feel free to point me in that direction.

Upvotes: 0

Views: 1061

Answers (2)

Luthervd
Luthervd

Reputation: 1406

Your second example isn't using entity framework. It seems you may have two different approaches to data access here although it is hard to tell from the repository snippet as it quite rightly hides the data access implementation. The second example is correctly using a "using" statement as you should on any object that implements IDisposable. It means you don't have to worry about calling dispose. This is using pure ADO.net which is what Entity Framework uses under the hood.

If the first example is using Entity framework you most likely have lazy loading in play in which case you need the DbContext to remain until the query has been executed. Entity Framework is an ORM tool. It too uses ADO.net under the hood to connect to the database but it also offers you alot more on top. A good book on both subjects should help you.

I found learning ADO.net first helps alot in understanding how Entity Framework retrieves info from the Database.

the using statement is good practice where ever you find an object that implements IDisposable. You can read more about that here : IDisposable the right way

In response to the change to the question - the answer still on the whole remains the same. In terms of performance - how fast are the queries returned? Does the performance of one work better than the other? Only your current system and set up can tell you that. Both approaches seem to be doing things the correct way.

I haven't worked with Migrations so not sure why you are getting ADO.net type queries integrating with your EF models but wouldn't be surprised by this functionality. Entity Framework as I have experienced it creates the queries for you and then executes them using the ADO.net objects from your second example. The key point is that you want to have the "using" block for SqlConnection and SqlCommand objects (although I don't think you need to nest them. everything inside the outer "using block will be disposed).

There is nothing stopping you putting a "using" block in your repository around the context but when it comes to lazily load the related Entities you will get an error as the context will have been disposed. If you need to make this change you can include the relevant elements in your query and do away with the lazy loading approach. There are performance gains in certain situations for doing this but again you need to balance this in terms to how your system is performing.

Upvotes: 1

Enigmativity
Enigmativity

Reputation: 117134

You seem to be making a comparison like this:

Is it better to build a house or to install plumbing in the bathroom?

You can have both. You could have a repository (house) that uses data connections (plumbing) so it's not an "OR" situation.

There is no reason why the call to ReturnData doesn't use a SqlCommand under the hood.

Now, the real important difference that is worth considering is whether or not the repository holds a resource (memory, connection, pipe, file, etc) open for its lifetime, or just per data call.

The advantage of using a using is that resources are only opened for the duration of the call. This helps immensely with scaling of the app.

On the other hand there's an overhead to opening connections, so it's better - particularly for single threaded apps - to open a connection, do several tasks, and then close it.

So it really boils down to what type of app you're writing as to which approach you use.

Upvotes: 2

Related Questions