using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Threading.Tasks; using System.Data.SqlClient; namespace boss { class Program { static void Main(string[] args) { //1、创建数据库连接类 string sql = "server=(local);database=Class0928;user=sa;pwd=123;";//编写连接字符串 SqlConnection conn = new SqlConnection(sql); //2、创建数据库操作对象，创建过程是与刚创建的连接对象匹配起来 SqlCommand cmd = conn.CreateCommand(); for (; ; ) { //try //{ Console.WriteLine("请输入您要执行的操作：1、查看 2、增加数据 3、修改数据 4、删除数据"); int code = int.Parse(Console.ReadLine()); if (code == 1) { cmd.CommandText = "select *from users"; conn.Open(); SqlDataReader dr = cmd.ExecuteReader(); if (dr.HasRows) { while (dr.Read()) { Console.WriteLine(dr["ids"] + " " + dr["usersname"] + " " + dr["password"] + " " + dr["nickname"] + " " + (Convert.ToBoolean(dr["sex"]) ? "男" : "女") + " " + dr["birthday"] + " " + dr["nation"] + " " + dr["class"]); } } conn.Close(); continue; } else if (code == 2) { Console.Write("请输入想要添加的用户名："); string uname = Console.ReadLine(); cmd.CommandText = "select *from users"; conn.Open(); SqlDataReader dr = cmd.ExecuteReader(); int a = 0; if (dr.HasRows) { while (dr.Read()) { //检查是否用户名重复 if (dr["usersname"].ToString() == uname) { a++; break; } } } conn.Close(); if (a == 0)//用户名无重复 { string pwd; for (; ; ) { Console.Write("请输入密码："); string mima = Console.ReadLine(); if (mima != "") { pwd = mima; break; } else { Console.WriteLine("密码不能为空，请重新输入："); continue; } } string nick; for (; ; ) { Console.Write("请输入昵称："); string miname = Console.ReadLine(); if (miname != "") { nick = miname; break; } else { Console.WriteLine("昵称不能为空，请重新输入："); continue; } } string sex; for (; ; ) { Console.Write("请输入性别(男/女)： "); string sex1 = Console.ReadLine(); if (sex1 == "男") { sex = "True"; break; } else if (sex1 == "女") { sex = "False"; break; } else { Console.WriteLine("输入性别有误，请重新输入："); continue; } } string bir; for (; ; ) { Console.Write("请输入您的生日："); try //尝试去做，如果有错，从错误的句直接跳转到catch { DateTime shengri = DateTime.Parse(Console.ReadLine()); bir = shengri.ToString(); break; } catch { Console.WriteLine("输入日期格式错误"); continue; } } string nation; for (; ; ) { Console.Write("请输入民族："); string nation1 = Console.ReadLine(); if (nation1 == "汉族") { nation = "N001"; break; } else if (nation1 == "满族") { nation = "N002"; break; } else if (nation1 == "藏族") { nation = "N003"; break; } else if (nation1 == "彝族") { nation = "N004"; break; } else { Console.WriteLine("输入民族有误，请重新输入！"); continue; } } string cla; for (; ; ) { Console.Write("请输入班级："); string banji1 = Console.ReadLine(); if (banji1 == "一班") { cla = "C001"; break; } else if (banji1 == "二班") { cla = "C002"; break; } else if (banji1 == "三班") { cla = "C003"; break; } else if (banji1 == "四班") { cla = "C004"; break; } else { Console.WriteLine("输入班级有误，请重新输入！"); continue; } } cmd.CommandText = "insert into users values('" + uname + "','" + pwd + "','" + nick + "','" + sex + "','" + bir + "','" + nation + "','" + cla + "')"; conn.Open(); int count = cmd.ExecuteNonQuery(); conn.Close(); if (count > 0) { Console.WriteLine("添加成功！"); } else { Console.WriteLine("添加失败！"); } } else//用户名重复 { Console.WriteLine("该用户名已存在！"); continue; } } else if (code == 3) { bool has = false;//判断用输入的数据是否存在，true表示存在，false不存在 Console.Write("请输入要修改的用户名："); string uname = Console.ReadLine(); cmd.CommandText = "select *from users where usersname = '" + uname + "'"; conn.Open(); SqlDataReader dr = cmd.ExecuteReader(); if (dr.HasRows) //如果查到此用户信息 { has = true;//将中间变量更改为true，证明有此条信息 } conn.Close(); if (has)//如果有此条信息，那么需要进行删除判断 { Console.Write("已查到此用户信息，是否要修改？（Y/N）"); string u = Console.ReadLine(); //记录用户的操作 if (u.ToUpper() == "Y")//判断用户操作，如果是Y，说明要删除 { Console.Write("请输入要修改的密码："); string pwd = Console.ReadLine(); Console.Write("请输入要修改的昵称："); string nick = Console.ReadLine(); Console.Write("请输入要修改的性别："); string sex = Console.ReadLine(); Console.Write("请输入要修改的生日："); string bir = Console.ReadLine(); Console.Write("请输入要修改的民族："); string nation = Console.ReadLine(); Console.Write("请输入要修改的班级："); string cla = Console.ReadLine(); cmd.CommandText = "update Users set PassWord=@pwd,NickName=@nick,Sex=@sex,Birthday=@bir,Nation=@nation,Class=@cla where UsersName=@uname"; cmd.Parameters.Clear(); cmd.Parameters.AddWithValue("@pwd", pwd); cmd.Parameters.AddWithValue("@nick", nick); cmd.Parameters.AddWithValue("@sex", sex); cmd.Parameters.AddWithValue("@bir", bir); cmd.Parameters.AddWithValue("@nation", nation); cmd.Parameters.AddWithValue("@cla", cla); cmd.Parameters.AddWithValue("@uname", uname); conn.Open(); cmd.ExecuteNonQuery(); conn.Close(); Console.WriteLine("修改成功！"); } else//用户选择不删除 { Console.WriteLine("修改取消！"); } } else//未查到用户信息，判断has=false { Console.WriteLine("用户名输入错误！未查到此用户信息！"); } } else if (code == 4) { Console.Write("请输入想要删除的用户名："); string user = Console.ReadLine(); cmd.CommandText = "select ids,UsersName,PassWord,NickName,Sex,Birthday,(select NationName from Nation where NationCode=Users.Nation) as 'Nation' ,ClassName as 'Class' from Users join Class on Class.ClassCode=Users.Class where UsersName='" + user + "'"; conn.Open(); SqlDataReader dr = cmd.ExecuteReader(); if (dr.HasRows) { conn.Close(); //数据存在情况下 Console.WriteLine("查到此数据，是否要执行删除操作，如果要删除请输入：1"); int sr = Convert.ToInt32(Console.ReadLine()); if (sr == 1) { cmd.CommandText = "delete from Users where UsersName='" + user + "'"; conn.Open(); int s = cmd.ExecuteNonQuery();//执行操作，并记录受影响的行数 if (s > 0) Console.WriteLine("删除成功。"); else { Console.WriteLine("删除失败"); } } else { Console.WriteLine("取消删除。"); } } else { //数据不存在 Console.WriteLine("输入的编号错误！"); } conn.Close(); } else { Console.WriteLine("无此操作序号有误，请重新输入！"); continue; } } //catch //{ // Console.WriteLine("输入有误，请重新输入！"); // continue; //} Console.ReadKey(); } } }
