asp中获取数据库数据，通过数据库管理多个asp页面制作api，并实现访问层，逻辑层，数据层分离

<think>我们来实现一个三层架构的ASP API系统： 1. 数据层（Data Access Layer, DAL）：负责与数据库交互，执行SQL语句并返回结果。 2. 业务逻辑层（Business Logic Layer, BLL）：处理业务规则，调用数据层的方法，并返回数据给访问层。 3. 访问层（也称为表示层或API层）：接收HTTP请求，调用业务逻辑层，返回JSON响应。 文件结构： /api/ ├─ dal/ # 数据访问层 │ ├─ db_conn.asp # 数据库连接 │ ├─ user_dal.asp # 用户数据访问 │ └─ product_dal.asp # 产品数据访问 ├─ bll/ # 业务逻辑层 │ ├─ user_bll.asp # 用户业务逻辑 │ └─ product_bll.asp # 产品业务逻辑 ├─ api/ # API访问层 │ ├─ user_api.asp # 用户API │ └─ product_api.asp # 产品API └─ common/ # 公共函数 ├─ json_utils.asp # JSON转换函数 ├─ security.asp # 安全相关函数 └─ response.asp # 响应处理 我们将逐步实现每个部分。</think># ASP三层架构API解决方案 我将为您设计一个完整的ASP三层架构API系统，实现访问层、逻辑层和数据层的分离，通过数据库管理多个ASP页面制作API。 ## 系统架构设计 ``` 客户端 → 访问层 (API层) → 业务逻辑层 (BLL) → 数据访问层 (DAL) → 数据库 ``` ### 目录结构 ``` /asp_api/ ├─ api/ # 访问层 (API接口) │ ├─ user_api.asp │ ├─ product_api.asp │ └─ order_api.asp ├─ bll/ # 业务逻辑层 │ ├─ user_bll.asp │ ├─ product_bll.asp │ └─ order_bll.asp ├─ dal/ # 数据访问层 │ ├─ db_conn.asp │ ├─ user_dal.asp │ ├─ product_dal.asp │ └─ order_dal.asp ├─ common/ # 公共函数 │ ├─ json_utils.asp │ ├─ security.asp │ └─ response.asp └─ config/ # 配置文件 ├─ constants.asp └─ settings.asp ``` ## 实现代码 ### 1. 配置文件 (`config/constants.asp`) ```asp <% ' 数据库连接配置 Const DB_PROVIDER = "SQLOLEDB" Const DB_SERVER = ".\SQLEXPRESS" Const DB_NAME = "MyAppDB" Const DB_USER = "sa" Const DB_PASSWORD = "SecurePassword123" ' API配置 Const API_KEY = "A1B2C3D4E5F6G7H8" Const MAX_RECORDS = 100 Const TOKEN_EXPIRY = 24 ' 小时 ' 错误代码 Const ERR_SUCCESS = 0 Const ERR_DB_CONNECTION = 1001 Const ERR_INVALID_PARAM = 2001 Const ERR_AUTH_FAILED = 3001 %> ``` ### 2. 数据库连接层 (`dal/db_conn.asp`) ```asp <!--#include file="../config/constants.asp"--> <% ' 数据库连接类 Class DatabaseConnection Private conn ' 构造函数 Private Sub Class_Initialize() Set conn = Server.CreateObject("ADODB.Connection") Dim connStr connStr = DB_PROVIDER & ";" & _ "Data Source=" & DB_SERVER & ";" & _ "Initial Catalog=" & DB_NAME & ";" & _ "User ID=" & DB_USER & ";" & _ "Password=" & DB_PASSWORD & ";" conn.Open connStr If Err.Number <> 0 Then Err.Raise ERR_DB_CONNECTION, "DatabaseConnection", "数据库连接失败: " & Err.Description End If End Sub ' 获取连接对象 Public Function GetConnection() Set GetConnection = conn End Function ' 关闭连接 Public Sub CloseConnection() If conn.State <> 0 Then conn.Close End If Set conn = Nothing End Sub ' 析构函数 Private Sub Class_Terminate() CloseConnection End Sub End Class ' 创建数据库连接实例 Function CreateDBConnection() Dim dbConn Set dbConn = New DatabaseConnection Set CreateDBConnection = dbConn End Function %> ``` ### 3. 数据访问层 - 用户模块 (`dal/user_dal.asp`) ```asp <!--#include file="db_conn.asp"--> <!--#include file="../common/json_utils.asp"--> <% ' 用户数据访问类 Class UserDAL Private dbConn ' 构造函数 Private Sub Class_Initialize() Set dbConn = CreateDBConnection End Sub ' 获取所有用户 Public Function GetAllUsers() Dim conn, rs, sql Set conn = dbConn.GetConnection() sql = "SELECT id, username, email, created_at FROM users" Set rs = conn.Execute(sql) If rs.EOF Then GetAllUsers = "[]" Else GetAllUsers = RecordsetToJSON(rs) End If rs.Close Set rs = Nothing End Function ' 根据ID获取用户 Public Function GetUserById(userId) Dim conn, rs, sql Set conn = dbConn.GetConnection() sql = "SELECT id, username, email, created_at FROM users WHERE id = " & userId Set rs = conn.Execute(sql) If rs.EOF Then GetUserById = "{}" Else GetUserById = RecordsetToJSON(rs) End If rs.Close Set rs = Nothing End Function ' 创建用户 Public Function CreateUser(username, email, password) Dim conn, sql, rs, newId Set conn = dbConn.GetConnection() ' 检查用户名是否已存在 sql = "SELECT id FROM users WHERE username = '" & username & "'" Set rs = conn.Execute(sql) If Not rs.EOF Then CreateUser = "-1" ' 用户名已存在 Exit Function End If ' 插入新用户 sql = "INSERT INTO users (username, email, password) VALUES ('" & _ username & "', '" & email & "', '" & password & "')" conn.Execute sql ' 获取新ID sql = "SELECT SCOPE_IDENTITY() AS new_id" Set rs = conn.Execute(sql) newId = rs("new_id") rs.Close Set rs = Nothing CreateUser = newId End Function ' 更新用户 Public Function UpdateUser(userId, username, email) Dim conn, sql Set conn = dbConn.GetConnection() sql = "UPDATE users SET " If username <> "" Then sql = sql & "username = '" & username & "', " If email <> "" Then sql = sql & "email = '" & email & "', " ' 移除末尾逗号 If Right(sql, 2) = ", " Then sql = Left(sql, Len(sql)-2) sql = sql & " WHERE id = " & userId conn.Execute sql UpdateUser = (conn.Errors.Count = 0) End Function ' 析构函数 Private Sub Class_Terminate() dbConn.CloseConnection Set dbConn = Nothing End Sub End Class ' 创建用户DAL实例 Function CreateUserDAL() Dim userDAL Set userDAL = New UserDAL Set CreateUserDAL = userDAL End Function %> ``` ### 4. 业务逻辑层 - 用户模块 (`bll/user_bll.asp`) ```asp <!--#include file="../dal/user_dal.asp"--> <!--#include file="../common/security.asp"--> <% ' 用户业务逻辑类 Class UserBLL Private userDAL ' 构造函数 Private Sub Class_Initialize() Set userDAL = CreateUserDAL() End Sub ' 获取所有用户 Public Function GetAllUsers() GetAllUsers = userDAL.GetAllUsers() End Function ' 获取单个用户 Public Function GetUserById(userId) If Not IsNumeric(userId) Then GetUserById = "{}" Exit Function End If GetUserById = userDAL.GetUserById(CLng(userId)) End Function ' 创建用户 Public Function CreateUser(username, email, password) ' 验证输入 If Len(username) < 3 Then CreateUser = "0|用户名至少需要3个字符" Exit Function End If If Not IsValidEmail(email) Then CreateUser = "0|无效的邮箱格式" Exit Function End If If Len(password) < 6 Then CreateUser = "0|密码至少需要6个字符" Exit Function End If ' 密码加密 password = HashPassword(password) Dim newId newId = userDAL.CreateUser(username, email, password) If newId = "-1" Then CreateUser = "0|用户名已存在" Else CreateUser = "1|" & newId End If End Function ' 更新用户 Public Function UpdateUser(userId, username, email) If Not IsNumeric(userId) Then UpdateUser = False Exit Function End If ' 验证输入 If username <> "" And Len(username) < 3 Then UpdateUser = False Exit Function End If If email <> "" And Not IsValidEmail(email) Then UpdateUser = False Exit Function End If UpdateUser = userDAL.UpdateUser(CLng(userId), username, email) End Function ' 析构函数 Private Sub Class_Terminate() Set userDAL = Nothing End Sub End Class ' 创建用户BLL实例 Function CreateUserBLL() Dim userBLL Set userBLL = New UserBLL Set CreateUserBLL = userBLL End Function %> ``` ### 5. 访问层 - 用户API (`api/user_api.asp`) ```asp <!--#include file="../bll/user_bll.asp"--> <!--#include file="../common/response.asp"--> <!--#include file="../common/security.asp"--> <% ' 验证API密钥 If Not ValidateAPIKey() Then SendErrorResponse "无效的API密钥", ERR_AUTH_FAILED Response.End End If ' 设置响应类型 Response.ContentType = "application/json" ' 获取请求方法和参数 Dim method, action, userId, username, email, password method = Request.ServerVariables("REQUEST_METHOD") action = Request.QueryString("action") userId = Request("id") username = Request("username") email = Request("email") password = Request("password") ' 创建业务逻辑对象 Dim userBLL Set userBLL = CreateUserBLL() ' 路由处理 Select Case method Case "GET" Select Case action Case "list" SendSuccessResponse "用户列表", userBLL.GetAllUsers() Case "single" If userId = "" Then SendErrorResponse "缺少用户ID参数", ERR_INVALID_PARAM Else SendSuccessResponse "用户信息", userBLL.GetUserById(userId) End If Case Else SendErrorResponse "无效的GET操作", ERR_INVALID_PARAM End Select Case "POST" Select Case action Case "create" If username = "" Or email = "" Or password = "" Then SendErrorResponse "缺少必要参数", ERR_INVALID_PARAM Else Dim createResult createResult = userBLL.CreateUser(username, email, password) Dim parts parts = Split(createResult, "|") If parts(0) = "1" Then SendSuccessResponse "用户创建成功", "{""id"":" & parts(1) & "}" Else SendErrorResponse parts(1), ERR_INVALID_PARAM End If End If Case "update" If userId = "" Then SendErrorResponse "缺少用户ID参数", ERR_INVALID_PARAM Else If userBLL.UpdateUser(userId, username, email) Then SendSuccessResponse "用户更新成功", "{}" Else SendErrorResponse "用户更新失败", ERR_INVALID_PARAM End If End If Case Else SendErrorResponse "无效的POST操作", ERR_INVALID_PARAM End Select Case Else SendErrorResponse "不支持的HTTP方法", ERR_INVALID_PARAM End Select ' 清理资源 Set userBLL = Nothing %> ``` ### 6. 公共函数 - JSON工具 (`common/json_utils.asp`) ```asp <% ' 将记录集转换为JSON Function RecordsetToJSON(rs) If rs.EOF Then RecordsetToJSON = "[]" Exit Function End If Dim result, field result = "[" Do While Not rs.EOF result = result & "{" For Each field In rs.Fields result = result & """" & field.Name & """:" ' 处理不同类型的数据 If IsNull(field.Value) Then result = result & "null" ElseIf VarType(field.Value) = vbString Then result = result & """" & EscapeJSON(field.Value) & """" ElseIf VarType(field.Value) = vbDate Then result = result & """" & FormatDateTime(field.Value, 2) & """" ElseIf VarType(field.Value) = vbBoolean Then If field.Value Then result = result & "true" Else result = result & "false" End If Else result = result & field.Value End If result = result & "," Next ' 移除最后一个逗号 result = Left(result, Len(result)-1) & "}," rs.MoveNext Loop ' 移除最后一个逗号并闭合数组 RecordsetToJSON = Left(result, Len(result)-1) & "]" End Function ' 转义JSON特殊字符 Function EscapeJSON(str) If IsNull(str) Then EscapeJSON = "" Exit Function End If Dim result result = Replace(str, "\", "\\") result = Replace(result, """", "\""") result = Replace(result, vbCr, "\r") result = Replace(result, vbLf, "\n") result = Replace(result, vbTab, "\t") EscapeJSON = result End Function %> ``` ### 7. 公共函数 - 安全工具 (`common/security.asp`) ```asp <!--#include file="../config/constants.asp"--> <% ' 验证API密钥 Function ValidateAPIKey() Dim apiKey apiKey = Request.ServerVariables("HTTP_X_API_KEY") If apiKey <> API_KEY Then ValidateAPIKey = False Else ValidateAPIKey = True End If End Function ' 密码哈希函数 Function HashPassword(password) ' 使用SHA256哈希算法 Dim sha256 Set sha256 = Server.CreateObject("System.Security.Cryptography.SHA256Managed") Dim bytes, hashBytes, hash bytes = ToUTF8Bytes(password) hashBytes = sha256.ComputeHash_2((bytes)) hash = ToHexString(hashBytes) Set sha256 = Nothing HashPassword = hash End Function ' 字符串转UTF8字节数组 Function ToUTF8Bytes(str) Dim stream, bytes Set stream = Server.CreateObject("ADODB.Stream") stream.Type = 2 ' adTypeText stream.Charset = "utf-8" stream.Open stream.WriteText str stream.Position = 0 stream.Type = 1 ' adTypeBinary stream.Position = 3 ' 跳过BOM bytes = stream.Read stream.Close Set stream = Nothing ToUTF8Bytes = bytes End Function ' 字节数组转十六进制字符串 Function ToHexString(bytes) Dim hex, i, hexChar hex = "" For i = 1 To LenB(bytes) hexChar = Hex(AscB(MidB(bytes, i, 1))) If Len(hexChar) = 1 Then hexChar = "0" & hexChar hex = hex & hexChar Next ToHexString = LCase(hex) End Function ' 验证邮箱格式 Function IsValidEmail(email) Dim regex Set regex = New RegExp regex.Pattern = "^[\w-\.]+@([\w-]+\.)+[\w-]{2,4}$" regex.IgnoreCase = True IsValidEmail = regex.Test(email) Set regex = Nothing End Function %> ``` ### 8. 公共函数 - 响应处理 (`common/response.asp`) ```asp <!--#include file="../config/constants.asp"--> <% ' 发送成功响应 Sub SendSuccessResponse(message, data) Response.ContentType = "application/json" Response.Write "{" Response.Write """status"": ""success""," Response.Write """code"": " & ERR_SUCCESS & "," Response.Write """message"": """ & EscapeJSON(message) & """," Response.Write """data"": " & data Response.Write "}" End Sub ' 发送错误响应 Sub SendErrorResponse(message, errorCode) Response.ContentType = "application/json" Response.Write "{" Response.Write """status"": ""error""," Response.Write """code"": " & errorCode & "," Response.Write """message"": """ & EscapeJSON(message) & """" Response.Write "}" End Sub ' 转义JSON特殊字符（简化版） Function EscapeJSON(str) If IsNull(str) Then str = "" EscapeJSON = Replace(Replace(str, "\", "\\"), """", "\""") End Function %> ``` ## 三层架构工作流程 1. **访问层 (API层)**: - 接收HTTP请求 - 验证API密钥 - 解析请求参数 - 调用业务逻辑层 - 返回JSON响应 2. **业务逻辑层 (BLL)**: - 验证业务规则 - 处理数据转换 - 调用数据访问层 - 处理业务异常 3. **数据访问层 (DAL)**: - 管理数据库连接 - 执行SQL语句 - 处理数据转换 - 封装数据库操作 ## API使用示例 ### 1. 获取用户列表 ``` GET /api/user_api.asp?action=list Headers: X-API-KEY: A1B2C3D4E5F6G7H8 ``` 响应: ```json { "status": "success", "code": 0, "message": "用户列表", "data": [ { "id": 1, "username": "john_doe", "email": "[email protected]", "created_at": "2023-05-15" }, { "id": 2, "username": "jane_smith", "email": "[email protected]", "created_at": "2023-05-16" } ] } ``` ### 2. 创建新用户 ``` POST /api/user_api.asp?action=create Headers: X-API-KEY: A1B2C3D4E5F6G7H8 Content-Type: application/x-www-form-urlencoded username=newuser&[email protected]&password=secure123 ``` 响应: ```json { "status": "success", "code": 0, "message": "用户创建成功", "data": { "id": 3 } } ``` ## 系统优势 1. **清晰的层次分离**： - 访问层：处理HTTP请求/响应 - 业务层：实现业务规则 - 数据层：处理数据存储 2. **代码复用性**： - 公共函数集中管理 - 业务逻辑复用 - 数据访问接口统一 3. **可维护性**： - 修改数据库结构只需调整DAL - 业务规则变更只需修改BLL - API接口变更只需修改访问层 4. **安全性**： - API密钥验证 - 参数化查询 - 密码哈希存储 - 输入验证 5. **可扩展性**： - 轻松添加新模块 - 支持多种数据库 - 易于集成其他服务 ## 部署建议 1. **IIS配置**： - 启用父路径 - 设置默认文档 - 配置HTTP响应头 - 启用Gzip压缩 2. **安全加固**： ```asp ' 在config/settings.asp中添加 <% ' 禁用详细错误信息 Response.AddHeader "Error-Details", "Off" ' 设置安全头 Response.AddHeader "X-Content-Type-Options", "nosniff" Response.AddHeader "X-Frame-Options", "DENY" Response.AddHeader "Content-Security-Policy", "default-src 'self'" %> ``` 3. **性能优化**： - 使用数据库连接池 - 实现查询缓存 - 启用输出缓存 - 使用存储过程 4. **日志记录**： ```asp ' 在common/logger.asp中添加 <% Sub LogRequest(status, message) Dim logFile, fso, ts Set fso = Server.CreateObject("Scripting.FileSystemObject") logFile = Server.MapPath("/logs/api_" & Year(Date) & Month(Date) & Day(Date) & ".log") Set ts = fso.OpenTextFile(logFile, 8, True) ts.WriteLine Now & " | " & Request.ServerVariables("REMOTE_ADDR") & _ " | " & Request.ServerVariables("REQUEST_METHOD") & " " & _ Request.ServerVariables("URL") & " | " & status & " | " & message ts.Close Set ts = Nothing Set fso = Nothing End Sub %> ``` ## 总结 这个ASP三层架构API系统实现了： 1. 清晰的层次分离（访问层、业务层、数据层） 2. 模块化的代码组织 3. 统一的安全机制 4. 标准化的API响应格式 5. 可扩展的架构设计 6. 完善的错误处理机制 通过这种架构，您可以轻松管理多个ASP页面作为API端点，实现复杂的业务逻辑，并保持代码的可维护性和可扩展性。