Initialize various commands for the SqlDataAdapter

Question

This is my code for my SqlDataAdapter which is similar to the code example given in the docs:

public static int CommitBirdData(DataSet pDataSet)
{
    int rowsAffected = 0;

    using (SqlConnection conn = new SqlConnection(connString))
    using (SqlDataAdapter birdDataAdapter = new SqlDataAdapter())
    {
        SqlCommand cmd = new SqlCommand(
            "INSERT INTO [dbo].[Bird] ([BirdID], [Name], [Description]) " +
            "VALUES (@BirdID, @Name, @Description)"
            , conn);

        cmd.Parameters.Add("@BirdID", SqlDbType.NVarChar, 10, "BirdID");
        cmd.Parameters.Add("@Name", SqlDbType.NVarChar, 50, "Name");
        cmd.Parameters.Add("@Description", SqlDbType.NVarChar, 200, "Description");

        birdDataAdapter.InsertCommand = cmd;

        cmd = new SqlCommand(
            "UPDATE [dbo].[Bird] " +
            "SET [BirdID] = @BirdID, [Name] = @Name, [Description] = @Description " +
            "WHERE [BirdID] = @BirdID"
            , conn);

        cmd.Parameters.Add("@BirdID", SqlDbType.NVarChar, 10, "BirdID");
        cmd.Parameters.Add("@Name", SqlDbType.NVarChar, 50, "Name");
        cmd.Parameters.Add("@Description", SqlDbType.NVarChar, 200, "Description");

        birdDataAdapter.UpdateCommand = cmd;

        cmd = new SqlCommand(
            "DELETE FROM [dbo].[BirdCount] WHERE [BirdID] = @BirdID " +
            "DELETE FROM[dbo].[Bird] WHERE[BirdID] = @BirdID"
            , conn);

        cmd.Parameters.Add("@BirdID", SqlDbType.NVarChar, 10, "BirdID");

        birdDataAdapter.DeleteCommand = cmd;

        rowsAffected = birdDataAdapter.Update(pDataSet, "Bird");

        return rowsAffected;

    }    

I noticed that this code is repetitive and I was just wondering whether there is a more efficient way to initialize the commands of the SqlDataAdapter with or without the using statements.

Answer 1

With the following, you'll only add each parameter a single time and just change the command text before assigning it to the adapter command, allowing you to reuse the existing cmd object.

using (SqlDataAdapter birdDataAdapter = new SqlDataAdapter())
{
    SqlCommand cmd = new SqlCommand("DELETE FROM [dbo].[BirdCount] WHERE [BirdID] = @BirdID; DELETE FROM[dbo].[Bird] WHERE[BirdID] = @BirdID",conn);
    cmd.Parameters.Add("@BirdID", SqlDbType.NVarChar, 10, "BirdID");

    birdDataAdapter.DeleteCommand = cmd;

    cmd.Parameters.Add("@Name", SqlDbType.NVarChar, 50, "Name");
    cmd.Parameters.Add("@Description", SqlDbType.NVarChar, 200, "Description");

    cmd.CommandText="INSERT INTO [dbo].[Bird] ([BirdID], [Name], [Description]) VALUES (@BirdID, @Name, @Description)";
    birdDataAdapter.InsertCommand = cmd;

    cmd.CommandText= "UPDATE [dbo].[Bird] SET [BirdID] = @BirdID, [Name] = @Name, [Description] = @Description WHERE [BirdID] = @BirdID";           
    birdDataAdapter.UpdateCommand = cmd;

    rowsAffected = birdDataAdapter.Update(pDataSet, "Bird");

    return rowsAffected;
}