简介这是一份基于ASP.NET构建的宠物商店数据访问层DAL项目工程包目标是为网页前端与SQL Server数据库之间提供稳定、高效的数据操作支持。压缩包共22个文件体积仅78KB结构精简却覆盖了完整的数据层要素6个C#源代码文件用于编写业务数据逻辑1个dbml模型文件及其布局文件承担数据库表到对象的映射定义3个DLL程序集保存编译后的可执行代码2个config文件则集中管理数据库连接字符串等运行参数。通过这份资源可以了解到如何使用LINQ to SQL优雅地完成增删改查并借助app.config、dbml设计器和自动生成的designer.cs理清数据访问层的职责边界。同时项目引入Bootstrap、jQuery与HTML/CSS让前端界面与后端数据层能够更好地联动形成一套适合学习的分层示例。目前已有248人学习下载。对于正在做ASP.NET课程设计、毕业设计或希望系统掌握.NET平台下数据访问层开发的初学者这份小体积资源提供了从模型设计、数据操作到项目文件组织的完整参照具有不错的启发性。1. MyPetShop.DAL 是什么以及为什么 ASP.NET 项目要单独抽出一层前段时间帮人收尾一个 ASP.NET WebForms 老项目打开页面代码一看SqlConnection 写在按钮事件里SQL 用字符串拼出来改一个查询条件要翻七八个文件还担心改漏了别处引用。Pet Shop 这类教学型项目本身不大但如果从一开始就把数据访问收敛到一个独立类库里后期维护会省掉大量这类返工。MyPetShop.DAL 就是这个独立类库把针对 SQL Server 的增删改查、事务、连接管理全部收进来页面和业务层只面向方法调用。这篇文章按一条完整路径来讲先从分层角度说清楚 DAL 该管什么、不该管什么再给出基于 ADO.NET 手写 DAL 的可运行代码最后把它接进 WebForms 和 MVC 的控制器并补上连接池、参数化缓存这几个容易踩坑的点。对于刚接触 ASP.NET 分层结构的开发者照着抄就能跑通对已经写了几年三层架构的工程师重点看参数写法和排查思路部分。2. 先划清边界再写代码DAL 层职责与数据访问选型2.1 哪些逻辑必须进 DAL哪些该留在 BLL 和页面很多项目把三层架构做成了三个项目文件夹代码却放得乱七八糟。判断一段代码是否属于 DAL我有一个很直接的标准如果数据源从 SQL Server 换成 Oracle或者从数据库换成 HTTP API这段代码必须跟着改那它就应该待在 DAL 里。代码内容所属层判断理由SELECT / INSERT / UPDATE / DELETE 语句DAL持久化语句只与具体数据源相关库存数量是否充足的判断BLL涉及业务状态且会影响后续动作价格显示成 ¥1,299.00UI / 视图展示格式随前端变化多张表必须在同一事务里提交DAL事务边界属于数据完整性范畴从 DataReader 读取列到实体对象DAL结果集映射是数据访问的一部分这里最容易出现的误用是把页面上的校验逻辑当成业务层或者把 DAL 方法写得过于万能。比如一个GetProducts(string whereClause)方法接受外部传入的条件字符串表面上是封装了查询实际上把 SQL 拼接的权限开放给了上层等于没做分层。DAL 的方法签名应该对应明确的业务意图比如GetProductsByCategory(int categoryId)、UpdateProductStock(int productId, int quantity)而不是一个通用的传参入口。2.2 ADO.NET、Dapper、EF 在 MyPetShop 里怎么选MyPetShop 这个名字带着经典 Pet Shop 示例的气质数据模型不会太复杂无非是 Category、Product、Order 这几张表。这个量级下数据访问方案有三条常见路线方案SQL 可见性上手成本样板代码量适用场景ADO.NET 基础类SqlConnection / SqlCommand完全可见低较多教学项目、需要精细控制 SQL 的场景Dapper完全可见低少中小项目SQL 想掌握在自己手里EF 6 / EF Core隐藏或混合中高少管理后台、模型驱动快速开发我的建议是 MyPetShop.DAL 先用 ADO.NET 把最小实现写通。原因很实际DAL 层本来就是为了集中管理 SQLADO.NET 基础类能让你看清每一条语句、每一个参数排查问题时的信息最完整。等代码量涨到重复样板太多再换 Dapper 也不迟因为调用方依赖的是 DAL 的方法签名内部实现怎么改都由这一层兜住。有一个细节值得注意即便将来把项目迁到 ASP.NET CoreMyPetShop.DAL这个类库里的数据访问代码可以原样保留变的只是连接字符串的读取方式Web.config 换到 appsettings.json。这也是数据访问独立成层带来的直接收益。2.3 连接字符串是 DAL 的第一份配置DAL 项目本身不应该硬编码任何连接信息。经典 ASP.NET 项目里连接字符串放在 Web.config 的 connectionStrings 节点下示例项目引用 System.Configuration 后通过 ConfigurationManager 读取。configuration connectionStrings add nameMyPetShop connectionStringData Source.;Initial CatalogMyPetShop;User IDshop_app;Password****;PoolingTrue;Connect Timeout15;Application NameMyPetShop providerNameSystem.Data.SqlClient / /connectionStrings /configuration几个容易被忽略的参数Connect Timeout指定建立连接的超时秒数默认是 15 秒如果网络环境差可以适当调大但不要超过 30 秒否则用户会以为页面卡死了。Application Name建议一定写上数据库侧做慢查询分析时通过它可以一眼看出连接来自哪个应用。PoolingTrue表示启用连接池这是 ASP.NET 默认行为显式写出来是为了让后面排查连接问题时有一个明确的参照。3. 用 ADO.NET 把 MyPetShop.DAL 从空项目写到能跑3.1 解决方案结构DAL 项目引用什么、不引用什么典型的 Pet Shop 解决方案会有四个项目MyPetShop.Model 放实体类Product、Category、OrderMyPetShop.DAL 放数据访问类MyPetShop.BLL 放业务规则MyPetShop.Web 是 ASP.NET 站点。引用关系是单向的Web 引用 BLLBLL 引用 DALDAL 只引用 Model 和 System.Data。namespace MyPetShop.Model { public class Product { public int ProductId { get; set; } public int CategoryId { get; set; } public string ProductName { get; set; } public decimal UnitPrice { get; set; } public decimal? ListPrice { get; set; } } }ListPrice用decimal?而不是decimal因为它可能是 NULL比如商品还没定市场价。实体类里用可空值类型DAL 层在读取时就不用拿特殊值去假装空值。DAL 项目不引用 System.Web这是很多人会忽略的原则一旦 DAL 引用了 System.Web就把它和 ASP.NET 运行时绑死了以后想把这个类库用在控制台程序或单元测试项目里都会很别扭。3.2 第一个查询方法与 SqlDataReader 的顺序读取从最常用的查询开始按分类取商品列表。下面这个ProductDAL是 MyPetShop.DAL 里最基础的一个类。using System; using System.Collections.Generic; using System.Data; using System.Data.SqlClient; using MyPetShop.Model; namespace MyPetShop.DAL { public class ProductDAL { private readonly string _connectionString; public ProductDAL(string connectionString) { _connectionString connectionString; } public ListProduct GetProductsByCategory(int categoryId) { const string sql SELECT ProductId, ProductName, UnitPrice, ListPrice, CategoryId FROM Product WHERE CategoryId CategoryId ORDER BY ProductName; var products new ListProduct(); using (var connection new SqlConnection(_connectionString)) using (var command new SqlCommand(sql, connection)) { command.Parameters.Add(CategoryId, SqlDbType.Int).Value categoryId; connection.Open(); using (var reader command.ExecuteReader()) { while (reader.Read()) { products.Add(new Product { ProductId reader.GetInt32(0), ProductName reader.GetString(1), UnitPrice reader.GetDecimal(2), ListPrice reader.IsDBNull(3) ? (decimal?)null : reader.GetDecimal(3), CategoryId reader.GetInt32(4) }); } } } return products; } } }这段代码里有几个点值得说明。SQL 用const string写在方法顶部而不是散落在代码中间方便集中审查。using保证即使查询抛异常SqlConnection 和 SqlDataReader 也会被释放。注意SqlConnection.Dispose()并不会立刻断开物理连接它只是把底层连接交还给连接池真正的断开会由连接池按空闲时间决定所以用完就释放是避免连接耗尽的第一道防线。读取列值时用的是reader.GetInt32(0)这种按序号的写法效率最高但要求 SELECT 语句的列顺序与读取顺序严格一致。ListPrice列要先判IsDBNull再取值否则会直接抛异常。如果不想手工管这些可以改用reader[ProductName]按列名取值但会有装箱开销在列表页这种高频查询里我通常不这么写。3.3 参数化写入与拿到自增主键的写法插入和更新走的是 ExecuteNonQuery 或 ExecuteScalar。下面这个新增方法除了插入数据还要拿到新记录的自增主键。public int Insert(Product product) { const string sql INSERT INTO Product (CategoryId, ProductName, UnitPrice, ListPrice) VALUES (CategoryId, ProductName, UnitPrice, ListPrice); SELECT CAST(SCOPE_IDENTITY() AS INT);; using (var connection new SqlConnection(_connectionString)) using (var command new SqlCommand(sql, connection)) { command.Parameters.Add(CategoryId, SqlDbType.Int).Value product.CategoryId; command.Parameters.Add(ProductName, SqlDbType.NVarChar, 50).Value product.ProductName; command.Parameters.Add(UnitPrice, SqlDbType.Decimal).Value product.UnitPrice; command.Parameters.Add(ListPrice, SqlDbType.Decimal).Value (object)product.ListPrice ?? DBNull.Value; connection.Open(); return (int)command.ExecuteScalar(); } }这里用ExecuteScalar而不是ExecuteNonQuery因为 INSERT 后面跟了SELECT CAST(SCOPE_IDENTITY() AS INT)ExecuteScalar 会返回结果集第一行第一列的值。为什么不用IDENTITY因为它会返回当前会话中最后生成的标识值如果 Product 表上有一个 INSERT 触发器IDENTITY拿到的是触发器里生成的标识值而不是本次插入的值。SCOPE_IDENTITY()只返回当前作用域内的标识值语义更准确。参数里有一个细节product.ListPrice是可空类型直接赋给Parameters.Add的 Value 会得到DBNull.Value吗不会可空类型为 null 时赋值给 object 参数会直接成为 null 引用SQL Server 驱动在部分场景下会报未将对象引用设置到对象的实例。所以要显式写成(object)product.ListPrice ?? DBNull.Value。这是新手最容易栽的坑之一。3.4 涉多表的写操作用事务一次性提交订单相关操作几乎必然涉及多张表向 Order 插入订单头、向 OrderItem 插入明细、扣减 Product 的库存。任何一个环节失败都应该让前面已写入的数据回滚。public void CreateOrder(Order order, ListOrderItem items) { using (var connection new SqlConnection(_connectionString)) { connection.Open(); using (var transaction connection.BeginTransaction()) { try { var orderId InsertOrder(connection, transaction, order); foreach (var item in items) { InsertOrderItem(connection, transaction, orderId, item); DecrementStock(connection, transaction, item.ProductId, item.Quantity); } transaction.Commit(); } catch { transaction.Rollback(); throw; } } } }这里的关键是把同一个 SqlTransaction 实例传进每一个写方法。SqlCommand 通过command.Transaction transaction关联事务所有命令持的是同一个连接。如果 InserOrder 和 InsertOrderItem 各自 new 一个 SqlConnection 去执行事务就失效了。SQL Server 默认事务隔离级别是 Serializable对 MyPetShop 这种量级完全够用不用特意去调整。非要优化的话可以显式设为 ReadCommitted 减少锁范围但要先确认业务能接受中途读取到已提交的旧数据。有些团队喜欢把事务放在 BLL 层而不是 DAL 层理由是跨多个 DAL 方法的业务操作需要事务。这个做法不是不行但事务一旦跨方法传导连接对象就得在多个方法之间传递代码很快会变成参数地狱。我更倾向于把一次完整的写操作定义为 DAL 的一个方法事务边界收在 DAL 内部BLL 层只管业务规则。4. 把 DAL 接进 ASP.NET 页面与 MVC 路由的三种姿势4.1 WebForms 页面后置代码里的最小调用WebForms 项目里DAL 最典型的接法是在页面后置代码中直接实例化数据访问类。以商品列表页为例public partial class ProductList : System.Web.UI.Page { private readonly ProductDAL _productDal; public ProductList() { var connectionString ConfigurationManager .ConnectionStrings[MyPetShop].ConnectionString; _productDal new ProductDAL(connectionString); } protected void Page_Load(object sender, EventArgs e) { if (!IsPostBack) { ProductRepeater.DataSource _productDal.GetProductsByCategory(1); ProductRepeater.DataBind(); } } }IsPostBack判断不能省WebForms 的每个按钮点击都会触发一次完整的页面生命周期如果不加判断每次回发都会重新查一次数据库并绑定数据。把 DAL 实例放在页面字段里而不是在每个事件方法里 new 一个可以让连接字符串只解析一次。像登录控件那种场景也是一样的套路Login 按钮的点击事件里调用 DAL 提供的用户校验方法控件本身不接触数据库。4.2 ASP.NET MVC 控制器路由参数与 DAL 参数对接MVC 项目里 DAL 的接入点在控制器。先看路由配置public static void RegisterRoutes(RouteCollection routes) { routes.IgnoreRoute({resource}.axd/{*pathInfo}); routes.MapRoute( name: Default, url: {controller}/{action}/{id}, defaults: new { controller Home, action Index, id UrlParameter.Optional } ); }这个路由模板把/Product/Index/3拆成 controllerProduct、actionIndex、id3。控制器接收 id 参数后传给 DALpublic class ProductController : Controller { private readonly ProductDAL _productDal; public ProductController() { var connectionString ConfigurationManager .ConnectionStrings[MyPetShop].ConnectionString; _productDal new ProductDAL(connectionString); } public ActionResult Index(int? categoryId) { var products _productDal.GetProductsByCategory(categoryId ?? 1); return View(products); } }id在路由里是可选的所以控制器参数要声明成int?再用categoryId ?? 1提供默认值。如果声明成int访问/Product/Index时会因为路由给 id 传了 null 而直接 500。这里没有用构造函数注入而是直接在控制器构造函数里读取 ConfigurationManager对一个依赖关系简单的项目来说这种写法比引入 IoC 容器更直观。DAL 的方法只接收明确的int categoryId路由层面的可空类型转换在控制器里完成不要让 DAL 去处理参数不存在这类 Web 层问题。4.3 DAL 里的服务端分页与参数对应表商品列表页早晚要面对分页问题。常见做法是让 DAL 返回一页数据而不是全量数据用 SQL Server 的 ROW_NUMBER() 实现SELECT ProductId, ProductName, UnitPrice, CategoryId FROM ( SELECT ProductId, ProductName, UnitPrice, CategoryId, ROW_NUMBER() OVER (ORDER BY ProductId) AS RowNum FROM Product WHERE CategoryId CategoryId ) AS Paged WHERE RowNum BETWEEN StartRow AND EndRow ORDER BY RowNum;外层查询的 RowNum 取值范围由两个参数决定。这里不用 OFFSET...FETCH 是因为老版本的 SQL Server2008 及更早不支持该语法如果项目部署环境不受限制OFFSET 写法更简洁。DAL 方法签名和参数对应关系如下参数SqlDbType含义CategoryIdInt商品分类筛选条件StartRowInt本页起始行号从 1 开始计数EndRowInt本页结束行号等于 StartRow PageSize - 1对应的方法public ListProduct GetProductsByPage(int categoryId, int pageIndex, int pageSize) { var startRow (pageIndex - 1) * pageSize 1; var endRow pageIndex * pageSize; const string sql SELECT ProductId, ProductName, UnitPrice, CategoryId FROM ( SELECT ..., ROW_NUMBER() OVER (ORDER BY ProductId) AS RowNum FROM Product WHERE CategoryId CategoryId ) AS Paged WHERE RowNum BETWEEN StartRow AND EndRow ORDER BY RowNum; // 执行查询参数赋值略 }pageIndex从 1 开始这是多数分页控件和前端表格组件的约定pageIndex1时 startRow 算出等于 1逻辑最自然。这里有个容易踩的坑如果页面上把 pageIndex 当作 0 开始的数组下标传过来startRow 会变成 0BETWEEN 0 AND ... 查不出任何数据需要在控制器里做一次pageIndex Math.Max(1, pageIndex)的保护。5. 连接池排查、AddWithValue 的坑与一层缓存5.1 连不上库先查连接池而不是查数据库应用突然报Timeout expired时第一反应往往是去查数据库是不是堵了但很多时候数据库很健康问题出在应用侧连接池被占满。排查按三步走先确认连接是否泄漏再查池是否耗尽最后才是数据库负载。应急时可以执行一次SqlConnection.ClearAllPools()它会立即关闭当前进程里所有空闲的物理连接让应用快速恢复。但这只是治标根因通常是某个 SqlDataReader 没释放。用下面这条 SQL 看当前有多少会话及应用名称SELECT DB_NAME(dbid) AS DatabaseName, login_name, COUNT(*) AS Connections FROM sys.dm_exec_sessions WHERE is_user_process 1 GROUP BY dbid, login_name ORDER BY Connections DESC;如果一个用户名的连接数持续稳定在某个高值不回落多半就是连接泄漏。5.2 AddWithValue 看起来省事实际会破坏参数化缓存很多人写参数化 SQL 时图省事用command.Parameters.AddWithValue(ProductName, name)。这个 API 的问题在于参数的 SqlDbType 和长度完全靠运行时推断。同一个查询第一次传入长度为 6 的字符串第二次传入长度为 12 的字符串SQL Server 会认为这是两个不同的参数化查询模板各自生成执行计划缓存里积累大量只差一个长度值的计划。正确的做法是像第 3 章那样显式指定类型和长度command.Parameters.Add(ProductName, SqlDbType.NVarChar, 50)。长度要与表结构里列的定义一致既让 SQL Server 能准确预估基数也避免隐式转换导致索引失效。这一条对查询频率高的接口影响尤其明显是老项目做性能优化时可以优先检查的点。5.3 只读查询用 MemoryCache 做一层短缓存商品分类列表、商品详情这类只读数据可以在 DAL 外面再套一层缓存减少数据库压力。用 System.Runtime.Caching 里的 MemoryCache不依赖 HttpRuntime.Cache方便以后迁移到 ASP.NET Core。public static class ProductCache { private static readonly MemoryCache Cache MemoryCache.Default; public static ListProduct GetOrAdd(string key, FuncListProduct load, int seconds) { if (Cache.Get(key) is ListProduct cached) return cached; var data load(); if (data ! null) { Cache.Set(key, data, DateTimeOffset.Now.AddSeconds(seconds)); } return data; } }调用方把原来的查询方法传给 load 委托ProductCache.GetOrAdd(products:1, () _productDal.GetProductsByCategory(1), 60)。缓存逻辑不放进 DAL 内部的原因是DAL 保持纯粹的数据访问职责缓存属于性能策略应该在调用方按场景决定。特别注意这个缓存存的是 List 的引用调用方如果会对列表做 Add 或 Remove需要先list.ToList()复制一份否则会污染后续所有读取缓存的对象。写操作发生后要记得ProductCache.Cache.Remove(key)短时间内的脏读可以接受长时间不失效就是事故了。本文还有配套的精品资源点击获取