How does SqlCommandBuilder do its stuff and how can I stop ReSharper's suggestion to delete it?

Question

I don't understand how SqlCommandBuilder does its thing. I have the following code:

public void TestCommandBuilder()
{
    var pubsDataSet = new DataSet("Pubs");
    var pubs = ConfigurationManager.ConnectionStrings["PubsConnectionString"];
    var connection = new SqlConnection(pubs.ConnectionString);
    SqlCommand cmd = connection.CreateCommand();
    cmd.CommandType = CommandType.Text;
    cmd.CommandText = "SELECT * FROM Publishers";
    var da = new SqlDataAdapter(cmd);

    da.Fill(pubsDataSet, "publishers");
    foreach (DataRow row in pubsDataSet.Tables["publishers"].Rows)
    {
        row["pub_name"] = "Updated " + DateTime.Now.Minute + DateTime.Now.Second;
    }

    // The variable builder is not used
    var builder = new SqlCommandBuilder(da);

    da.UpdateBatchSize = 3;
    da.RowUpdated += DaRowUpdated;

    da.Update(pubsDataSet, "publishers");
}

private void DaRowUpdated(object sender, SqlRowUpdatedEventArgs e)
{
    Console.WriteLine("Rows: " + e.RecordsAffected + "\r\n");
}

The variable builder is not used anywhere and I do not call the GetUpdateCommand() method, like they do on MSDN. I only create the SqlCommandBuilder, passing it a SqlDataAdapter. But the code just works fine.

If you look at the code, it seems like the line

var builder = new SqlCommandBuilder(da);

can be safely deleted. In fact, ReSharper is suggesting to remove it. But if I do, the code doesn't run anymore, as the SqlDataAdapter doesn't know how to perform an update.

In debug mode I noticed that after executing that line, the SqlDataAdapter's UpdateCommand property is still null. From the MSDN-docs I get that the SqlCommandBuilder registers itself to the RowUpdated event of the SqlDataAdapter.

But what does it do when that event is triggered? Is the SqlDataBuilder actually performing the update itself?

Something else I noticed, if I remove the SqlCommandBuilder, my DaRowUpdated method is triggered once, just before the InvalidOperationException occurs on the da.Update statement. I did not expect that. I'd think the RowUpdated event only occurs when a row has actually been updated.

So... three concrete questions:

1. How can I prevent ReSharper from suggesting to delete this line?
2. Is it bad practice to program a class like SqlCommandBuilder, where the code doesn't indicate in any way that creating an instance is doing something with the SqlDataAdapter passed in?
3. Is this a design pattern?

Answer 1

If you are not consuming the variable, it is definitely not necessary. If something is going on in its constructor that is useful, you can still invoke that:

var builder = new SqlCommandBuilder(da);

Should be:

new SqlCommandBuilder(da);

This should take care of the warning without changing the behavior of the code. It looks a little weird, and if this is really necessary, I question the design of SqlCommandBuilder a bit. But in practice, a usage like this is essentially the same as an ordinary method invocation.

Answer 2

As per my comment on the question:

    public void TestCommandBuilder()
    {
        var pubs = ConfigurationManager.ConnectionStrings["PubsConnectionString"];

        using (var pubsDataSet = new DataSet("Pubs"))
        using (var connection = new SqlConnection(pubs.ConnectionString))
        using (SqlCommand cmd = connection.CreateCommand())
        {
            cmd.CommandType = CommandType.Text;
            cmd.CommandText = "SELECT * FROM Publishers";

            using (var da = new SqlDataAdapter(cmd))
            using (new SqlCommandBuilder(da))
            {
                da.UpdateBatchSize = 3;
                da.RowUpdated += DaRowUpdated;
                da.Fill(pubsDataSet, "publishers");
                foreach (DataRow row in pubsDataSet.Tables["publishers"].Rows)
                {
                    row["pub_name"] = "Updated " + DateTime.Now.Minute + DateTime.Now.Second;
                }

                da.Update(pubsDataSet, "publishers");
            }
        }
    }

    private void DaRowUpdated(object sender, SqlRowUpdatedEventArgs e)
    {
        Console.WriteLine("Rows: " + e.RecordsAffected);
    }

Answer 3

My understanding that is during the construction of the command builder object it adds a reference to itself on the DataAdapter so it knows how to build CRUD commands.

Note this section under the remarks of the link you posted above.

*

The SqlDataAdapter does not automatically generate the Transact-SQL statements required to reconcile changes made to a DataSet with the associated instance of SQL Server. However, you can create a SqlCommandBuilder object to automatically generate Transact-SQL statements for single-table updates if you set the SelectCommand property of the SqlDataAdapter. Then, any additional Transact-SQL statements that you do not set are generated by the SqlCommandBuilder.

*