Hi guys,

I just want an opinion on the proper usage or a proper design with regards to using SqlConnection object. Which of the 2 below is the best use:

A data provider class whose methods (each of them) contain SqlConnection object (and disposed when done). Like:

IList<Employee> GetAllEmployees() 
{ 
  using (SqlConnection connection = new SqlConnection(this.connectionString)) { 
  // Code goes here... 
  } 
} 

Employee GetEmployee(int id) 
{ 
  using (SqlConnection connection = new SqlConnection(this.connectionString)) { 
  // Code goes here... 
  } 
}   

or

SqlConnection connection; // initialized in constructor 
IList<Employee> GetAllEmployees() 
{ 
  this.TryOpenConnection(); // tries to open member SqlConnection instance 
  // Code goes here... 
  this.CloseConnection(); 
  // return 
} 

Employee GetEmployee(int id) 
{ 
  this.TryOpenConnection(); // tries to open member SqlConnection instance 
  // Code goes here... 
  this.CloseConnection(); 
  // return 
}

Or is there a better approach than this? I have a focused web crawler type of application and this application will crawl 50 or more websites simultaneously (multithreaded) with each website contained in a crawler object and each crawler object has an instance of a data provider class (above). Please advise. Thanks.