using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.SqlClient;

namespace lxc_属性扩展.APP_Code
{
    public class UsersData
    {
        SqlConnection conn = null;
        SqlCommand cmd = null;

        public UsersData()//主函数实例化时自动实例化下列 数据库连接类 和 数据库操作类 语句
        {
            conn = new SqlConnection("server=.;database=Data0928;user=sa;pwd=asdf");
            cmd = conn.CreateCommand();
        }

        /// <summary>
        /// 将数据加入到数据库中，如果返回true说明添加成功
        /// </summary>
        /// <param name="u">要加入到数据库表中的Users对象</param>
        /// <returns></returns>
        public bool Insert(Users u)
        {
            bool ok = false;
            cmd.CommandText = "insert into Users values(@a,@b,@c,@d,@e,@f,@g)";
            cmd.Parameters.Clear();
            cmd.Parameters.AddWithValue("@a", u.Username);
            cmd.Parameters.AddWithValue("@b", u.Password);
            cmd.Parameters.AddWithValue("@c", u.Nickname);
            cmd.Parameters.AddWithValue("@d", u.Sex);
            cmd.Parameters.AddWithValue("@e", u.Birthday);
            cmd.Parameters.AddWithValue("@f", u.Nation);
            cmd.Parameters.AddWithValue("@g", u.Class);
            int count = 0;
            conn.Open();
            count = cmd.ExecuteNonQuery();
            conn.Close();
            if (count > 0)
                ok = true;
            return ok;
        }

        /// <summary>
        /// 将数据从数据库中删除，如果返回true说明删除成功
        /// </summary>
        /// <param name="username"></param>
        /// <returns></returns>
        public bool Delete(string username)
        {
            bool ok = false;
            cmd.CommandText = "delete from Users where username=@a";
            cmd.Parameters.Clear();
            cmd.Parameters.AddWithValue("@a", username);
            int count = 0;
            conn.Open();
            count = cmd.ExecuteNonQuery();
            conn.Close();
            if (count > 0)
                ok = true;
            return ok;
        }

        /// <summary>
        /// 对数据库中的数据进行修改，如果返回true说明修改成功
        /// </summary>
        /// <param name="username"></param>
        /// <param name="u"></param>
        /// <returns></returns>
        public bool Update(string username, Users u)
        {
            bool ok = false;
            cmd.CommandText = "update Users set username=@a,password=@b,nickname=@c,sex=@d,birthday=@e,nation=@f,class=@g where username=@un";
            cmd.Parameters.Clear();
            cmd.Parameters.AddWithValue("@a", u.Username);
            cmd.Parameters.AddWithValue("@b", u.Password);
            cmd.Parameters.AddWithValue("@c", u.Nickname);
            cmd.Parameters.AddWithValue("@d", u.Sex);
            cmd.Parameters.AddWithValue("@e", u.Birthday);
            cmd.Parameters.AddWithValue("@f", u.Nation);
            cmd.Parameters.AddWithValue("@g", u.Class);
            cmd.Parameters.AddWithValue("@un", username);
            int i = 0;
            conn.Open();
            i = cmd.ExecuteNonQuery();
            conn.Close();
            if (i > 0)
                ok = true;
            return ok;
        }

        /// <summary>
        /// 查询数据库中所有的数据，返回List<>集合
        /// </summary>
        /// <returns></returns>
        public List<Users> Select()
        {
            List<Users> list = new List<Users>();
            cmd.CommandText = "select *from Users";
            conn.Open();
            SqlDataReader dr = cmd.ExecuteReader();
            if (dr.HasRows)
            {                
                while (dr.Read())
                {
                    Users u = new Users();
                    u.Ids = (int)dr["ids"];
                    u.Username = dr["username"].ToString();
                    u.Password = dr["password"].ToString();
                    u.Nickname = dr["nickname"].ToString();
                    u.Sex = (Boolean)dr["sex"];
                    u.Birthday = (DateTime)dr["birthday"];
                    u.Nation = dr["nation"].ToString();
                    u.Class = dr["class"].ToString();

                    list.Add(u);
                }
            }
            conn.Close();
            return list;
        }

        /// <summary>
        /// 查询数据库中某条数据，返回List<>集合
        /// </summary>
        /// <param name="username">要查询的数据的用户名</param>
        /// <returns></returns>
        public List<Users> Select(string username)
        {
            List<Users> list = new List<Users>();
            cmd.CommandText = "select *from Users where username=@a";
            cmd.Parameters.Clear();
            cmd.Parameters.AddWithValue("@a", username);

            conn.Open();
            SqlDataReader dr = cmd.ExecuteReader();
            if (dr.HasRows)
            {
                dr.Read();
                Users u = new Users();
                u.Ids = (int)dr["ids"];
                u.Username = dr["username"].ToString();
                u.Password = dr["password"].ToString();
                u.Nickname = dr["nickname"].ToString();
                u.Sex = (Boolean)dr["sex"];
                u.Birthday = (DateTime)dr["birthday"];
                u.Nation = dr["nation"].ToString();
                u.Class = dr["class"].ToString();

                list.Add(u);
            }
            conn.Close();
            return list;
        }
    }
}