文件结构(物理上)
基本流程
建立三层架构总文件夹
建立三层架构窗体程序
右键:建立BLL、DAL、Model文件夹
建立模型(Model):人
id、Age、Name
数据访问层(DAL)
SqlHelper.cs //专业数据库打交道,各种连接传值过程
PersonDAL.cs //模型的增删改查
业务逻辑层(BLL)
调用DAL层的方法,实现各种业务逻辑的需求
UI层(Form.cs)
调用BLL层的方法,传进各种实现业务逻辑的动作
//可以调用GetAll方法
总结:
Model是在三层之间传递数据的,UI层调用BLL,BLL调用DAL。
代码
宏观简析(内容理解)
Model(用户订单时写要求的单子)
class Person
public int Id { get; set;}
public int Age { get; set;}
public string Name { get; set;}
SqlHelper
staticreadonlystring connstr= ConfigurationManager.ConnectionString["connstr"].ConnectionString;
//设置为只读的全局变量
intExecuteNonQuery(string cmdText,params SqlParameter[] parameters)
//传进来sql命令和参数
using(SqlConnection conn=new SqlConnection(connstr())//连接服务器
conn.Open();
using (SqlCommand cmd = conn.CreateCommand())
cmd.CommandText=;
cmd.Parameters.AddRang(parameters);
return cmd.ExecuteNonQuery();
objectExecuteScalar(string cmdText,params SqlParameter[] parameters)
…同上…
DataTable ExecuteDataTable(string cmdText,paramsSqlParameter[] parameters)
using(SqlConnection conn=new SqlConnection(connstr())//连接服务器
conn.Open();
using (SqlCommand cmd = conn.CreateCommand())
cmd.CommandText=;
cmd.Parameters.AddRang(parameters);
DataTable dt =new DataTable();
SqlAdapter adapter =new SqlDataAdapter(cmd);
adapter.Fill(dt);
return dt;
Form1 窗体(订蛋糕的用户)
Form1_load( );
dataGridView1.DataSource=new PersonBLL().GetAll()
button1_Click( );
PersonBLL bll = new PersonBLL();
Person p1 = bll.Get(1);
p1.Name = "小宝bao";
bll.Update(p1);
PersonBLL业务逻辑层(接单的售货员)
class PersonBLL
int Delete(int id)
int Update(Person model)
Person Get(int id)
IEnumerable<Person> GetAll()//这些都只是声明,但是并不调用
PersonDAL数据访问层(只管做蛋糕的师傅)
//该层只进行简单的数据库操作,但是数据、操作前或操作后是否有意义,它不管(比如年龄不能小于0)
int AddNew(Person model)
int Delete(int id)
int Update(Person model)
Person Get(int id)
IEnumerable<Person> GetAll()//与具体的数据库打交道,实现相应的方法
【Model】模型层,实体类
namespace 第一个三层架构.Model
{
class Person
{
public int Id { get; set; }
public int Age { get; set; }
public string Name { get; set; }
}
}
【PersonBLL】业务逻辑层
namespace 第一个三层架构.BLL
{
class PersonBLL
{
//把要插入数据的模型对象传递给AddNew
public int AddNew(Person model)
{
return new PersonDAL().AddNew(model);
}
public int Delete(int id)
{
return new PersonDAL().Delete(id);
}
public int Update(Person model)
{
if (model.Age < 0)
{
throw new Exception("年龄值非法");
}
//BLL中进行逻辑判断,DAL只是进行数据库的操作
return new PersonDAL().Update(model);
}
public Person Get(int id)
{
return new PersonDAL().Get(id);
}
public IEnumerable<Person> GetAll()
{
return new PersonDAL().GetAll();
}
}
}
【PersonDAL】数据访问层
namespace 第一个三层架构.DAL
{
class PersonDAL
{
//把要插入数据的模型对象传递给AddNew
public int AddNew(Person model)
{
object obj = SqlHelper.ExecuteScalar(
"insert into Person(Age,Name)values(@Age,@Name);select @@identity"
, new SqlParameter("Age", model.Age), new SqlParameter("Name", model.Name));
//SqlParameter加不加@都一样。
return Convert.ToInt32(obj);
}
public int Delete(int id)
{
return SqlHelper.ExecuteNonQuery("delete from Person where id=@id",
new SqlParameter("id",id));
}
public int Update(Person model)
{
return SqlHelper.ExecuteNonQuery(
"update Person set Age=@Age,Name=@Namewhere id=@id",
new SqlParameter("id", model.Id), new SqlParameter("Age", model.Age)
, new SqlParameter("name", model.Name));
}
public Person Get(int id)
{
DataTable dt = SqlHelper.ExecuteDataTable("select * from Person where id=@id",
new SqlParameter("id",id));
if (dt.Rows.Count <= 0)
{
return null;
}
else if (dt.Rows.Count == 1)
{
Person model = new Person();
DataRow row = dt.Rows[0];
model.Id = (int)row["id"];
model.Name = (string)row["Name"];
model.Age = (int)row["Age"];
return model;
}
else//考虑意外情况
{
throw new Exception("出现多条数据!");
}
}
public IEnumerable<Person> GetAll()
{
DataTable dt = SqlHelper.ExecuteDataTable("select * fromPerson");
List<Person> list = new List<Person>();
foreach (DataRow row in dt.Rows)
{
Person model = new Person();
model.Id = (int)row["id"];
model.Name = (string)row["Name"];
model.Age = (int)row["Age"];
list.Add(model);
}
return list;
}
}
}
【SqlHelper】
namespace 第一个三层架构.DAL
{
class SqlHelper
{
private static readonly string connstr =
ConfigurationManager.ConnectionStrings["connstr"].ConnectionString;
public static int ExecuteNonQuery(string cmdText,params SqlParameter[] parameters)
//传进来Sql命令和参数
{
using (SqlConnection conn = new SqlConnection(connstr))//连接服务器
{
conn.Open();
using (SqlCommand cmd = conn.CreateCommand())
{
cmd.CommandText = cmdText;
cmd.Parameters.AddRange(parameters);
return cmd.ExecuteNonQuery();
}
}
}
public static object ExecuteScalar(string cmdText,
params SqlParameter[] parameters)
{
using (SqlConnection conn = new SqlConnection(connstr))
{
conn.Open();
using (SqlCommand cmd = conn.CreateCommand())
{
cmd.CommandText = cmdText;
cmd.Parameters.AddRange(parameters);
return cmd.ExecuteScalar();
}
}
}
public static DataTable ExecuteDataTable(string cmdText,
params SqlParameter[] parameters)
{
using (SqlConnection conn = new SqlConnection(connstr))
{
conn.Open();
using (SqlCommand cmd = conn.CreateCommand())
{
cmd.CommandText = cmdText;
cmd.Parameters.AddRange(parameters);
DataTable dt = new DataTable();
SqlDataAdapter adapter = new SqlDataAdapter(cmd);
adapter.Fill(dt);
return dt;//这四行与上面处理是不同的
}
}
}
}
}
【Form1】
namespace 第一个三层架构
{
public partialclass Form1 : Form
{
public Form1()
{
InitializeComponent();
}
private void button1_Click(object sender, EventArgs e)
{
PersonBLL bll = new PersonBLL();
Person p1 = bll.Get(1);//查询数据
p1.Name = "小宝bao";
bll.Update(p1);//保存修改
}
private void Form1_Load(object sender, EventArgs e)
{
dataGridView1.DataSource = new PersonBLL().GetAll();
}
}
}