初识三层架构
复杂项目不能把SQL语句直接写到程序里，不模块化、难以维护，应该采用三层架构。Web开发中的三层架构也是同样的结构。 模型层Model；数据访问层DAL（Data Access Layer）；业务逻辑层BLL（business logic layer ）。实体类就是Model；对数据进行操作的代码写在DAL中，一般就是SQL语句，DAL只有对数据的操作；BLL调用DAL中的代码进行逻辑操作SQL语句一般只应该出现在DAL中。 三层：UI（界面，User Interface）、BLL、DAL。Model是在三层之间进行数据传递的。UI层调用BLL、BLL调用DAL，数据用Model传递，UI不能直接调用DAL。
SQLHelper
using System;
using System.Collections.Generic;
using System.Text;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
namespace 三层架构.DAL
{
class SQLHelper
{
public static readonly string conStr = ConfigurationManager.ConnectionStrings["conStr"].ConnectionString;
/// <summary>
/// 执行非查询语句
/// </summary>
/// <param name="sql">SQL语句</param>
/// <param name="parameters">传入变量</param>
/// <returns>影响行数</returns>
public static int ExecuteNonQuery(string sql, params SqlParameter[] parameters)
{
using (SqlConnection con = new SqlConnection(conStr))
{
con.Open();
using (SqlCommand cmd = con.CreateCommand())
{
cmd.CommandText = sql;
if (parameters != null)
{
foreach (SqlParameter param in parameters)
{
cmd.Parameters.Add(param);
}
}
return cmd.ExecuteNonQuery();
}
}
}
/// <summary>
/// 执行非查询语句
/// </summary>
/// <param name="sql">SQL语句</param>
/// <param name="parameters">传入变量</param>
/// <returns>返回查询结果中的第一行第一列的值</returns>
public static object ExecuteScalar(string sql, params SqlParameter[] parameters)
{
using (SqlConnection con = new SqlConnection(conStr))
{
con.Open();
using (SqlCommand cmd = con.CreateCommand())
{
cmd.CommandText = sql;
if (parameters != null)
{
foreach (SqlParameter param in parameters)
{
cmd.Parameters.Add(param);
}
}
return cmd.ExecuteScalar();
}
}
}
public static DataTable ExecuteDataTable(string sql, params SqlParameter[] parameters)
{
using (SqlConnection con = new SqlConnection(conStr))
{
con.Open();
using (SqlCommand cmd = con.CreateCommand())
{
cmd.CommandText = sql;
if (parameters != null)
{
foreach (SqlParameter param in parameters)
{
cmd.Parameters.Add(param);
}
}
DataTable dt = new DataTable();
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(dt);
return dt;
}
}
}
}
}
DAL
1 using System;
2 using 三层架构.Model;
3 using System.Data.SqlClient;
4 using System.Data;
5 using System.Collections.Generic;
6
7 namespace 三层架构.DAL
8 {
9 class PersonDAL
10 {
11 //增加字段
12 public static int AddNew(Person model)//返回新添加字段的主键
13 {
14 object obj= SQLHelper.ExecuteScalar("insert into T_person(Age,Name) output inserted.id values(@Age,@Name)",new SqlParameter("Age",model.Age),new SqlParameter("Name",model.Name));
15 return Convert.ToInt32(obj);
16 }
17 //删除操作
18 public static int Delete(int id)
19 {
20 return SQLHelper.ExecuteNonQuery("delete from T_person where id=@id", new SqlParameter("id", id));
21 }
22 //更新数据
23 public static int UpDate(Person model)
24 {
25 return SQLHelper.ExecuteNonQuery("update T_Person set age=@age,name=@name where id=@id",new SqlParameter("age",model.Age), new SqlParameter("name",model.Name),new SqlParameter("id",model.Id));
26 }
27 //查询数据
28 public static Person GetPerson(int id)
29 {
30 DataTable dt=SQLHelper.ExecuteDataTable("select * from T_Person where id=@id",new SqlParameter("id",id));
31 if (dt.Rows.Count <= 0)
32 {
33 return null;
34 }
35 else if (dt.Rows.Count ==1)
36 {
37 DataRow row =dt.Rows[0];
38 Person person = new Person();
39 person.Id = (int)row["Id"];
40 person.Name = (string)row["Name"];
41 person.Age =Convert.ToInt32( row["Age"]);
42 return person;
43 }
44 else
45 {
46 throw new Exception("未知数据错误！");
47 }
48 }
49 public static IEnumerable<Person> GetAllPerson()
50 {
51 DataTable dt = SQLHelper.ExecuteDataTable("select * from T_Person");
52 List<Person> list = new List<Person>();
53 foreach (DataRow row in dt.Rows)
54 {
55 Person person = new Person();
56 person.Id = (int)row["Id"];
57 person.Name = (string)row["Name"];
58 person.Age = Convert.ToInt32(row["Age"]);
59 list.Add(person);
60 }
61 return list;
62 }
63 }
64 }
BLL
1 using 三层架构.Model;
2 using 三层架构.DAL;
3 using System;
4 using System.Collections;
5 using System.Collections.Generic;
6
7 namespace 三层架构.BLL
8 {
9 class PersonBLL
10 {
11 //增加字段
12 public static int AddNew(Person model)//返回新添加字段的主键
13 {
14 return PersonDAL.AddNew(model);
15 }
16 //删除操作
17 public static int Delete(int id)
18 {
19 return PersonDAL.Delete(id);
20 }
21 //更新数据
22 public static int UpDate(Person model)
23 {
24 if (model.Age < 0)
25 {
26 throw new Exception("年龄不能为负数！");
27 }
28 return PersonDAL.UpDate(model);
29 }
30 //查询一条数据
31 public static Person GetPerson(int id)
32 {
33 return PersonDAL.GetPerson(id);
34 }
35 //查询所有数据
36 public static IEnumerable<Person> GetAllPerson()
37 {
38 return PersonDAL.GetAllPerson();
39 }
40 }
41 }
Model
1 namespace 三层架构.Model
2 {
3 class Person
4 {
5 public int Id { get; set; }
6 public int Age { get; set;}
7 public string Name { get; set; }
8 }
9 }
