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 finally
101 {
102 conn.Close();
103 }
104 }
105 public static bool checkNation(string nationCode)
106 {
107 bool checkNation = true;
108 SqlConnection conn = new SqlConnection(CONSTR);
109 try
110 {
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 else
123 {
124 checkNation = false;
125 Console.WriteLine("民族编号输入不正确！");
126 }
127
128 return checkNation;
129
130 }
131 finally
132 {
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 try
140 {
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 finally
155 {
156 conn.Close();
157
158 }
159
160 }
161 public static void insert()
162 {
163 string code, name, sex, nation, birthday;
164 do
165 {
166 Console.Write("编号：");
167 code = Console.ReadLine();
168
169 } while (!checkPK(code));
170 Console.Write("姓名：");
171 name = Console.ReadLine();
172 do
173 {
174 Console.Write("性别(0/1)：");
175 sex = Console.ReadLine();
176 } while (!check("Sex", sex));
177 do
178 {
179 Console.Write("民族：");
180 nation = Console.ReadLine();
181 } while (!checkNation(nation));
182 do
183 {
184 Console.Write("生日：");
185 birthday = Console.ReadLine();
186 } while (!check("Birthday", birthday));
187 addInfo(code, name, sex, nation, birthday);
188 }
189 }
190
191 class Class2
192 {
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 try
199 {
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 else
213 {
214 str = "";
215 }
216
217
218 return str;
219
220 }
221 finally
222 {
223 conn.Close();
224
225 }
226 }
227 public static void Show()
228 {
229 SqlConnection conn = new SqlConnection(CONSTR);
230 try
231 {
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 finally
251 {
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 try
261 {
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 finally
275 {
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 try
289 {
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 catch
305 {
306 sw.Rollback();
307 }
308 finally
309 {
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 else
325 {
326 Console.WriteLine("找不到要删除的人员编码，删除失败！");
327 }
328 Show();
329 }
330 }
331
332 class Class3
333 {
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 do
340 {
341 Console.Write("编号：");
342 code = Console.ReadLine();
343 } while (ww.Class1.checkPK(code));
344 Console.Write("姓名：");
345 name = Console.ReadLine();
346 do
347 {
348 Console.Write("性别(0/1)：");
349 sex = Console.ReadLine();
350 } while (!ww.Class1.check("Sex", sex));
351 do
352 {
353 Console.Write("民族：");
354 nation = Console.ReadLine();
355 } while (!ww.Class1.checkNation(nation));
356 do
357 {
358 Console.Write("生日：");
359 birthday = Console.ReadLine();
360 } while (!ww.Class1.check("Birthday", birthday));
361 SqlConnection scon = new SqlConnection(CONSTR);
362 try
363 {
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 finally
378 {
379 scon.Close();
380
381 }
382
383
384
385 }
386 }
387
388 class Class4
389 {
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 try
395 {
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 finally
423 {
424 con.Close();
425 }
426
427 }
428 public static string getNation(string nation)
429 {
430 string tr = "";
431 SqlConnection con = new SqlConnection(CONSTR);
432 try
433 {
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 finally
446 {
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 try
457 {
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 finally
474 {
475 con.Close();
476
477 }
478 }
479 public static string getFamily(string code)
480 {
481 string tr = "";
482 SqlConnection con = new SqlConnection(CONSTR);
483 try
484 {
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 finally
502 {
503 con.Close();
504 }
505 }
506 public static string getTitle(string title)
507 {
508 string tr = "";
509 SqlConnection con = new SqlConnection(CONSTR);
510 try
511 {
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 finally
523 {
524 con.Close();
525 }
526 }
527 }
528
529 }
浙公网安备 33010602011771号