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.

Because opening and closing connection are expensive operations. So when you have multiple data operations to do in single go, it is good to open connection once and perform all the operations using that opened connection and close as soon as you are done. The same thing happens when you do `UsersPhonesDBContext.Database.SqlQuery` but it happens behind the scene. 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. — Chetan, Jan 17 '19 at 13:15
@ChetanRanpariya: thanks but if UsersPhonesDBContext.Database.SqlQuery has all behind the scenes then why to manually open and close for the former? It should also have/may have that way of handling? — azure boy, Jan 18 '19 at 05:12
For the former case you are doing multiple db operations in one go. So to avoid multiple time connection open and close the connection is opened once and after all the operations are done the connection is closed. — Chetan, Jan 18 '19 at 05:31
@ChetanRanpariya: put that in answers box and I will accept as an answer. — azure boy, Jan 18 '19 at 05:59
The goal of most libraries/frameworks that try to abstract away from direct database operations is to make the most *common* usage simple but often to allow more complex usage *possible*. `SqlQuery` is the "shortcut" method that does more work behind the scenes for the *common* case of retrieving a single result set. It's not beyond the wit of man to write a variant of it that can cope with multiple result sets with different types but multiple result sets is *relatively* uncommon. — Damien_The_Unbeliever, Jan 18 '19 at 07:06
@Damien_The_Unbeliever: Microsoft should have done that but what can we do, but thanks for the answer it clears things. — azure boy, Jan 18 '19 at 08:48

score 2 · Accepted Answer · answered Jan 18 '19 at 06:23

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.

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

1 Answers1