第十七章代码

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

namespace qqinfo
{
    class UserManager
    {
        private const string strConn = "Data Source=.;Initial Catalog=QQ用户信息;Integrated Security=True";
        DBHandle dbh = new DBHandle();



        public void Login()     
        {
            int count = 1;
            bool flag;

            do
            {
                Console.WriteLine("请输入用户名：");
                string userName = Console.ReadLine();
                Console.WriteLine("请输入密码：");
                string pwd = Console.ReadLine();
                string Msg = "";
                flag = dbh.CheckAdminInfo(userName, pwd, ref Msg);
                if (flag)
                {
                    Console.WriteLine("登陆成功!");
                    dbh.ShowMenu();
                }
                else
                {
                    Console.WriteLine("登陆失败!");
                }
                count++;
                if (count > 3)
                {
                    Console.WriteLine("\n");
                    Console.WriteLine("连续三次登录失败，退出本系统");
                    break;
                }
            } while (flag == false);
        }




        public string ShowDesign(string strLevel)     //等级符号
        {
            string strDesign = "";
            switch (strLevel)
            {
                case "无等级":
                    strDesign = "―";
                    break;
                case "星星":
                    strDesign = "☆";
                    break;
                case "月亮":
                    strDesign = "€";
                    break;
                case "太阳":
                    strDesign = "◎";
                    break;
                default:
                    break;
            }
            return strDesign;
        }






        public void ShowUserInfo()           //显示列表
        {
            SqlDataReader reader = dbh.GetUserList();
            if (reader == null)
            {
                Console.WriteLine("出现异常");
            }
            DisplayUserInfo(reader);

        }






        public void DisplayUserInfo(SqlDataReader reader)         //输出QQ用户清单
        {
            Console.WriteLine("--------------------------------------------------------------------------------");
            Console.WriteLine("编号\t昵称\t\t等级\t\t邮箱\t\t在线天数");
            Console.WriteLine("--------------------------------------------------------------------------------");
            while (reader.Read())
            {
                Console.Write(reader["UserId"] + "\t");
                Console.Write(reader["UserName"] + "\t");
                Console.Write(ShowDesign((string)reader["LevelName"]) + "\t\t");
                Console.Write(reader["Email"] + "\t");
                Console.WriteLine(reader["OnLineDay"]);
            }
            Console.WriteLine("--------------------------------------------------------------------------------");
        }






        public void UpdateOnlineDay()       //更新在线天数
        {
            try
            {
                Console.WriteLine("请输入用户编号：");
                int userid = Convert.ToInt32(Console.ReadLine());
                Console.WriteLine("请输入更新的在线天数：");
                double Onelineday = Convert.ToDouble(Console.ReadLine());


                int num = dbh.UpdateOnlineDay(userid, Onelineday);
                if (num == 0)
                {
                    Console.WriteLine("出现异常：");
                }
                else
                {
                    Console.WriteLine("修改成功！");
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
                Console.WriteLine("输入错误");
            }
        }






        public void InsertUserInfo()        //插入用户
        {
            SqlConnection con = new SqlConnection(strConn);
            try
            {
                Console.WriteLine("请输入昵称：");
                string name = Console.ReadLine();
                Console.WriteLine("请输入密码：");
                string pwd = Console.ReadLine();
                Console.WriteLine("请输入邮箱：");
                string email = Console.ReadLine();
                int num = dbh.InsertUserInfo(name, pwd, email);
                if (num > 0)
                {
                    Console.WriteLine("插入成功！用户编号是：" + num);
                }
                else
                {
                    Console.WriteLine("插入失败！");
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
            finally
            {
                con.Close();
            }
        }






        private int JudgeLevelByOnLineDay(double iOnlineDay)       //判定等级
        {
            int Level = 0;


            if (iOnlineDay < 5)
            {
                Level = 1;
            }
            if (iOnlineDay >= 5 && iOnlineDay < 32)
            {
                Level = 2;
            }
            else if (iOnlineDay >= 32 && iOnlineDay < 320)
            {
                Level = 3;
            }
            else if (iOnlineDay >= 320)
            {
                Level = 4;
            }
            else
            {
                Level = 1;
            }
            return Level;
        }






        public void UpdateUserLevel()      //更新等级
        {
            //取得所有用户的用户编号和在线天数
            SqlDataReader reader = dbh.GetUserIdAndOnlineDay();
            if (reader == null)
            {
                Console.WriteLine("出现异常");
            }
            int count = 0;
            while (reader.Read())
            {
                int iUserId = Convert.ToInt32(reader["UserId"]);
                double iLineDay = Convert.ToDouble(reader["OnLineDay"]);
                int iLevelId = JudgeLevelByOnLineDay(iLineDay);
                dbh.UpdateUser(iUserId, iLevelId);
                count++;
            }
            Console.WriteLine("本次共更新用户记录数：{0}", count);
            Console.WriteLine("更新成功！");
            reader.Close();
        }






        public void DeleteUserInfo()          //删除
        {
            SqlConnection con = new SqlConnection(strConn);
            try
            {
                Console.WriteLine("请输入删除的用户编号：");
                int userid = Convert.ToInt32(Console.ReadLine());
                Console.WriteLine("将要删除的用户信息是：");
                SqlDataReader reader = dbh.GetUserByID(userid);
                if (reader == null)
                {
                    Console.WriteLine("出现异常！");
                }
                DisplayUserInfo(reader);
                Console.WriteLine("要删除该用户记录吗？（y/n）");
                string yn = Console.ReadLine();
                if (yn.Trim().ToUpper() != "Y")
                {
                    Console.WriteLine("退出删除操作！");
                }
                int num = dbh.DeleteUserInfo(userid);
                if (num == -1)
                {
                    Console.WriteLine("操作失败");
                }
                else
                {
                    Console.WriteLine("删除成功");
                }
            }
            catch (Exception e)
            {
                Console.WriteLine("删除失败" + e.Message);
            }
        }






        public bool IsExit()            //退出
        {
            Console.WriteLine("是否退出？（Y/N）");
            string n = Console.ReadLine();
            n = n.Trim().ToUpper();
            if (n.Equals("Y"))
            {
                return true;
            }
            else
            {
                return false;
            }
        }
    }
}















using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.SqlClient; 
namespace qqinfo
{
    class DBHandle
    {
        private const string strConn = "Data Source=.;Initial Catalog=QQ用户信息;Integrated Security=True";
        public bool CheckAdminInfo(string userName, string pwd, ref string strMsg) 
        {
            SqlConnection con = new SqlConnection(strConn);
            string sql = string.Format("select count(*) from admin where LoginId='" + userName + "' and LoginPwd='" + pwd + "'");
            try
            {
                con.Open();
                SqlCommand cmd = new SqlCommand(sql, con);
                int num = (int)cmd.ExecuteScalar();
                if (num != 1)
                {

                    return false;
                }
                else
                {

                    return true;
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
                return false;
            }
            finally
            {
                con.Close();
            }
        }




        public void ShowMenu()     
        {
            UserManager user = new UserManager();
            int num;
            do
            {
                Console.WriteLine("==================欢迎登录QQ用户信息管理系统==================");
                Console.WriteLine("-------------------------请选择菜单项-------------------------");
                Console.WriteLine("1.显示用户清单");
                Console.WriteLine("2.更新在线天数");
                Console.WriteLine("3.添加用户新记录");
                Console.WriteLine("4.更新用户等级");
                Console.WriteLine("5.删除用户记录");
                Console.WriteLine("0.退出");
                Console.WriteLine("=============================================================");
                num = int.Parse(Console.ReadLine());
                switch (num)
                {
                    case 1:
                        user.ShowUserInfo();
                        continue;
                    case 2:
                        user.UpdateOnlineDay();
                        continue;
                    case 3:
                        user.InsertUserInfo();
                        continue;
                    case 4:
                        user.UpdateUserLevel();
                        continue;
                    case 5:
                        user.DeleteUserInfo();
                        continue;
                    case 0:
                        if (user.IsExit())
                        {
                            break;
                        }
                        else
                        {
                            continue;
                        }
                }
                break;
            } while (true);
        }






        public SqlDataReader GetUserList()    //显示清单
        {
            SqlConnection con = new SqlConnection(strConn);
            string sql = "select a.UserId,a.UserName,b.LevelName,a.Email,a.OnLineDay from UserInfo a,Level b where a.LevelId=b.LevelId";
            try
            {
                con.Open();
                SqlCommand cmd = new SqlCommand(sql, con);
                return cmd.ExecuteReader();
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
                return null;
            }
        }






        public int UpdateOnlineDay(int userId, double newOnlineDay)     //更新天数
        {
            SqlConnection con = new SqlConnection(strConn);
            string sql = "update UserInfo set OnLineDay='" + newOnlineDay + "' where UserId=" + userId + "";
            try
            {
                con.Open();
                SqlCommand cmd = new SqlCommand(sql, con);
                return cmd.ExecuteNonQuery();
                
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
                return -1;
            }
            finally
            {
                con.Close();
            }
        }






        public int InsertUserInfo(string userName, string userPwd, string email)      //加记录
        {
            SqlConnection con = new SqlConnection(strConn);
            string sql = string.Format("insert into UserInfo values ('{0}','{1}',1,'{2}','0')", userName, userPwd, email);
            try
            {
                con.Open();
                StringBuilder sb = new StringBuilder();
                sb.AppendLine(" insert into");
                sb.AppendLine("[UserInfo]");
                sb.AppendLine(" values");
                sb.AppendLine("('" + userName + "','" + userPwd + "',1,'" + email + "',0);");
                sb.AppendLine("SELECT      @@Identity;");


                SqlCommand cmd = new SqlCommand(sb.ToString(), con);
                int num = Convert.ToInt32(cmd.ExecuteScalar());
                
                if (num > 0)
                {
                    return num;
                }
                else
                {
                    return -1;
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
                return -1;
            }
            finally
            {
                con.Close();
            }

        }






        public SqlDataReader GetUserIdAndOnlineDay()             //查编号
        {
            SqlConnection con = new SqlConnection(strConn);
            try
            {
                string sql = "select UserId,onlineday from UserInfo";
                con.Open();
                SqlCommand cmd = new SqlCommand(sql, con);
                return cmd.ExecuteReader();
            }
            catch (Exception)
            {
                return null;
            }
        }






        public int UpdateUser(int userId, int iLevel)         //更新等级
        {
            SqlConnection con = new SqlConnection(strConn);
            try
            {
                con.Open();
                string sql = string.Format("update UserInfo set LevelId={0} where UserId={1}", iLevel, userId);
                SqlCommand cmd = new SqlCommand(sql, con);
                return cmd.ExecuteNonQuery();
            }
            catch (Exception)
            {
                return -1;
            }
            finally
            {
                con.Close();
            }
        }






        public SqlDataReader GetUserByID(int id)    //查信息
        {
            SqlConnection con = new SqlConnection(strConn);
            try
            {
                string sql = "select a.userid,a.username,b.levelname,a.email,a.onlineday from UserInfo a,Level b where a.UserId=" + id + " and a.LevelId=b.LevelId";
                con.Open();
                SqlCommand cmd = new SqlCommand(sql, con);
                return cmd.ExecuteReader();
            }
            catch (Exception)
            {
                return null;
            }
        }






        public int DeleteUserInfo(int strUserId)   //删除
        {
            SqlConnection con = new SqlConnection(strConn);
            try
            {
                con.Open();
                string sql = "delete from UserInfo where UserId=" + strUserId + "";
                SqlCommand cmd = new SqlCommand(sql, con);
                return cmd.ExecuteNonQuery();
            }
            catch (Exception)
            {
                return -1;
            }
        }

    }

}














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

namespace qqinfo
{
    class Program
    {
        static void Main(string[] args)
        {
            UserManager user = new UserManager();
            user.Login();
            Console.ReadLine();
        }
    }
}