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).
The actual database connections will be pooled. As long as all of your SqlConnection
instances use the same connection string, they'll all really use the same connection.
I find it cleaner to create the connection instance, use it, then dispose it (in a using
block). That way, if the code needs to change to use a different connection string, to use a transaction, or whatever, you have all you need to make the change available right there.