ado.net增删改查练习

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();
            for (; ; )
            {
                Console.Write("请输入你想要的操作序号（1、删除 2、添加 3、更改 4、查看）");
                try
                {
                    
                    int code = int.Parse(Console.ReadLine());
                    if (code == 1)
                    {
                        //删除
                        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"] + "	" + dr["UserName"] + "	" + dr["PassWord"] + "	" + dr["NickName"] + "	" + (Convert.ToBoolean(dr["Sex"]) ? "男" : "女") + "	" + Convert.ToDateTime(dr["Birthday"]).ToString("yyyy年MM月dd日") + "	" + dr["Nation"] + "	" + 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();
                    }
                    else if (code == 2)
                    {
                        //添加
                        Console.Write("请输入想要添加的用户名：");
                        string uname = Console.ReadLine();
                        cmd.CommandText = "select * from Users";
                        conn.Open();
                        SqlDataReader dr = cmd.ExecuteReader();
                        int biao = 0;
                        //查询用户名是否重复
                        if (dr.HasRows)
                        {
                            while (dr.Read())
                            {
                                if (dr["UserName"].ToString() == uname)
                                {
                                    biao++;
                                    break;
                                }
                            }
                        }
                        conn.Close();
                        if (biao == 0)//用户名无重复
                        {
                            string pwd;
                            for (; ; )
                            {
                                Console.Write("请输入密码（6到18位）：");
                                string pwd1 = Console.ReadLine();
                                if (pwd1.Length >= 6 && pwd1.Length <= 18)
                                {
                                    pwd = pwd1;
                                    break;
                                }
                                else
                                {
                                    Console.WriteLine("密码长度不正确，请重新输入！");
                                    continue;
                                }
                            }
                            Console.Write("请输入昵称：");
                            string nick = Console.ReadLine();
                            string sex;
                            for (; ; )
                            {
                                Console.Write("请输入性别（请输入男女或者0、1）：");
                                string sex1 = Console.ReadLine();
                                if(sex1=="0"||sex1=="1")
                                {
                                    sex = sex1;
                                    break;
                                }
                                else if (sex1 == "男" || sex1 == "女")
                                {
                                    if (sex1 == "男")
                                    {
                                        sex = "1";
                                        break;
                                    }
                                    else
                                    {
                                        sex = "0";
                                        break;
                                    }
                                }
                                else
                                {
                                    Console.WriteLine("性别输入有误，请重新输入！");
                                    continue;
                                }
                            }
                            string bir;
                            for (; ; )
                            {
                                Console.Write("请输入生日：");
                                try
                                {
                                    DateTime dt = DateTime.Parse(Console.ReadLine());
                                    bir = dt.ToString();
                                    break;
                                }
                                catch
                                {
                                    Console.WriteLine("生日日期输入有误，请重新输入！");
                                    continue;
                                }
                            }
                            string nation;
                            for (; ; )
                            {
                                Console.Write("请输入民族：");
                                string nation1 = Console.ReadLine();
                                if(nation1=="汉族"||nation1=="汉")
                                {
                                    nation = "N001";
                                    break;
                                }
                                else if (nation1 == "满族" || nation1 == "满")
                                {
                                    nation = "N002";
                                    break;
                                }
                                else if (nation1 == "藏族" || nation1 == "藏")
                                {
                                    nation = "N003";
                                    break;
                                }
                                else if (nation1 == "彝族" || nation1 == "彝")
                                {
                                    nation = "N004";
                                    break;
                                }
                                else
                                {
                                    Console.WriteLine("输入民族有误，请重新输入！");
                                    continue;
                                }
                            }
                            string cla;
                            for (; ; )
                            {
                                Console.Write("请输入班级：");
                                string cla1 = Console.ReadLine();
                                if(cla1=="一班"||cla1=="一")
                                {
                                    cla = "C001";
                                    break;
                                }
                                else if (cla1 == "二班" || cla1 == "二")
                                {
                                    cla = "C002";
                                    break;
                                }
                                else if (cla1 == "三班" || cla1 == "三")
                                {
                                    cla = "C003";
                                    break;
                                }
                                else if (cla1 == "四班" || cla1 == "四")
                                {
                                    cla = "C004";
                                    break;
                                }
                                else
                                {
                                    Console.WriteLine("输入班级有误，请重新输入！");
                                    continue;
                                }
                            }
                            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("该用户名已存在！");
                        }
                    }
                    else if(code==3)
                    {
                        //更改
                        Console.Write("请输入想要更改的用户名：");
                        string user = Console.ReadLine();
                        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();
                        if (dr.HasRows)
                        {
                            while (dr.Read())
                            {
                                Console.WriteLine(dr["ids"] + "	" + dr["UserName"] + "	" + dr["PassWord"] + "	" + dr["NickName"] + "	" + (Convert.ToBoolean(dr["Sex"]) ? "男" : "女") + "	" + Convert.ToDateTime(dr["Birthday"]).ToString("yyyy年MM月dd日") + "	" + dr["Nation"] + "	" + dr["Class"]);
                            }
                            conn.Close();
                            Console.Write("是否更改此条数据？(Y/N)");
                            string yn = Console.ReadLine();
                            if (yn == "y")
                            {
                                string pwd;
                                for (; ; )
                                {
                                    Console.Write("请输入密码（6到18位）：");
                                    string pwd1 = Console.ReadLine();
                                    if (pwd1.Length >= 6 && pwd1.Length <= 18)
                                    {
                                        pwd = pwd1;
                                        break;
                                    }
                                    else
                                    {
                                        Console.WriteLine("密码长度不正确，请重新输入！");
                                        continue;
                                    }
                                }
                                Console.Write("请输入昵称：");
                                string nick = Console.ReadLine();
                                string sex;
                                for (; ; )
                                {
                                    Console.Write("请输入性别（请输入男女或者0、1）：");
                                    string sex1 = Console.ReadLine();
                                    if (sex1 == "0" || sex1 == "1")
                                    {
                                        sex = sex1;
                                        break;
                                    }
                                    else if (sex1 == "男" || sex1 == "女")
                                    {
                                        if (sex1 == "男")
                                        {
                                            sex = "1";
                                            break;
                                        }
                                        else
                                        {
                                            sex = "0";
                                            break;
                                        }
                                    }
                                    else
                                    {
                                        Console.WriteLine("性别输入有误，请重新输入！");
                                        continue;
                                    }
                                }
                                string bir;
                                for (; ; )
                                {
                                    Console.Write("请输入生日：");
                                    try
                                    {
                                        DateTime dt = DateTime.Parse(Console.ReadLine());
                                        bir = dt.ToString();
                                        break;
                                    }
                                    catch
                                    {
                                        Console.WriteLine("生日日期输入有误，请重新输入！");
                                        continue;
                                    }
                                }
                                string nation;
                                for (; ; )
                                {
                                    Console.Write("请输入民族：");
                                    string nation1 = Console.ReadLine();
                                    if (nation1 == "汉族" || nation1 == "汉")
                                    {
                                        nation = "N001";
                                        break;
                                    }
                                    else if (nation1 == "满族" || nation1 == "满")
                                    {
                                        nation = "N002";
                                        break;
                                    }
                                    else if (nation1 == "藏族" || nation1 == "藏")
                                    {
                                        nation = "N003";
                                        break;
                                    }
                                    else if (nation1 == "彝族" || nation1 == "彝")
                                    {
                                        nation = "N004";
                                        break;
                                    }
                                    else
                                    {
                                        Console.WriteLine("输入民族有误，请重新输入！");
                                        continue;
                                    }
                                }
                                string cla;
                                for (; ; )
                                {
                                    Console.Write("请输入班级：");
                                    string cla1 = Console.ReadLine();
                                    if (cla1 == "一班" || cla1 == "一")
                                    {
                                        cla = "C001";
                                        break;
                                    }
                                    else if (cla1 == "二班" || cla1 == "二")
                                    {
                                        cla = "C002";
                                        break;
                                    }
                                    else if (cla1 == "三班" || cla1 == "三")
                                    {
                                        cla = "C003";
                                        break;
                                    }
                                    else if (cla1 == "四班" || cla1 == "四")
                                    {
                                        cla = "C004";
                                        break;
                                    }
                                    else
                                    {
                                        Console.WriteLine("输入班级有误，请重新输入！");
                                        continue;
                                    }
                                }
                                cmd.CommandText = "update User set Password='"+pwd+"',NickName='"+nick+"',Sex="+sex+",Birthday='"+bir+"',Nation='"+nation+"',Class='"+cla+"' 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();
                    }
                    else if(code==4)
                    {
                        //查看
                        Console.Write("请输入想要查看的用户名（输入*代表查看全部）：");
                        string user = Console.ReadLine();
                        if (user != "*")
                        {
                            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 + "'";
                        }
                        else
                        {
                            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";
                        }
                        conn.Open();
                        SqlDataReader dr = cmd.ExecuteReader();
                        if (dr.HasRows)
                        {
                            while (dr.Read())
                            {
                                Console.WriteLine(dr["ids"] + "	" + dr["UserName"] + "	" + dr["PassWord"] + "	" + dr["NickName"] + "	" + (Convert.ToBoolean(dr["Sex"]) ? "男" : "女") + "	" + Convert.ToDateTime(dr["Birthday"]).ToString("yyyy年MM月dd日") + "	" + dr["Nation"] + "	" + dr["Class"]);
                            }
                            conn.Close();
                        }
                        else
                        {
                            Console.WriteLine("查无此条数据！");
                        }
                        conn.Close();
                    }
                    else
                    {
                        Console.WriteLine("无此操作序号有误，请重新输入！");
                        continue;
                    }
                }
                catch
                {
                    Console.WriteLine("输入有误，请重新输入！");
                    continue;
                }
            }
        }
    }
}