实体类,数据访问类应用
一,表中某个数据进行重新排序
(1)数据访问类
using System; using System.Collections.Generic; using System.Data.SqlClient; using System.Linq; using System.Text; namespace ConsoleApplication2.App_Code { public class StudentsData { SqlConnection conn = null; SqlCommand cmd = null; public StudentsData() { conn = new SqlConnection("server=.;database=Data0216;user=sa;pwd=123"); cmd = conn.CreateCommand(); } public void ResetNumber() { List<Students> slist = new List<Students>(); cmd.CommandText = "select *from Students"; conn.Open(); SqlDataReader dr = cmd.ExecuteReader(); while (dr.Read()) { Students s = new Students(); s.Ids = Convert.ToInt32(dr["Ids"]); s.Scode = dr["Scode"].ToString(); s.Sname = dr["Sname"].ToString(); slist.Add(s); } conn.Close(); int count = 1; foreach (Students sss in slist) { sss.Scode = "S" + count.ToString("000"); count++; } conn.Open(); foreach (Students ss in slist) { cmd.CommandText = "Update Students set Scode=@a where Ids = @b"; cmd.Parameters.Clear(); cmd.Parameters.AddWithValue("@a", ss.Scode); cmd.Parameters.AddWithValue("@b", ss.Ids); cmd.ExecuteNonQuery(); } conn.Close(); }
(2)实体类
using System; using System.Collections.Generic; using System.Linq; using System.Text; namespace ConsoleApplication2.App_Code { public class Students { private int _Ids; public int Ids { get { return _Ids; } set { _Ids = value; } } private string _Scode; public string Scode { get { return _Scode; } set { _Scode = value; } } private string _Sname; public string Sname { get { return _Sname; } set { _Sname = value; } } } }
(3)主界面
using System; using System.Collections.Generic; using System.Linq; using System.Text; using ConsoleApplication2.App_Code; namespace ConsoleApplication2 { class Program { static void Main(string[] args) { new StudentsData().ResetNumber(); Console.ReadKey(); } } }
二.比较完善的增删改查
User表
using System; using System.Collections.Generic; using System.Linq; using System.Text; namespace ConsoleApplication2.APP_Code { public class Users { private int _Ids; public int Ids { get { return _Ids; } set { _Ids = value; } } private string _Username; public string Username { get { return _Username; } set { _Username = value; } } private string _Password; public string Password { get { return _Password; } set { _Password = value; } } private string _Nickname; public string Nickname { get { return _Nickname; } set { _Nickname = value; } } private bool _Sex; public bool Sex { get { return _Sex; } set { _Sex = value; } } public string Sexie { get { return _Sex ? "男" : "女"; } } private DateTime _Birthday; public DateTime Birthday { get { return _Birthday; } set { _Birthday = value; } } public string Birthdayie { get { return _Birthday.ToString("yyyy年MM月dd日"); } } public int Age { get { return DateTime.Now.Year - _Birthday.Year; } } private String _Nation; public String Nation { get { return _Nation; } set { _Nation = value; } } public string Naname { get { return new NationDate().CeleNa(_Nation); } } } }
using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Data.SqlClient; namespace ConsoleApplication2.APP_Code { public class UsersData { // 数据库连接 SqlConnection conn = null; SqlCommand cmd = null; public UsersData() { conn = new SqlConnection("server=.;database=ADO;user=sa;pwd=123;"); cmd = conn.CreateCommand(); } //数据库数据查询 public List<Users> Celect() { List<Users> li = new List<Users>(); cmd.CommandText = "select*from Users"; conn.Open(); SqlDataReader dr = cmd.ExecuteReader(); while (dr.Read()) { Users n = new Users(); n.Ids = Convert.ToInt32(dr["Ids"]); n.Username = dr["Username"].ToString(); n.Password = dr["Password"].ToString(); n.Nickname = dr["Nickname"].ToString(); n.Sex = Convert.ToBoolean(dr["Sex"]); n.Birthday = Convert.ToDateTime(dr["Birthday"]); n.Nation = dr["Nation"].ToString(); li.Add(n); } conn.Close(); return li; } //数据库数据添加 public int Add(Users mm) { conn.Open(); cmd.CommandText = "insert into Users values(@a,@b,@c,@d,@e,@f);"; cmd.Parameters.Clear(); cmd.Parameters.AddWithValue("@a", mm.Username); cmd.Parameters.AddWithValue("@b", mm.Password); cmd.Parameters.AddWithValue("@c", mm.Nickname); cmd.Parameters.AddWithValue("@d", mm.Sex); cmd.Parameters.AddWithValue("@e", mm.Birthday); cmd.Parameters.AddWithValue("@f", mm.Nation); int a = cmd.ExecuteNonQuery(); conn.Close(); return a; } //数据库数据删除 //1.查询数据库中有没有该用户名 public bool Cele(string c) { bool ce; cmd.CommandText = "select @a from Users"; cmd.Parameters.Clear(); cmd.Parameters.AddWithValue("@a", c); conn.Open(); SqlDataReader dr = cmd.ExecuteReader(); dr.Read(); if (dr[0].ToString() != "") { ce = true; } else { ce = false; } conn.Close(); return ce; } //2.如果有该数据,执行删除 public int del(string d) { cmd.CommandText = "delete from Users where Username=@a;"; cmd.Parameters.Clear(); cmd.Parameters.AddWithValue("@a", d); conn.Open(); int z = cmd.ExecuteNonQuery(); conn.Close(); return z; } //数据库数据修改 //上面判断有没有该用户名 //如果有改用户名,执行修改 public int Change(Users u) { cmd.CommandText = "update Users set Password=@a,Nickname=@b,Sex=@c,Birthday=@d,Nation=@e where Username=@f;"; cmd.Parameters.Clear(); cmd.Parameters.AddWithValue("@a", u.Password); cmd.Parameters.AddWithValue("@b", u.Nickname); cmd.Parameters.AddWithValue("@c", u.Sex); cmd.Parameters.AddWithValue("@d", u.Birthday); cmd.Parameters.AddWithValue("@e", u.Nation); cmd.Parameters.AddWithValue("@f", u.Username); conn.Open(); int zz = cmd.ExecuteNonQuery(); conn.Close(); return zz; } } }
Nation表
using System; using System.Collections.Generic; using System.Linq; using System.Text; namespace ConsoleApplication2.APP_Code { public class Nation { private String _Nationcode; public String Nationcode { get { return _Nationcode; } set { _Nationcode = value; } } private string _Nationname; public string Nationname { get { return _Nationname; } set { _Nationname = value; } } } }
using System; using System.Collections.Generic; using System.Data.SqlClient; using System.Linq; using System.Text; namespace ConsoleApplication2.APP_Code { public class NationDate { // 数据库连接 SqlConnection conn = null; SqlCommand cmd = null; public NationDate() { conn = new SqlConnection("server=.;database=ADO;user=sa;pwd=123;"); cmd = conn.CreateCommand(); } //查询Nation表 public string CeleNa(string a) { string mz; cmd.CommandText = "select Nationname from Nation where Nationcode=@a;"; cmd.Parameters.Clear(); cmd.Parameters.AddWithValue("@a", a); conn.Open(); SqlDataReader dr = cmd.ExecuteReader(); dr.Read(); if (dr[0].ToString() != "") { mz = dr[0].ToString(); } else { mz = "<暂无>"; } conn.Close(); return mz; } } }
其他:
(1)判断:
using System; using System.Collections.Generic; using System.Linq; using System.Text; namespace ConsoleApplication2.APP_Code { public class judge { //性别判断 public int pdxb(string n) { int a = 0; if (n == "男") return a = 1; else return a; } //生日判断 public bool dt(string b) { try { Convert.ToDateTime(b); return true; } catch { return false; } } //民族判断 public string mz(string c) { if (c == "汉族") { return "N001"; } else if (c == "满族") { return "N002"; } else if (c == "维吾尔族") { return "N003"; } else if (c == "回族") { return "N004"; } else if (c == "藏族") { return "N005"; } else { return "N001"; } } } }
(2)主界面:
using System; using System.Collections.Generic; using System.Linq; using System.Text; using ConsoleApplication2.APP_Code; namespace ConsoleApplication2 { class Program { static void Main(string[] args) { while (true) { #region 每次开始展示表数据 //调用UserData类并实例化 UsersData hh = new UsersData(); //调用UserData中查询Celete的方法,并用泛型集合接收 List<Users> hq = hh.Celect(); //遍历并打印数据 Console.WriteLine("现有表的数据内容:"); Console.WriteLine("序号" + "\t" + "用户名" + "\t" + "密码" + "\t" + "昵称" + "\t" + "性别" + "\t" + "生日" + "\t" + "年龄" + "\t" + "民族"); foreach (Users i in hq) { Console.WriteLine(i.Ids + "\t" + i.Username + "\t" + i.Password + "\t" + i.Nickname + "\t" + i.Sexie + "\t" + i.Birthdayie + "\t" + i.Age + "\t" + i.Naname); } Console.WriteLine("按任意键继续."); Console.ReadKey(); #endregion //功能开始执行 Console.Write("请输入你要进行的操作(添加 / 修改 / 删除):"); string x = Console.ReadLine(); //判断用户选择的哪种功能 //如果是添加数据 #region if (x == "添加") { //实例化users对象 Users mm = new Users(); //开始询问用户要添加数据 Console.Write("请输入用户名:"); mm.Username = Console.ReadLine(); Console.Write("请输入密码:"); mm.Password = Console.ReadLine(); Console.Write("请输入昵称:"); mm.Nickname = Console.ReadLine(); Console.Write("请输入性别:"); string pd = Console.ReadLine(); judge h = new judge(); mm.Sex = Convert.ToBoolean(h.pdxb(pd)); while (true) { Console.Write("请输入生日:"); string bir = Console.ReadLine(); judge h1 = new judge(); bool ab = h1.dt(bir); if (ab) { mm.Birthday = Convert.ToDateTime(bir); break; } else { Console.Write("输入日期不正确,"); } } Console.Write("请输入民族:"); string s = Console.ReadLine(); judge h2 = new judge(); mm.Nation = h2.mz(s); //调用UserData类并实例化 UsersData hh1 = new UsersData(); //调用UserData中查询add的方法,并判断是否添加成功 int js = hh1.Add(mm); if (js > 0) { Console.WriteLine("数据添加成功!"); } } #endregion //如果是删除数据 #region else if (x == "删除") { string de = ""; while (true) { Console.Write("请输入你要删除数据的用户名:"); string yhm = Console.ReadLine(); //判断有没有这个用户名 //调用UsersData类并实例化 UsersData hh2 = new UsersData(); bool xx = hh2.Cele(yhm); if (xx) { de = yhm; UsersData hh3 = new UsersData(); int cd = hh3.del(de); Console.WriteLine("数据删除成功,本次共删除" + cd + "行"); break; } else { Console.Write("没有该用户名,"); } } } #endregion //如果是修改数据 if (x == "修改") { while (true) { Users u = new Users(); Console.Write("请输入你要修改数据的用户名"); string yhm1 = Console.ReadLine(); //判断有没有这个用户名 //调用UsersData类并实例化 UsersData hh2 = new UsersData(); bool xx = hh2.Cele(yhm1); if (xx) { u.Username = yhm1; Console.Write("请输入修改后的密码:"); u.Password = Console.ReadLine().ToString(); Console.Write("请输入修改后的昵称:"); u.Nickname = Console.ReadLine().ToString(); Console.Write("请输入修改后的性别:"); string pd2 = Console.ReadLine().ToString(); judge h5 = new judge(); u.Sex = Convert.ToBoolean(h5.pdxb(pd2)); while (true) { Console.Write("请输入修改后的生日:"); string bir1 = Console.ReadLine(); judge h3 = new judge(); bool abc = h3.dt(bir1); if (abc) { u.Birthday = Convert.ToDateTime(bir1); break; } else { Console.Write("输入日期不正确,"); } } Console.Write("请输入修改后的民族:"); string ss = Console.ReadLine(); judge h4 = new judge(); u.Nation = h4.mz(ss); UsersData xhx = new UsersData(); int sx = xhx.Change(u); if (sx > 0) { Console.WriteLine("数据修改成功!"); break; } } else { Console.Write("没有该用户名,"); } } } } } } }