Why do we need to create, open and close db connections to retrieve multiple result sets but not if retrieving one set?

Question

In order to retrieve different result sets being returned by the stored procedure we have to use this:

                    UsersPhonesDBContext.Database.Initialize(force: false);
                    DbConnection con = UsersPhonesDBContext.Database.Connection;    

                    var cmd = con.CreateCommand();
                    cmd.CommandText = "[dbo].[SelectPhones]";

                    con.Open();

                    var reader = cmd.ExecuteReader();

                    Phones phones = ((IObjectContextAdapter)UsersPhonesDBContext)
                        .ObjectContext
                        .Translate<Phones>(reader, "Phones", MergeOption.AppendOnly).First();

                    reader.NextResult();

                    Users users = ((IObjectContextAdapter)UsersPhonesDBContext)
                       .ObjectContext
                       .Translate<Users>(reader, "Users", MergeOption.AppendOnly).First();

But if the procedure is returning a single result set then we can simply do that with

var phones1 = UsersPhonesDBContext.Database.SqlQuery<Phones>("dbo.InsertPhones @model", sqlParam);

without creating, opening and closing DB connection etc.

So my question is that why do we have to create & open connections if multiple sets but not for the single since both are accessed from the database.

Answer 1

Opening and close database connections are expensive operations in terms of time and resources. At the same time if the connection is left open for long time it might cause a memory leak or security risk.

So the recommended approach is to open database connection only when needed and close as soon as the purpose of opening it is served.

In the example you shared, there are multiple database operations happening in one go. So instead of opening connection individually for every operation, only one connection is opened and it is close after the operation are done. Here connection is opened and close manually.

The new approaches of DataAccess Layers such as EntityFramework etc does this job internally.

UsersPhonesDBContext.Database.SqlQuery<Phones>("dbo.InsertPhones @model", sqlParam);

In this line of code, the connection to database is opened and close internally without us worrying about it.

So the main motive is to minimize the connection open and close operations and close the connection as soon as the purpose of opening it is over.