一个用VS2010对数据库文件修改的问题

Question

如题：通过ADO.NET技术对数据库中的数据进行更新的操作 ：using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;namespace QQManger
{
    class AdminTools
    {
         private SqlDataReader GetUserInfo()  //获取用户数据
        {
            try
            {
                StringBuilder sb = new StringBuilder();
                sb.AppendLine("select UI.[UserId],UI.[UserName],L.[LevelName],UI.[Email],UI.[OnLineDay]");
                sb.AppendLine("from [UserInfo] AS UI,[Level] AS L");
                sb.AppendLine("where UI.[LevelId] = L.[LevelId]");
                SqlCommand command = new SqlCommand(sb.ToString(), DBHelper.conn);
                DBHelper.conn.Open();
                return command.ExecuteReader();
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
                return null;
            }
            
        }
        public string CheckLevel(int OnLineDay) //等级计算的方法
        {   int level=0;
            string sql = string.Format("update UserInfo set LevelId ='{0}'",level);
            if (OnLineDay >= 150)
            {
                level = 4;
            }
            else if (OnLineDay >= 100 && OnLineDay < 150)
            {
                level = 3;
            }
            else if (OnLineDay >= 50 && OnLineDay < 100)
            {
                level = 2;
            }
            else
            {
                level = 1;
            }
            return sql;
        }
           public void UpdateUserLevel() //更新等级
        {
            int count = 0;
            SqlDataReader reader = GetUserInfo();
            
                try
                {
                    
                    while (reader.Read())
                    {
                    int OnLineDay = Convert.ToInt32(reader["OnLineDay"]);
                    string sql = CheckLevel(OnLineDay);
                    SqlCommand command = new SqlCommand(sql, DBHelper.conn);
                    int index = command.ExecuteNonQuery();
                    if (index == 1)
                    {
                        count++;
                    }
                    
                }
                }
                catch (Exception ex)
                {
                    Console.WriteLine(ex.Message);
                }
                finally
                {
                    DBHelper.conn.Close();
                }
           Console.WriteLine("本次一共更新"+count+"条记录");
        ｝
    }
}
运行时提示已有打开的与此command 相关联的DataReader,必须先将其关闭。 
没有发现那里打开啊。
新手求教如何修改程序

Answer 1

SqlDataReader reader = GetUserInfo();  这个不就是打开的DataReader嘛
使用完后要reader.Close();

Answer 2

你是说还要加个finally块是么？finally{
DBHelper.conn.Close();
}

Answer 3

使用DataReader的时候必须保持Connection未关闭。DataReader使用完后需要Close()
ExecuteReader(CommandBehavior.CloseConnection);作用自己看CommandBehavior

Answer 4

public void UpdateUserLevel() //更新等级
        {
            int count = 0;
            using(SqlDataReader reader = GetUserInfo())
            {
                try
                {
                    
                    while (reader.Read())
                    {
                    int OnLineDay = Convert.ToInt32(reader["OnLineDay"]);
                    string sql = CheckLevel(OnLineDay);
                    SqlCommand command = new SqlCommand(sql, DBHelper.conn);
                    int index = command.ExecuteNonQuery();
                    if (index == 1)
                    {
                        count++;
                    }
                    
                }
                }
                catch (Exception ex)
                {
                    Console.WriteLine(ex.Message);
                }
                finally
                {
                    DBHelper.conn.Close();
                }
           Console.WriteLine("本次一共更新"+count+"条记录");
        }
    ｝
使用using吧，他会自动释放using(...)括号中创建的变量