Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: C# VB.NET ms-sql-server
in a large application where several module need to connect ms sql server.
 
1) open a connection at very beginning and do all the data retrieval and close the data connection only when application terminate.
 
0r
 
2) every data retrieval need to connect database and after retrieval close the connection.
 
I like to know which one is the best approach in large database software development.
 
thanks
Posted 4-May-13 11:57am
Edited 4-May-13 11:58am
v2
Comments
ThePhantomUpvoter at 4-May-13 18:57pm
   
And use "using" blocks on your objects so they are properly disposed.
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

Option #2. Always option #2.
 
It's NEVER a good idea to leave a connection open for the lifetime of an application.
  Permalink  
v2
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 2

Whatever may be the architecture, disposing objects, when it is no longer required will be the best practice.
 
Benefits
-> It helps to improve your Execution Time.
-> As you are disposing the object, so it can give that connection to other application.
 
Note
As you might know the DataBases have limited number of connections.
So, if you will open and dispose after a long time without any need, then that will just increase the number.
After a certain number of connections, DataBase will not connect even if you create connections.
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 4

Quote:
Most "best practices" tell us to open connections as late as possible (right before executing any SQL) and closing them as soon as possible (right after the last bit of data has been extracted.

 
using (SqlConnection conn = new SqlConnection(...))
{
    using(SqlCommand cmd = new SqlCommand(..., conn))
    {
        conn.Open();
        using(DataReader dr = cmd.ExecuteReader())  // or load a DataTable, ExecuteScalar, etc.
        {
             ...
        {
    }
}
 

 
From:
C# Data Connections Best Practice[^]
  Permalink  
Comments
Dave Kreskowiak at 20-Apr-14 10:08am
   
Why are you replying to a question that's a year old?? Also, you're answer is no different than the ones that have been given, a year ago. Don't resurrect only questions like this.
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 3

It is best practice to close the connection when ever data retrieval has completed.
 
=>data base has limited connections it gives the connections to others if u close that.
=>if the connection is in open state and you are try to open it again in your application
it throws an error
=> and if you are not intreasting in closing the connection every time you open,just wrap the
data base code in side using,it will take care of closing the connection automatically
when scope is lost.
=>its a good practice to open the connection as late as possible and close it as early as possible
  Permalink  
v2

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
0 OriginalGriff 5,130
1 DamithSL 4,237
2 Maciej Los 3,700
3 Kornfeld Eliyahu Peter 3,470
4 Sergey Alexandrovich Kryukov 2,846


Advertise | Privacy | Mobile
Web01 | 2.8.141216.1 | Last Updated 20 Apr 2014
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100