Initialize various commands for the SqlDataAdapter

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))
{
SqlCommand cmd = new SqlCommand(
"INSERT INTO [dbo].[Bird] ([BirdID], [Name], [Description]) " +
"VALUES (@BirdID, @Name, @Description)"
, conn);

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

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

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.

• You can always try to use Dapper or Entity Framework. Have you tried them already? – t3chb0t Apr 30 '19 at 18:52
• 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? – Asel S Apr 30 '19 at 19:06

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.CommandText="INSERT INTO [dbo].[Bird] ([BirdID], [Name], [Description]) VALUES (@BirdID, @Name, @Description)";