ado.net 属性扩展 综合练习
实现数据查询，添加，删除，修改各项功能
业务逻辑层：
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<Users> list = new UsersData().Select(); //List<Users> list = new List<Users>(); //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<Users> list = new UsersData().Select(operate_select); //List<Users> list = new List<Users>(); //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; /// <summary> /// code /// </summary> public int Code { get { return _Code; } set { _Code = value; } } private string _Name; /// <summary> /// 用户名 /// </summary> public string Name { get { return _Name; } set { _Name = value; } } private string _Password; /// <summary> /// 密码 /// </summary> public string Password { get { return _Password; } set { _Password = value; } } private string _Nick; /// <summary> /// 昵称 /// </summary> public string Nick { get { return _Nick; } set { _Nick = value; } } private bool _Sex; /// <summary> /// 性别 /// </summary> public bool Sex { get { return _Sex; } set { _Sex = value; } } /// <summary> ///性别的扩展属性 /// </summary> public string SexStr { get { return _Sex ? "男" : "女"; } } private string _Nation; /// <summary> /// 民族 /// </summary> public string Nation { get { return _Nation; } set { _Nation = value; } } /// <summary> /// 民族的扩展属性 /// </summary> 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; /// <summary> /// 班级 /// </summary> public string Class { get { return _Class; } set { _Class = value; } } /// <summary> /// 班级的扩展属性 /// </summary> 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; /// <summary> /// 生日 /// </summary> public DateTime Birthday { get { return _Birthday; } set { _Birthday = value; } } /// <summary> /// 生日的扩展属性 /// </summary> 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(); } /// <summary> /// 查询所有数据 /// </summary> /// <returns></returns> public List<Users> Select() { List<Users> list = new List<Users>(); //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; } /// <summary> /// 查询单个用户的数据 /// </summary> /// <param name="username"></param> /// <returns></returns> public List<Users> Select(string username) { List<Users> ulist = new List<Users>(); //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; } /// <summary> /// 将数据添加到Users表中 /// </summary> /// <param name="us">要添加到表中的Users对象</param> /// <returns>添加成功返回true,失败返回false</returns> 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; } /// <summary> /// 完整删除和完整修改中，查询此用户是否存在 /// </summary> /// <param name="username">要查询的Users对象</param> /// <returns>返回TRUE表示由此用户</returns> 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; } /// <summary> /// 完整删除中查到用户后执行的删除操作 /// </summary> /// <param name="uname">要删除的Users对象<</param> /// <returns>返回true表示删除成功</returns> 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; } /// <summary> /// 完整修改用户所有信息中查到用户后执行的修改操作 /// </summary> /// <param name="user"></param> /// <returns></returns> public bool Update(Users user) { bool ok = false; int count = 0; comm.CommandText = "update Users set password=@b,nick=@c,sex=@d,nation=@e,class=@f,birthday=@g"; comm.Parameters.Clear(); comm.Parameters.AddWithValue("@b",user.Password); comm.Parameters.AddWithValue("@c", user.Nick); comm.Parameters.AddWithValue("@d", user.Sex); comm.Parameters.AddWithValue("@e", user.Nation); comm.Parameters.AddWithValue("@f", user.Class); comm.Parameters.AddWithValue("@g", user.Birthday); try { conn.Open(); count = comm.ExecuteNonQuery(); } catch { ok = false; } finally { conn.Close(); } if(count>0) { ok=true; } return ok; } /// <summary> /// 完整修改用户中查到用户后执行的单独修改密码操作 /// </summary> /// <param name="user1"></param> /// <returns></returns> public bool Update1(Users user) { bool ok = false; int count = 0; comm.CommandText = "update Users set password=@b"; comm.Parameters.Clear(); comm.Parameters.AddWithValue("@b", user.Password); try { conn.Open(); count = comm.ExecuteNonQuery(); } catch { ok = false; } finally { conn.Close(); } if (count > 0) { ok = true; } return ok; } /// <summary> /// 完整修改用户中查到用户后执行的单独修改昵称操作 /// </summary> /// <param name="user1"></param> /// <returns></returns> public bool Update2(Users user1) { bool ok = false; int count = 0; comm.CommandText = "update Users set nick=@b"; comm.Parameters.Clear(); comm.Parameters.AddWithValue("@b", user1.Nick); try { conn.Open(); count = comm.ExecuteNonQuery(); } catch { ok = false; } finally { conn.Close(); } if (count > 0) { ok = true; } return ok; } /// <summary> /// 完整修改用户中查到用户后执行的单独修改性别操作 /// </summary> /// <param name="user1"></param> /// <returns></returns> public bool Update3(Users user1) { bool ok = false; int count = 0; comm.CommandText = "update Users set sex=@b"; comm.Parameters.Clear(); comm.Parameters.AddWithValue("@b", user1.Sex); try { conn.Open(); count = comm.ExecuteNonQuery(); } catch { ok = false; } finally { conn.Close(); } if (count > 0) { ok = true; } return ok; } /// <summary> /// 完整修改用户中查到用户后执行的单独修改民族操作 /// </summary> /// <param name="user1"></param> /// <returns></returns> public bool Update4(Users user1) { bool ok = false; int count = 0; comm.CommandText = "update Users set nation=@b"; comm.Parameters.Clear(); comm.Parameters.AddWithValue("@b", user1.Nation); try { conn.Open(); count = comm.ExecuteNonQuery(); } catch { ok = false; } finally { conn.Close(); } if (count > 0) { ok = true; } return ok; } /// <summary> /// 完整修改用户中查到用户后执行的单独修改班级操作 /// </summary> /// <param name="user1"></param> /// <returns></returns> public bool Update5(Users user1) { bool ok = false; int count = 0; comm.CommandText = "update Users set class=@b"; comm.Parameters.Clear(); comm.Parameters.AddWithValue("@b", user1.Class); try { conn.Open(); count = comm.ExecuteNonQuery(); } catch { ok = false; } finally { conn.Close(); } if (count > 0) { ok = true; } return ok; } /// <summary> /// 完整修改用户中查到用户后执行的单独修改生日操作 /// </summary> /// <param name="user1"></param> /// <returns></returns> public bool Update6(Users user1) { bool ok = false; int count = 0; comm.CommandText = "update Users set birthday=@b"; comm.Parameters.Clear(); comm.Parameters.AddWithValue("@b", user1.Birthday); try { conn.Open(); count = comm.ExecuteNonQuery(); } catch { ok = false; } finally { conn.Close(); } if (count > 0) { ok = true; } return ok; } } }
