Ado.Net 数据库增删改查（联合版）

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

namespace 增删改查综合练习
{
    class Program
    {
        /*
         * 需求：
         * 1、用户输入代号，查询一条数据
         * 2、输出提示：1删除 2修改 3添加
         *    输入1：删除这条数据
         *    输入2：让用户输入修改的其他值，输入完成修改这条数据
         *    输入3：让用户输入所有的列，保存到数据
         */

        /// <summary>
        /// 民族名称转换名族代号
        /// </summary>
        /// <param name="nationName">民族名称</param>
        /// <returns>民族代号</returns>
        public static string NationToCode(string nationName)
        {
            SqlConnection conn = new SqlConnection("server=.; database=mydb; user=sa; pwd=ray; ");
            SqlCommand cmd = conn.CreateCommand();
            cmd.CommandText = "select *from Nation where Name=@nationName";
            cmd.Parameters.Clear();
            cmd.Parameters.AddWithValue("@nationName",nationName);
            conn.Open();
            SqlDataReader dr = cmd.ExecuteReader();
            string nationCode = "n001";
            if (dr.HasRows)
            {
                dr.Read();
                nationCode = Convert.ToString(dr[0]);
            }
            conn.Close();
            return nationCode; 
        }


        /// <summary>
        /// 民族代号转换成名称
        /// </summary>
        /// <param name="nationCode">民族代号</param>
        /// <returns>民族名称</returns>
        public static string CodeToNation(string nationCode)
        {
            string nation = "汉族";
            SqlConnection conn = new SqlConnection("server=.; database=mydb; user=sa; pwd=ray; ");
            SqlCommand cmd = conn.CreateCommand();
            cmd.CommandText = "select *from Nation where Code=@nationCode";
            cmd.Parameters.Clear();
            cmd.Parameters.AddWithValue("@nationCode",nationCode);
            conn.Open();
            SqlDataReader dr = cmd.ExecuteReader();
            if (dr.HasRows)
            {
                dr.Read();
                nation = Convert.ToString(dr[1]);
            }
            conn.Close();
            return nation;
        } 

        static void Main(string[] args)
        {
            while (true)
            {
                //1、用户输入代号，查询数据
                Console.WriteLine("请输入要查询的编号：");
                string code = Console.ReadLine();
                SqlConnection conn = new SqlConnection("server=.; database=mydb; user=sa; pwd=ray; ");
                SqlCommand cmd = conn.CreateCommand();
                cmd.CommandText = "select *from Info where Code=@code";
                cmd.Parameters.Clear();
                cmd.Parameters.AddWithValue("@code", code);
                conn.Open();
                SqlDataReader dr = cmd.ExecuteReader();
                if (dr.HasRows)
                {
                    while (dr.Read())
                    {
                        string gender = Convert.ToString(dr["Sex"]) == "True" ? "男" : "女";
                        int age = DateTime.Now.Year - Convert.ToDateTime(dr["Birthday"]).Year;
                        string birthday = Convert.ToDateTime(dr["Birthday"]).ToString("yyyy年MM月dd日");
                        Console.WriteLine
　　　　　　　　　　　　　　　　("查询数据为：\n" + dr["Code"] + "\t" + dr["Name"] + "\t" 
　　　　　　　　　　　　　　　　　　+ gender + "\t" + CodeToNation(Convert.ToString(dr["Nation"])) 
　　　　　　　　　　　　　　　　　　+ "\t" + birthday+"\t"+age+"岁");
                    }

                    //2、用户输入操作 1删除 2修改 3添加
                    while (true)
                    {
                        Console.WriteLine("请输入要执行的操作:1删除 2修改 3添加");
                        string n = Console.ReadLine();

                        #region 1、删除
                        if (n == "1")
                        {
                            cmd.CommandText = "delete from Info where Code=@code";
                            cmd.Parameters.Clear();
                            cmd.Parameters.AddWithValue("@code",code);
                            dr.Close();
                            int delete=cmd.ExecuteNonQuery();
                            if (delete > 0)
                            {
                                Console.WriteLine("删除成功，按任意键刷新数据");
                                Console.ReadKey();
                                Console.Clear();
                                //刷新数据
                                Console.WriteLine("=================刷新数据=================");
                                cmd.CommandText = "select *from Info";
                                SqlDataReader dr1 = cmd.ExecuteReader();
                                if (dr1.HasRows)
                                {
                                    while (dr1.Read())
                                    {
                                        string genderSC = Convert.ToString(dr1["Sex"]) == "True" ? "男" : "女";
                                        int age = DateTime.Now.Year - Convert.ToDateTime(dr1["Birthday"]).Year;
                                        string birthday = Convert.ToDateTime(dr1["Birthday"]).ToString("yyyy年MM月dd日");
                                        Console.WriteLine
                                            ("编号:{0}\n 姓名：{1}\n 性别：{2}\n 民族：{3}\n 生日：{4}\n 年龄：{5}岁"
                                            ,dr1["Code"],dr1["Name"], genderSC,CodeToNation(Convert.ToString(dr1["Nation"])),birthday,age);
                                    }
                                }
                                else 
                                {
                                    Console.WriteLine("刷新失败");
                                }
                            }
                            else 
                            {
                                Console.WriteLine("删除失败");
                            }
                            break;
                        }
                        #endregion

                        #region 2、修改
                        if (n == "2")
                        {
                            Console.WriteLine("请输入要修改的姓名：");
                            string nameXG = Console.ReadLine();
                            Console.WriteLine("请输入要修改的性别：");
                            bool genderXG = Convert.ToBoolean(Console.ReadLine()=="男" ? true:false);
                            Console.WriteLine("请输入要修改的民族：");
                            string nationXG = Console.ReadLine();
                            Console.WriteLine("请输入要修改的生日：");
                            string birthdayXG = Console.ReadLine();

                            cmd.CommandText = "update Info set Name=@name,Sex=@gender,Nation=@nationCode,Birthday=@birthday where Code=@code";
                            cmd.Parameters.Clear();
                            cmd.Parameters.AddWithValue("@code",code);
                            cmd.Parameters.AddWithValue("@name",nameXG);
                            cmd.Parameters.AddWithValue("@gender",genderXG);
                            cmd.Parameters.AddWithValue("@nationCode",NationToCode(nationXG));
                            cmd.Parameters.AddWithValue("@birthday",birthdayXG);
                            dr.Close();
                            int nXG=cmd.ExecuteNonQuery();
                            if (nXG > 0)
                            {
                                Console.WriteLine("修改成功，按任意键刷新数据");
                                Console.ReadKey();
                                Console.Clear();

                                //刷新数据
                                Console.WriteLine("=================刷新数据====================");
                                cmd.CommandText = "select *from Info";
                                SqlDataReader dr1 = cmd.ExecuteReader();
                                if (dr1.HasRows)
                                {
                                    while (dr1.Read())
                                    {
                                        string gender = Convert.ToString(dr1["Sex"]) == "true" ? "男" : "女";
                                        int age = DateTime.Now.Year - Convert.ToDateTime(dr1["Birthday"]).Year;
                                        string birthday = Convert.ToDateTime(dr1["Birthday"]).ToString("yyyy年MM月dd日");
                                        Console.WriteLine
                                            (" 编号:{0}\n 姓名：{1}\n 性别：{2}\n 民族：{3}\n 生日：{4}\n 年龄：{5}岁"
                                            , dr1["Code"], dr1["Name"], genderXG, CodeToNation(Convert.ToString(dr1["Nation"])), birthday, age);
                                        Console.WriteLine();
                                    }
                                }
                                else
                                {
                                    Console.WriteLine("刷新失败");
                                }
                            }
                            else 
                            {
                                Console.WriteLine("修改失败");
                            }

                            break;
                        }
                        #endregion

                        #region 3、添加
                        if (n == "3")
                        {
                            Console.WriteLine("请输入要添加的编号：");
                            string codeTJ = Console.ReadLine();
                            Console.WriteLine("请输入要添加的姓名：");
                            string nameTJ = Console.ReadLine();
                            Console.WriteLine("请输入要添加的性别：");
                            bool genderTJ = Convert.ToBoolean(Console.ReadLine()=="男"?true:false);
                            Console.WriteLine("请输入要添加的民族：");
                            string nationTJ = Console.ReadLine();
                            Console.WriteLine("请输入要添加的生日：");
                            DateTime birthdayTJ = Convert.ToDateTime(Console.ReadLine());

                            cmd.CommandText = "insert into Info values(@codeTJ,@nameTJ,@genderTJ,@nationTJ,@birthdayTJ)";
                            cmd.Parameters.Clear();
                            cmd.Parameters.AddWithValue("@codeTJ", codeTJ);
                            cmd.Parameters.AddWithValue("@nameTJ", nameTJ);
                            cmd.Parameters.AddWithValue("@genderTJ", genderTJ);
                            cmd.Parameters.AddWithValue("@nationTJ", NationToCode(nationTJ));
                            cmd.Parameters.AddWithValue("@birthdayTJ", birthdayTJ);
                            dr.Close();
                            int nTJ=cmd.ExecuteNonQuery();
                            if (nTJ > 0)
                            {
                                Console.WriteLine("添加成功，按任意键刷新数据");
                                Console.ReadKey();
                                Console.Clear();

                                //刷新数据
                                Console.WriteLine("==========================刷新数据========================");
                                cmd.CommandText = "select *from Info";
                                SqlDataReader dr1 = cmd.ExecuteReader();
                                if (dr1.HasRows)
                                {
                                    while (dr1.Read())
                                    {
                                        string gender = Convert.ToString(dr1["Sex"]) == "true" ? "男"  :"女" ;
                                        int age = DateTime.Now.Year - Convert.ToDateTime(dr1["Birthday"]).Year;
                                        string birthday = Convert.ToDateTime(dr1["Birthday"]).ToString("yyyy年MM月dd日");
                                        Console.WriteLine
                                            (" 编号:{0}\n 姓名：{1}\n 性别：{2}\n 民族：{3}\n 生日：{4}\n 年龄：{5}岁"
                                            , dr1["Code"], dr1["Name"], genderTJ, CodeToNation(Convert.ToString(dr1["Nation"])), birthday, age);
                                        Console.WriteLine();
                                    }          
                                }
                                else
                                {
                                    Console.WriteLine("刷新失败");
                                }
                            }
                            else 
                            {
                                Console.WriteLine("添加失败");
                            }
                            break;
                        }
                        #endregion

                        else
                        {
                            Console.WriteLine("输入错误");
                        }
                    }
                    break;
                }
                else
                {
                    Console.WriteLine("编号输入不正确");
                }
                conn.Close();  
            }
            Console.ReadKey();
        }
    }
}