?
Solved

Close SQL connection

Posted on 2010-01-12
14
Medium Priority
?
235 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

764 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