title: SQL connection performance question
body: <p>Using C#, visual studio 2008, SQL Server 2000 (and 2008)</p>
  
  <p>I use a DataLayer class for my SQL call in my Web Application:</p>
  
  <p>In the DataLayer are all the SQL calls for the application and all SQL activity happens in that class.</p>
  
  <p>For example:</p>
  
  <pre><code>protected void UpdatePeople(int iPeopleID, string Lastname......)
  {
      InitCmd("sUpdatePeople");
      c_objSQLCmd.Parameters.Add(new SqlParameter("@PeopleID", iPeopleID));
      c_objSQLCmd.Parameters.Add(new SqlParameter("@LastName", szLastName));
      CmdExecuteNonQuery();
  }
  </code></pre>
  
  <p>Then, I have helper objects that do the SQL work:</p>
  
  <pre><code>    private void InitCmdSP(string szStoredProc)
      {
          c_objSQLCmd.Parameters.Clear();
          c_objSQLCmd.CommandType = CommandType.StoredProcedure;
          c_objSQLCmd.CommandText = szStoredProc;
      }
      private int CmdExecuteNonQuery()
      {
          int iReturn = 0;
          if (c_objSQLConn.State != ConnectionState.Open)
              c_objSQLConn.Open();
          iReturn = c_objSQLCmd.ExecuteNonQuery();
          c_objSQLConn.Close();
          return iReturn;
      }
  </code></pre>
  
  <p>I also have helper obects that return DataTable or DataSet and that ExecuteScalar and return int, string, etc. as appropriate. </p>
  
  <p>My question is this, what performance hit am I taking by opening/closing the SQL connection each time?</p>
  
  <p>So far, the appplications have generally less than 600-700 users and not many of them simultaneously, so the perceived performance to the user is probably not a reall issue. I'm just trying to consider best practices.</p>
  
  <p>Thanks,
  John</p>