SQL Connections

Question

I'm doing a DataBase intensive application, and I came up with some doubts as I am new to ASP.NET MVC4 C#:

1) Is it better to Open a connection and keep it open for all the requests? Or to make Open() and Close() for each request? (If the first option is the best, when/where do I close the connection??)

2) I come from PHP and I am used to Open a connection on the FrontController (just at the begining) and close it at the end, so every request opens only one connection, uses it for everything required, and drops it just before serving the HTTP response. Is that the way to go in ASP.NET MVC 4?

3) Any in-code performance tip will be appreciated!! :)

have you researched connection pooling in C# .net..? there is a difference on how this works in .NET vs a language such as `Delphi` for example — MethodMan, Sep 03 '13 at 09:52

score 1 · Accepted Answer · edited May 23 '17 at 12:29

You could have a look at this thread, which should help you with SQL connection and the closing / disposing of the connection.

SQL Connection

Code snippet of using the SQL connection within a using statement;

public void InsertMember(Member member)
{
    string INSERT = "INSERT INTO Members (Name, Surname, EntryDate) VALUES (@Name, @Surname, @EntryDate)";

    using (sqlConnection = new SqlConnection(sqlConnectionString_WORK))
    {
        sqlConnection.Open();

        using (SqlCommand sqlCommand = new SqlCommand(INSERT, sqlConnection))
        {
            sqlCommand.Parameters.Add("@Name", SqlDbType.VarChar).Value = member.Name;
            sqlCommand.Parameters.Add("@Surname", SqlDbType.VarChar).Value = member.Surname;
            sqlCommand.Parameters.Add("@EntryDate", SqlDbType.Date).Value = member.EntryDate;

            sqlCommand.ExecuteNonQuery();
        }
    }
}

This should help with not only an example but also an explanation on the concept.

Hope this helps.

MSDN further reading on the SQL connection

score 1 · Answer 2 · answered Sep 03 '13 at 09:57

1

The way to go in MVC is to use an ORM, such as EntityFramework or nHibernate.

The Object Relational Mapper will take care of connection handling for you, your main objective is to get the information via a DBContext or some sort of repository object. Using ADO.NET and SqlConnection objects are not really pure MVC, although they can still be used in the application.

answered Sep 03 '13 at 09:57

Darren

68,902
24
138
144

Well the problem here is about the performance, as there are queries that need to perform searches over 10.000+ rows with joins (it is an application that provices daily pricings for products based on N restriction rules). Think that won't be easy/possible to handle with EntityFramework. Am I right? – Vicenç Gascó Sep 03 '13 at 10:04
@VicençGascó - it is possible using an ORM. If you were looking for RAW SQL performance you could use stored procedures to get the benefit from the execution plan and tools like Entity Framework profiler to se what SQL is generated behind the scenes. nHibernate also has it's own profiling tool to help perfomrnace. I.e. you can load all your data in one hit, rather than requesting each time etc. – Darren Sep 03 '13 at 10:40
Sure we are using Stored Procedures ;-) I'll take a deeper look into EF. – Vicenç Gascó Sep 03 '13 at 12:00

score 1 · Answer 3 · answered Sep 03 '13 at 10:29

1) Better one is:

make Open() and Close() for each request

when using connection pool in ADO.net Close() will return the database connection to connection pool(not disconnect from database server), then it can be used in next request.

2) You can do it like your PHP code, my experience is: when database connection was opened, close/release it as soon as possible(after your query has done in database), it help to improve response time of request sometimes.

3) Not only database connection, those scarce resources(File handles, tcp connections etc.) should be closed/released as soon as possible.

Hope this helps you.

score 0 · Answer 4 · answered Sep 03 '13 at 09:57

IMHO, the way to go in ASP.Net 4 is to use Entity Framework or some other ORM like NHibernate :D

If you decide to use your own Database Access Layer, it mostly comes down to the specifics of the application. For example, if you have an information-heavy website, like an eshop, then loading a lot of information on one connection will speed it up, but if you expect a lot of users, there might not be enough connections available for this method of work.

SQL Connections

4 Answers4