Please Review my Code that give Connection Leaking

Question

4.00/5 (1 vote)

See more:

Hello To all ,

i make a Website but some times when the multiple user access something from the website at sametime that gives Connection leaking or get Error.

i use like :

protected void btn_add_cat_Click(object sender, EventArgs e)
    {
        c.setcon();
        string found = "select category from ctgry where category='" + txt_cat_name.Text + "'";
        SqlDataAdapter ad1 = new SqlDataAdapter(found, c.getcon());
        DataSet ds1 = new DataSet();
        ad1.Fill(ds1);
        if (ds1.Tables[0].Rows.Count == 0)
        {
            string s1 = "select max(cat_id) from ctgry";
            SqlDataAdapter ad = new SqlDataAdapter(s1, c.getcon());
            DataSet ds = new DataSet();
            ad.Fill(ds);

            if (ds.Tables[0].Rows[0][0] == DBNull.Value)
            {
                cat_no = "1";
            }
            else
            {
                cat_no = (System.Convert.ToInt32(ds.Tables[0].Rows[0][0]) + 1).ToString();
            }

            string s = "insert into ctgry values('" + cat_no + "','" + txt_cat_name.Text + "')";
            cmd = new SqlCommand(s, c.getcon());
            cmd.ExecuteNonQuery();
}

so, by this query inside query Leak the connection or not ?
c.setcon() is My Appcode function in that i set the connection. and i use c.getcon() in Main as well as subquery , so i m not sure the error main problem is that or not ?

Please Tell me this query inside query leak the COnnection or not ?

Posted 17-Jun-12 22:34pm

Mits Machhi

Updated 17-Jun-12 22:38pm

Prasad_Kulkarni

v3

Add a Solution

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

db7uk · Answer 1 · 2012-06-17T22:44:00

For each connection you need to instantiate an new sql connection and not re-use a connection unless you are using connection pooling. What is the getCon and setCon functions?

Personally you should re-write your code above to:

C#

protected void btn_add_cat_Click(object sender, EventArgs e)
    {
        sqlConnection cn = New sqlconnection("connection string");
        string found = "select category from ctgry where category='" + txt_cat_name.Text + "'";
        SqlDataAdapter ad1 = new SqlDataAdapter(found, cn );
        DataSet ds1 = new DataSet();
        ad1.Fill(ds1);
        if (ds1.Tables[0].Rows.Count == 0)
        {
            string s1 = "select max(cat_id) from ctgry";
            SqlDataAdapter ad = new SqlDataAdapter(s1, cn );
            DataSet ds = new DataSet();
            ad.Fill(ds);
 
            if (ds.Tables[0].Rows[0][0] == DBNull.Value)
            {
                cat_no = "1";
            }
            else
            {
                cat_no = (System.Convert.ToInt32(ds.Tables[0].Rows[0][0]) + 1).ToString();
            }
 
            string s = "insert into ctgry values('" + cat_no + "','" + txt_cat_name.Text + "')";
            cmd = new SqlCommand(s, cn );
            cmd.ExecuteNonQuery();
}

My other suggestion would be to move the data access into a seperate class and event class library. You should not really have this sort of thing in the code behind IMHO.

UPDATE

use this to get your connection if you want to run your code the way you have written it.

C#

public static SqlConnection GetConnection() 
        {
            try
            {
                con = new SqlConnection();
                con.ConnectionString = @"connection String";
                con.Open();
                return con;
            }
            catch (Exception ex)
            {
                //consider logging.
                //consider throwing exception. Where would a failed connection be handled?
            }          
        }

Also get rid of your set connection. Not needed.

Also if data access is an issue for you I would recommend the DataAccess blocks from Enterprise library 5. http://entlib.codeplex.com/releases[^] makes life a bit cleaner!!

Prosan · Answer 2 · 2012-06-17T22:53:00

use your connection in web.config file not in appcode. and what have you written in setcon function. and as per solution 1 each time instantiate an new sql connection.

XML

<configuration>
  <connectionstrings>
    <add name="DataB" connectionstring="Data Source=.\SQLEXPRESS;<br mode=" hold=" />      AttachDbFilename=|DataDirectory|MyDatabase.mdf;Integrated Security=True;<br mode=" />
  </connectionstrings>
</configuration>

**AmitGajjar** · Answer 3 · 2012-06-17T23:23:00

Hi,

To prevent open connection, destroy your connection when it is not in use.

you can use either using or finally block to dispose the connection. below code may be useful for you, you can also use SQLHelper[^] class.

C#

DataSet resultSet = new DataSet();
using (SqlConnection sqlConnection = new SqlConnection(ConnectionString))
{
using (SqlCommand sqlCommand = new SqlCommand(commandText, sqlConnection))
{
if (parameters != default(IList<sqlparameter>) && parameters.Count > 0)
                    {
                        parameters.ToList().ForEach(p =>
                        {
                            sqlCommand.Parameters.Add(p);
                        });
                    }

                    SqlDataAdapter sqlDataAdapter = new SqlDataAdapter();
                    sqlDataAdapter.SelectCommand = sqlCommand;
                    sqlDataAdapter.Fill(resultSet);
                    sqlCommand.Parameters.Clear();
}
}
return resultSet;

above function would be best to return your dataset.

Thanks
-Amit Gajjar

Please Review my Code that give Connection Leaking

3 solutions

Solution 1

Solution 2

Solution 3

Add your solution here

Preview 0