Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Close SQL connection

Posted on 2010-01-12
14
Medium Priority
?
242 Views
Last Modified: 2012-08-14
Hi there

I have this code, my problem is when I have lots of users accessing the site, I get

the connection was not closed. The connection state is opened --> The connection was not closed. The connections current state is open.

How to i change the code so that it opens and closes the connection as quickly as possible?

Thanks
internal static void uploadFile(Attachment aAttachment)
    {

      using (SqlConnection objConnection = new SqlConnection(_connectionstring))
      {
        using (SqlCommand objCommand = CreateSqlCommand("Attachment", objConnection))
        {
          objCommand.Parameters.Add("@FileName", SqlDbType.VarChar, 300).Value = aAttachment.FileName;
          objConnection.Open();
          objCommand.ExecuteNonQuery();
        }
      }
    }

Open in new window

0
Comment
Question by:mousemat24
  • 3
  • 3
  • 2
  • +4
14 Comments
 
LVL 10

Expert Comment

by:krunal_shah
ID: 26292416
hi,

modify like,

objConnection.Open();
objCommand.ExecuteNonQuery();
objConnection.Close();
0
 
LVL 4

Expert Comment

by:fsouzabrasil
ID: 26292420
Try this... Close connection after execute.
internal static void uploadFile(Attachment aAttachment) 
    { 
 
      using (SqlConnection objConnection = new SqlConnection(_connectionstring)) 
      { 
        using (SqlCommand objCommand = CreateSqlCommand("Attachment", objConnection)) 
        { 
          objCommand.Parameters.Add("@FileName", SqlDbType.VarChar, 300).Value = aAttachment.FileName; 
          objConnection.Open(); 
          objCommand.ExecuteNonQuery(); 
          objConnection.Close(); 
        } 
      } 
    }

Open in new window

0
 

Author Comment

by:mousemat24
ID: 26292441
is it same for code like this

internal static DataSet getDocOwner(int admid)
    {

      using (SqlConnection objConnection = new SqlConnection(_connectionstring))
      {
        using (SqlCommand objCommand = CreateSqlCommand("Categories", objConnection))
        {
          objCommand.Parameters.Add("@AdminID", SqlDbType.Int).Value = admid;
          using (DataSet objDS = new DataSet())
          using (SqlDataAdapter objDataAdapter = new SqlDataAdapter(objCommand))
          {
            objDataAdapter.Fill(objDS);
            return objDS;
          }
        }
      }
    }

do I place

objConnection.Close();  
after return objDS;


so

return objDS;
objConnection.Close();  
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 10

Expert Comment

by:krunal_shah
ID: 26292448
Hi,

if you are using it like this,
  using (SqlConnection objConnection = new SqlConnection(_connectionstring))
      {
      }

you do not need to close it.

Thanks,
Krunal
0
 
LVL 75

Expert Comment

by:käµfm³d 👽
ID: 26292449
A DataAdapter should take care of opening and closing the DB connection for you (when you call Fill()). Are you sure you are not manually opening a connection somewhere else?
0
 

Author Comment

by:mousemat24
ID: 26292491
i have this code too, dont know if this helps?

private static string _connectionstring = "connString";

static DALClass()
    {
      ConnectionStringSettings conString = ConfigurationManager.ConnectionStrings[_connectionstring];
      if (conString == null) throw new ConfigurationErrorsException(string.Format("You must have a {0} connection string in the config file", _connectionstring));
      _connectionstring = conString.ConnectionString;
    }
0
 
LVL 7

Expert Comment

by:waltersnowslinarnold
ID: 26292507
It's always advisable to close a connection inside finally as below..,
internal static void uploadFile(Attachment aAttachment)
    {

      using (SqlConnection objConnection = new SqlConnection(_connectionstring))
      {
        using (SqlCommand objCommand = CreateSqlCommand("Attachment", objConnection))
        {
          objCommand.Parameters.Add("@FileName", SqlDbType.VarChar, 300).Value = aAttachment.FileName;
          objConnection.Open();
          objCommand.ExecuteNonQuery();          
        }
      }
    }

finally
{
  objConnection.Close();
}

Open in new window

0
 
LVL 7

Accepted Solution

by:
waltersnowslinarnold earned 800 total points
ID: 26292520
Sorry it should be like below, not as above..

internal static void uploadFile(Attachment aAttachment)
    {

      using (SqlConnection objConnection = new SqlConnection(_connectionstring))
      {
        using (SqlCommand objCommand = CreateSqlCommand("Attachment", objConnection))
        {
          objCommand.Parameters.Add("@FileName", SqlDbType.VarChar, 300).Value = aAttachment.FileName;
          objConnection.Open();
          objCommand.ExecuteNonQuery();          
        }
      }
      finally
     {
      objConnection.Close();
     }
}

Open in new window

0
 
LVL 13

Assisted Solution

by:zadeveloper
zadeveloper earned 800 total points
ID: 26292690
try:
You will notice I change it from a static class - depending on the senario this could be part of the problem.

internal void uploadFile(Attachment aAttachment)
        {
            SqlConnection.ClearPool(_connectionstring);
            using (SqlConnection objConnection = new SqlConnection(_connectionstring))
            {
                objConnection.Open();
                using (SqlCommand objCommand = CreateSqlCommand("Attachment", objConnection))
                {
                    objCommand.Parameters.AddWithValue("@FileName", aAttachment.FileName);
                    objCommand.ExecuteNonQuery();
                }
                objConnection.Close();
            }
        }

Open in new window

0
 
LVL 14

Expert Comment

by:amar31282
ID: 26292733
hi i would prefer this code

internal static void uploadFile(Attachment aAttachment)
    {
SqlConnection objConnection ;
     try
      {
    objConnection = new SqlConnection(_connectionstring)
         using (SqlCommand objCommand = CreateSqlCommand("Attachment", objConnection))
        {
          objCommand.Parameters.Add("@FileName", SqlDbType.VarChar, 300).Value = aAttachment.FileName;
          objConnection.Open();
          objCommand.ExecuteNonQuery();
        }
      }
        catch(Exception ex)
        {
        }
        finally
        {
        objConnection.Close();
          }
}

Open in new window

0
 
LVL 75

Assisted Solution

by:käµfm³d 👽
käµfm³d   👽 earned 400 total points
ID: 26292812
Actually, according to the documentation, putting a connection in a "using" statement implicitly closes the connection:

  "To ensure that connections are always closed, open the connection inside of a using block, as shown in the following code fragment. Doing so ensures that the connection is automatically closed when the code exits the block. "

http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlconnection.aspx
0
 

Author Comment

by:mousemat24
ID: 26297349
Yes zadeveloper, I looked at my code, and the problem was because of methods and variables set as static.

And kaufmed, you too were right, I shouldnt have to close it, because the using block should take care of it.

Thanks to all the people who helped me out

Take care
0
 
LVL 13

Expert Comment

by:zadeveloper
ID: 26297693
no problems ... good luck.
0
 
LVL 75

Expert Comment

by:käµfm³d 👽
ID: 26303624
NP. Glad to help :)
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
High user turnover can cause old/redundant user data to consume valuable space. UserResourceCleanup was developed to address this by automatically deleting user folders when the user account is deleted.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

569 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question