程序分三层：界面层、业务逻辑层、数据访问层
比较规范的写程序方法，要把业务逻辑层和数据访问层分开，此时需要创建实体类和数据访问类
实体类：
例
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 = ;
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 = ; 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 > )
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 = ;
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 > )
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 = ;
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 > )
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 == )
{
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 == )
{
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 == )
{
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 == ) //也可以调用 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 == )
break;
else
Console.WriteLine("请输入正确的操作编号！！");
} Console.ReadLine(); }
}
}