练习题:
1、查询显示2、请输入你想要做的操作(1:添加,2:删除,3:修改):3、提示用户操作是否成功,刷新数据,回到2等待用户操作建表:
1 create database ADOlianxi0425 2 go 3 use ADOlianxi0425 4 go 5 create table student 6 ( 7 code int primary key identity(100,1), 8 name nvarchar(50), 9 sex char(5),10 birthday datetime,11 score decimal(18,2) 12 )13 go14 insert into student values('张三','男','1993-2-5',89.5)15 insert into student values('张四','男','1994-8-13',80)16 insert into student values('张五','女','1995-6-20',99)17 insert into student values('张六','男','1996-11-11',62)18 insert into student values('张七','男','1997-4-9',69)19 go
操作:
1 SqlConnection a = new SqlConnection("server=.;database=ADOlianxi0425;user=sa;pwd=123;"); 2 SqlCommand b = a.CreateCommand(); 3 bool e = true; 4 for (; ; ) 5 { 6 string ML = ""; 7 if (e == false) 8 { 9 Console.Write("请输入添加/删除/修改/结束:"); 10 ML = Console.ReadLine(); 11 if (ML == "添加")//添加数据 12 { 13 for (; ; ) 14 { 15 try 16 { 17 Console.Write("请输入要添加的学生姓名:"); 18 string name = Console.ReadLine(); 19 Console.Write("请输入要添加的学生性别:"); 20 string sex = Console.ReadLine(); 21 Console.Write("请输入要添加的学生生日:"); 22 DateTime birthday = Convert.ToDateTime(Console.ReadLine()); 23 Console.Write("请输入要添加的学生成绩:"); 24 decimal score = Convert.ToDecimal(Console.ReadLine()); 25 b.CommandText = "insert into student values('" + name + "','" + sex + "','" + birthday + "','" + score + "')"; 26 a.Open(); 27 b.ExecuteNonQuery(); 28 a.Close(); 29 break; 30 } 31 catch 32 { 33 Console.WriteLine("输入错误请重新输入!"); 34 } 35 } 36 } 37 else if (ML == "删除")//删除数据 38 { 39 for (; ; ) 40 { 41 try 42 { 43 Console.Write("请输入要删除的学生学号:"); 44 int code = int.Parse(Console.ReadLine()); 45 b.CommandText = "select * from student where code = " + code; 46 a.Open(); 47 SqlDataReader c1 = b.ExecuteReader(); 48 bool d; 49 if (c1.HasRows)//判断数据库中是否存在此学生信息 50 { 51 d = true; 52 } 53 else 54 { 55 d = false; 56 } 57 a.Close(); 58 if (d == true) 59 { 60 b.CommandText = "delete from student where code = " + code; 61 a.Open(); 62 b.ExecuteNonQuery(); 63 a.Close(); 64 break; 65 } 66 else 67 { 68 Console.WriteLine("没有此学生信息,请重新输入!"); 69 } 70 } 71 catch 72 { 73 Console.WriteLine("输入错误请重新输入!"); 74 } 75 } 76 } 77 else if (ML == "修改")//修改数据 78 { 79 for (; ; ) 80 { 81 try 82 { 83 Console.Write("请输入要修改的学生的学号:"); 84 int code = int.Parse(Console.ReadLine()); 85 b.CommandText = "select * from student where code = " + code; 86 a.Open(); 87 SqlDataReader c1 = b.ExecuteReader(); 88 bool d; 89 if (c1.HasRows)//判断数据库中是否存在此学生信息 90 { 91 d = true; 92 } 93 else 94 { 95 d = false; 96 } 97 a.Close(); 98 if (d == true) 99 {100 Console.Write("请输入修改后的学生姓名:");101 string name = Console.ReadLine();102 Console.Write("请输入修改后的学生性别:");103 string sex = Console.ReadLine();104 Console.Write("请输入修改后的学生生日:");105 DateTime birthday = Convert.ToDateTime(Console.ReadLine());106 Console.Write("请输入修改后的学生成绩:");107 decimal score = Convert.ToDecimal(Console.ReadLine());108 b.CommandText = "update student set name = '" + name + "',sex = '" + sex + "',birthday = '" + birthday + "' ,score = " + score + "where code = " + code;109 a.Open();110 b.ExecuteNonQuery();111 a.Close();112 break;113 }114 else115 {116 Console.WriteLine("没有此学生信息,请重新输入!");117 }118 }119 catch120 {121 Console.WriteLine("输入错误请重新输入!");122 }123 }124 }125 else if (ML == "结束")//结束运行126 {127 Console.Clear();128 Console.WriteLine("----------------------------------谢谢使用!-----------------------------------");129 break;130 }131 else132 {133 Console.WriteLine("您的输入有误请重新输入!");134 }135 }136 b.CommandText = "select * from student";137 a.Open();138 SqlDataReader c = b.ExecuteReader();//显示表的全部数据139 Console.Clear();140 if (c.HasRows)141 {142 while (c.Read())143 {144 Console.WriteLine("学号:" + c["code"] + ",姓名:" + c["name"] + ",性别:" + c["sex"] + ",生日:" + ((DateTime)c["birthd ay"]).ToString("yyyy年MM月dd日") + ",成绩" + ((decimal)c["score"]).ToString("#.##"));145 }146 }147 a.Close();148 if (e == true)149 {150 e = false;151 continue;152 }153 if (ML == "添加" || ML == "修改" || ML == "删除")//如果输入错误不执行下面的代码154 { 155 if (ML == "添加")156 {157 Console.WriteLine("添加成功!");158 }159 else if (ML == "修改")160 {161 Console.WriteLine("修改成功!");162 }163 else if (ML == "删除")164 {165 Console.WriteLine("删除成功!");166 }167 }168 }