C#与数据库链接---小练3--插入语句的验证

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

namespace ConsoleApplication2
{
    class Charuyanzheng
    {
        public const string AB = "server=.;database=wo0505;uid=sa;pwd=123";

        public static void Show()
        {
            SqlConnection con = new SqlConnection(AB);
            con.Open();

            SqlCommand cmd = con.CreateCommand();
            cmd.CommandText = "select * from Info";
            SqlDataReader dr = cmd.ExecuteReader();
            while (dr.Read())
            {
                string sex = ((bool)dr["Sex"]) ? "0" : "1";
                Console.WriteLine(dr["Code"].ToString() + " " + dr["Name"].ToString() + " " + sex + " " + dr["Nation"].ToString() + " " + dr["Birthday"].ToString());
            }

            con.Close();
        }//显示数据库中该表的所有数据

        public static void Main(string[] args)
        {
            Show();
            Shuju();
            Show();
            Console.ReadLine();
        }//主函数，进行调用个函数
        public static void Shuju()
        {
           
            Console.WriteLine("请填写相关数据进行输入：");
            Console.Write("编号：");
            string aa=Console.ReadLine();
            Console.Write("姓名：");
            string bb = Console.ReadLine();
            Console.Write("性别(0:女、1:男)：");
            string cc = Console.ReadLine();
            Console.Write("民族：");
            string dd = Console.ReadLine();
            Console.Write("生日：");
            string ee = Console.ReadLine();
    
            string Ok = Panduan(aa,bb,cc,dd,ee);
            if (Ok == "you")
            {
                Add(aa, bb, cc, dd, ee);
            }
            else
            {
                Console.WriteLine("因输入出现错误，故不能执行操作！");
            }
        }
        //输入要插入数据表的内容，并进行调用，判断所有输入格式是否正确，在调用插入函数
        public static string Panduan(string aa,string bb,string cc,string dd,string ee)
        {
            string Ok = "you";
            //以下两行是对主外键的判断验证
            string Ok0 = Duancode(aa);
            string Ok1 = Duannation(dd);
            //以下两行是对输入格式的验证
            string Ok2 = Duansex("Sex",cc);
            string Ok3 = Duanbirthday("Birthday",ee);
            if (Ok0 == "wu" && Ok1 == "you" && Ok2 == "ke" && Ok3 == "ke")
            { Ok = "you"; }
            else { Ok = "bu"; }
            return Ok;
        }
        //主要是进行调用各部分的判断函数
        public static string Duancode(string co)
        {
            string ok0 = "wu";
            SqlConnection con = new SqlConnection(AB);
            con.Open();

            SqlCommand cmd = con.CreateCommand();
            cmd.CommandText = "select * from Info";
            SqlDataReader dr = cmd.ExecuteReader();
            while (dr.Read())
            {
                if (dr["Code"].ToString() == co)
                {ok0 = "bu";break; }
                else
                { ok0 = "wu";}
            }
            con.Close();
            if(ok0!="wu")
            Console.WriteLine("您输入的编号已存在，请重新输入！");
            return ok0;
        }//判断编号是否已存在，存在的不能再添加
        public static string Duannation(string na)
        {
            string ok1 = "you";
            SqlConnection con = new SqlConnection(AB);
            con.Open();

            SqlCommand cmd = con.CreateCommand();
            cmd.CommandText = "select * from Nation";
            SqlDataReader dr = cmd.ExecuteReader();
            while(dr.Read())
            {
                if (dr["Code"].ToString() == na)
                {
                    ok1 = "you"; break;
                }
                else { ok1 = "bu"; }
            }

            con.Close();
            if (ok1 != "you")
                Console.WriteLine("您输入的民族不存在，请重新输入！");

            return ok1;
        }//判断民族是否已存在，不存在的不能添加
        public static string Duansex(string sex,string se)
        {
            string ok2 = "ke";
            if (sex == "Sex")
            {
                if (se == "0" || se == "1")
                { ok2 = "ke"; }
                else
                {
                    ok2 = "bu";
                    Console.WriteLine("您输入的性别格式出现错误,请重新输入！");
                }
            }
            else { Console.WriteLine("文件出现错误！"); }
            return ok2;
        }//判断性别输入格式是否正确
        public static string Duanbirthday(string birthday,string bi)
        {
            string ok3 = "ke";

            if (birthday == "Birthday")
            {
                try
                {  
                    Convert.ToDateTime(bi);//判断日期的格式
                    ok3 = "ke";
                }
                catch
                {
                    ok3 = "bu";
                    Console.WriteLine("你输入的生日格式出现错误，请重新输入");
                }
            }
            else { Console.WriteLine("文件出现错误！"); }
            return ok3;
        }//判断生日输入格式是否正确
        public static void Add(string aa,string bb,string cc,string dd,string ee)
        {
            SqlConnection con = new SqlConnection(AB);
            con.Open();
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = con;
            cmd.CommandText = "insert into Info values ('"+aa+"','"+bb+"','"+cc+"','"+dd+"','"+ee+"')";
            cmd.ExecuteNonQuery();
            con.Close();
        }//插入数据函数
    }//插入数据，进行简单验证后，再插入
}