我在编程中碰到一个问题,事务的原则就是要么全部成功,要么全部失败。可我想在一个事务结构中要对两个不同名的数据库表(都是Sql Server 2000的数据库)进行操作,这样摆在一个事务中合适吗,应该如何处理才好?
下面是公共类中的代码片段,其中有getcon()和getcon1()方法,分别连接两个不同数据库名的表,还有ExecuteSqlTran()方法,用来对一系列的SQL语句实现事务功能,如下:public SqlConnection getcon()
{
INIClass iniclass = new INIClass(Application.StartupPath.ToString() + "\\Config.ini");
if (!iniclass.ExistINIFile())
{
MessageBox.Show("配置文件(Config.ini)缺失!");
Application.Exit();
}
string server = iniclass.IniReadValue("Server", "Name");
if (server.Length == 0)
{
server = "(Local)";
}
string M_str_sqlcon = "Data Source=" + server + ";Database=Weighing;User id=sa;PWD=";
SqlConnection myCon = new SqlConnection(M_str_sqlcon);
return myCon;
} public SqlConnection getcon1()
{ INIClass iniclass = new INIClass(Application.StartupPath.ToString() + "\\Config.ini");
if (!iniclass.ExistINIFile())
{
MessageBox.Show("配置文件(Config.ini)缺失!");
Application.Exit();
}
string remoteServer = iniclass.IniReadValue("RemoteServer", "Name");
if (remoteServer.Length == 0 || remoteServer == ".")
{
remoteServer = "null";
}
string M_str_sqlcon = "Data Source=" + remoteServer + ";Database=Performance;User id=sa;PWD=sa";
SqlConnection myCon1 = new SqlConnection(M_str_sqlcon);
return myCon1;
}
public void ExecuteSqlTran(ArrayList SQLStringList)
{
using (SqlConnection conn = this.getcon())
{
conn.Close();
conn.Open();
SqlTransaction trans = conn.BeginTransaction();
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
cmd.Transaction = trans;
try
{
for (int n = 0; n < SQLStringList.Count; n++)
{
string strsql = SQLStringList[n].ToString();
if (strsql.Trim().Length > 1)
{
cmd.CommandText = strsql;
cmd.ExecuteNonQuery();
}
}
trans.Commit();
}
catch (Exception ex)
{
trans.Rollback();
throw new Exception(ex.Message);
}
finally
{
conn.Close();
}
}
}
以下是运行的程序代码。我做了简化,//所代表的就是一条Sql语句:ArrayList List = new ArrayList();
//将A库的表1的所有记录传送给B库的表2 <- 具体代码我省略了,下同
List.Add(sql);
//将A库的表1的所有记录删除掉
List.Add(sql);
try
{
ExecuteSqlTran(List);
}
catch (Exception ex)
{
MessageBox.Show("向计算中心传送数据失败!", "消息提示框", MessageBoxButtons.OK, MessageBoxIcon.Information);
return;
}我的问题是:由于要连接到两个不同名的数据库(尽管都是Sql Server 2000),那么在一个Transact事务语句中该如何处理呢?请高手明示,谢谢!
下面是公共类中的代码片段,其中有getcon()和getcon1()方法,分别连接两个不同数据库名的表,还有ExecuteSqlTran()方法,用来对一系列的SQL语句实现事务功能,如下:public SqlConnection getcon()
{
INIClass iniclass = new INIClass(Application.StartupPath.ToString() + "\\Config.ini");
if (!iniclass.ExistINIFile())
{
MessageBox.Show("配置文件(Config.ini)缺失!");
Application.Exit();
}
string server = iniclass.IniReadValue("Server", "Name");
if (server.Length == 0)
{
server = "(Local)";
}
string M_str_sqlcon = "Data Source=" + server + ";Database=Weighing;User id=sa;PWD=";
SqlConnection myCon = new SqlConnection(M_str_sqlcon);
return myCon;
} public SqlConnection getcon1()
{ INIClass iniclass = new INIClass(Application.StartupPath.ToString() + "\\Config.ini");
if (!iniclass.ExistINIFile())
{
MessageBox.Show("配置文件(Config.ini)缺失!");
Application.Exit();
}
string remoteServer = iniclass.IniReadValue("RemoteServer", "Name");
if (remoteServer.Length == 0 || remoteServer == ".")
{
remoteServer = "null";
}
string M_str_sqlcon = "Data Source=" + remoteServer + ";Database=Performance;User id=sa;PWD=sa";
SqlConnection myCon1 = new SqlConnection(M_str_sqlcon);
return myCon1;
}
public void ExecuteSqlTran(ArrayList SQLStringList)
{
using (SqlConnection conn = this.getcon())
{
conn.Close();
conn.Open();
SqlTransaction trans = conn.BeginTransaction();
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
cmd.Transaction = trans;
try
{
for (int n = 0; n < SQLStringList.Count; n++)
{
string strsql = SQLStringList[n].ToString();
if (strsql.Trim().Length > 1)
{
cmd.CommandText = strsql;
cmd.ExecuteNonQuery();
}
}
trans.Commit();
}
catch (Exception ex)
{
trans.Rollback();
throw new Exception(ex.Message);
}
finally
{
conn.Close();
}
}
}
以下是运行的程序代码。我做了简化,//所代表的就是一条Sql语句:ArrayList List = new ArrayList();
//将A库的表1的所有记录传送给B库的表2 <- 具体代码我省略了,下同
List.Add(sql);
//将A库的表1的所有记录删除掉
List.Add(sql);
try
{
ExecuteSqlTran(List);
}
catch (Exception ex)
{
MessageBox.Show("向计算中心传送数据失败!", "消息提示框", MessageBoxButtons.OK, MessageBoxIcon.Information);
return;
}我的问题是:由于要连接到两个不同名的数据库(尽管都是Sql Server 2000),那么在一个Transact事务语句中该如何处理呢?请高手明示,谢谢!
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货