+ Reply to Thread
Results 1 to 2 of 2

Thread: Connection error to SQL

  1. #1
    Junior Member
    Join Date
    Jan 2012
    Posts
    31

    Connection error to SQL

    I am creating an application that will display a list of records in a GridView and by checking multiple checkboxes you can mass delete the records.
    Here is the code :
    Code:
    protected void btnDelete_Click(object sender, EventArgs e)
    {
        int i = 0;
        try
        {
            foreach (GridViewRow row in GridView1.Rows)
            {
                CheckBox cb = (CheckBox)row.FindControl("ID");
                if (cb != null && cb.Checked)
                {
                    int profileID = Convert.ToInt32(GridView1.DataKeys[row.RowIndex].Value);
                    Profile profile = new Profile(profileID);
                    profile.Delete();
                    i++;
                }
            }
            if (i > 0)
            {
                //report success to UI
            }
        }
        catch (Exception ex)
        {
            Message.Show(ex.ToString());
        }
    }
    In the profile constructor, it hydrates the object by opening up a connection, opening a datareader and then setting the properties of the object. I am meticulous about using() blocks in my code so every db connection looks about like this:
    Code:
    using (SQLHelper db = new SQLHelper())
    {
        db.AddParameterToSQLCommand("@ProfileID", SqlDbType.Int);
        db.SetSQLCommandParameterValue("@ProfileID", id);
    
        using (SqlDataReader dr = db.GetReaderByCmd("up_GetProfile"))
        {
            if (dr.Read())
            {
                _profileID = id;
                if (!dr.IsDBNull(0))
                    ProfileName = dr.GetString(0);
                //... and so on
                return true;
            }
            else
            {
                return false;
            }
        }
    }
    A datareader implements iDisposible, as does my helper class, and the destructor looks like this:
    Code:
    public void Dispose()
    {
        try
        {
            //Clean Up Connection Object
            if (mobj_SqlConnection != null)
            {
                if (mobj_SqlConnection.State != ConnectionState.Closed)
                {
                    mobj_SqlConnection.Close();
                }
                mobj_SqlConnection.Dispose();
            }
    
            //Clean Up Command Object
            if (mobj_SqlCommand != null)
            {
                mobj_SqlCommand.Dispose();
            }
        }
    
        catch (Exception ex)
        {
            throw new Exception("Error disposing data class." + Environment.NewLine + ex.Message);
        }
    }
    And when i run , i get that error :
    Code:
    Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool. This may have occurred because all pooled connections were in use and max pool size was reached
    I am absolutely doing something wrong , but what is it ?!!!!

  2. #2
    It seems that you are trying to close your Connection objects before the Command objects, and since the Command objects reference a connection, it might be keeping the connection alive.

    Try switching them around:
    Code:
    //Clean Up Command Object
    if (mobj_SqlCommand != null)
    {
      mobj_SqlCommand.Dispose();
    }
    
    if (mobj_SqlConnection != null)
    {
      if (mobj_SqlConnection.State != ConnectionState.Closed)
      {
        mobj_SqlConnection.Close();
      }
      mobj_SqlConnection.Dispose();
    }

+ Reply to Thread

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts