标题:循环执行sql 语句,一起提交,多行的时候总是在等待状态,一行就没有问题
只看楼主
zsyutian
Rank: 1
等 级:新手上路
帖 子:48
专家分:0
注 册:2006-6-30
 问题点数:0 回复次数:2 
循环执行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;
                  }              
              }
              ();
          }
          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;
          _ = CommandType.Text;                    
          _ = 6000;
          try
          {
              _Scmd.ExecuteNonQuery();
          }
          catch
          {
              str.Rollback();
          }        }
搜索更多相关主题的帖子: sql insert 语句 string 状态 
2008-07-11 11:40
liyazhou
Rank: 1
等 级:新手上路
帖 子:2
专家分:0
注 册:2008-8-15
得分:0 
看看我写的这个.我的没有这个问题了
/// <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)
                {
                     = "select name from moto_omc.dbo.sysobjects where type='u' and status>0";
                }
                else
                {
                     = "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();
                     = "TRUNCATE TABLE  " + table + "";
                    MyCommand.Connection = MyConn;
                    MyCommand.ExecuteNonQuery();
                    MyCommand.Dispose();
                    MyConn.Dispose();
                    MyConn.Close();
                }
                = Sql;
                = 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();
            }
        }
    }
2008-08-15 00:41
seiya027848
Rank: 1
等 级:新手上路
帖 子:60
专家分:0
注 册:2008-6-27
得分:0 
首先,在ExecuteQuery()方法中已经设置了事务操作,那么在ExecuteQueryEnd()中就不要再用rollback()了。
其次,你看是不是因为使用了静态方法造成的。
2008-08-15 09:23



参与讨论请移步原网站贴子:https://bbs.bccn.net/thread-223485-1-1.html




关于我们 | 广告合作 | 编程中国 | 清除Cookies | TOP | 手机版

编程中国 版权所有,并保留所有权利。
Powered by Discuz, Processed in 0.018372 second(s), 8 queries.
Copyright©2004-2024, BCCN.NET, All Rights Reserved