1 using System;  2 using System.Collections.Generic;  3 using System.Linq;  4 using System.Text;  5 using System.Threading.Tasks;  6 using System.Data;  7 using System.Data.SqlClient;  8   9 namespace ww 10 { 11     class Program 12     { 13         static void Main(string[] args) 14         { 15             Console.Write("请选择操作： 1：增加记录 2：删除记录 3：修改记录 4：查询记录\n"); 16             string tr = Console.ReadLine(); 17             switch (tr) 18             { 19                 case "1": 20                     Console.WriteLine("**************************增加记录*****************************"); 21                     ww.Class1.insert(); 22                     break; 23                 case "2": 24                     Console.WriteLine("**************************删除记录*****************************"); 25                     ww.Class2.delete(); 26                     break; 27                 case "3": 28                     Console.WriteLine("**************************修改Info表记录*****************************"); 29  30                     ww.Class3.updeteInfo(); 31                     break; 32                 case "4": 33                     Console.WriteLine("**************************查询记录*****************************"); 34  35                     ww.Class4.chaXun(); 36                     break; 37                 default: 38                     break; 39             } 40             Main(args); 41         } 42     } 43  44     class Class1 45     { 46         public const string CONSTR = "server=.;database=mydb;uid=sa;pwd=111111"; 47         public static bool check(string col, string value) 48         { 49             if (col == "Sex") 50             { 51                 if (value == "0" || value == "1") 52                 { 53                     return true; 54                 } 55                 else 56                 { 57                     Console.WriteLine("性别格式不正确"); 58                     return false; 59                 } 60             } 61             else if (col == "Birthday") 62             { 63                 try 64                 { 65                     Convert.ToDateTime(value); 66                     return true; 67                 } 68                 catch 69                 { 70                     Console.WriteLine("生日格式不正确"); 71                     return false; 72                 } 73             } 74             else 75             { 76                 return true; 77             } 78         } 79         public static bool checkPK(string pk) 80         { 81             bool notHasPK = true; 82             SqlConnection conn = new SqlConnection(CONSTR); 83             try 84             { 85                 conn.Open(); 86                 SqlCommand cmd = conn.CreateCommand(); 87                 cmd.CommandText = "select * from info where Code=@pk"; 88                 cmd.Parameters.Clear(); 89                 cmd.Parameters.AddWithValue("@pk", pk); 90                 SqlDataReader dr = cmd.ExecuteReader(); 91                 if (dr.HasRows) 92                 { 93                     notHasPK = false; 94                     Console.WriteLine("主键已存在"); 95                 } 96  97                 return notHasPK; 98  99             }100             finally101             {102                 conn.Close();103             }104         }105         public static bool checkNation(string nationCode)106         {107             bool checkNation = true;108             SqlConnection conn = new SqlConnection(CONSTR);109             try110             {111                 conn.Open();112 113                 SqlCommand cmd = conn.CreateCommand();114                 cmd.CommandText = "select * from nation where Code=@nationCode ";115                 cmd.Parameters.Clear();116                 cmd.Parameters.AddWithValue("@nationCode", nationCode);117                 SqlDataReader dr = cmd.ExecuteReader();118                 if (dr.HasRows)119                 {120                     checkNation = true;121                 }122                 else123                 {124                     checkNation = false;125                     Console.WriteLine("民族编号输入不正确！");126                 }127 128                 return checkNation;129 130             }131             finally132             {133                 conn.Close();134             }135         }136         public static void addInfo(string code, string name, string sex, string nation, string birthday)137         {138             SqlConnection conn = new SqlConnection(CONSTR);139             try140             {141                 conn.Open();142 143                 SqlCommand cmd = conn.CreateCommand();144                 cmd.CommandText = "insert into info values(@code,@name,@sex,@nation,@birthday)";145                 cmd.Parameters.Clear();146                 cmd.Parameters.AddWithValue("@code", code);147                 cmd.Parameters.AddWithValue("@name", name);148                 cmd.Parameters.AddWithValue("@sex", sex);149                 cmd.Parameters.AddWithValue("@nation", nation);150                 cmd.Parameters.AddWithValue("@birthday", birthday);151                 cmd.ExecuteNonQuery();152 153             }154             finally155             {156                 conn.Close();157 158             }159 160         }161         public static void insert()162         {163             string code, name, sex, nation, birthday;164             do165             {166                 Console.Write("编号：");167                 code = Console.ReadLine();168 169             } while (!checkPK(code));170             Console.Write("姓名：");171             name = Console.ReadLine();172             do173             {174                 Console.Write("性别(0/1)：");175                 sex = Console.ReadLine();176             } while (!check("Sex", sex));177             do178             {179                 Console.Write("民族：");180                 nation = Console.ReadLine();181             } while (!checkNation(nation));182             do183             {184                 Console.Write("生日：");185                 birthday = Console.ReadLine();186             } while (!check("Birthday", birthday));187             addInfo(code, name, sex, nation, birthday);188         }189     }190 191     class Class2192     {193         public const string CONSTR = "server=.;database=mydb;uid=sa;pwd=111111";194         static string getNation(string nation)195         {196             string str = "";197             SqlConnection conn = new SqlConnection(CONSTR);198             try199             {200                 conn.Open();201 202                 SqlCommand cmd = conn.CreateCommand();203                 cmd.CommandText = "select * from nation where Code=@nation ";204                 cmd.Parameters.Clear();205                 cmd.Parameters.AddWithValue("@nation", nation);206                 SqlDataReader dr = cmd.ExecuteReader();207                 if (dr.HasRows)208                 {209                     dr.Read();210                     str = dr["Name"].ToString();211                 }212                 else213                 {214                     str = "";215                 }216 217 218                 return str;219 220             }221             finally222             {223                 conn.Close();224 225             }226         }227         public static void Show()228         {229             SqlConnection conn = new SqlConnection(CONSTR);230             try231             {232                 conn.Open();233 234                 SqlCommand cmd = conn.CreateCommand();235                 cmd.CommandText = "select * from info";236                 SqlDataReader dr = cmd.ExecuteReader();237                 while (dr.Read())238                 {239                     string code = dr["Code"].ToString();240                     string name = dr["Name"].ToString();241                     string sex = ((bool)dr["Sex"]) ? "男" : "女";242                     string nation = getNation(dr["Nation"].ToString());243                     string birthday = ((DateTime)dr["Birthday"]).ToString("yyyy年MM月dd日");244 245                     Console.WriteLine(code + "\t" + name + "\t" + sex + "\t" + nation + "\t" + birthday);246                 }247 248 249             }250             finally251             {252                 conn.Close();253 254             }255         }256         public static bool checkPK(string pk)257         {258             bool hasPK = true;259             SqlConnection conn = new SqlConnection(CONSTR);260             try261             {262                 conn.Open();263 264                 SqlCommand cmd = conn.CreateCommand();265                 cmd.CommandText = "select * from info where code=@pk";266                 cmd.Parameters.Clear();267                 cmd.Parameters.AddWithValue("@pk", pk);268                 SqlDataReader dr = cmd.ExecuteReader();269                 hasPK = dr.HasRows;270 271                 return hasPK;272 273             }274             finally275             {276                 conn.Close();277 278             }279         }280         public static void deleteInfo(string pk)281         {282             SqlConnection conn = new SqlConnection(CONSTR);283             conn.Open();284                 SqlTransaction sw = conn.BeginTransaction();285                 SqlCommand cmd = conn.CreateCommand();286                 cmd.Transaction = sw;287 288             try289             {290                 cmd.CommandText = "delete from family where InfoCode=@pk";291                 cmd.Parameters.Clear();292                 cmd.Parameters.AddWithValue("@pk", pk);293                 cmd.ExecuteNonQuery();294                 cmd.CommandText = "delete from work where InfoCode=@pk";295                 cmd.Parameters.Clear();296                 cmd.Parameters.AddWithValue("@pk", pk);297                 cmd.ExecuteNonQuery();298                 cmd.CommandText = "delete from info where Code=@pk";299                 cmd.Parameters.Clear();300                 cmd.Parameters.AddWithValue("@pk", pk);301                 cmd.ExecuteNonQuery();302                 sw.Commit();303             }304             catch305             {306                 sw.Rollback();307             }308             finally309             {310                 conn.Close();311 312             }313         }314         public static void delete()315         {316             Show();317             Console.Write("输入要删的人员编码：");318             string code = Console.ReadLine();319             if (checkPK(code))320             {321                 deleteInfo(code);322                 Console.WriteLine("删除成功");323             }324             else325             {326                 Console.WriteLine("找不到要删除的人员编码，删除失败！");327             }328             Show();329         }330     }331 332     class Class3333     {334         public const string CONSTR = "server=.;database=mydb;uid=sa;pwd=111111";335         public static void updeteInfo()336         {337             string code, name, sex, nation, birthday;338             Console.WriteLine("*************************通过编号修改info表记录*************************");339             do340             {341                 Console.Write("编号：");342                 code = Console.ReadLine();343             } while (ww.Class1.checkPK(code));344             Console.Write("姓名：");345             name = Console.ReadLine();346             do347             {348                 Console.Write("性别(0/1)：");349                 sex = Console.ReadLine();350             } while (!ww.Class1.check("Sex", sex));351             do352             {353                 Console.Write("民族：");354                 nation = Console.ReadLine();355             } while (!ww.Class1.checkNation(nation));356             do357             {358                 Console.Write("生日：");359                 birthday = Console.ReadLine();360             } while (!ww.Class1.check("Birthday", birthday));361             SqlConnection scon = new SqlConnection(CONSTR);362             try363             {364                 scon.Open();365                 SqlCommand scmd = scon.CreateCommand();366                 scmd.CommandText = "update info set Name=@name,Sex=@sex,Nation=@nation,Birthday=@birthday where Code=@code";367                 scmd.Parameters.Clear();368                 scmd.Parameters.AddWithValue("@name", name);369                 scmd.Parameters.AddWithValue("@sex", sex);370                 scmd.Parameters.AddWithValue("@nation", nation);371                 scmd.Parameters.AddWithValue("@birthday", birthday);372                 scmd.Parameters.AddWithValue("@code", code);373                 scmd.ExecuteNonQuery();374                 Console.WriteLine("OK!");375 376             }377             finally378             {379                 scon.Close();380 381             }382 383 384 385         }386     }387 388     class Class4389     {390         public const string CONSTR = "server=.;database=mydb;uid=sa;pwd=111111";391         public static void chaXun()392         {393             SqlConnection con = new SqlConnection(CONSTR);394             try395             {396                 con.Open();397                 SqlCommand cmd = con.CreateCommand();398                 cmd.CommandText = "select * from info";399                 SqlDataReader re = cmd.ExecuteReader();400                 while (re.Read())401                 {402                     string code = re["Code"].ToString();403                     string name = re["Name"].ToString();404                     string sex = ((bool)re["Sex"]) ? "男" : "女";405                     string nation = getNation(re["Nation"].ToString());406                     string birthday = ((DateTime)re["Birthday"]).ToString("yyyy年MM月dd日");407 408                     Console.WriteLine(code + "\t" + name + "\t" + sex + "\t" + nation + "\t" + birthday + "\n");409                     Console.ForegroundColor = ConsoleColor.Yellow;410                     Console.WriteLine("**************************个人简历***************************");411                     Console.WriteLine(getWork(code));412                     Console.ResetColor();413                     Console.ForegroundColor = ConsoleColor.Blue;414                     Console.WriteLine("**************************家庭情况***************************");415                     Console.WriteLine(getFamily(code));416                     Console.ResetColor();417 418                 }419 420 421             }422             finally423             {424                 con.Close();425             }426 427         }428         public static string getNation(string nation)429         {430             string tr = "";431             SqlConnection con = new SqlConnection(CONSTR);432             try433             {434                 con.Open();435                 SqlCommand cmd = con.CreateCommand();436                 cmd.CommandText = "select Name from nation where Code=@nation";437                 cmd.Parameters.Clear();438                 cmd.Parameters.AddWithValue("@nation", nation);439                 SqlDataReader re = cmd.ExecuteReader();440                 while (re.Read())441                 {442                     tr = re["Name"].ToString();443                 }444             }445             finally446             {447                 con.Close();448             }449 450             return tr;451         }452         public static string getWork(string code)453         {454             string tr = "";455             SqlConnection con = new SqlConnection(CONSTR);456             try457             {458                 con.Open();459                 SqlCommand cmd = con.CreateCommand();460                 cmd.CommandText = "select * from work where InfoCode=@code";461                 cmd.Parameters.Clear();462                 cmd.Parameters.AddWithValue("@code", code);463                 SqlDataReader re = cmd.ExecuteReader();464                 while (re.Read())465                 {466                     tr += ((DateTime)re["StartDate"]).ToString("yyyy年MM月dd日") + "\t";467                     tr += ((DateTime)re["EndDate"]).ToString("yyyy年MM月dd日") + "\t";468                     tr += re["Firm"].ToString() + "\t";469                     tr += re["Depart"].ToString() + "\n";470                 }471                 return tr;472             }473             finally474             {475                 con.Close();476 477             }478         }479         public static string getFamily(string code)480         {481             string tr = "";482             SqlConnection con = new SqlConnection(CONSTR);483             try484             {485                 con.Open();486                 SqlCommand cmd = con.CreateCommand();487                 cmd.CommandText = "select * from family where InfoCode=@code";488                 cmd.Parameters.Clear();489                 cmd.Parameters.AddWithValue("@code", code);490                 SqlDataReader re = cmd.ExecuteReader();491                 while (re.Read())492                 {493 494                     tr += re["Name"].ToString() + "\t";495                     tr += getTitle(re["title"].ToString()) + "\t";496                     tr += re["Firm"].ToString() + "\n";497 498                 }499                 return tr;500             }501             finally502             {503                 con.Close();504             }505         }506         public static string getTitle(string title)507         {508             string tr = "";509             SqlConnection con = new SqlConnection(CONSTR);510             try511             {512                 con.Open();513                 SqlCommand cmd = con.CreateCommand();514                 cmd.CommandText = "select * from title where Code='" + title + "'";515                 SqlDataReader re = cmd.ExecuteReader();516                 while (re.Read())517                 {518                     tr += re["Name"].ToString();519                 }520                 return tr;521             }522             finally523             {524                 con.Close();525             }526         }527     }528 529 }