服务中使用事务循环提交的问题 急等...

Question

 string sql = "SELECT * FROM dbo.DIS WHERE FILEKEY='" + ConfigurationSettings.AppSettings["fileKey"].ToString() + "' AND GUID NOT IN(SELECT GUID FROM  cc ) and  status='2'";
                SqlConnection con = new SqlConnection(ConfigurationSettings.AppSettings["dbConn"].ToString());
                //打开连接
                con.Open();                SqlDataAdapter sda = new SqlDataAdapter(sql, con);
                DataSet ds = new DataSet();
                sda.Fill(ds);                SqlTransaction sqltr;
                //开始事务
                sqltr = con.BeginTransaction();
                SqlCommand Comm = new SqlCommand();
                Comm.Connection = con;
                Comm.Transaction = sqltr;
                try
                {
                    if (ds != null && ds.Tables[0].Rows.Count > 0)
                    {
                        for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                        {
                            string fileyear = "";                            DataRow dr = ds.Tables[0].Rows[i];                            if (dr["FILEYEAR"].ToString() == "")
                            {
                                //fileyear = DateTime.Now.Year.ToString();
                                fileyear = dr["formdate"].ToString().Split('-')[0];
                            }
                            else
                            {
                                fileyear = dr["FILEYEAR"].ToString();
                            }                            string sqlcomm = "insert into cc(guid,condate,conyear,num,title,currentuser,contype,qianfapeople) ";
                            sqlcomm += " values ('" + dr["guid"].ToString() + "','" + dr["formdate"].ToString() + "','" + fileyear + "','" + dr["FILENUMBER"].ToString() + "','" + dr["title"].ToString() + "',";
                            sqlcomm += "'" + ConfigurationSettings.AppSettings["currentuser"].ToString() + "','" + ConfigurationSettings.AppSettings["fileKey"].ToString() + "','" + dr["subscriber"].ToString() + "')";                            Comm.CommandText = sqlcomm;
                            Comm.ExecuteNonQuery();                                                    
                        }
                       Comm.Transaction.Commit();
                        issendDayWork = true;
                    }                }
                catch (Exception ex)
                {
                    exa = ex.ToString();
                    System.Diagnostics.Debug.Write(ex);
                   Comm.Transaction.Rollback();
                }
                finally
                {
                    con.Close();
                }
代码如上：怎么开启服务时没有反应、难道事务的问题 还是？？？？

Answer 1

不建议这样 循环访问数据库 。把所以insert 拼接成 下面的样子 一次性执行 SQL任务CREATE TABLE #aaa(
 [id] [int] NULL,
 [pid] [int] NULL,
 [name] [nchar](10)
)
GO
INSERT INTO #aaa VALUES(1,0,'a')
INSERT INTO #aaa VALUES(2,0,'b')
INSERT INTO #aaa VALUES(3,1,'c')
INSERT INTO #aaa VALUES(4,1,'d')
INSERT INTO #aaa VALUES(5,2,'e')
INSERT INTO #aaa VALUES(6,3,'f')
INSERT INTO #aaa VALUES(7,3,'g')
INSERT INTO #aaa VALUES(8,4,'h')