34,590
社区成员
发帖
与我相关
我的任务
分享
public void ExecuteMACSTransaction(string[] oldvalues, string[] newvalues, DataBaseSelected dbs, string ip)
{
string connectionString = "Data Source=" + ip + ";Persist Security Info=True;Initial Catalog=MACSN数据管理;User ID=sa;Password=sql";
using (SqlConnection connection = new SqlConnection(connectionString))
{
connection.Open();
SqlCommand command = connection.CreateCommand();
SqlTransaction transaction;
// Start a local transaction.
transaction = connection.BeginTransaction("SampleTransaction");
// Must assign both transaction object and connection
// to Command object for a pending local transaction
command.Connection = connection;
command.Transaction = transaction;
try
{
int count = oldvalues.Length;
switch (dbs)
{
case DataBaseSelected.MAC1SN:
for (int i = 0; i < count; i++)
{
command.CommandText = string.Format("update " + dbs.ToString() + " set MAC='{0}' where MAC='{1}'",newvalues[i],oldvalues[i]);
command.ExecuteNonQuery();
}
break;
case DataBaseSelected.MAC2SN:
for (int i = 0; i < count; i++)
{
command.CommandText = string.Format("update " + dbs.ToString() + " set MAC1=case MAC1 when '{0}' then '{1}' else MAC1 end,MAC2=case MAC2 when '{2}' then '{3}' else MAC2 end where MAC1='{4}' or MAC2='{5}'", oldvalues[i], newvalues[i], oldvalues[i], newvalues[i], oldvalues[i], oldvalues[i]);
command.ExecuteNonQuery();
}
break;
case DataBaseSelected.MAC3SN:
for (int i = 0; i < count; i++)
{
command.CommandText = string.Format("update " + dbs.ToString() + " set MAC1=case MAC1 when '{0}' then '{1}' else MAC1 end,MAC2=case MAC2 when '{2}' then '{3}' else MAC2 end,MAC3=case MAC3 when '{4}' then '{5}' else MAC3 end where MAC1='{6}' or MAC2='{7}' or MAC3='{8}'", oldvalues[i], newvalues[i], oldvalues[i], newvalues[i], oldvalues[i], newvalues[i], oldvalues[i], oldvalues[i], oldvalues[i]);
command.ExecuteNonQuery();
}
break;
case DataBaseSelected.MAC4SN:
for (int i = 0; i < count; i++)
{
command.CommandText = string.Format("update " + dbs.ToString() + " set MAC1=case MAC1 when '{0}' then '{1}' else MAC1 end,MAC2=case MAC2 when '{2}' then '{3}' else MAC2 end,MAC3=case MAC3 when '{4}' then '{5}' else MAC3 end,MAC4=case MAC4 when '{6}' then '{7}'else MAC4 end where MAC1='{8}' or MAC2='{9}' or MAC3='{10}' or MAC4='{11}'", oldvalues[i], newvalues[i], oldvalues[i], newvalues[i], oldvalues[i], newvalues[i], oldvalues[i], newvalues[i], oldvalues[i], oldvalues[i], oldvalues[i], oldvalues[i]);
command.ExecuteNonQuery();
}
break;
case DataBaseSelected.MAC21SN:
for (int i = 0; i < count; i++)
{
command.CommandText = string.Format("update " + dbs.ToString() + " set MAC='{0}' where MAC='{1}'", newvalues[i], oldvalues[i]);
command.ExecuteNonQuery();
}
break;
case DataBaseSelected.MAC22SN:
for (int i = 0; i < count; i++)
{
command.CommandText = string.Format("update " + dbs.ToString() + " set MAC1=case MAC1 when '{0}' then '{1}' else MAC1 end,MAC2=case MAC2 when '{2}' then '{3}' else MAC2 end where MAC1='{4}' or MAC2='{5}'", oldvalues[i], newvalues[i], oldvalues[i], newvalues[i], oldvalues[i], oldvalues[i]);
command.ExecuteNonQuery();
}
break;
case DataBaseSelected.MAC23SN:
for (int i = 0; i < count; i++)
{
command.CommandText = string.Format("update " + dbs.ToString() + " set MAC1=case MAC1 when '{0}' then '{1}' else MAC1 end,MAC2=case MAC2 when '{2}' then '{3}' else MAC2 end,MAC3=case MAC3 when '{4}' then '{5}' else MAC3 end where MAC1='{6}' or MAC2='{7}' or MAC3='{8}'", oldvalues[i], newvalues[i], oldvalues[i], newvalues[i], oldvalues[i], newvalues[i], oldvalues[i], oldvalues[i], oldvalues[i]);
command.ExecuteNonQuery();
}
break;
case DataBaseSelected.MAC24SN:
for (int i = 0; i < count; i++)
{
command.CommandText = string.Format("update " + dbs.ToString() + " set MAC1=case MAC1 when '{0}' then '{1}' else MAC1 end,MAC2=case MAC2 when '{2}' then '{3}' else MAC2 end,MAC3=case MAC3 when '{4}' then '{5}' else MAC3 end,MAC4=case MAC4 when '{6}' then '{7}'else MAC4 end where MAC1='{8}' or MAC2='{9}' or MAC3='{10}' or MAC4='{11}'", oldvalues[i], newvalues[i], oldvalues[i], newvalues[i], oldvalues[i], newvalues[i], oldvalues[i], newvalues[i], oldvalues[i], oldvalues[i], oldvalues[i], oldvalues[i]);
command.ExecuteNonQuery();
}
break;
case DataBaseSelected.MAC31SN:
for (int i = 0; i < count; i++)
{
command.CommandText = string.Format("update " + dbs.ToString() + " set MAC='{0}' where MAC='{1}'", newvalues[i], oldvalues[i]);
command.ExecuteNonQuery();
}
break;
case DataBaseSelected.MAC32SN:
for (int i = 0; i < count; i++)
{
command.CommandText = string.Format("update " + dbs.ToString() + " set MAC1=case MAC1 when '{0}' then '{1}' else MAC1 end,MAC2=case MAC2 when '{2}' then '{3}' else MAC2 end where MAC1='{4}' or MAC2='{5}'", oldvalues[i], newvalues[i], oldvalues[i], newvalues[i], oldvalues[i], oldvalues[i]);
command.ExecuteNonQuery();
}
break;
case DataBaseSelected.MAC33SN:
for (int i = 0; i < count; i++)
{
command.CommandText = string.Format("update " + dbs.ToString() + " set MAC1=case MAC1 when '{0}' then '{1}' else MAC1 end,MAC2=case MAC2 when '{2}' then '{3}' else MAC2 end,MAC3=case MAC3 when '{4}' then '{5}' else MAC3 end where MAC1='{6}' or MAC2='{7}' or MAC3='{8}'", oldvalues[i], newvalues[i], oldvalues[i], newvalues[i], oldvalues[i], newvalues[i], oldvalues[i], oldvalues[i], oldvalues[i]);
command.ExecuteNonQuery();
}
break;
case DataBaseSelected.MAC34SN:
for (int i = 0; i < count; i++)
{
command.CommandText = string.Format("update " + dbs.ToString() + " set MAC1=case MAC1 when '{0}' then '{1}' else MAC1 end,MAC2=case MAC2 when '{2}' then '{3}' else MAC2 end,MAC3=case MAC3 when '{4}' then '{5}' else MAC3 end,MAC4=case MAC4 when '{6}' then '{7}'else MAC4 end where MAC1='{8}' or MAC2='{9}' or MAC3='{10}' or MAC4='{11}'", oldvalues[i], newvalues[i], oldvalues[i], newvalues[i], oldvalues[i], newvalues[i], oldvalues[i], newvalues[i], oldvalues[i], oldvalues[i], oldvalues[i], oldvalues[i]);
command.ExecuteNonQuery();
}
break;
default:
break;
}
transaction.Commit();
connection.Close();
connection.Dispose();
}
catch (Exception ex)
{
connection.Close();
connection.Dispose();
DevExpress.XtraEditors.XtraMessageBox.Show(ex.Message,"提示");
}
}
}
using (SqlConnection connection = new SqlConnection(connectionString))
{
connection.Open();
SqlCommand command = connection.CreateCommand();
SqlTransaction transaction;
// Start a local transaction.
transaction = connection.BeginTransaction("SampleTransaction");
// Must assign both transaction object and connection
// to Command object for a pending local transaction
command.Connection = connection;
command.Transaction = transaction;
try
{
command.CommandText =
"Insert into Region (RegionID, RegionDescription) VALUES (100, 'Description')";
command.ExecuteNonQuery();
command.CommandText =
"Insert into Region (RegionID, RegionDescription) VALUES (101, 'Description')";
command.ExecuteNonQuery();
// Attempt to commit the transaction.
transaction.Commit();
Console.WriteLine("Both records are written to database.");
}
catch (Exception ex)
{
Console.WriteLine("Commit Exception Type: {0}", ex.GetType());
Console.WriteLine(" Message: {0}", ex.Message);
// Attempt to roll back the transaction.
try
{
transaction.Rollback();
}
catch (Exception ex2)
{
// This catch block will handle any errors that may have occurred
// on the server that would cause the rollback to fail, such as
// a closed connection.
Console.WriteLine("Rollback Exception Type: {0}", ex2.GetType());
Console.WriteLine(" Message: {0}", ex2.Message);
}
}
}
update MAC2SN
set MAC1 = case MAC1 when ‘00231F70EA46’ then ‘00231F70E70E’ else MAC1 end,
MAC2 = case MAC2 when ‘00231F70EA46’ then ‘00231F70E70E’ else MAC2 end