1
\$\begingroup\$

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.

\$\endgroup\$
  • \$\begingroup\$ You can always try to use Dapper or Entity Framework. Have you tried them already? \$\endgroup\$ – t3chb0t Apr 30 at 18:52
  • \$\begingroup\$ I haven't worked with either of those technologies but I will check them out, thank you for listing them. Is there a way to clean this code up without the help of other technologies? \$\endgroup\$ – Asel S Apr 30 at 19:06
1
\$\begingroup\$

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;
}
\$\endgroup\$
  • \$\begingroup\$ With this approach - do you mean your code or OP's? I'm not sure it's a good review... could you summarize what you have improved exactly? \$\endgroup\$ – t3chb0t Jun 18 at 18:59
  • 1
    \$\begingroup\$ Edited for clarity \$\endgroup\$ – Wes H Jun 18 at 19:39

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.