初识三层架构

复杂项目不能把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 }