数据访问----事务实例

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

namespace ww
{
    class Program
    {
        static void Main(string[] args)
        {
            Console.Write("请选择操作： 1：增加记录 2：删除记录 3：修改记录 4：查询记录
");
            string tr = Console.ReadLine();
            switch (tr)
            {
                case "1":
                    Console.WriteLine("**************************增加记录*****************************");
                    ww.Class1.insert();
                    break;
                case "2":
                    Console.WriteLine("**************************删除记录*****************************");
                    ww.Class2.delete();
                    break;
                case "3":
                    Console.WriteLine("**************************修改Info表记录*****************************");

                    ww.Class3.updeteInfo();
                    break;
                case "4":
                    Console.WriteLine("**************************查询记录*****************************");

                    ww.Class4.chaXun();
                    break;
                default:
                    break;
            }
            Main(args);
        }
    }

    class Class1
    {
        public const string CONSTR = "server=.;database=mydb;uid=sa;pwd=111111";
        public static bool check(string col, string value)
        {
            if (col == "Sex")
            {
                if (value == "0" || value == "1")
                {
                    return true;
                }
                else
                {
                    Console.WriteLine("性别格式不正确");
                    return false;
                }
            }
            else if (col == "Birthday")
            {
                try
                {
                    Convert.ToDateTime(value);
                    return true;
                }
                catch
                {
                    Console.WriteLine("生日格式不正确");
                    return false;
                }
            }
            else
            {
                return true;
            }
        }
        public static bool checkPK(string pk)
        {
            bool notHasPK = true;
            SqlConnection conn = new SqlConnection(CONSTR);
            try
            {
                conn.Open();
                SqlCommand cmd = conn.CreateCommand();
                cmd.CommandText = "select * from info where Code=@pk";
                cmd.Parameters.Clear();
                cmd.Parameters.AddWithValue("@pk", pk);
                SqlDataReader dr = cmd.ExecuteReader();
                if (dr.HasRows)
                {
                    notHasPK = false;
                    Console.WriteLine("主键已存在");
                }

                return notHasPK;

            }
            finally
            {
                conn.Close();
            }
        }
        public static bool checkNation(string nationCode)
        {
            bool checkNation = true;
            SqlConnection conn = new SqlConnection(CONSTR);
            try
            {
                conn.Open();

                SqlCommand cmd = conn.CreateCommand();
                cmd.CommandText = "select * from nation where Code=@nationCode ";
                cmd.Parameters.Clear();
                cmd.Parameters.AddWithValue("@nationCode", nationCode);
                SqlDataReader dr = cmd.ExecuteReader();
                if (dr.HasRows)
                {
                    checkNation = true;
                }
                else
                {
                    checkNation = false;
                    Console.WriteLine("民族编号输入不正确！");
                }

                return checkNation;

            }
            finally
            {
                conn.Close();
            }
        }
        public static void addInfo(string code, string name, string sex, string nation, string birthday)
        {
            SqlConnection conn = new SqlConnection(CONSTR);
            try
            {
                conn.Open();

                SqlCommand cmd = conn.CreateCommand();
                cmd.CommandText = "insert into info values(@code,@name,@sex,@nation,@birthday)";
                cmd.Parameters.Clear();
                cmd.Parameters.AddWithValue("@code", code);
                cmd.Parameters.AddWithValue("@name", name);
                cmd.Parameters.AddWithValue("@sex", sex);
                cmd.Parameters.AddWithValue("@nation", nation);
                cmd.Parameters.AddWithValue("@birthday", birthday);
                cmd.ExecuteNonQuery();

            }
            finally
            {
                conn.Close();

            }

        }
        public static void insert()
        {
            string code, name, sex, nation, birthday;
            do
            {
                Console.Write("编号：");
                code = Console.ReadLine();

            } while (!checkPK(code));
            Console.Write("姓名：");
            name = Console.ReadLine();
            do
            {
                Console.Write("性别(0/1)：");
                sex = Console.ReadLine();
            } while (!check("Sex", sex));
            do
            {
                Console.Write("民族：");
                nation = Console.ReadLine();
            } while (!checkNation(nation));
            do
            {
                Console.Write("生日：");
                birthday = Console.ReadLine();
            } while (!check("Birthday", birthday));
            addInfo(code, name, sex, nation, birthday);
        }
    }

    class Class2
    {
        public const string CONSTR = "server=.;database=mydb;uid=sa;pwd=111111";
        static string getNation(string nation)
        {
            string str = "";
            SqlConnection conn = new SqlConnection(CONSTR);
            try
            {
                conn.Open();

                SqlCommand cmd = conn.CreateCommand();
                cmd.CommandText = "select * from nation where Code=@nation ";
                cmd.Parameters.Clear();
                cmd.Parameters.AddWithValue("@nation", nation);
                SqlDataReader dr = cmd.ExecuteReader();
                if (dr.HasRows)
                {
                    dr.Read();
                    str = dr["Name"].ToString();
                }
                else
                {
                    str = "";
                }


                return str;

            }
            finally
            {
                conn.Close();

            }
        }
        public static void Show()
        {
            SqlConnection conn = new SqlConnection(CONSTR);
            try
            {
                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 + "	" + name + "	" + sex + "	" + nation + "	" + birthday);
                }


            }
            finally
            {
                conn.Close();

            }
        }
        public static bool checkPK(string pk)
        {
            bool hasPK = true;
            SqlConnection conn = new SqlConnection(CONSTR);
            try
            {
                conn.Open();

                SqlCommand cmd = conn.CreateCommand();
                cmd.CommandText = "select * from info where code=@pk";
                cmd.Parameters.Clear();
                cmd.Parameters.AddWithValue("@pk", pk);
                SqlDataReader dr = cmd.ExecuteReader();
                hasPK = dr.HasRows;

                return hasPK;

            }
            finally
            {
                conn.Close();

            }
        }
        public static void deleteInfo(string pk)
        {
            SqlConnection conn = new SqlConnection(CONSTR);
            conn.Open();
                SqlTransaction sw = conn.BeginTransaction();
                SqlCommand cmd = conn.CreateCommand();
                cmd.Transaction = sw;

            try
            {
                cmd.CommandText = "delete from family where InfoCode=@pk";
                cmd.Parameters.Clear();
                cmd.Parameters.AddWithValue("@pk", pk);
                cmd.ExecuteNonQuery();
                cmd.CommandText = "delete from work where InfoCode=@pk";
                cmd.Parameters.Clear();
                cmd.Parameters.AddWithValue("@pk", pk);
                cmd.ExecuteNonQuery();
                cmd.CommandText = "delete from info where Code=@pk";
                cmd.Parameters.Clear();
                cmd.Parameters.AddWithValue("@pk", pk);
                cmd.ExecuteNonQuery();
                sw.Commit();
            }
            catch
            {
                sw.Rollback();
            }
            finally
            {
                conn.Close();

            }
        }
        public static void delete()
        {
            Show();
            Console.Write("输入要删的人员编码：");
            string code = Console.ReadLine();
            if (checkPK(code))
            {
                deleteInfo(code);
                Console.WriteLine("删除成功");
            }
            else
            {
                Console.WriteLine("找不到要删除的人员编码，删除失败！");
            }
            Show();
        }
    }

    class Class3
    {
        public const string CONSTR = "server=.;database=mydb;uid=sa;pwd=111111";
        public static void updeteInfo()
        {
            string code, name, sex, nation, birthday;
            Console.WriteLine("*************************通过编号修改info表记录*************************");
            do
            {
                Console.Write("编号：");
                code = Console.ReadLine();
            } while (ww.Class1.checkPK(code));
            Console.Write("姓名：");
            name = Console.ReadLine();
            do
            {
                Console.Write("性别(0/1)：");
                sex = Console.ReadLine();
            } while (!ww.Class1.check("Sex", sex));
            do
            {
                Console.Write("民族：");
                nation = Console.ReadLine();
            } while (!ww.Class1.checkNation(nation));
            do
            {
                Console.Write("生日：");
                birthday = Console.ReadLine();
            } while (!ww.Class1.check("Birthday", birthday));
            SqlConnection scon = new SqlConnection(CONSTR);
            try
            {
                scon.Open();
                SqlCommand scmd = scon.CreateCommand();
                scmd.CommandText = "update info set Name=@name,Sex=@sex,Nation=@nation,Birthday=@birthday where Code=@code";
                scmd.Parameters.Clear();
                scmd.Parameters.AddWithValue("@name", name);
                scmd.Parameters.AddWithValue("@sex", sex);
                scmd.Parameters.AddWithValue("@nation", nation);
                scmd.Parameters.AddWithValue("@birthday", birthday);
                scmd.Parameters.AddWithValue("@code", code);
                scmd.ExecuteNonQuery();
                Console.WriteLine("OK!");

            }
            finally
            {
                scon.Close();

            }



        }
    }

    class Class4
    {
        public const string CONSTR = "server=.;database=mydb;uid=sa;pwd=111111";
        public static void chaXun()
        {
            SqlConnection con = new SqlConnection(CONSTR);
            try
            {
                con.Open();
                SqlCommand cmd = con.CreateCommand();
                cmd.CommandText = "select * from info";
                SqlDataReader re = cmd.ExecuteReader();
                while (re.Read())
                {
                    string code = re["Code"].ToString();
                    string name = re["Name"].ToString();
                    string sex = ((bool)re["Sex"]) ? "男" : "女";
                    string nation = getNation(re["Nation"].ToString());
                    string birthday = ((DateTime)re["Birthday"]).ToString("yyyy年MM月dd日");

                    Console.WriteLine(code + "	" + name + "	" + sex + "	" + nation + "	" + birthday + "
");
                    Console.ForegroundColor = ConsoleColor.Yellow;
                    Console.WriteLine("**************************个人简历***************************");
                    Console.WriteLine(getWork(code));
                    Console.ResetColor();
                    Console.ForegroundColor = ConsoleColor.Blue;
                    Console.WriteLine("**************************家庭情况***************************");
                    Console.WriteLine(getFamily(code));
                    Console.ResetColor();

                }


            }
            finally
            {
                con.Close();
            }

        }
        public static string getNation(string nation)
        {
            string tr = "";
            SqlConnection con = new SqlConnection(CONSTR);
            try
            {
                con.Open();
                SqlCommand cmd = con.CreateCommand();
                cmd.CommandText = "select Name from nation where Code=@nation";
                cmd.Parameters.Clear();
                cmd.Parameters.AddWithValue("@nation", nation);
                SqlDataReader re = cmd.ExecuteReader();
                while (re.Read())
                {
                    tr = re["Name"].ToString();
                }
            }
            finally
            {
                con.Close();
            }

            return tr;
        }
        public static string getWork(string code)
        {
            string tr = "";
            SqlConnection con = new SqlConnection(CONSTR);
            try
            {
                con.Open();
                SqlCommand cmd = con.CreateCommand();
                cmd.CommandText = "select * from work where InfoCode=@code";
                cmd.Parameters.Clear();
                cmd.Parameters.AddWithValue("@code", code);
                SqlDataReader re = cmd.ExecuteReader();
                while (re.Read())
                {
                    tr += ((DateTime)re["StartDate"]).ToString("yyyy年MM月dd日") + "	";
                    tr += ((DateTime)re["EndDate"]).ToString("yyyy年MM月dd日") + "	";
                    tr += re["Firm"].ToString() + "	";
                    tr += re["Depart"].ToString() + "
";
                }
                return tr;
            }
            finally
            {
                con.Close();

            }
        }
        public static string getFamily(string code)
        {
            string tr = "";
            SqlConnection con = new SqlConnection(CONSTR);
            try
            {
                con.Open();
                SqlCommand cmd = con.CreateCommand();
                cmd.CommandText = "select * from family where InfoCode=@code";
                cmd.Parameters.Clear();
                cmd.Parameters.AddWithValue("@code", code);
                SqlDataReader re = cmd.ExecuteReader();
                while (re.Read())
                {

                    tr += re["Name"].ToString() + "	";
                    tr += getTitle(re["title"].ToString()) + "	";
                    tr += re["Firm"].ToString() + "
";

                }
                return tr;
            }
            finally
            {
                con.Close();
            }
        }
        public static string getTitle(string title)
        {
            string tr = "";
            SqlConnection con = new SqlConnection(CONSTR);
            try
            {
                con.Open();
                SqlCommand cmd = con.CreateCommand();
                cmd.CommandText = "select * from title where Code='" + title + "'";
                SqlDataReader re = cmd.ExecuteReader();
                while (re.Read())
                {
                    tr += re["Name"].ToString();
                }
                return tr;
            }
            finally
            {
                con.Close();
            }
        }
    }

}