使用相同的SQLParameter更新多个表
本文关键字:更新 SQLParameter | 更新日期: 2023-09-27 18:06:03
我需要更新一些有相同字段的表
现在我有一个单独的更新语句为每个表,像这样:
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();
}
}
}
我可以优化代码使其调用更少吗?也许将所有更新合并到一个命令文本中,只执行一次?
因为它是所有相同的参数,我认为它应该是可能的?还是应该创建一个存储过程?
理想情况下,您应该调用一个存储过程来处理这个。但是,在语句批处理中使用相同的参数也可以:
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();
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();
}
删除
cmd.Parameters.Clear();
这个代码只用一次
cmd.Parameters.Add(new SqlParameter("@id", id));
:
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();