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

namespace boss
{
    class Program
    {
        static void Main(string[] args)
        {
            //1、创建数据库连接类
            string sql = "server=(local);database=Class0928;user=sa;pwd=123;";//编写连接字符串
            SqlConnection conn = new SqlConnection(sql);
            //2、创建数据库操作对象，创建过程是与刚创建的连接对象匹配起来
            SqlCommand cmd = conn.CreateCommand();

            for (; ; )
            {
                //try
                //{
                    Console.WriteLine("请输入您要执行的操作：1、查看 2、增加数据 3、修改数据 4、删除数据");
                    int code = int.Parse(Console.ReadLine());
                    if (code == 1)
                    {
                        cmd.CommandText = "select *from users";
                        conn.Open();
                        SqlDataReader dr = cmd.ExecuteReader();
                        if (dr.HasRows)
                        {
                            while (dr.Read())
                            {
                                Console.WriteLine(dr["ids"] + "\t" + dr["usersname"] + "\t" + dr["password"] + "\t" + dr["nickname"] + "\t" + (Convert.ToBoolean(dr["sex"]) ? "男" : "女") + "\t" + dr["birthday"] + "\t" + dr["nation"] + "\t" + dr["class"]);
                            }
                        }
                        conn.Close();
                        continue;
                    }
                    else if (code == 2)
                    {
                        Console.Write("请输入想要添加的用户名：");
                        string uname = Console.ReadLine();
                        cmd.CommandText = "select *from users";
                        conn.Open();
                        SqlDataReader dr = cmd.ExecuteReader();
                        int a = 0;
                        if (dr.HasRows)
                        {
                            while (dr.Read())
                            {   //检查是否用户名重复
                                if (dr["usersname"].ToString() == uname)
                                {
                                    a++;
                                    break;
                                }
                            }
                        }
                        conn.Close();
                        if (a == 0)//用户名无重复
                        {
                            string pwd;
                            for (; ; )
                            {
                                Console.Write("请输入密码：");
                                string mima = Console.ReadLine();
                                if (mima != "")
                                {
                                    pwd = mima;
                                    break;
                                }
                                else
                                {
                                    Console.WriteLine("密码不能为空，请重新输入：");
                                    continue;
                                }
                            }
                            string nick;
                            for (; ; )
                            {
                                Console.Write("请输入昵称：");
                                string miname = Console.ReadLine();
                                if (miname != "")
                                {
                                    nick = miname;
                                    break;
                                }
                                else
                                {
                                    Console.WriteLine("昵称不能为空，请重新输入：");
                                    continue;
                                }
                            }
                            string sex;
                            for (; ; )
                            {
                                Console.Write("请输入性别(男/女)： ");
                                string sex1 = Console.ReadLine();
                                if (sex1 == "男")
                                {
                                    sex = "True";
                                    break;
                                }
                                else if (sex1 == "女")
                                {
                                    sex = "False";
                                    break;
                                }
                                else
                                {
                                    Console.WriteLine("输入性别有误，请重新输入：");
                                    continue;
                                }
                            }
                            string bir;
                            for (; ; )
                            {
                                Console.Write("请输入您的生日：");
                                try   //尝试去做，如果有错，从错误的句直接跳转到catch
                                {
                                    DateTime shengri = DateTime.Parse(Console.ReadLine());
                                    bir = shengri.ToString();
                                    break;
                                }
                                catch
                                {
                                    Console.WriteLine("输入日期格式错误");
                                    continue;
                                }
                            }
                            string nation;
                            for (; ; )
                            {
                                Console.Write("请输入民族：");
                                string nation1 = Console.ReadLine();
                                if (nation1 == "汉族")
                                {
                                    nation = "N001";
                                    break;
                                }
                                else if (nation1 == "满族")
                                {
                                    nation = "N002";
                                    break;
                                }
                                else if (nation1 == "藏族")
                                {
                                    nation = "N003";
                                    break;
                                }
                                else if (nation1 == "彝族")
                                {
                                    nation = "N004";
                                    break;
                                }
                                else
                                {
                                    Console.WriteLine("输入民族有误，请重新输入！");
                                    continue;
                                }
                            }
                            string cla;
                            for (; ; )
                            {
                                Console.Write("请输入班级：");
                                string banji1 = Console.ReadLine();
                                if (banji1 == "一班")
                                {
                                    cla = "C001";
                                    break;
                                }
                                else if (banji1 == "二班")
                                {
                                    cla = "C002";
                                    break;
                                }
                                else if (banji1 == "三班")
                                {
                                    cla = "C003";
                                    break;
                                }
                                else if (banji1 == "四班")
                                {
                                    cla = "C004";
                                    break;
                                }
                                else
                                {
                                    Console.WriteLine("输入班级有误，请重新输入！");
                                    continue;
                                }
                            }
                            cmd.CommandText = "insert into users values(‘" + uname + "‘,‘" + pwd + "‘,‘" + nick + "‘,‘" + sex + "‘,‘" + bir + "‘,‘" + nation + "‘,‘" + cla + "‘)";
                            conn.Open();
                            int count = cmd.ExecuteNonQuery();
                            conn.Close();
                            if (count > 0)
                            {
                                Console.WriteLine("添加成功！");
                            }
                            else
                            {
                                Console.WriteLine("添加失败！");
                            }
                  
                        }
                        else//用户名重复
                        {
                            Console.WriteLine("该用户名已存在！");
                            continue;
                        }
                    }
                    else if (code == 3)
                    { 
                    
                    }
                    else if (code == 4)
                    {
                        Console.Write("请输入想要删除的用户名：");
                        string user = Console.ReadLine();
                        cmd.CommandText = "select ids,UsersName,PassWord,NickName,Sex,Birthday,(select NationName from Nation where NationCode=Users.Nation) as ‘Nation‘ ,ClassName as ‘Class‘ from Users join Class on Class.ClassCode=Users.Class where UsersName=‘" + user + "‘";
                        conn.Open();
                        SqlDataReader dr = cmd.ExecuteReader();

                        if (dr.HasRows)
                        {
                            conn.Close();
                            //数据存在情况下
                            Console.WriteLine("查到此数据，是否要执行删除操作，如果要删除请输入：1");
                            int sr = Convert.ToInt32(Console.ReadLine());

                            if (sr == 1)
                            {
                                cmd.CommandText = "delete from Users where UsersName=‘" + user + "‘";
                                conn.Open();
                                int s = cmd.ExecuteNonQuery();//执行操作，并记录受影响的行数
                                if (s > 0)
                                    Console.WriteLine("删除成功。");
                                else
                                {
                                    Console.WriteLine("删除失败");
                                }
                            }
                            else
                            {
                                Console.WriteLine("取消删除。");
                            }
                        }
                        else
                        {
                            //数据不存在
                            Console.WriteLine("输入的编号错误！");
                        }
                        conn.Close();
                    }
                    else
                    {
                        Console.WriteLine("无此操作序号有误，请重新输入！");
                        continue;
                    }
                }
                //catch
                //{
                //    Console.WriteLine("输入有误，请重新输入！");
                //    continue;
                //}
                Console.ReadKey();
            }
        }
    }