using System;using System.Collections.Generic;using System.Linq;using System.Text;using System.Data.SqlClient;namespace ConsoleApplication2{ class Program { static void Main(string[] args) { //创建数据库连接对象,并编写连接字符串,注意连接字符串不要写错 SqlConnection conn = new SqlConnection("server=.;database=Data0928;user=sa;pwd=123"); //创建数据库操作对象,创建过程是与刚创建的连接对象匹配起来 SqlCommand cmd = conn.CreateCommand(); for (; ; ) { Console.Write("请输入你想要的操作序号(1、删除 2、添加 3、更改 4、查看)"); try { int code = int.Parse(Console.ReadLine()); if (code == 1) { //删除 Console.Write("请输入想要删除的用户名:"); string user = Console.ReadLine(); //编写操作语句 TSQL语句 cmd.CommandText = "select ids,UserName,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 UserName='" + user + "'"; //数据库连接打开,准备执行操作 conn.Open(); SqlDataReader dr = cmd.ExecuteReader();//使用SqlDataReader类接受查询的数据 if (dr.HasRows)//判断dr里是否有数据 返回布尔类型 { while (dr.Read())//循环每一行 当超出时返回false { Console.WriteLine(dr["ids"] + "\t" + dr["UserName"] + "\t" + dr["PassWord"] + "\t" + dr["NickName"] + "\t" + (Convert.ToBoolean(dr["Sex"]) ? "男" : "女") + "\t" + Convert.ToDateTime(dr["Birthday"]).ToString("yyyy年MM月dd日") + "\t" + dr["Nation"] + "\t" + dr["Class"]); } //关闭数据库连接 conn.Close(); Console.Write("是否确定删除此条数据?(Y/N)"); string yn = Console.ReadLine(); if (yn == "y") { cmd.CommandText = "delete from Users where UserName='" + user + "'"; conn.Open(); int i = cmd.ExecuteNonQuery();//执行操作,并记录受影响的行数 if (i > 0) Console.WriteLine("删除成功。"); else { Console.WriteLine("删除失败"); } conn.Close(); } else if (yn == "n") { Console.WriteLine("取消删除。"); } else { Console.WriteLine("输入有误!"); } } else { Console.WriteLine("查无此条数据!"); } conn.Close(); } else if (code == 2) { //添加 Console.Write("请输入想要添加的用户名:"); string uname = Console.ReadLine(); cmd.CommandText = "select * from Users"; conn.Open(); SqlDataReader dr = cmd.ExecuteReader(); int biao = 0; //查询用户名是否重复 if (dr.HasRows) { while (dr.Read()) { if (dr["UserName"].ToString() == uname) { biao++; break; } } } conn.Close(); if (biao == 0)//用户名无重复 { string pwd; for (; ; ) { Console.Write("请输入密码(6到18位):"); string pwd1 = Console.ReadLine(); if (pwd1.Length >= 6 && pwd1.Length <= 18) { pwd = pwd1; break; } else { Console.WriteLine("密码长度不正确,请重新输入!"); continue; } } Console.Write("请输入昵称:"); string nick = Console.ReadLine(); string sex; for (; ; ) { Console.Write("请输入性别(请输入男女或者0、1):"); string sex1 = Console.ReadLine(); if(sex1=="0"||sex1=="1") { sex = sex1; break; } else if (sex1 == "男" || sex1 == "女") { if (sex1 == "男") { sex = "1"; break; } else { sex = "0"; break; } } else { Console.WriteLine("性别输入有误,请重新输入!"); continue; } } string bir; for (; ; ) { Console.Write("请输入生日:"); try { DateTime dt = DateTime.Parse(Console.ReadLine()); bir = dt.ToString(); break; } catch { Console.WriteLine("生日日期输入有误,请重新输入!"); continue; } } string nation; for (; ; ) { Console.Write("请输入民族:"); string nation1 = Console.ReadLine(); if(nation1=="汉族"||nation1=="汉") { nation = "N001"; break; } else if (nation1 == "满族" || nation1 == "满") { nation = "N002"; break; } else if (nation1 == "藏族" || nation1 == "藏") { nation = "N003"; break; } else if (nation1 == "彝族" || nation1 == "彝") { nation = "N004"; break; } else { Console.WriteLine("输入民族有误,请重新输入!"); continue; } } string cla; for (; ; ) { Console.Write("请输入班级:"); string cla1 = Console.ReadLine(); if(cla1=="一班"||cla1=="一") { cla = "C001"; break; } else if (cla1 == "二班" || cla1 == "二") { cla = "C002"; break; } else if (cla1 == "三班" || cla1 == "三") { cla = "C003"; break; } else if (cla1 == "四班" || cla1 == "四") { cla = "C004"; break; } else { Console.WriteLine("输入班级有误,请重新输入!"); continue; } } cmd.CommandText = "insert into Users values('" + uname + "','" + pwd + "','" + nick + "','" + sex + "','" + bir + "','" + nation + "','" + cla + "')"; conn.Open(); int s = cmd.ExecuteNonQuery(); if (s > 0) { Console.WriteLine("添加成功!"); } else { Console.WriteLine("添加失败!"); } conn.Close(); } else//用户名重复 { Console.WriteLine("该用户名已存在!"); } } else if(code==3) { //更改 Console.Write("请输入想要更改的用户名:"); string user = Console.ReadLine(); cmd.CommandText = "select ids,UserName,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 UserName='" + user + "'"; conn.Open(); SqlDataReader dr = cmd.ExecuteReader(); if (dr.HasRows) { while (dr.Read()) { Console.WriteLine(dr["ids"] + "\t" + dr["UserName"] + "\t" + dr["PassWord"] + "\t" + dr["NickName"] + "\t" + (Convert.ToBoolean(dr["Sex"]) ? "男" : "女") + "\t" + Convert.ToDateTime(dr["Birthday"]).ToString("yyyy年MM月dd日") + "\t" + dr["Nation"] + "\t" + dr["Class"]); } conn.Close(); Console.Write("是否更改此条数据?(Y/N)"); string yn = Console.ReadLine(); if (yn == "y") { string pwd; for (; ; ) { Console.Write("请输入密码(6到18位):"); string pwd1 = Console.ReadLine(); if (pwd1.Length >= 6 && pwd1.Length <= 18) { pwd = pwd1; break; } else { Console.WriteLine("密码长度不正确,请重新输入!"); continue; } } Console.Write("请输入昵称:"); string nick = Console.ReadLine(); string sex; for (; ; ) { Console.Write("请输入性别(请输入男女或者0、1):"); string sex1 = Console.ReadLine(); if (sex1 == "0" || sex1 == "1") { sex = sex1; break; } else if (sex1 == "男" || sex1 == "女") { if (sex1 == "男") { sex = "1"; break; } else { sex = "0"; break; } } else { Console.WriteLine("性别输入有误,请重新输入!"); continue; } } string bir; for (; ; ) { Console.Write("请输入生日:"); try { DateTime dt = DateTime.Parse(Console.ReadLine()); bir = dt.ToString(); break; } catch { Console.WriteLine("生日日期输入有误,请重新输入!"); continue; } } string nation; for (; ; ) { Console.Write("请输入民族:"); string nation1 = Console.ReadLine(); if (nation1 == "汉族" || nation1 == "汉") { nation = "N001"; break; } else if (nation1 == "满族" || nation1 == "满") { nation = "N002"; break; } else if (nation1 == "藏族" || nation1 == "藏") { nation = "N003"; break; } else if (nation1 == "彝族" || nation1 == "彝") { nation = "N004"; break; } else { Console.WriteLine("输入民族有误,请重新输入!"); continue; } } string cla; for (; ; ) { Console.Write("请输入班级:"); string cla1 = Console.ReadLine(); if (cla1 == "一班" || cla1 == "一") { cla = "C001"; break; } else if (cla1 == "二班" || cla1 == "二") { cla = "C002"; break; } else if (cla1 == "三班" || cla1 == "三") { cla = "C003"; break; } else if (cla1 == "四班" || cla1 == "四") { cla = "C004"; break; } else { Console.WriteLine("输入班级有误,请重新输入!"); continue; } } cmd.CommandText = "update User set Password='"+pwd+"',NickName='"+nick+"',Sex="+sex+",Birthday='"+bir+"',Nation='"+nation+"',Class='"+cla+"' where UserName='" + user + "'"; conn.Open(); int i = cmd.ExecuteNonQuery();//执行操作,并记录受影响的行数 if (i > 0) Console.WriteLine("更改成功。"); else { Console.WriteLine("更改失败"); } conn.Close(); } else if (yn == "n") { Console.WriteLine("取消更改。"); } else { Console.WriteLine("输入有误!"); } } else { Console.WriteLine("查无此条数据!"); } conn.Close(); } else if(code==4) { //查看 Console.Write("请输入想要查看的用户名(输入*代表查看全部):"); string user = Console.ReadLine(); if (user != "*") { cmd.CommandText = "select ids,UserName,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 UserName='" + user + "'"; } else { cmd.CommandText = "select ids,UserName,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"; } conn.Open(); SqlDataReader dr = cmd.ExecuteReader(); if (dr.HasRows) { while (dr.Read()) { Console.WriteLine(dr["ids"] + "\t" + dr["UserName"] + "\t" + dr["PassWord"] + "\t" + dr["NickName"] + "\t" + (Convert.ToBoolean(dr["Sex"]) ? "男" : "女") + "\t" + Convert.ToDateTime(dr["Birthday"]).ToString("yyyy年MM月dd日") + "\t" + dr["Nation"] + "\t" + dr["Class"]); } conn.Close(); } else { Console.WriteLine("查无此条数据!"); } conn.Close(); } else { Console.WriteLine("无此操作序号有误,请重新输入!"); continue; } } catch { Console.WriteLine("输入有误,请重新输入!"); continue; } } } }}