循环执行sql 语句,一起提交,多行的时候总是在等待状态,一行就没有问题
public static Boolean ExecuteQuery(string ConnectionSql, string[] ExeuteSql)//这里有个sql 语句string数组{
Boolean Rtn=true;
SqlConnection _SqlConn = new SqlConnection(ConnectionSql);
_SqlConn.Open();
SqlTransaction trans = _SqlConn.BeginTransaction();
try
{
foreach (string sql in ExeuteSql)
{
if (sql.Trim() != "")
{
ExecuteQueryEnd(_SqlConn, trans, sql);//执行下面的方法,到后面一起提交,防止出现有的提交有的不能提交的问题
}
else
{
continue;
}
}
trans.Commit();
}
catch (System.Data.SqlClient.SqlException e)
{
trans.Rollback();
Rtn = false;
throw new Exception(e.Message);
}
_SqlConn.Close();
return Rtn;
}
public static void ExecuteQueryEnd(SqlConnection conn, SqlTransaction str,string sqlcmd)
{
SqlCommand _Scmd = new SqlCommand(sqlcmd,conn);
_Scmd.Transaction = str;
_Scmd.CommandType = CommandType.Text;
_Scmd.CommandTimeout = 6000;
try
{
_Scmd.ExecuteNonQuery();
}
catch
{
str.Rollback();
} }
看看我写的这个.我的没有这个问题了
/// <summary>/// 将数据写到正式库中
/// </summary>
/// <param name="omc">trueOMC;FalseOMC1</param>
private void InsertIntoData(bool omc)
{
SqlConnection Conncl = new SqlConnection();
Conncl.ConnectionString = SqlConn;
Conncl.Open();
SqlCommand Command = new SqlCommand();
SqlDataAdapter DataAd = new SqlDataAdapter();
DataSet MyData = new DataSet();
try
{
if (omc == true)
{
Command.CommandText = "select name from moto_omc.dbo.sysobjects where type='u' and status>0";
}
else
{
Command.CommandText = "select name from moto_omc.dbo.sysobjects where type='u' and status>0";
}
Command.Connection = Conncl;
DataAd.SelectCommand = Command;
DataAd.Fill(MyData, "tablenames");
Command.Dispose();
DataAd.Dispose();
Conncl.Dispose();
Conncl.Close();
for (int i = 0; i < MyData.Tables[0].Rows.Count; i++)
{
SqlExpro MySqlexe = new SqlExpro();
if (omc == true)
{
MySqlexe.Conn = SqlCon;
}
else
{
MySqlexe.Conn = SqlConn;
}
MySqlexe.InsertData(MyData.Tables[0].Rows[i]["name"].ToString(),omc);
}
}
catch (Exception Err)
{
MessageBox.Show(Err.Message);
}
}
/// <summary>
/// 将数据写到正式库中
/// </summary>
/// <param name="table">表名</param>
/// <param name="omc">true表示OMC,flase表示OMC1</param>
public void InsertData(string table, bool omc)
{
SqlConnection MyCon = new SqlConnection();
SqlCommand MyCmd = new SqlCommand();
MyCon.ConnectionString = Conn;
MyCon.Open();
string Sql;
try
{
WriteReadini Myini = new WriteReadini(Application.StartupPath + "\\InsertInto.ini");
if (omc == true)
{
Sql = Myini.IniReadValue("insert into", table);
}
else
{
Sql = Myini.IniReadValue("insert into OMC1", table);
}
if (table == "network_classes")
{
SqlCommand MyCommand = new SqlCommand();
SqlConnection MyConn = new SqlConnection();
MyConn.ConnectionString = Conn;
MyConn.Open();
MyCommand.CommandText = "TRUNCATE TABLE " + table + "";
MyCommand.Connection = MyConn;
MyCommand.ExecuteNonQuery();
MyCommand.Dispose();
MyConn.Dispose();
MyConn.Close();
}
MyCmd.CommandText = Sql;
MyCmd.CommandTimeout = 360000;
MyCmd.Connection = MyCon;
MyCmd.ExecuteNonQuery();
MyCmd.Dispose();
MyCon.Dispose();
MyCon.Close();
}
catch(Exception Err)
{
StreamWriter write = new StreamWriter(Application.StartupPath + "\\Error.log", true);
//Application.StartupPath;
write.WriteLine("库名称为:"+omc+"写数据表"+table+"出错；错误信息:" + Err.Message + "时间为:" + System.DateTime.Now);
write.Dispose();
write.Close();
}
}
} 首先，在ExecuteQuery（）方法中已经设置了事务操作，那么在ExecuteQueryEnd（）中就不要再用rollback()了。
其次，你看是不是因为使用了静态方法造成的。[tk02]
页:
[1]