实现数据查询，添加，删除，修改各项功能
业务逻辑层：
using System;using System.Collections.Generic;using System.Linq;using System.Text;using 实体类_数据访问类练习.App_Code;namespace 实体类_数据访问类练习{ class Program { static void Main(string[] args) { for (; ; ) { Console.WriteLine("请输入您要选择的操作：1、查询 2、添加 3、删除 4、修改 0、退出 "); string operate = Console.ReadLine(); //查询 if (operate == "1") { Console.WriteLine(""); Console.Write("请输入您要查询的用户名（查询所有人员信息请输入*，退出请输入0。）:"); string operate_select = Console.ReadLine(); if (operate_select == "*") { //完整查询操作，创建泛型数组引用UsersDate的Select方法 List list = new UsersData().Select(); //List list = new List (); //UsersData ud = new UsersData(); //list = ud.Select(); //遍历数组 Console.WriteLine(""); Console.WriteLine("============================数据信息=============================="); Console.WriteLine("序号\t用户名\t密码\t昵称\t性别\t民族\t班级\t生日"); foreach (Users uuu in list) { //Console.WriteLine(uuu.Code + "\t" + uuu.Name + "\t" + uuu.Password + "\t" + uuu.Nick + "\t" + (uuu.Sex ? "男" : "女") + "\t" + uuu.Nation + "\t" + uuu.Class + "\t" + Convert.ToDateTime(uuu.Birthday).ToShortDateString()); Console.WriteLine(uuu.Code + "\t" + uuu.Name + "\t" + uuu.Password + "\t" + uuu.Nick + "\t" + uuu.SexStr + "\t" + uuu.NationName + "\t" + uuu.ClassName + "\t" + uuu.BirthdayStr); Console.WriteLine(""); } } else if (operate_select == "0") { break; } else { //完整查询操作，创建泛型数组引用UsersDate的Select方法 List list = new UsersData().Select(operate_select); //List list = new List (); //UsersData ud = new UsersData(); //list = ud.Select(); //遍历数组 Console.WriteLine(""); Console.WriteLine("============================数据信息=============================="); Console.WriteLine("序号\t用户名\t密码\t昵称\t性别\t民族\t班级\t生日"); foreach (Users uuu in list) { //Console.WriteLine(uuu.Code + "\t" + uuu.Name + "\t" + uuu.Password + "\t" + uuu.Nick + "\t" + (uuu.Sex ? "男" : "女") + "\t" + uuu.Nation + "\t" + uuu.Class + "\t" + Convert.ToDateTime(uuu.Birthday).ToShortDateString()); Console.WriteLine(uuu.Code + "\t" + uuu.Name + "\t" + uuu.Password + "\t" + uuu.Nick + "\t" + uuu.SexStr + "\t" + uuu.NationName + "\t" + uuu.ClassName + "\t" + uuu.BirthdayStr);//引用扩展属性 Console.WriteLine(""); } } } //添加用户操作 else if (operate == "2") { Console.WriteLine(""); Users user = new Users(); Console.Write("请输入要添加的用户名："); user.Name = Console.ReadLine(); Console.Write("请输入要添加的密码："); user.Password = Console.ReadLine(); Console.Write("请输入要添加的昵称："); user.Nick = Console.ReadLine(); Console.Write("请输入要添加的性别："); user.Sex = Convert.ToBoolean(Console.ReadLine()); Console.Write("请输入要添加的民族："); user.Nation = Console.ReadLine(); Console.Write("请输入要添加的班级："); user.Class = Console.ReadLine(); Console.Write("请输入要添加的生日："); user.Birthday = Convert.ToDateTime(Console.ReadLine()); if (new UsersData().Insert(user))//数据访问类中insert方法的返回值 { Console.WriteLine("添加成功！"); } else { Console.WriteLine("添加失败！"); } Console.WriteLine(""); } //删除用户操作 else if (operate == "3") { Console.WriteLine(""); Console.Write("请输入你要删除的用户名："); string operate_delete = Console.ReadLine();//用户操作 //判断是否插寻到此用户信息，返回TRUE表示有此用户信息 if (new UsersData().select(operate_delete)) { Console.WriteLine("查询到此用户信息，是否删除（Y/N）："); string yn = Console.ReadLine();//用户操作 if (yn.ToUpper() == "Y") { //判断删除此用户信息是否成功，返回TRUE表示删除成功 if (new UsersData().Delete(operate_delete)) { Console.WriteLine("删除成功！"); } //返回false表示删除失败 else { Console.WriteLine("删除失败！！"); } } else { Console.WriteLine("删除操作去取消！！"); } } //判断是否插寻到此用户信息，返回false表示无此用户信息 else { Console.WriteLine("未查询到此用户！！"); } Console.WriteLine(""); } //修改用户信息操作 else if (operate == "4") { Console.WriteLine(""); for (; ; ) { Console.Write("请输入要修改的用户名(输入0退出修改返回上一级)："); string operate_updata = Console.ReadLine(); if(operate_updata=="0") { break; } //查询是否有此用户，返回TRUE表示有此用户 if (new UsersData().select(operate_updata)) { Console.WriteLine("查询到此用户信息，是否修改（Y/N）："); string yn = Console.ReadLine();//用户操作 if (yn.ToUpper() == "Y") { Console.WriteLine ("请选择：*、修改用户所有信息；1、修改用户密码；2、修改用户昵称；3、修改用户性别；4、修改用户民族；5、修改用户班级；6、修改用户生日；0、结束修改"); string updata_opt = Console.ReadLine();//用户操作 //修改用户所有信息 if(updata_opt=="*") { Users us = new Users(); Console.Write("请输入要修改的密码："); us.Password = Console.ReadLine(); Console.Write("请输入要修改的昵称："); us.Nick = Console.ReadLine(); Console.Write("请输入要修改的性别："); us.Sex = Convert.ToBoolean(Console.ReadLine()); Console.Write("请输入要修改的民族："); us.Nation = Console.ReadLine(); Console.Write("请输入要修改的班级："); us.Class = Console.ReadLine(); Console.Write("请输入要修改的生日："); us.Birthday = Convert.ToDateTime(Console.ReadLine()); if (new UsersData().Update(us)) { Console.WriteLine("修改成功！！"); } else { Console.WriteLine("修改失败！！"); } } //修改用户密码 else if(updata_opt=="1") { Users us = new Users(); Console.Write("请输入要修改的密码："); us.Password = Console.ReadLine(); if (new UsersData().Update1(us)) { Console.WriteLine("修改成功！！"); } else { Console.WriteLine("修改失败！！"); } } //修改用户昵称 else if (updata_opt == "2") { Users us = new Users(); Console.Write("请输入要修改的昵称："); us.Nick = Console.ReadLine(); if (new UsersData().Update2(us)) { Console.WriteLine("修改成功！！"); } else { Console.WriteLine("修改失败！！"); } } //修改性别 else if (updata_opt == "3") { Users us = new Users(); Console.Write("请输入要修改的性别："); us.Sex = Convert.ToBoolean(Console.ReadLine()); if (new UsersData().Update3(us)) { Console.WriteLine("修改成功！！"); } else { Console.WriteLine("修改失败！！"); } } //修改民族 else if (updata_opt == "4") { Users us = new Users(); Console.Write("请输入要修改的民族："); us.Nation= Console.ReadLine(); if (new UsersData().Update4(us)) { Console.WriteLine("修改成功！！"); } else { Console.WriteLine("修改失败！！"); } } //修改班级 else if (updata_opt == "5") { Users us = new Users(); Console.Write("请输入要修改的班级："); us.Class = Console.ReadLine(); if (new UsersData().Update5(us)) { Console.WriteLine("修改成功！！"); } else { Console.WriteLine("修改失败！！"); } } //修改生日 else if (updata_opt == "6") { Users us = new Users(); Console.Write("请输入要修改的生日："); us.Birthday = Convert.ToDateTime(Console.ReadLine()); if (new UsersData().Update6(us)) { Console.WriteLine("修改成功！！"); } else { Console.WriteLine("修改失败！！"); } } //退出修改操作 else { break; } } else { Console.WriteLine("修改操作去取消！！"); } } //查询是否有此用户，返回false表示无此用户 else { Console.WriteLine("未查询到此用户！！"); } } Console.WriteLine(""); } //结束操作,退出 else { break; } Console.WriteLine(""); } Console.ReadLine(); } }} 实体类：
using System;using System.Collections.Generic;using System.Linq;using System.Text;using System.Data.SqlClient;namespace 实体类_数据访问类练习.App_Code{ public class Users { SqlConnection conn = null; SqlCommand comm = null; public Users() { conn = new SqlConnection("server=.;database=Data1128;user=sa;pwd=123"); comm = conn.CreateCommand(); } private int _Code; /// /// code /// public int Code { get { return _Code; } set { _Code = value; } } private string _Name; /// /// 用户名 /// public string Name { get { return _Name; } set { _Name = value; } } private string _Password; /// /// 密码 /// public string Password { get { return _Password; } set { _Password = value; } } private string _Nick; /// /// 昵称 /// public string Nick { get { return _Nick; } set { _Nick = value; } } private bool _Sex; /// /// 性别 /// public bool Sex { get { return _Sex; } set { _Sex = value; } } /// ///性别的扩展属性 /// public string SexStr { get { return _Sex ? "男" : "女"; } } private string _Nation; /// /// 民族 /// public string Nation { get { return _Nation; } set { _Nation = value; } } /// /// 民族的扩展属性 /// public string NationName { get { string end = "未填写"; comm.CommandText = "select *from Nation where nationcode=@a"; comm.Parameters.Clear(); comm.Parameters.AddWithValue("@a", _Nation); conn.Open(); SqlDataReader dr = comm.ExecuteReader(); if (dr.HasRows) { dr.Read(); end = dr["nationname"].ToString(); } conn.Close(); return end; } } private string _Class; /// /// 班级 /// public string Class { get { return _Class; } set { _Class = value; } } /// /// 班级的扩展属性 /// public string ClassName { get { string end = "未填写"; comm.CommandText = "select *from Class where classcode=@a"; comm.Parameters.Clear(); comm.Parameters.AddWithValue("@a", _Class); conn.Open(); SqlDataReader dr = comm.ExecuteReader(); if (dr.HasRows) { dr.Read(); end = dr["classname"].ToString(); } conn.Close(); return end; } } private DateTime _Birthday; /// /// 生日 /// public DateTime Birthday { get { return _Birthday; } set { _Birthday = value; } } /// /// 生日的扩展属性 /// public string BirthdayStr { get { return _Birthday.ToString("yyyy年MM月dd日"); } } }} 数据访问类
using System;using System.Collections.Generic;using System.Linq;using System.Text;using System.Data.SqlClient;namespace 实体类_数据访问类练习.App_Code{ public class UsersData { SqlConnection conn = null; SqlCommand comm = null; public UsersData() { conn = new SqlConnection("server=.;database=Data1128;user=sa;pwd=123"); comm = conn.CreateCommand(); } /// /// 查询所有数据 /// /// Select() { List list = new List (); //comm.CommandText = "select code,usename,password,nick,sex,(select nationname from Nation where nationcode=Users.nation) as 'nation' ,classname as 'class',birthday from Users join Class on Class.classcode=Users.class"; comm.CommandText = "select * from Users"; conn.Open(); SqlDataReader dr = comm.ExecuteReader(); if (dr.HasRows) { while (dr.Read()) { Users u = new Users(); u.Code = Convert.ToInt32(dr["Code"]); u.Name = dr["USENAME"].ToString(); u.Password = dr["Password"].ToString(); u.Nick = dr["Nick"].ToString(); u.Sex = Convert.ToBoolean(dr["Sex"]); u.Nation = dr["Nation"].ToString(); u.Class = dr["Class"].ToString(); u.Birthday = Convert.ToDateTime(dr["Birthday"]); list.Add(u); } } conn.Close(); return list; } /// /// 查询单个用户的数据 /// /// /// Select(string username) { List ulist = new List (); //comm.CommandText = "select code,usename,password,nick,sex,(select nationname from Nation where nationcode=Users.nation) as 'nation' ,classname as 'class',birthday from Users join Class on Class.classcode=Users.class where usename='" + username + "'"; comm.CommandText = "select *from Users where usename='" + username + "'"; conn.Open(); SqlDataReader dr = comm.ExecuteReader(); if (dr.HasRows) { while (dr.Read()) { Users u = new Users(); u.Code = Convert.ToInt32(dr["Code"]); u.Name = dr["USEName"].ToString(); u.Password = dr["Password"].ToString(); u.Nick = dr["Nick"].ToString(); u.Sex = Convert.ToBoolean(dr["Sex"]); u.Nation = dr["Nation"].ToString(); u.Class = dr["Class"].ToString(); u.Birthday = Convert.ToDateTime(dr["Birthday"]); ulist.Add(u); } } conn.Close(); return ulist; } /// /// 将数据添加到Users表中 /// /// 要添加到表中的Users对象 /// 添加成功返回true,失败返回false public bool Insert(Users us) { bool ok=false; int count = 0; comm.CommandText = "insert into Users values(@a,@b,@c,@d,@e,@f,@g)"; comm.Parameters.Clear(); comm.Parameters.AddWithValue("@a",us.Name); comm.Parameters.AddWithValue("@b", us.Password); comm.Parameters.AddWithValue("@c", us.Nick); comm.Parameters.AddWithValue("@d", us.Sex); comm.Parameters.AddWithValue("@e", us.Nation); comm.Parameters.AddWithValue("@f", us.Class); comm.Parameters.AddWithValue("@g", us.Birthday); try { conn.Open(); count = comm.ExecuteNonQuery(); } catch { ok = false; } finally { conn.Close(); } if (count > 0) ok = true; else ok = false; return ok; } /// /// 完整删除和完整修改中，查询此用户是否存在 /// /// 要查询的Users对象 /// 返回TRUE表示由此用户 public bool select(string username) { bool has = false; comm.CommandText = "select *from Users where usename=@a"; comm.Parameters.Clear(); comm.Parameters.AddWithValue("@a",username); conn.Open(); SqlDataReader dr = comm.ExecuteReader(); if(dr.HasRows) { has = true; } conn.Close(); return has; } /// /// 完整删除中查到用户后执行的删除操作 /// /// 要删除的Users对象< /// 返回true表示删除成功 public bool Delete(string uname) { bool have = false; int count_del = 0; comm.CommandText = "delete from Users where usename=@a"; comm.Parameters.Clear(); comm.Parameters.AddWithValue("@a",uname); try { conn.Open(); count_del = comm.ExecuteNonQuery(); } catch { have = false; } finally { conn.Close(); } if (count_del > 0) have = true; return have; } /// /// 完整修改用户所有信息中查到用户后执行的修改操作 /// /// /// /// 完整修改用户中查到用户后执行的单独修改密码操作 /// /// /// /// 完整修改用户中查到用户后执行的单独修改昵称操作 /// /// /// /// 完整修改用户中查到用户后执行的单独修改性别操作 /// /// /// /// 完整修改用户中查到用户后执行的单独修改民族操作 /// /// /// /// 完整修改用户中查到用户后执行的单独修改班级操作 /// /// /// /// 完整修改用户中查到用户后执行的单独修改生日操作 /// /// ///