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

namespace ConsoleApplication2
{
    class Program
    {
        static void Main(string[] args)
        {
            //删除
            //创建数据库连接对象，并编写连接字符串，注意连接字符串不要写错
            SqlConnection conn = new SqlConnection("server=.;database=Data0928;user=sa;pwd=123");
            //创建数据库操作对象，创建过程是与刚创建的连接对象匹配起来
            SqlCommand cmd = conn.CreateCommand();
            Console.Write("请输入想要删除的用户名：");
            string user = Console.ReadLine();
            //编写操作语句 TSQL语句
            cmd.CommandText = "select ids,UserName,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 UserName=‘"+user+"‘";
            //数据库连接打开，准备执行操作
            conn.Open();
            SqlDataReader dr = cmd.ExecuteReader();//使用SqlDataReader类接受查询的数据
            if (dr.HasRows)//判断dr里是否有数据 返回布尔类型
            {
                while(dr.Read())//循环每一行 当超出时返回false
                {
                    Console.WriteLine(dr["ids"]+"\t"+dr["UserName"]+"\t"+dr["PassWord"]+"\t"+dr["NickName"]+"\t"+(Convert.ToBoolean(dr["Sex"])?"男":"女")+"\t"+Convert.ToDateTime(dr["Birthday"]).ToString("yyyy年MM月dd日")+"\t"+dr["Nation"]+"\t"+dr["Class"]);
                }
                //关闭数据库连接
                conn.Close();
                Console.Write("是否确定删除此条数据？(Y/N)");
                string yn = Console.ReadLine();
                if (yn == "y")
                {
                    cmd.CommandText = "delete from Users where UserName=‘"+user+"‘";
                    conn.Open();
                    int i = cmd.ExecuteNonQuery();//执行操作，并记录受影响的行数
                    if (i > 0)
                        Console.WriteLine("删除成功。");
                    else
                    {
                        Console.WriteLine("删除失败");
                    }
                    conn.Close();
                }
                else if (yn == "n")
                {
                    Console.WriteLine("取消删除。");
                }
                else
                {
                    Console.WriteLine("输入有误！");
                }
            }
            else
            {
                Console.WriteLine("查无此条数据！");
            }
            conn.Close();

            //添加
            Console.Write("请输入想要添加的用户名：");
            string uname = Console.ReadLine();
            cmd.CommandText = "select * from Users";
            conn.Open();
            dr = cmd.ExecuteReader();
            int biao = 0;
            //查询用户名是否重复
            if(dr.HasRows)
            {
                while(dr.Read())
                {
                    if(dr["UserName"].ToString()==uname)
                    {
                        biao++;
                        break;
                    }
                }
            }
            conn.Close();
            if (biao == 0)//用户名无重复
            {
                Console.Write("请输入密码：");
                string pwd = Console.ReadLine();
                Console.Write("请输入昵称：");
                string nick = Console.ReadLine();
                Console.Write("请输入性别：");
                string sex = Console.ReadLine();
                Console.Write("请输入生日：");
                string bir = Console.ReadLine();
                Console.Write("请输入民族：");
                string nation = Console.ReadLine();
                Console.Write("请输入班级：");
                string cla = Console.ReadLine();
                cmd.CommandText = "insert into Users values(‘" + uname + "‘,‘" + pwd + "‘,‘" + nick + "‘,‘" + sex + "‘,‘" + bir + "‘,‘" + nation + "‘,‘" + cla + "‘)";
                conn.Open();
                int s = cmd.ExecuteNonQuery();
                if (s > 0)
                {
                    Console.WriteLine("添加成功！");
                }
                else
                {
                    Console.WriteLine("添加失败！");
                }
                conn.Close();
            }
            else//用户名重复
            {
                Console.WriteLine("该用户名已存在！");
            }
            Console.ReadKey();
        }
    }
}