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 conn.Open();
84
85 SqlCommand cmd = conn.CreateCommand();
86 cmd.CommandText = "select * from info where Code='" + pk + "'";
87 SqlDataReader dr = cmd.ExecuteReader();
88 if (dr.HasRows)
89 {
90 notHasPK = false;
91 Console.WriteLine("主键已存在");
92 }
93 conn.Close();
94
95 return notHasPK;
96 }
97 public static bool checkNation(string nationCode)
98 {
99 bool checkNation = true;
100 SqlConnection conn = new SqlConnection(CONSTR);
101 conn.Open();
102
103 SqlCommand cmd = conn.CreateCommand();
104 cmd.CommandText = "select * from nation where Code='" + nationCode + "'";
105 SqlDataReader dr = cmd.ExecuteReader();
106 if (dr.HasRows)
107 {
108 checkNation = true;
109 }
110 else
111 {
112 checkNation = false;
113 Console.WriteLine("民族编号输入不正确！");
114 }
115 conn.Close();
116 return checkNation;
117 }
118 public static void addInfo(string code, string name, string sex, string nation, string birthday)
119 {
120 SqlConnection conn = new SqlConnection(CONSTR);
121 conn.Open();
122
123 SqlCommand cmd = conn.CreateCommand();
124 cmd.CommandText = "insert into info values('" + code + "','" + name + "','" + sex + "','" + nation + "','" + birthday + "')";
125 cmd.ExecuteNonQuery();
126
127 conn.Close();
128 }
129 public static void insert()
130 {
131 string code, name, sex, nation, birthday;
132 do
133 {
134 Console.Write("编号：");
135 code = Console.ReadLine();
136
137 } while (!checkPK(code));
138 Console.Write("姓名：");
139 name = Console.ReadLine();
140 do
141 {
142 Console.Write("性别(0/1)：");
143 sex = Console.ReadLine();
144 } while (!check("Sex", sex));
145 do
146 {
147 Console.Write("民族：");
148 nation = Console.ReadLine();
149 } while (!checkNation(nation));
150 do
151 {
152 Console.Write("生日：");
153 birthday = Console.ReadLine();
154 } while (!check("Birthday", birthday));
155 addInfo(code, name, sex, nation, birthday);
156 }
157 }
158
159 class Class2
160 {
161 public const string CONSTR = "server=.;database=mydb;uid=sa;pwd=111111";
162 static string getNation(string nation)
163 {
164 string str = "";
165 SqlConnection conn = new SqlConnection(CONSTR);
166 conn.Open();
167
168 SqlCommand cmd = conn.CreateCommand();
169 cmd.CommandText = "select * from nation where Code='" + nation + "' ";
170 SqlDataReader dr = cmd.ExecuteReader();
171 if (dr.HasRows)
172 {
173 dr.Read();
174 str = dr["Name"].ToString();
175 }
176 else
177 {
178 str = "";
179 }
180
181 conn.Close();
182
183 return str;
184 }
185 public static void Show()
186 {
187 SqlConnection conn = new SqlConnection(CONSTR);
188 conn.Open();
189
190 SqlCommand cmd = conn.CreateCommand();
191 cmd.CommandText = "select * from info";
192 SqlDataReader dr = cmd.ExecuteReader();
193 while (dr.Read())
194 {
195 string code = dr["Code"].ToString();
196 string name = dr["Name"].ToString();
197 string sex = ((bool)dr["Sex"]) ? "男" : "女";
198 string nation = getNation(dr["Nation"].ToString());
199 string birthday = ((DateTime)dr["Birthday"]).ToString("yyyy年MM月dd日");
200
201 Console.WriteLine(code + "\t" + name + "\t" + sex + "\t" + nation + "\t" + birthday);
202 }
203
204 conn.Close();
205 }
206 public static bool checkPK(string pk)
207 {
208 bool hasPK = true;
209 SqlConnection conn = new SqlConnection(CONSTR);
210 conn.Open();
211
212 SqlCommand cmd = conn.CreateCommand();
213 cmd.CommandText = "select * from info where code='" + pk + "'";
214 SqlDataReader dr = cmd.ExecuteReader();
215 hasPK = dr.HasRows;
216 conn.Close();
217
218 return hasPK;
219 }
220 public static void deleteInfo(string pk)
221 {
222 SqlConnection conn = new SqlConnection(CONSTR);
223 conn.Open();
224
225 SqlCommand cmd = conn.CreateCommand();
226 cmd.CommandText = "delete from family where InfoCode='" + pk + "'";
227 cmd.ExecuteNonQuery();
228 cmd.CommandText = "delete from work where InfoCode='" + pk + "'";
229 cmd.ExecuteNonQuery();
230 cmd.CommandText = "delete from info where Code='" + pk + "'";
231 cmd.ExecuteNonQuery();
232
233 conn.Close();
234 }
235 public static void delete()
236 {
237 Show();
238 Console.Write("输入要删的人员编码：");
239 string code = Console.ReadLine();
240 if (checkPK(code))
241 {
242 deleteInfo(code);
243 Console.WriteLine("删除成功");
244 }
245 else
246 {
247 Console.WriteLine("找不到要删除的人员编码，删除失败！");
248 }
249 Show();
250 }
251 }
252
253 class Class3
254 {
255 public const string CONSTR = "server=.;database=mydb;uid=sa;pwd=111111";
256 public static void updeteInfo()
257 {
258 string code, name, sex, nation, birthday;
259 Console.WriteLine("*************************通过编号修改info表记录*************************");
260 do
261 {
262 Console.Write("编号：");
263 code = Console.ReadLine();
264 }while(ww.Class1.checkPK(code));
265 Console.Write("姓名：");
266 name = Console.ReadLine();
267 do
268 {
269 Console.Write("性别(0/1)：");
270 sex = Console.ReadLine();
271 } while (!ww.Class1.check("Sex", sex));
272 do
273 {
274 Console.Write("民族：");
275 nation = Console.ReadLine();
276 } while (!ww.Class1.checkNation(nation));
277 do
278 {
279 Console.Write("生日：");
280 birthday = Console.ReadLine();
281 } while (!ww.Class1.check("Birthday", birthday));
282 SqlConnection scon = new SqlConnection(CONSTR);
283 scon.Open();
284 SqlCommand scmd = new SqlCommand();
285 scmd.Connection = scon;
286 scmd.CommandText = "update info set Name='" + name
287 + "',Sex='" + sex + "',Nation='" + nation + "',Birthday='" + birthday + "' where Code='" + code + "'";
288
289 scmd.ExecuteNonQuery();
290 Console.WriteLine("OK!");
291 scon.Close();
292
293
294
295 }
296 }
297
298 class Class4
299 {
300 public const string CONSTR = "server=.;database=mydb;uid=sa;pwd=111111";
301 public static void chaXun()
302 {
303 SqlConnection con = new SqlConnection(CONSTR);
304 con.Open();
305 SqlCommand cmd = con.CreateCommand();
306 cmd.CommandText = "select * from info";
307 SqlDataReader re = cmd.ExecuteReader();
308 while (re.Read())
309 {
310 string code = re["Code"].ToString();
311 string name = re["Name"].ToString();
312 string sex = ((bool)re["Sex"]) ? "男" : "女";
313 string nation = getNation(re["Nation"].ToString());
314 string birthday = ((DateTime)re["Birthday"]).ToString("yyyy年MM月dd日");
315
316 Console.WriteLine(code + "\t" + name + "\t" + sex + "\t" + nation + "\t" + birthday + "\n");
317 Console.ForegroundColor = ConsoleColor.Yellow;
318 Console.WriteLine("**************************个人简历***************************");
319 Console.WriteLine(getWork(code));
320 Console.ResetColor();
321 Console.ForegroundColor = ConsoleColor.Blue;
322 Console.WriteLine("**************************家庭情况***************************");
323 Console.WriteLine(getFamily(code));
324 Console.ResetColor();
325
326 }
327
328 con.Close();
329
330 }
331 public static string getNation(string nation)
332 {
333 string tr = "";
334 SqlConnection con = new SqlConnection(CONSTR);
335 con.Open();
336 SqlCommand cmd = con.CreateCommand();
337 cmd.CommandText = "select Name from nation where Code='" + nation + "'";
338 SqlDataReader re = cmd.ExecuteReader();
339 while (re.Read())
340 {
341 tr = re["Name"].ToString();
342 }
343
344 con.Close();
345
346 return tr;
347 }
348 public static string getWork(string code)
349 {
350 string tr = "";
351 SqlConnection con = new SqlConnection(CONSTR);
352 con.Open();
353 SqlCommand cmd = con.CreateCommand();
354 cmd.CommandText = "select StartDate,EndDate,Firm,Depart from work where InfoCode='" + code + "'";
355 SqlDataReader re = cmd.ExecuteReader();
356 while (re.Read())
357 {
358 tr += ((DateTime)re["StartDate"]).ToString("yyyy年MM月dd日") + "\t";
359 tr += ((DateTime)re["EndDate"]).ToString("yyyy年MM月dd日") + "\t";
360 tr += re["Firm"].ToString() + "\t";
361 tr += re["Depart"].ToString() + "\n";
362 }
363 con.Close();
364 return tr;
365 }
366 public static string getFamily(string code)
367 {
368 string tr = "";
369 SqlConnection con = new SqlConnection(CONSTR);
370 con.Open();
371 SqlCommand cmd = con.CreateCommand();
372 cmd.CommandText = "select * from family where InfoCode='" + code + "'";
373 SqlDataReader re = cmd.ExecuteReader();
374 while (re.Read())
375 {
376
377 tr += re["Name"].ToString() + "\t";
378 tr += getTitle(re["title"].ToString()) + "\t";
379 tr += re["Firm"].ToString() + "\n";
380
381 }
382 con.Close();
383
384 return tr;
385 }
386 public static string getTitle(string title)
387 {
388 string tr = "";
389 SqlConnection con = new SqlConnection(CONSTR);
390 con.Open();
391 SqlCommand cmd = con.CreateCommand();
392 cmd.CommandText = "select * from title where Code='" + title + "'";
393 SqlDataReader re = cmd.ExecuteReader();
394 while (re.Read())
395 {
396
397 tr += re["Name"].ToString();
398 }
399 con.Close();
400 return tr;
401 }
402 }
403
404 }
