C#循环取值更新问题

Question

以下代码是Timer自动处理的状态更新事件，执行一遍后提示数据库连接未关闭，不知道改哪，请大家帮看看，谢谢!
//private void PrTimer_Tick(object sender, EventArgs e)
        {
            string sql,sql2,temp;
            int upNo;
            sql = "select a.Pid,a.TempParameter,a.PR_State,b.AutoId,b.StartTime,b.EndTime from PR_Setup as a left join FeaturePlan as b on(a.Pid=b.ProjectionRoom)";
            try
            {
                comm = new SqlCommand(sql, DBHelper.conn);
                DBHelper.conn.Open();
                SqlDataReader dr = comm.ExecuteReader();
                if (dr.HasRows)
                {
                    while (dr.Read())
                    {
                        sql2 = "";
                        temp="";
                        if (Convert.IsDBNull(dr["AutoId"]))
                        {
                            sql = string.Format("update PR_Setup set PR_State=0 where Pid={0}", dr["Pid"]);
                        }
                        else if (Convert.ToDateTime(dr["StartTime"]) <= DateTime.Now && Convert.ToDateTime(dr["EndTime"]) >= DateTime.Now)
                        {
                            sql = string.Format("update PR_Setup set PR_State=1 where Pid={0}", dr["Pid"]);
                            sql2 = string.Format("update FeaturePlan set State=1 where AutoID={0}", dr["AutoId"]);
                        }
                        else if (Convert.ToDateTime(dr["EndTime"]) < DateTime.Now)
                        {
                            temp=dr["TempParameter"].ToString().Replace(",1",",0");
                            sql = string.Format("update PR_Setup set PR_State=1,TempParameter='{0}' where Pid={1}", temp,dr["Pid"]);
                            sql2 = string.Format("update FeaturePlan set State=3 where AutoID={0}", dr["AutoId"]);
                        }
                        comm = new SqlCommand(sql, DBHelper.conn);
                        if (DBHelper.conn.State == ConnectionState.Closed)
                        {
                            DBHelper.conn.Open();
                        }
                        upNo=comm.ExecuteNonQuery();
                        DBHelper.conn.Close();
                        if (sql2 != "")
                        {
                            comm = new SqlCommand(sql2, DBHelper.conn);
                            if (DBHelper.conn.State == ConnectionState.Closed)
                            {
                               DBHelper.conn.Open();
                            }
                            upNo=comm.ExecuteNonQuery();
                            DBHelper.conn.Close();
                        }                    }
                }
                dr.Close();
            }
            catch (Exception ex)
            {
                MessageBox.Show("数据库操作错误"+ex.Message, "错误提示", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
            finally
            {
                DBHelper.conn.Close();
           }
        }

Answer 1

finally执行完之后还没关闭还是，抛出异常说没关闭？

Answer 2

while循环 里面 必需开一次管一次
if (sql2 != "")
                        {
                            comm = new SqlCommand(sql2, DBHelper.conn);
                            if (DBHelper.conn.State == ConnectionState.Closed)
                            {
                               DBHelper.conn.Open();
                            }
                            upNo=comm.ExecuteNonQuery();
                            DBHelper.conn.Close();
                        }不行，把 DBHelper.conn.Close();
拿到 IF 外面就可以的

Answer 3

while (dr.Read())
这里面不能有关闭数据库的操作，关了dr就和数据库失去联系了！dr是一行一行读取的