数据访问-----ADO.NET 练习2

一、向Info中插入数据。
1.从界面输入信息。
2.输入内容的检查：基本格式的检查，主外键的检查。
3.执行插入

 class Program
    {
        //定义常量
        public const string CONNECTIONSTRING = "server=.;database=mydb;uid=sa;pwd=5587725";
        //检查格式
        public static bool check(string ColumnName,string value)
        {
            if (ColumnName=="sex")
            {
                if (value=="0"||value=="1")
                {
                    return true;
                }
                else
                {
                    return false;
                    Console.WriteLine("输入的性别格式不正确");
                }
            }
            else if (ColumnName=="Birthday")
            {
                try
                {
                    Convert.ToDateTime(value);
                    return true;
                }
                catch
                {

                    Console.WriteLine("生日格式不正确");
                    return false;
                }
            }
            else
            {
                return true;
            }
        }
        //检查主键
        public static bool checkPK(string pk)
        {
            bool HasPk = true;
            SqlConnection conn = new SqlConnection(CONNECTIONSTRING);
            conn.Open();
            SqlCommand cmd = conn.CreateCommand();
            cmd.CommandText = "select * from info where code=‘"+pk+"‘";
            SqlDataReader dr = cmd.ExecuteReader();
            if (dr.HasRows)
            {
                HasPk = false;
                Console.WriteLine("主键已存在");
            }
            else
            {
                HasPk = true;
            }
            conn.Close();
            return HasPk;
        }
        //检查民族
        public static bool checkNation(string nation)
        {
            bool HasNation = true;
            SqlConnection conn = new SqlConnection(CONNECTIONSTRING);
            conn.Open();
            SqlCommand cmd = conn.CreateCommand();
            cmd.CommandText = "select * from info where nation=‘"+nation+"‘";
            SqlDataReader dr = cmd.ExecuteReader();
            if (dr.HasRows)
            {
                HasNation = true;
            }
            else
            {
                Console.WriteLine("民族代号输入不正确");
                HasNation = false;
            }
            conn.Close();
            return HasNation;
        }
        //检查综合
        public static bool checkPut(string code,string sex,string nation,string birthday)
        {
            //检查
            //1.对输入的数据格式检查
            bool isOK = true;
            bool isOK1 = check("Sex", sex);
            bool isOK2 = check("Birthday", birthday);
            //2.对主外键的检查
            bool isOK3 = checkPK(code);
            bool isOK4 = checkNation(nation);
            isOK = isOK && isOK1 && isOK2 && isOK3 && isOK4;
            return isOK;
        }
        //插入
        public static void ADDinfo(string code,string name,string sex,string nation,string birthday)
        {
            SqlConnection conn = new SqlConnection(CONNECTIONSTRING);
            conn.Open();
            SqlCommand cmd = conn.CreateCommand();
            cmd.CommandText = "insert into info values(‘"+code+"‘,‘"+name+"‘,‘"+sex+"‘,‘"+nation+"‘,‘"+birthday+"‘)";
            cmd.ExecuteNonQuery();
            conn.Close();
        }

        static void Main(string[] args)
        {
            //输入
            Console.Write("代号：");
            string code = Console.ReadLine();
            Console.Write("姓名：");
            string name = Console.ReadLine();
            Console.Write("性别（0-女，1-男）：");
            string sex = Console.ReadLine();
            Console.Write("民族：");
            string nation = Console.ReadLine();
            Console.Write("生日：");
            string birthday = Console.ReadLine();
          
            //检查
            bool isOK = checkPut(code,sex,nation,birthday);
            //插入
            if（isOK==true）
            {
                 ADDinfo(code, name, sex, nation, birthday);
            }
        }
    }

二、从Info表中删除数据。
1.显示信息
2.输入要删除的主键
3.检查输入的主键是否存在。
4.执行删除（外键的情况——数据库中使用级联删除解决；写代码先删从表最后删除主表来解决。）
5.显示信息

    class Program
    {
        public const string CONCNNECTIONSTRING = "server=.;database=mydb;uid=sa;pwd=5587725";
        //显示所有人员信息
        public static string GETnation(string code)
        {
            string str = "";
            SqlConnection conn = new SqlConnection(CONCNNECTIONSTRING);
            conn.Open();
            SqlCommand cmd = conn.CreateCommand();
            cmd.CommandText = "select *from nation where code=‘"+code+"‘";
            SqlDataReader dr = cmd.ExecuteReader();
            if (dr.HasRows)
            {
                dr.Read();
                str = dr["name"].ToString();
            }
            else
            {
                str = "";
            }
            conn.Close();
            return str;
        }
        public static void show()
        {
            SqlConnection conn = new SqlConnection(CONCNNECTIONSTRING);
            conn.Open();
            SqlCommand cmd = conn.CreateCommand();
            cmd.CommandText="select * from info";
            SqlDataReader dr = cmd.ExecuteReader();
            while (dr.Read())
            {
                string code = dr["code"].ToString() ;
                string name = dr["name"].ToString() ;
                string sex = ((bool)dr["sex"])?"男":"女" ;
                string nation = GETnation(dr["nation"].ToString()) ;
                string birthday = ((DateTime)dr["birthday"]).ToString("yyyy年MM月dd日") ;
                Console.WriteLine(code+ "\t"+name+ "\t"+sex+ "\t"+nation+ "\t"+birthday+ "\n");
                
            }
            conn.Close();
        }
        //检查主键是否存在
        public static bool checkPK(string pk)
        {
            bool HasPK = true;
            SqlConnection conn = new SqlConnection(CONCNNECTIONSTRING);
            conn.Open();
            SqlCommand cmd = conn.CreateCommand();
            cmd.CommandText = "select * from info where code=‘"+pk+"‘";
            SqlDataReader dr = cmd.ExecuteReader();
            HasPK = dr.HasRows;
            conn.Close();
            return HasPK;
        }
        //删除
        public static void Deleteinfo(string pk)
        {
            SqlConnection conn = new SqlConnection(CONCNNECTIONSTRING);
            conn.Open();
            SqlCommand cmd = conn.CreateCommand();
            cmd.CommandText = "delete from work where infocode=‘" + pk + "‘";
            cmd.ExecuteNonQuery();
            cmd.CommandText = "delete from family where infocode=‘" + pk + "‘";
            cmd.ExecuteNonQuery();
            cmd.CommandText = "delete from info where code=‘"+pk+"‘";
            cmd.ExecuteNonQuery();
            conn.Close();
        }
        static void Main(string[] args)
        {
            //显示
            show();
            //输入要删除的主键
            Console.WriteLine("请输入你要删除的人员代码：");
            string code = Console.ReadLine();
            //检查输入的主键是否存在
            bool isOK = checkPK(code);
           
            //删除
            if (isOK==true)
            {
                Deleteinfo(code);
                Console.WriteLine("删除成功");
            }
            else
            {
                Console.WriteLine("找不到要删除的人员代号，删除失败，请重新检查要删除的人员代号。");
            }
            //显示
            show();
            Console.ReadLine();
        }