Not performance when open a new connection.

phucphlq posted on Friday, June 30, 2006

I see ProjectTracker, everytime DataPortal_XX is called, a connection is opened. I think program is slowly.

private void DataPortal_Fetch(Criteria criteria)
    {
      using (SqlConnection cn = new SqlConnection(Database.PTrackerConnection))
      {
        cn.Open();
        using (SqlCommand cm = cn.CreateCommand())
        {
          cm.CommandType = CommandType.StoredProcedure;
          cm.CommandText = "getResources";

          using (SafeDataReader dr =
            new SafeDataReader(cm.ExecuteReader()))
          {
            IsReadOnly = false;
            while (dr.Read())
            {
              ResourceInfo info = new ResourceInfo(dr);
              this.Add(info);
            }
            IsReadOnly = false;
          }
        }
      }
    }

cmellon replied on Friday, June 30, 2006

Hi,

You will notice if you test this out that you only get the initial connection hit the first time you connect (and even that is minimal), any subsequent calls after that are almost instant due to connection pooling.

I haven't had any perfomance issues with the connection. 

Regards

Craig

Massong replied on Friday, June 30, 2006

Hi,

CSLA is a form of disconnected data: the connection to the database should only be held open as short as possible while retrieving or storing data but not while the user works with the data or drinks a cup of coffee. This way ADO.NET can use connection pooling and can reuse closed connections.

Greetings,
Christian

 

cultofluna replied on Tuesday, May 08, 2007

ok, so connection pooling is a smart solution. however, for my current project it is kind of disastrous... i use a remote mysql database and for every connection i make the waitingtime is approx. 8 sec!! pooling or no pooling, i would really like the possibility now to keep my connection open, but i guess this is not possible as it would break usage of the dataportal on a remote server?

any thoughts?