Proper Usage of SqlConnection in .NET
- by Jojo
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.
