数据访问----实例3
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
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 }