ADO.Net 精简的三层架构（初学者）

DAL（Data Access Layer）

三层架构是企业开发中常用的设计模式，把数据库访问、业务逻辑、界面分离。

初学者直接学习三层架构比较难，因此先学习精简的三层架构，只用DAL层，把数据库访问封装到DAL中，UI调用DAL，原则“UI中不出现SQL”。

DAL常用封装：ToModel、ListAll、GetById、DeleteById、Update、Insert

下面是一个使用DAL的实例：

 

数据库表

T_Customer定义

 

 

代码清单：

配置文件：App.config

<?xml version="1.0" encoding="utf-8" ?><configuration>  <connectionStrings>    <add name="myconnstr" connectionString="Data Source=.; Initial Catalog = ADOTest; User ID = sa; Password = 123456"/>  </connectionStrings></configuration>

 

/Model：模型类

using System;using System.Collections.Generic;using System.Linq;using System.Text;namespace ADOTest5.Model{    public class Customer    {        public long Id {get;set;}        public string Name { get; set; }        public DateTime? Birthday { get; set; }        public string Address { get; set; }        public string TelNum { get; set; }        public int CustLevel { get; set; }    }}

 

/DAL/CustomerDAL.cs

 

using System;using System.Collections.Generic;using System.Linq;using System.Text;using ADOTest5.Model;using System.Data;using System.Data.SqlClient;namespace ADOTest5.DAL{    public  class CustomerDAL    {        //根据Id获取GetById、Update、DeleteById、GetAll、GetPagedData（分页数据）        //Insert（插入新数据）        //把公共的代码封装到一个方法中，这样可以避免重复性的代码，提高代码复用性        private Customer ToCustomer(DataRow row)        {            Customer cust = new Customer();            cust.Id = (int)row["Id"];            cust.Name = (string)row["Name"];            cust.Birthday = (DateTime?)SqlHelper.FromDbValue(row["Birthday"]);            cust.Address = (string)row["Address"];            cust.CustLevel = (int)row["CustLevel"];            cust.TelNum = (string)row["TelNum"];            return cust;        }        /// <summary>        /// 根据Id查询结果        /// </summary>        /// <param name="id"></param>        /// <returns></returns>        public Customer GetById(long id)        {            DataTable dt = SqlHelper.ExecuteDataTable("select * from T_Customer where Id = @Id"                ,new SqlParameter("@Id",id));            if (dt.Rows.Count <= 0)            {                return null;            }            else if (dt.Rows.Count > 1)            {                throw new Exception("严重错误，查出多条数据！");            }            else            {                DataRow row = dt.Rows[0];                return ToCustomer(row);            }        }        /// <summary>        /// 根据Id删除数据        /// </summary>        /// <param name="id"></param>        public void DeleteById(long id)        {            SqlHelper.ExecuteNonQuery("delete from T_Customer where Id = @Id",                new SqlParameter("@Id", id));        }        /// <summary>        /// 往数据库中插入数据        /// </summary>        /// <param name="customer"></param>        public void Insert(Customer customer)        {            SqlHelper.ExecuteNonQuery(@"Insert into T_Customer(Name,                Birthday, Address, TelNum, CustLevel)                values(@Name, @Birthday, @Address, @TelNum, @CustLevel)",                new SqlParameter("@Name",customer.Name),                new SqlParameter("@Birthday",SqlHelper.ToDbValue(customer.Birthday)),                new SqlParameter("@Address", customer.Address),                new SqlParameter("@TelNum", customer.TelNum),                new SqlParameter("@CustLevel",customer.CustLevel));        }        /// <summary>        /// 更新数据        /// </summary>        /// <param name="customer"></param>        public void Update(Customer customer)        {            SqlHelper.ExecuteNonQuery(@"Update T_Customer set                     Name = @Name, Birthday = @Birthday, Address = @Address,                    TelNum = @TelNum, CustLevel = @CustLevel                     where Id = @Id",                    new SqlParameter("@Name", customer.Name),                    new SqlParameter("@Birthday", SqlHelper.ToDbValue(customer.Birthday)),                    new SqlParameter("@Address", customer.Address),                    new SqlParameter("@TelNum", customer.TelNum),                    new SqlParameter("@CustLevel", customer.CustLevel));        }        /// <summary>        /// 查询所有数据        /// </summary>        /// <returns></returns>        public Customer[] GetAll()        {            DataTable table = SqlHelper.ExecuteDataTable("select * from T_Customer");            Customer[] customers = new Customer[table.Rows.Count];            for (int i = 0; i < table.Rows.Count; i++)            {                DataRow row = table.Rows[i];                customers[i] = ToCustomer(row);            }            return customers;        }    }}

 

 

 

/DAL/SqlHelper.cs

using System;using System.Collections.Generic;using System.Linq;using System.Text;using System.Configuration;using System.Data.SqlClient;using System.Data;namespace ADOTest5.DAL{    static class SqlHelper    {        public static readonly string connstr = ConfigurationManager.ConnectionStrings["myconnstr"].ConnectionString;        public static int ExecuteNonQuery(string sql,            params SqlParameter[] parameters)        {            using (SqlConnection conn = new SqlConnection(connstr))            {                conn.Open();                using (SqlCommand cmd = conn.CreateCommand())                {                    cmd.CommandText = sql;                    cmd.Parameters.AddRange(parameters);                    return cmd.ExecuteNonQuery();                }            }        }        public static object ExecuteScalar(string sql,            params SqlParameter[] parameters)        {            using (SqlConnection conn = new SqlConnection(connstr))            {                conn.Open();                using (SqlCommand cmd = conn.CreateCommand())                {                    cmd.CommandText = sql;                    cmd.Parameters.AddRange(parameters);                    return cmd.ExecuteScalar();                }            }        }        public static DataTable ExecuteDataTable(string sql,            params SqlParameter[] parameters)        {            using (SqlConnection conn = new SqlConnection(connstr))            {                conn.Open();                using (SqlCommand cmd = conn.CreateCommand())                {                    cmd.CommandText = sql;                    cmd.Parameters.AddRange(parameters);                    DataSet ds = new DataSet();                    SqlDataAdapter adapter = new SqlDataAdapter(cmd);                    adapter.Fill(ds);                    return ds.Tables[0];                }            }        }        public static object FromDbValue(object value)        {            if (value == DBNull.Value)            {                return null;            }            else            {                return value;            }        }        public static object ToDbValue(object value)        {            if (value == null)            {                return DBNull.Value;            }            else            {                return value;            }        }    }}