Update Multiple tables using the same SQLParameter?

Question

i need to update a few tabless that all have the same field in common.

right now i have a separate update statement for each table like so:

try
        {
            using (SqlConnection conn = new SqlConnection(cCon.getConn()))
            {

                using (SqlCommand cmd = conn.CreateCommand())
                {
conn.Open();
                    cmd.CommandText = "update table0 set active= 'N' where id=@id";
                    cmd.Parameters.Add(new SqlParameter("@id", id));
                    cmd.ExecuteNonQuery();

                    cmd.Parameters.Clear();
                    cmd.CommandText = "update table1 set active= 'N' where id= @id ";
                    cmd.Parameters.Add(new SqlParameter("@id", id));
                    cmd.ExecuteNonQuery();

                    cmd.Parameters.Clear();
                     cmd.CommandText = "update table2 set active= 'N' where id= @id "
                    cmd.Parameters.Add(new SqlParameter("@id", id));
                    cmd.ExecuteNonQuery();

                    cmd.Parameters.Clear();
                    cmd.CommandText = "update table4 set active= 'N' where id= @id "
                    cmd.Parameters.Add(new SqlParameter("@id", id));
                    cmd.ExecuteNonQuery();
                }
            }
        }

can i optimize the code to make it less calls? maybe combine all updates into 1 commandtext and only execute once?

since it's all the same parameters, i'd think it should be possible? or should i just make a stored procedure?

Brandon Moretz · Accepted Answer · 2013-03-27 15:59:07Z

1

Ideally, you would be calling a stored procedure to handle this.. however using the same paramter in a statement batch would work as well:

cmd.CommandText = @"update table0 set active= 'N' where id=@id;
      update table1 set active= 'N' where id= @id;
      update table2 set active= 'N' where id= @id;
      update table4 set active= 'N' where id= @id;";
cmd.Parameters.Add(new SqlParameter("@id", id));
cmd.ExecuteNonQuery();

answered Mar 27, 2013 at 15:59

Brandon Moretz

7,6713 gold badges35 silver badges43 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Dustin Kingen · Accepted Answer · 2013-03-27 16:04:42Z

1

const string query = @"update {0} set active= 'N' where id=@id;";

public string GetCommandText(string table)
{
    return string.Format(query, table);
}

public IEnumerable<string> GetTables()
{
    yield return "table0";
    yield return "table1";
    yield return "table2";
    yield return "table4";
}

using (SqlCommand cmd = conn.CreateCommand())
{
    cmd.Parameters.Add(new SqlParameter("@id", id));
    cmd.CommandText = GetTables().Select(GetCommandText).Aggregate((s, s1) => s + s1);

    conn.Open();
    cmd.ExecuteNonQuery();
}

answered Mar 27, 2013 at 16:04

Dustin Kingen

21.4k7 gold badges55 silver badges94 bronze badges

Comments

Aghilas Yakoub · Accepted Answer · 2013-03-27 15:56:58Z

You delete

cmd.Parameters.Clear();

And you use this code just one time

 cmd.Parameters.Add(new SqlParameter("@id", id));

so :

cmd.CommandText = "update table0 set active= 'N' where id=@id";
                    cmd.Parameters.Add(new SqlParameter("@id", id));
                    cmd.ExecuteNonQuery();


                    cmd.CommandText = "update table1 set active= 'N' where id= @id ";
                    cmd.ExecuteNonQuery();

                     cmd.CommandText = "update table2 set active= 'N' where id= @id "
                    cmd.ExecuteNonQuery();

                    cmd.CommandText = "update table4 set active= 'N' where id= @id "
                    cmd.ExecuteNonQuery();
                    cmd.Parameters.Clear();

Collectives™ on Stack Overflow

Update Multiple tables using the same SQLParameter?

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related