一个ADO的综合小练习

题目：

一、数据显示：

二、功能操作：

“请输入您要继续的操作（输入数字1为新增，输入数字2为修改，输入数字3为删除）：”，输入其它内容提示输入有误，并回到上面继续等待用户操作。

三、新增：

当用户输入1并回车后，按照顺序让用户分别输入：学生编号，学生姓名，性别，生日，专业等内容。

每次输入都需要做以下判断，如果输入不正确，则提示输入有误，并让用户重新输入，当每一项输入正确后才可以继续下一项的输入；

1、学生编号不允许重复

2、学生姓名不能为空

3、性别只能输入男/女，并自动转换为true/false

4、生日格式是否是正确

5、专业需要用户输入中文，如：当用户输入“商务英语”或“英语”，自动查到“Z001”专业编号，如果查不到则提示“没有此专业，请重新输入。”

当以上内容全部填写完毕并无误后，将此学生信息打印显示出来，并询问用户“以上为新增学生的信息，是否确定添加？（Y/N）：”，当用户输入Y确定添加后，才进行添加，如果添加成功，则将控制台程序清空，并刷新显示最新的Student表数据，在底部提示”添加成功”，并继续回到“第2步”等待用户继续操作。

四、修改：

当用户输入2并回车时，提示用户输入需要更改的学生编号，如果有此学生，那么除学生编号外，其余的内容全部进行修改。

已查询到的学生信息，请进行以下修改：

每次修改后都需要与新增时一样的判断，当全部内容都修改完毕后，将修改后的学生信息打印显示出来，并询问“以上为修改后的学生信息，是否确定修改？（Y/N）：”，当用户输入Y并回车才真正确定修改，并提示修改是否成功，如果修改成功，则清空控制台内容，刷新显示最新的Student表数据，并提示修改成功，继续回到“二”等待用户继续操作。

五、删除：

当用户输入3并回车时，提示用户输入需要删除的学生编号，如果有此学生，那么将此学生的信息展示出来，并提示“是否要删除此学生的信息？（Y/N)”，当用户输入Y确定删除后，才进行删除，并提示删除是否成功，如果删除成功，则清空控制台内容，刷新显示最新的Student表数据，并提示删除成功，继续回到“二”等待用户继续操作。

答案：

1.建表

 1 create database ADOkaoshi
 2 go
 3 use ADOkaoshi
 4 go
 5 create table Student
 6 (
 7     Code nvarchar(50) primary key not null,
 8     Name nvarchar(50) not null,
 9     Sex bit not null,
10     Birthday datetime not null,
11     SubjectCode nvarchar(50)
12 )
13 create table Ssubject
14 (
15     SubjectCode nvarchar(50) primary key not null,
16     SubjectName nvarchar(50) not null
17 )
18 insert into Ssubject values('Z001','商务英语')
19 insert into Ssubject values('Z002','软件工程')
20 insert into Ssubject values('Z003','旅游')
21 insert into Ssubject values('Z004','工商管理')
22 go
23 insert into Student values('S001','张三',1,'1994-1-12','Z001')
24 insert into Student values('S002','李四',1,'1995-2-22','Z002')
25 insert into Student values('S003','王五',1,'1996-8-28','Z002')
26 insert into Student values('S004','小花',0,'1997-3-6','Z003')
27 go
28 select * from Ssubject
29 select * from Student
30

2.建立数据模型

 1  public class student
 2     {
 3         private string _Code;
 4 
 5         public string Code
 6         {
 7             get { return _Code; }
 8             set { _Code = value; }
 9         }
10         private string _Name;
11 
12         public string Name
13         {
14             get { return _Name; }
15             set { _Name = value; }
16         }
17         private bool _Sex;
18 
19         public bool Sex
20         {
21             get { return _Sex; }
22             set { _Sex = value; }
23         }
24         private DateTime _Birthday;
25 
26         public DateTime Birthday
27         {
28             get { return _Birthday; }
29             set { _Birthday = value; }
30         }
31         private string _SubjectCode;
32 
33         public string SubjectCode
34         {
35             get { return _SubjectCode; }
36             set { _SubjectCode = value; }
37         }
38 
39         public string Subjectname
40         {
41             get
42             {
43                 string sub = "";
44                 SqlConnection conn = new SqlConnection("server=.;database=ADOkaoshi;user=sa;pwd=123;");
45                 SqlCommand com = conn.CreateCommand();
46                 com.CommandText = "select * from Ssubject where SubjectCode = @Subjectcode";
47                 com.Parameters.Clear();
48                 com.Parameters.Add("@Subjectcode",_SubjectCode);
49                 conn.Open();
50                 SqlDataReader data = com.ExecuteReader();
51                 if(data.HasRows)
52                 {
53                     data.Read();
54                     sub = data["SubjectName"].ToString();
55                 }
56                 conn.Close();
57                 return sub;
58             }
59         }
60     }

3.写数据访问方法

  1 public class studentway
  2     {
  3         SqlConnection conn = null;
  4         SqlCommand com = null;
  5         public studentway()
  6         {
  7             conn = new SqlConnection("server=.;database=ADOkaoshi;user=sa;pwd=123;");
  8             com = conn.CreateCommand();
  9         }
 10         /// <summary>
 11         /// 查询表
 12         /// </summary>
 13         /// <returns></returns>
 14         public List<student> chaxun()
 15         {
 16             List<student> list = new List<student>();
 17             com.CommandText = "select * from Student";
 18             conn.Open();
 19             SqlDataReader data = com.ExecuteReader();
 20             if (data.HasRows)
 21             {
 22                 while (data.Read())
 23                 {
 24                     student stu = new student();
 25                     stu.Code = data["Code"].ToString();
 26                     stu.Name = data["Name"].ToString();
 27                     stu.Sex = Convert.ToBoolean(data["Sex"]);
 28                     stu.Birthday = Convert.ToDateTime(data["Birthday"]);
 29                     stu.SubjectCode = data["SubjectCode"].ToString();
 30                     list.Add(stu);
 31                 }
 32             }
 33             conn.Close();
 34             return list;
 35         }
 36         /// <summary>
 37         /// 查询
 38         /// </summary>
 39         /// <returns></returns>
 40         public List<student> chaxun1(string code)
 41         {
 42             List<student> list = new List<student>();
 43             com.CommandText = "select * from Student where Code = @code";
 44             com.Parameters.Clear();
 45             com.Parameters.Add("@code",code);
 46             conn.Open();
 47             SqlDataReader data = com.ExecuteReader();
 48             if (data.HasRows)
 49             {
 50                 while (data.Read())
 51                 {
 52                     student stu = new student();
 53                     stu.Code = data["Code"].ToString();
 54                     stu.Name = data["Name"].ToString();
 55                     stu.Sex = Convert.ToBoolean(data["Sex"]);
 56                     stu.Birthday = Convert.ToDateTime(data["Birthday"]);
 57                     stu.SubjectCode = data["SubjectCode"].ToString();
 58                     list.Add(stu);
 59                 }
 60             }
 61             conn.Close();
 62             return list;
 63         }
 64         /// <summary>
 65         /// 修改
 66         /// </summary>
 67         /// <param name="code">编号</param>
 68         /// <param name="name">名字</param>
 69         /// <param name="sex">性别</param>
 70         /// <param name="birthday">生日</param>
 71         /// <param name="subjectcode">课程号</param>
 72         public void xiugai(string code, string name, bool sex, DateTime birthday, string subjectcode)
 73         {
 74             com.CommandText = "update student set Name = @Name,Sex = @Sex,Birthday = @Birthday,SubjectCode = @SubjectCode where Code = @Code";
 75             com.Parameters.Clear();
 76             com.Parameters.Add("@Code", code);
 77             com.Parameters.Add("@Name", name);
 78             com.Parameters.Add("@Sex", sex);
 79             com.Parameters.Add("@Birthday", birthday);
 80             com.Parameters.Add("@SubjectCode", subjectcode);
 81             conn.Open();
 82             com.ExecuteNonQuery();
 83             conn.Close();
 84         }
 85         /// <summary>
 86         /// 添加
 87         /// </summary>
 88         /// <param name="code">学号</param>
 89         /// <param name="name">名字</param>
 90         /// <param name="sex">性别</param>
 91         /// <param name="birthday">生日</param>
 92         /// <param name="subjectcode">课程号</param>
 93         public void tianjia(string code, string name, bool sex, DateTime birthday, string subjectcode)
 94         {
 95             com.CommandText = "insert into Student values(@Code,@Name,@Sex,@Birthday,@SubjectCode)";
 96             com.Parameters.Clear();
 97             com.Parameters.Add("@Code", code);
 98             com.Parameters.Add("@Name", name);
 99             com.Parameters.Add("@Sex", sex);
100             com.Parameters.Add("@Birthday", birthday);
101             com.Parameters.Add("@SubjectCode", subjectcode);
102             conn.Open();
103             com.ExecuteNonQuery();
104             conn.Close();
105         }
106         /// <summary>
107         /// 删除
108         /// </summary>
109         /// <param name="code">学号</param>
110         public void shanchu(string code)
111         {
112             com.CommandText = "delete from Student where Code = @Code";
113             com.Parameters.Clear();
114             com.Parameters.Add("@Code", code);
115             conn.Open();
116             com.ExecuteNonQuery();
117             conn.Close();
118         }
119     }

4.打印的方法

 1  class dayin
 2     {
 3         /// <summary>
 4         /// 打印全部
 5         /// </summary>
 6         public void dayin1()
 7         {
 8             List<student> list = new studentway().chaxun();
 9             Console.WriteLine("编号" + "      " + "姓名" + "     " + "性别" + "       " + "年龄" + "       " + "生日" + "            " + "专业");
10             foreach (student a in list)
11             {
12                 string sex = (a.Sex) ? "男" : "女";
13                 Console.WriteLine(a.Code + "      " + a.Name + "      " + sex + "          " + (DateTime.Now.Year - a.Birthday.Year) + "    " + (a.Birthday.ToString("yyyy年MM月dd日")) + "   " + a.Subjectname);
14             }
15         }
16         /// <summary>
17         /// 打印用户输入的数据
18         /// </summary>
19         /// <param name="Code"></param>
20         /// <param name="Name"></param>
21         /// <param name="Sex"></param>
22         /// <param name="Birthday"></param>
23         /// <param name="Subjectname"></param>
24         public void dayin2(string Code,string Name,bool Sex,DateTime Birthday,string Subjectname)
25         {
26             Console.WriteLine("编号" + "      " + "姓名" + "     " + "性别" + "       " + "年龄" + "       " + "生日" + "            " + "专业");
27             string sex = (Sex) ? "男" : "女";
28             Console.WriteLine(Code + "      " + Name + "      " + sex + "          " + (DateTime.Now.Year - Birthday.Year) + "    " + (Birthday.ToString("yyyy年MM月dd日")) + "   " + Subjectname);
29         }
30     }
31 }

5.定义的变量

1 class shuxing
2     {
3         public string code;
4         public string name;
5         public bool sex;
6         public DateTime birthday;
7         public string subjectname;
8         public string subjectcode;
9    }

 

6.Main函数中的代码

  1 //1.数据显示
  2             dayin aa = new dayin();
  3             aa.dayin1();
  4             //2.用户输入
  5             for (; ; )
  6             {
  7                 string shuru = "";
  8                 for (; ; )
  9                 {
 10                     Console.Write("请输入您要继续的操作（输入数字1为新增，输入数字2为修改，输入数字3为删除）:");
 11                     shuru = Console.ReadLine();
 12                     if (shuru == "1" || shuru == "2" || shuru == "3")
 13                     {
 14                         break;
 15                     }
 16                     else
 17                     {
 18                         Console.WriteLine("您的输入有误，请重新输入！");
 19                     }
 20                 }
 21                 //3.新增
 22                 #region
 23                 if (shuru == "1")
 24                 {
 25                     for (; ; )
 26                     {
 27                         shuxing bb = new shuxing();
 28                         for (; ; )
 29                         {
 30                             Console.Write("请输入学生编号：");
 31                             bb.code = Console.ReadLine();
 32                             List<student> list = new studentway().chaxun1(bb.code);
 33                             if (list.Count != 0)
 34                             {
 35                                 Console.WriteLine("该学生已存在，请重新输入！");
 36                             }
 37                             else
 38                             {
 39                                 break;
 40                             }
 41                         }
 42                         for (; ; )
 43                         {
 44                             Console.Write("请输入学生姓名：");
 45                             bb.name = Console.ReadLine();
 46                             if (bb.name == "")
 47                             {
 48                                 Console.WriteLine("学生姓名不能为空，请重新输入！");
 49                             }
 50                             else
 51                             {
 52                                 break;
 53                             }
 54                         }
 55                         for (; ; )
 56                         {
 57                             Console.Write("请输入学生性别：");
 58                             string a1 = Console.ReadLine();
 59                             if (a1 == "男")
 60                             {
 61                                 bb.sex = true;
 62                                 break;
 63                             }
 64                             else if (a1 == "女")
 65                             {
 66                                 bb.sex = false;
 67                                 break;
 68                             }
 69                             else
 70                             {
 71                                 Console.WriteLine("输入错误请重新输入！");
 72                             }
 73                         }
 74                         for (; ; )
 75                         {
 76                             try
 77                             {
 78                                 Console.Write("请输入学生生日（XXXX-XX-XX）：");
 79                                 bb.birthday = Convert.ToDateTime(Console.ReadLine());
 80                                 break;
 81                             }
 82                             catch
 83                             {
 84                                 Console.WriteLine("输入错误请重新输入！");
 85                             }
 86                         }
 87                         for (; ; )
 88                         {
 89                             Console.Write("请输入学生课程名称：");
 90                             bb.subjectname = Console.ReadLine();
 91                             SqlConnection conn = new SqlConnection("server=.;database=ADOkaoshi;user=sa;pwd=123;");
 92                             SqlCommand com = conn.CreateCommand();
 93                             com.CommandText = "select * from Ssubject where SubjectName like @subject";
 94                             com.Parameters.Clear();
 95                             com.Parameters.Add("@subject", "%" + bb.subjectname + "%");
 96                             conn.Open();
 97                             SqlDataReader a2 = com.ExecuteReader();
 98                             if (a2.HasRows)
 99                             {
100                                 a2.Read();
101                                 bb.subjectcode = a2["SubjectCode"].ToString();
102                                 break;
103                             }
104                             else
105                             {
106                                 Console.WriteLine("不存在这门课程，请重新输入！");
107                             }
108                         }
109 
110                         studentway stuway = new studentway();
111                         dayin dayinX = new dayin();
112                         dayinX.dayin2(bb.code, bb.name, bb.sex, bb.birthday, bb.subjectname);
113                         Console.Write("您是否要添加本条数据（Y/N）：");
114                         string panduan = Console.ReadLine();
115                         if (panduan == "Y")
116                         {
117                             stuway.tianjia(bb.code, bb.name, bb.sex, bb.birthday, bb.subjectcode);
118                             Console.Clear();
119                             dayinX.dayin1();
120                             Console.WriteLine("添加成功！");
121                             break;
122                         }
123                         else
124                         {
125                             Console.WriteLine("请重新输入信息！");
126                         }
127 
128                     }
129                 }
130                 #endregion
131 
132                 //4.修改
133                 #region
134                 else if (shuru == "2")
135                 {
136                     for (; ; )
137                     {
138                         shuxing bb = new shuxing();
139                         for (; ; )
140                         {
141                             Console.Write("请输入学生编号：");
142                             bb.code = Console.ReadLine();
143                             List<student> list = new studentway().chaxun1(bb.code);
144                             if (list.Count != 0)
145                             {
146                                 break;
147                             }
148                             else
149                             {
150                                 Console.WriteLine("该学生不存在，请重新输入！");
151                             }
152                         }
153                         for (; ; )
154                         {
155                             Console.Write("请输入修改后的学生姓名：");
156                             bb.name = Console.ReadLine();
157                             if (bb.name == "")
158                             {
159                                 Console.WriteLine("学生姓名不能为空，请重新输入！");
160                             }
161                             else
162                             {
163                                 break;
164                             }
165                         }
166                         for (; ; )
167                         {
168                             Console.Write("请输入修改后的学生性别：");
169                             string a1 = Console.ReadLine();
170                             if (a1 == "男")
171                             {
172                                 bb.sex = true;
173                                 break;
174                             }
175                             else if (a1 == "女")
176                             {
177                                 bb.sex = false;
178                                 break;
179                             }
180                             else
181                             {
182                                 Console.WriteLine("输入错误请重新输入！");
183                             }
184                         }
185                         for (; ; )
186                         {
187                             try
188                             {
189                                 Console.Write("请输入修改后的学生生日（XXXX-XX-XX）：");
190                                 bb.birthday = Convert.ToDateTime(Console.ReadLine());
191                                 break;
192                             }
193                             catch
194                             {
195                                 Console.WriteLine("输入错误请重新输入！");
196                             }
197                         }
198                         for (; ; )
199                         {
200                             Console.Write("请输入修改后的学生课程名称：");
201                             bb.subjectname = Console.ReadLine();
202                             SqlConnection conn = new SqlConnection("server=.;database=ADOkaoshi;user=sa;pwd=123;");
203                             SqlCommand com = conn.CreateCommand();
204                             com.CommandText = "select * from Ssubject where SubjectName like @subject";
205                             com.Parameters.Clear();
206                             com.Parameters.Add("@subject", "%" + bb.subjectname + "%");
207                             conn.Open();
208                             SqlDataReader a2 = com.ExecuteReader();
209                             if (a2.HasRows)
210                             {
211                                 a2.Read();
212                                 bb.subjectcode = a2["SubjectCode"].ToString();
213                                 break;
214                             }
215                             else
216                             {
217                                 Console.WriteLine("不存在这门课程，请重新输入！");
218                             }
219                         }
220                         studentway stuway = new studentway();
221                         dayin dayinX = new dayin();
222                         dayinX.dayin2(bb.code, bb.name, bb.sex, bb.birthday, bb.subjectname);
223                         Console.Write("您是否要修改为本条数据（Y/N）：");
224                         string panduan = Console.ReadLine();
225                         if (panduan == "Y")
226                         {
227                             stuway.xiugai(bb.code, bb.name, bb.sex, bb.birthday, bb.subjectcode);
228                             Console.Clear();
229                             dayinX.dayin1();
230                             Console.WriteLine("修改成功！");
231                             break;
232                         }
233                         else
234                         {
235                             Console.WriteLine("请重新输入信息！");
236                         }
237                     }
238                 }
239                 #endregion
240 
241                 //5.删除
242                 #region
243                 else if (shuru == "3")
244                 {
245                     for (; ; )
246                     {
247                         shuxing bb = new shuxing();
248                         for (; ; )
249                         {
250                             Console.Write("请输入学生编号：");
251                             bb.code = Console.ReadLine();
252                             List<student> list = new studentway().chaxun1(bb.code);
253                             if (list.Count != 0)
254                             {
255                                 break;
256                             }
257                             else
258                             {
259                                 Console.WriteLine("该学生不存在，请重新输入！");
260                             }
261                         }
262                         studentway stuway = new studentway();
263                         dayin dayinX = new dayin();
264                         Console.Write("您是否要删除编号为" + bb.code + "的数据（Y/N）：");
265                         string panduan = Console.ReadLine();
266                         if (panduan == "Y")
267                         {
268                             stuway.shanchu(bb.code);
269                             Console.Clear();
270                             dayinX.dayin1();
271                             Console.WriteLine("删除成功！");
272                             break;
273                         }
274                         else
275                         {
276                             Console.WriteLine("请重新输入信息！");
277                         }
278                     }
279                 }
280                 #endregion
281             }

 

posted on 2016-06-25 18:41  马MZJ  阅读(205)  评论(0)  编辑  收藏  举报
Copyright © 2024 马MZJ
Powered by .NET 8.0 on Kubernetes Powered By: 博客园