【求查错】做小练习时的问题

Question

//SQLHelper.cs
 class SQLHelper
    {
        private static readonly string conStr;
        static SQLHelper()
        {
            conStr = ConfigurationManager.ConnectionStrings["con1"].ConnectionString;
        }
        public static SqlDataReader ExecuteReader(string sql)
        {
            using(SqlConnection con=new SqlConnection(conStr))
            {
                using(SqlCommand cmd=new SqlCommand(sql,con))
                {
                    if (con.State == ConnectionState.Closed)
                    {
                        con.Open();
                    }
                    return cmd.ExecuteReader();
                }
            }
        }
    }
  //窗体后台程序
   private void Form1_Load(object sender, EventArgs e)
        {
            LoadSearchGroups(comboBox1);
        }
        private void LoadSearchGroups(ComboBox cbo)
        {
            string sql = "select * from PhoneType";
            SqlDataReader reader = SQLHelper.ExecuteReader(sql);
          
                if (reader.HasRows)
                {
                    while (reader.Read())
                    {
                        UserGroups ug = new UserGroups() {GroupsId=reader.GetInt32(0)};
                        cbo.Items.Add(ug);
                    }
                }
            
        }//UserGroups.cs
 class UserGroups
    {
        public int GroupsId
        {
            get;
            set;
        }
    }是一道winform程序，目的是将数据库第一行数据载入到combox1 里，但出现的问题是
      if (reader.HasRows)// 问题所在： 阅读器关闭时尝试调用 HasRows 无效。 
                {
                    while (reader.Read())
                    {
                        UserGroups ug = new UserGroups() {GroupsId=reader.GetInt32(0)};
                        cbo.Items.Add(ug);
                    }
                }
不知道要怎样改

Answer 1

using(SqlConnection con=new SqlConnection(conStr))
using内会关闭连接

Answer 2

你可以考虑ExecuteReader返回GroupsId

Answer 3

public static SqlDataReader ExecuteReader(string sql)
        {
            using(SqlConnection con=new SqlConnection(conStr))
            {
                using(SqlCommand cmd=new SqlCommand(sql,con))
                {
                    if (con.State == ConnectionState.Closed)
                    {
                        con.Open();
                    }
                    return cmd.ExecuteReader();
                }
            }
        }
这个方法 直接就给UserGroups 赋值

Answer 4

 public UserGroups GetTopOne(string sql)
        {
           UserGroups ug;
            using(SqlConnection con=new SqlConnection(conStr))
            {
                using(SqlCommand cmd=new SqlCommand(sql,con))
                {
                    if (con.State == ConnectionState.Closed)
                    {
                        con.Open();
                    }
                    if (reader.HasRows)
                {
                    if (reader.Read())
                    {
                       ug  = new UserGroups() {GroupsId=reader.GetInt32(0)};
                        
                    }
                }
                     return ug;
                }
            }
        }

Answer 5

SqlConnection con=new SqlConnection(conStr);
SqlCommand cmd=new SqlCommand(sql,con);
if (con.State == ConnectionState.Closed)
 {
           con.Open();
}
return cmd.ExecuteReader(CommandBehavior.CloseConnection);

Answer 6

 public static SqlDataReader ExecuteReader(string sql)
这个函数里不要用using,会自动关闭reader