CODEWITHSUNDEEP
c#ado.net
John Henckel
John Henckel

Reputation: 11377

Does the order matter in using SqlConnection and SqlCommand

I've seen a lot of code like this

using (SqlConnection con = new SqlConnection(connectionString))
using (SqlCommand command = new SqlCommand("SELECT TOP 2 * FROM Dogs1", con))
{
    con.Open();
    // and so on...
}

I am refactoring and it would be very nice to be able to create the command first. Is there any problem with reversing them, like this?

using (SqlCommand command = new SqlCommand("SELECT TOP 2 * FROM Dogs1"))     
using (command.Connection = new SqlConnection(connectionString))
{        
    command.Connection.Open();
    // and so on...
}

EDIT:

Several people in the comments wondered why I would do this. I agree that reusing commands in multiple threads is bad. My reason for the inversion is to refactor the code. I have a Web API 2 REST API that reads data from many tables (Audit is one of them) so I have many controller classes. Beneath each controller is a Service class. So I have AuditService, PersonService, etc. Add there is a very high level of duplicate code in the services. To reduce that I am moving as much as possible into a CommonService class.

For example,

public class AuditService
{
    private readonly CommonService _database;

    public DataTable FindAll()
    {
        using (var cmd = new SqlCommand("SELECT * FROM Audit"))
        {
            return _database.FindAll(cmd);
        }
    }
}

and the CommonService has this method.

public DataTable FindAll(SqlCommand cmd)
{
    using (cmd.Connection = new SqlConnection(connectionString))
    {
        var dataSet = new DataSet();
        var adapter = new SqlDataAdapter(cmd);
        adapter.Fill(dataSet);
        return dataSet.Tables[0];
    }
}

That seems very clear to me, and easier to unit test.

Upvotes: 0

Views: 106

Answers (1)

Onaefe
Onaefe

Reputation: 456

If you would not be using the full constructor i.e new SqlCommand(query, sqlCon); then you can declare your SqlCommand before initializing the SqlConnection. Then you can go ahead to set the sqlCon as a property of the SqlCommand object. i.e. cmd.Connection = sqlCon;

Just try either ways and see how they work. But I advise that you follow best practice (probably for the person who may maintain your code) to always declare and initialize your SqlConnection before that of the SqlCommand.

Upvotes: 1

Related Questions