ADO.net 实体类 、数据访问类
程序分三层：界面层、业务逻辑层、数据访问层
比较规范的写程序方法，要把业务逻辑层和数据访问层分开，此时需要创建实体类和数据访问类
实体类：
例
using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Threading.Tasks; namespace 增删改查.app_ado { public class users { private int _Ids; /// <summary> /// ids /// </summary> public int Ids { get { return _Ids; } set { _Ids = value; } } private string _UserName; /// <summary> /// 用户名 /// </summary> public string UserName { get { return _UserName; } set { _UserName = value; } } private string _PassWord; /// <summary> /// 密码 /// </summary> public string PassWord { get { return _PassWord; } set { _PassWord = value; } } private string _NickName; /// <summary> /// 昵称 /// </summary> public string NickName { get { return _NickName; } set { _NickName = value; } } private bool _Sex; /// <summary> /// 性别 /// </summary> public bool Sex { get { return _Sex; } set { _Sex = value; } } private DateTime _Birthday; /// <summary> /// 生日 /// </summary> public DateTime Birthday { get { return _Birthday; } set { _Birthday = value; } } private string _Nation; /// <summary> /// 民族 /// </summary> public string Nation { get { return _Nation; } set { _Nation = value; } } private string _Class; /// <summary> /// 班级 /// </summary> public string Class { get { return _Class; } set { _Class = value; } } } }
创建一个类，把整个数据库表单的数据都封装一下
数据访问类：
例
using System; using System.Collections.Generic; using System.Data.SqlClient; using System.Linq; using System.Text; using System.Threading.Tasks; namespace 增删改查.app_ado { class userdata { SqlConnection conn = null; SqlCommand cmd = null; public userdata() { conn = new SqlConnection("server=.;database=Data0928;user=sa;pwd=123"); cmd = conn.CreateCommand(); } /// <summary> /// 查询所有信息 /// </summary> public void select() { cmd.CommandText = "select ids,username,password,nickname,sex,birthday,nationname,classname from users join nation on users.nation=nation.nationcode join class on users.class=class.classcode"; conn.Open(); SqlDataReader dr = cmd.ExecuteReader(); if (dr.HasRows) { while (dr.Read()) { int n = 0; while (n < dr.FieldCount) { if (dr[n] is Boolean) Console.Write(((Boolean)dr[n] ? "男" : "女") + "\t"); else if (dr[n] is DateTime) Console.Write((((DateTime)dr[n]).ToShortDateString()) + "\t"); else Console.Write(dr[n] + "\t"); n++; } Console.WriteLine(); } } conn.Close(); } /// <summary> /// 添加信息 /// </summary> /// <param name="u"></param> /// <returns></returns> public bool Insert(users u) { bool ok = false; int count = 0; cmd.CommandText = "insert into users values(@a,@b,@c,@d,@e,@f,@g)"; cmd.Parameters.Clear(); cmd.Parameters.AddWithValue("@a", u.UserName); cmd.Parameters.AddWithValue("@b", u.PassWord); cmd.Parameters.AddWithValue("@c", u.NickName); cmd.Parameters.AddWithValue("@d", u.Sex); cmd.Parameters.AddWithValue("@e", u.Birthday); cmd.Parameters.AddWithValue("@f", u.Nation); cmd.Parameters.AddWithValue("@g", u.Class); try { conn.Open(); count = cmd.ExecuteNonQuery(); } catch { ok = false; } finally { conn.Close(); } if (count > 0) ok = true; return ok; } /// <summary> /// 根据用户名查询 /// </summary> /// <param name="username"></param> /// <returns></returns> public List<users> Select(string username) { List<users> list = new List<users>(); cmd.CommandText = "select *from Users where UserName = @a"; cmd.Parameters.Clear(); cmd.Parameters.Add("@a", username); conn.Open(); SqlDataReader dr = cmd.ExecuteReader(); if (dr.HasRows) while (dr.Read()) { users u = new users(); u.Ids = Convert.ToInt32(dr["ids"]); u.UserName = dr["UserName"].ToString(); u.PassWord = dr["PassWord"].ToString(); u.NickName = dr["NickName"].ToString(); u.Sex = Convert.ToBoolean(dr["Sex"]); u.Birthday = Convert.ToDateTime(dr["Birthday"]); u.Nation = dr["Nation"].ToString(); u.Class = dr["Class"].ToString(); list.Add(u); } conn.Close(); return list; } /// <summary> /// 根据列名查询信息 /// </summary> /// <param name="lname"></param> /// <param name="username"></param> /// <returns></returns> public List<users> Select(string lname,string username) { List<users> list = new List<users>(); cmd.CommandText = "select *from Users where "+lname+" = @a"; cmd.Parameters.Clear(); cmd.Parameters.AddWithValue("@a", username); conn.Open(); SqlDataReader dr = cmd.ExecuteReader(); if (dr.HasRows) while (dr.Read()) { users u = new users(); u.Ids = Convert.ToInt32(dr["ids"]); u.UserName = dr["UserName"].ToString(); u.PassWord = dr["PassWord"].ToString(); u.NickName = dr["NickName"].ToString(); u.Sex = Convert.ToBoolean(dr["Sex"]); u.Birthday = Convert.ToDateTime(dr["Birthday"]); u.Nation = dr["Nation"].ToString(); u.Class = dr["Class"].ToString(); list.Add(u); } conn.Close(); return list; } /// <summary> /// 判断是否有此用户名 /// </summary> /// <param name="username"></param> /// <returns></returns> public bool select(string username) { bool has = false; cmd.CommandText = "select *from Users where UserName = @a"; cmd.Parameters.Clear(); cmd.Parameters.Add("@a", username); conn.Open(); SqlDataReader dr = cmd.ExecuteReader(); if (dr.HasRows) has = true; conn.Close(); return has; } /// <summary> /// 删除信息 /// </summary> /// <param name="uname"></param> public bool Delete(string uname) { bool ok = false; int count = 0; cmd.CommandText = "delete from Users where UserName = @a"; cmd.Parameters.Clear(); cmd.Parameters.Add("@a", uname); try { conn.Open(); count = cmd.ExecuteNonQuery(); } catch { ok = false; } finally { conn.Close(); } if (count > 0) ok = true; return ok; } /// <summary> /// 修改信息 /// </summary> /// <param name="uname"></param> /// <returns></returns> public bool Update(string uname,string uname1,string uname2) { bool ok = false; int count = 0; cmd.CommandText = "update Users set "+uname1+"=@b where UserName = @a"; cmd.Parameters.Clear(); cmd.Parameters.Add("@a", uname); cmd.Parameters.Add("@b", uname2); try { conn.Open(); count = cmd.ExecuteNonQuery(); } catch { ok = false; } finally { conn.Close(); } if (count > 0) ok = true; return ok; } } }
相当于把所有需要从数据库取数据进行操作的部分，统一放入此类中，使用时可以调用，这样可以简化主程序，并且看起来调理更加清晰。
注：
1、一般约定，在项目里新建一个叫做App_xx的文件夹，将实体类和数据访问类统一放在其中
2、一般实体类使用想要进行操作的数据库中的表名来命名，数据库访问类用此表的表名后面加上Data来命名
3、数据访问类开头格式：
class userdata
{
SqlConnection conn = null;
SqlCommand cmd = null;
public userdata()
{
conn = new SqlConnection("server=.;database=Data0928;user=sa;pwd=123");
cmd = conn.CreateCommand();
}
4、匿名方法
例
List<Users> ulist = new UsersData().Select();
直接使用UserData类中的Select方法（查询表中所有数据），并赋值给叫做ulist的泛型集合，从而比较简便的获取到所有数据，节省代码
有些方法也可以不用赋值直接进行调用
if (new userdata().select(uname))
综合练习
using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Threading.Tasks; using 增删改查.app_ado; namespace 增删改查 { class Program { static void Main(string[] args) { #region//输出列表信息 Console.WriteLine("========================列表信息============================"); Console.Write("编号" + "\t" + "用户名" + "\t" + "密码" + "\t" + "昵称" + "\t" + "性别" + "\t" + "出生日期" + "\t" + "民族" + "\t" + "班级" + "\n"); new userdata().select(); #endregion Console.WriteLine("1.添加"); Console.WriteLine("2.删除"); Console.WriteLine("3.修改"); Console.WriteLine("4.查询"); Console.WriteLine("5.退出"); for (; ; ) { Console.Write("请选择您要进行的操作编号："); int bian = int.Parse(Console.ReadLine()); //增加信息 if (bian == 1) { users user = new users(); Console.Write("请输入用户名："); user.UserName = Console.ReadLine(); Console.Write("请输入密码："); user.PassWord = Console.ReadLine(); Console.Write("请输入昵称："); user.NickName = Console.ReadLine(); Console.Write("请输入性别："); user.Sex = Convert.ToBoolean(Console.ReadLine()); Console.Write("请输入生日："); user.Birthday = Convert.ToDateTime(Console.ReadLine()); Console.Write("请输入民族："); user.Nation = Console.ReadLine(); Console.Write("请输入班级："); user.Class = Console.ReadLine(); userdata ud = new userdata(); bool isok = ud.Insert(user); if (isok) Console.WriteLine("添加成功！"); else Console.WriteLine("添加失败！"); } //删除信息 else if (bian == 2) { Console.Write("请输入要删除的用户名："); string uname = Console.ReadLine(); if (new userdata().select(uname)) { List<users> ulist = new userdata().Select(uname); foreach (users uuu in ulist) { Console.WriteLine(uuu.Ids + " " + uuu.UserName + " " + uuu.PassWord + " " + uuu.NickName + " " + uuu.Sex + " " + uuu.Birthday + " " + uuu.Nation + " " + uuu.Class); } Console.WriteLine("以上为此用户信息，是否要删除？(Y/N)"); string yn = Console.ReadLine(); if (yn.ToUpper() == "Y") { new userdata().Delete(uname); Console.WriteLine("删除成功！"); } } else Console.WriteLine("未找到此用户名！"); } //修改信息 else if (bian == 3) { Console.Write("请输入要修改的用户名："); string uname = Console.ReadLine(); if (new userdata().select(uname)) { Console.Write("请输入要修改的列名："); string uname1 = Console.ReadLine(); Console.Write("请输入要修改的内容："); string uname2 = Console.ReadLine(); List<users> ulist = new userdata().Select(uname); foreach (users uuu in ulist) { Console.WriteLine(uuu.Ids + " " + uuu.UserName + " " + uuu.PassWord + " " + uuu.NickName + " " +((bool)uuu.Sex?"男":"女")+ " " + ((DateTime)uuu.Birthday).ToShortDateString() + " " + uuu.Nation + " " + uuu.Class); } Console.WriteLine("以上为此用户信息，是否要修改？(Y/N)"); string yn = Console.ReadLine(); if (yn.ToUpper() == "Y") { new userdata().Update(uname, uname1, uname2); Console.WriteLine("修改成功！"); } } else Console.WriteLine("未找到此用户名！"); } else if (bian == 4) //也可以调用 public List<users> Select(string lname,string username) 通过 输入列名 和此列名下的内容 来进行查询 { Console.Write("请输入要查询的用户名："); string uname = Console.ReadLine(); if (new userdata().select(uname)) { List<users> ulist = new userdata().Select(uname); foreach (users uuu in ulist) { Console.WriteLine(uuu.Ids + " " + uuu.UserName + " " + uuu.PassWord + " " + uuu.NickName + " " + ((bool)uuu.Sex ? "男" : "女") + " " + ((DateTime)uuu.Birthday).ToShortDateString() + " " + uuu.Nation + " " + uuu.Class); } } else Console.WriteLine("未找到此用户名！"); } else if (bian == 5) break; else Console.WriteLine("请输入正确的操作编号！！"); } Console.ReadLine(); } } }
