引言：ASP与SQL Server的强大组合
ASP（Active Server Pages）作为微软早期推出的服务器端脚本技术，与SQL Server数据库的结合堪称经典搭档。尽管现在有更多现代技术栈，但理解这一组合对于维护遗留系统和学习Web开发基础仍然至关重要。本文将全面介绍如何实现ASP与SQL Server的完美结合，从基础连接到高级优化技巧，帮助您掌握这一经典技术组合的精髓。
一、基础环境配置
1.1 必需软件与组件
要开始ASP与SQL Server的开发，您需要以下环境：
IIS（Internet Information Services）
：Windows自带的Web服务器
SQL Server
：建议使用2008R2或更高版本
MDAC（Microsoft Data Access Components）
：数据访问组件
适当的Windows权限
：确保IIS用户对数据库有访问权限
1.2 创建第一个ASP页面
让我们从一个简单的ASP页面开始，该页面将连接到SQL Server并显示数据：
<%@ Language=VBScript CodePage=65001 %>
<% Option Explicit %>

员工信息列表

<% ' 定义数据库连接变量 Dim conn, rs, sql ' 创建数据库连接对象 Set conn = Server.CreateObject("ADODB.Connection") ' 创建记录集对象 Set rs = Server.CreateObject("ADODB.Recordset") ' 定义连接字符串 - 使用Windows身份验证 ' 注意：实际使用时请替换为您的服务器名和数据库名 Dim connectionString connectionString = "Provider=SQLOLEDB;Data Source=YOUR_SERVER_NAME;Initial Catalog=YOUR_DATABASE_NAME;Integrated Security=SSPI;" ' 定义SQL查询 sql = "SELECT EmployeeID, FirstName, LastName, Department FROM Employees ORDER BY LastName" On Error Resume Next ' 打开数据库连接 conn.Open connectionString If Err.Number <> 0 Then
Response.Write “

数据库连接错误: ” & Err.Description & “

”
Err.Clear
Else
‘ 执行查询
rs.Open sql, conn, 1, 1 ‘ adOpenKeyset, adLockReadOnly

If Not rs.EOF Then
‘ 创建HTML表格显示数据
Response.Write “

”
Response.Write “

”

Do While Not rs.EOF
Response.Write “

”
Response.Write “

”
Response.Write “

”
Response.Write “

”
Response.Write “

”
rs.MoveNext
Loop

Response.Write “

ID	姓名	部门
” & rs(“EmployeeID”) & “	” & rs(“FirstName”) & ” ” & rs(“LastName”) & “	” & rs(“Department”) & “

”
Else
Response.Write “

没有找到员工记录

”
End If

‘ 关闭记录集
rs.Close
End If

‘ 关闭连接
conn.Close

‘ 释放对象
Set rs = Nothing
Set conn = Nothing
%>


1.3 连接字符串详解
连接字符串是ASP与SQL Server通信的关键。以下是几种常见的连接字符串格式：
使用Windows身份验证（推荐用于开发环境）
“Provider=SQLOLEDB;Data Source=SERVER_NAME;Initial Catalog=DATABASE_NAME;Integrated Security=SSPI;”
使用SQL Server身份验证
“Provider=SQLOLEDB;Data Source=SERVER_NAME;Initial Catalog=DATABASE_NAME;User ID=username;Password=password;”
使用OLE DB提供程序（SQL Native Client）
“Provider=SQLNCLI11;Server=SERVER_NAME;Database=DATABASE_NAME;Trusted_Connection=yes;”
使用ODBC驱动程序
“Driver={SQL Server Native Client 11.0};Server=SERVER_NAME;Database=DATABASE_NAME;Trusted_Connection=yes;”
二、高级连接技术
2.1 连接池管理
连接池是提高ASP应用性能的关键技术。合理配置连接池参数可以显著提升应用响应速度：
<% ' 高级连接字符串，包含连接池参数 Dim advancedConnString advancedConnString = "Provider=SQLOLEDB;" & _ "Data Source=YOUR_SERVER;" & _ "Initial Catalog=YOUR_DB;" & _ "Integrated Security=SSPI;" & _ "Connect Timeout=15;" & _ "Connection Lifetime=300;" & _ "Min Pool Size=5;" & _ "Max Pool Size=100;" & _ "Pooling=true;" %>
连接池参数说明：
Connect Timeout
：连接超时时间（秒）
Connection Lifetime
：连接在池中保持活动的最长时间（秒）
Min Pool Size
：池中保持的最小连接数
Max Pool Size
：池中允许的最大连接数
Pooling
：是否启用连接池（true/false）
2.2 错误处理与重试机制
健壮的数据库连接应该包含错误处理和重试逻辑：
<% Function ExecuteWithRetry(sql, maxRetries) Dim conn, rs, attempt, success attempt = 0 success = False Do While attempt < maxRetries And Not success attempt = attempt + 1 On Error Resume Next Set conn = Server.CreateObject("ADODB.Connection") Set rs = Server.CreateObject("ADODB.Recordset") conn.Open GetConnectionString() If Err.Number = 0 Then rs.Open sql, conn, 1, 1 If Err.Number = 0 Then success = True Set ExecuteWithRetry = rs Exit Function End If End If ' 记录错误日志 LogError "数据库操作失败（尝试" & attempt & "）：" & Err.Description ' 等待一段时间后重试 If attempt < maxRetries Then Response.Write "

连接失败，正在重试（” & attempt & “/” & maxRetries & “）…

”
‘ 实际应用中应使用更优雅的等待方式
Dim i
For i = 1 To 1000000
‘ 空循环实现延迟
Next
End If

‘ 清理资源
If Not rs Is Nothing Then rs.Close
If Not conn Is Nothing Then conn.Close
Set rs = Nothing
Set conn = Nothing
Err.Clear
Loop

If Not success Then
Response.Write “

数据库操作失败，请稍后再试

”
Set ExecuteWithRetry = Nothing
End If
End Function

‘ 使用示例
Dim rsEmployees
Set rsEmployees = ExecuteWithRetry(“SELECT * FROM Employees”, 3)

If Not rsEmployees Is Nothing Then
‘ 处理数据…
rsEmployees.Close
Set rsEmployees = Nothing
End If
%>
2.3 安全连接最佳实践
使用参数化查询防止SQL注入
<% ' 创建命令对象并使用参数化查询 Dim cmd, param Set cmd = Server.CreateObject("ADODB.Command") cmd.ActiveConnection = conn cmd.CommandText = "SELECT * FROM Users WHERE Username = ? AND Password = ?" cmd.CommandType = 1 ' adCmdText ' 添加参数 Set param = cmd.CreateParameter("@username", 200, 1, 50, Request.Form("username")) ' adVarChar, adParamInput cmd.Parameters.Append param Set param = cmd.CreateParameter("@password", 200, 1, 50, Request.Form("password")) cmd.Parameters.Append param ' 执行查询 Set rs = cmd.Execute %>
加密连接字符串
<% ' 使用简单的XOR加密（实际应用应使用更强的加密） Function EncryptConnectionString(connStr) Dim key, result, i key = "MySecretKey123" result = "" For i = 1 To Len(connStr) result = result & Chr(Asc(Mid(connStr, i, 1)) Xor Asc(Mid(key, (i Mod Len(key)) + 1, 1))) Next EncryptConnectionString = result End Function Function DecryptConnectionString(encryptedStr) ' XOR加密是对称的，解密使用相同函数 DecryptConnectionString = EncryptConnectionString(encryptedStr) End Function ' 存储加密后的连接字符串 Dim encryptedConnStr encryptedConnStr = EncryptConnectionString("Provider=SQLOLEDB;Data Source=SERVER;Initial Catalog=DB;Integrated Security=SSPI;") ' 使用时解密 Dim connStr connStr = DecryptConnectionString(encryptedConnStr) %>
三、数据操作与CRUD实现
3.1 创建（Create）操作
<% Sub AddEmployee(firstName, lastName, department, email) Dim conn, cmd, param On Error Resume Next Set conn = Server.CreateObject("ADODB.Connection") conn.Open GetConnectionString() Set cmd = Server.CreateObject("ADODB.Command") cmd.ActiveConnection = conn cmd.CommandText = "INSERT INTO Employees (FirstName, LastName, Department, Email) VALUES (?, ?, ?, ?)" cmd.CommandType = 1 ' 添加参数 cmd.Parameters.Append cmd.CreateParameter("@firstName", 200, 1, 50, firstName) cmd.Parameters.Append cmd.CreateParameter("@lastName", 200, 1, 50, lastName) cmd.Parameters.Append cmd.CreateParameter("@department", 200, 1, 50, department) cmd.Parameters.Append cmd.CreateParameter("@email", 200, 1, 100, email) cmd.Execute If Err.Number = 0 Then Response.Write "

员工添加成功！

”
Else
Response.Write “

添加失败：” & Err.Description & “

”
End If

conn.Close
Set cmd = Nothing
Set conn = Nothing
End Sub

‘ 使用示例
If Request.Form(“action”) = “add” Then
AddEmployee Request.Form(“firstName”), Request.Form(“lastName”), _
Request.Form(“department”), Request.Form(“email”)
End If
%>
3.2 读取（Read）操作
<% Function GetEmployeeByID(employeeID) Dim conn, rs, sql Set conn = Server.CreateObject("ADODB.Connection") Set rs = Server.CreateObject("ADODB.Recordset") conn.Open GetConnectionString() sql = "SELECT * FROM Employees WHERE EmployeeID = ?" ' 使用参数化查询 Dim cmd Set cmd = Server.CreateObject("ADODB.Command") cmd.ActiveConnection = conn cmd.CommandText = sql cmd.CommandType = 1 cmd.Parameters.Append cmd.CreateParameter("@id", 3, 1, , employeeID) ' adInteger Set rs = cmd.Execute If Not rs.EOF Then ' 返回记录集 Set GetEmployeeByID = rs Else Set GetEmployeeByID = Nothing End If ' 注意：调用者需要关闭记录集和连接 End Function ' 使用示例 Dim emp Set emp = GetEmployeeByID(123) If Not emp Is Nothing Then Response.Write "姓名：" & emp("FirstName") & " " & emp("LastName") emp.Close Set emp = Nothing End If %>
3.3 更新（Update）操作
<% Sub UpdateEmployee(employeeID, firstName, lastName, department, email) Dim conn, cmd On Error Resume Next Set conn = Server.CreateObject("ADODB.Connection") conn.Open GetConnectionString() Set cmd = Server.CreateObject("ADODB.Command") cmd.ActiveConnection = conn cmd.CommandText = "UPDATE Employees SET FirstName = ?, LastName = ?, Department = ?, Email = ? WHERE EmployeeID = ?" cmd.CommandType = 1 cmd.Parameters.Append cmd.CreateParameter("@firstName", 200, 1, 50, firstName) cmd.Parameters.Append cmd.CreateParameter("@lastName", 200, 1, 50, lastName) cmd.Parameters.Append cmd.CreateParameter("@department", 200, 1, 50, department) cmd.Parameters.Append cmd.CreateParameter("@email", 200, 1, 100, email) cmd.Parameters.Append cmd.CreateParameter("@id", 3, 1, , employeeID) cmd.Execute If Err.Number = 0 Then Response.Write "

员工信息更新成功！

”
Else
Response.Write “

更新失败：” & Err.Description & “

”
End If

conn.Close
Set cmd = Nothing
Set conn = Nothing
End Sub
%>
3.4 删除（Delete）操作
<% Sub DeleteEmployee(employeeID) Dim conn, cmd On Error Resume Next Set conn = Server.CreateObject("ADODB.Connection") conn.Open GetConnectionString() Set cmd = Server.CreateObject("ADODB.Command") cmd.ActiveConnection = conn cmd.CommandText = "DELETE FROM Employees WHERE EmployeeID = ?" cmd.CommandType = 1 cmd.Parameters.Append cmd.CreateParameter("@id", 3, 1, , employeeID) cmd.Execute If Err.Number = 0 Then Response.Write "

员工删除成功！

”
Else
Response.Write “

删除失败：” & Err.Description & “

”
End If

conn.Close
Set cmd = Nothing
Set conn = Nothing
End Sub
%>
四、性能优化技巧
4.1 数据库索引优化
— 创建适当的索引
— 1. 为经常查询的列创建索引
CREATE INDEX IX_Employees_LastName ON Employees(LastName);

— 2. 为经常一起查询的列创建复合索引
CREATE INDEX IX_Employees_LastName_FirstName ON Employees(LastName, FirstName);

— 3. 为经常用于WHERE条件的列创建索引
CREATE INDEX IX_Employees_Department ON Employees(Department);

— 4. 查看索引使用情况
SELECT
OBJECT_NAME(s.object_id) AS TableName,
i.name AS IndexName,
s.user_seeks,
s.user_scans,
s.user_lookups,
s.user_updates
FROM sys.dm_db_index_usage_stats s
JOIN sys.indexes i ON s.object_id = i.object_id AND s.index_id = i.index_id
WHERE OBJECT_NAME(s.object_id) = ‘Employees’;
4.2 ASP代码优化
4.2.1 使用记录集缓存
<% ' 缓存经常访问的数据 Function GetCachedData(cacheKey, sql, cacheDuration) Dim data, cacheTime ' 检查缓存是否存在且未过期 If Application(cacheKey & "_Time") <> “” Then
cacheTime = CDate(Application(cacheKey & “_Time”))
If DateDiff(“s”, cacheTime, Now) < cacheDuration Then Set GetCachedData = Application(cacheKey) Exit Function End If End If ' 从数据库获取新数据 Dim conn, rs Set conn = Server.CreateObject("ADODB.Connection") Set rs = Server.CreateObject("ADODB.Recordset") conn.Open GetConnectionString() rs.Open sql, conn, 1, 1 ' 存入缓存 Set Application(cacheKey) = rs Application(cacheKey & "_Time") = Now Set GetCachedData = rs ' 注意：调用者需要处理克隆的记录集以避免缓存污染 End Function ' 使用示例 Dim rsDepartments Set rsDepartments = GetCachedData("Departments", "SELECT * FROM Departments", 300) ' 缓存5分钟 If Not rsDepartments Is Nothing Then ' 克隆记录集以避免影响缓存 Dim rsClone Set rsClone = rsDepartments.Clone Do While Not rsClone.EOF Response.Write rsClone("DepartmentName") & "
”
rsClone.MoveNext
Loop

rsClone.Close
Set rsClone = Nothing
End If
%>
4.2.2 分页查询优化
<% ' 高效的分页查询 Function GetPagedRecords(sql, pageNumber, pageSize, ByRef totalPages, ByRef totalRecords) Dim conn, cmd, rs, startRow, endRow ' 计算分页范围 startRow = (pageNumber - 1) * pageSize + 1 endRow = pageNumber * pageSize Set conn = Server.CreateObject("ADODB.Connection") Set cmd = Server.CreateObject("ADODB.Command") Set rs = Server.CreateObject("ADODB.Recordset") conn.Open GetConnectionString() ' 使用ROW_NUMBER()进行高效分页（SQL Server 2005+） Dim pagedSQL pagedSQL = "WITH PagedResults AS (" & _ "SELECT *, ROW_NUMBER() OVER (ORDER BY EmployeeID) AS RowNum " & _ "FROM (" & sql & ") AS BaseQuery" & _ ") SELECT * FROM PagedResults WHERE RowNum BETWEEN ? AND ?" cmd.ActiveConnection = conn cmd.CommandText = pagedSQL cmd.CommandType = 1 cmd.Parameters.Append cmd.CreateParameter("@startRow", 3, 1, , startRow) cmd.Parameters.Append cmd.CreateParameter("@endRow", 3, 1, , endRow) Set rs = cmd.Execute ' 获取总记录数和总页数 Dim countCmd, countRs Set countCmd = Server.CreateObject("ADODB.Command") countCmd.ActiveConnection = conn countCmd.CommandText = "SELECT COUNT(*) AS Total FROM (" & sql & ") AS CountQuery" countCmd.CommandType = 1 Set countRs = countCmd.Execute totalRecords = countRs("Total") totalPages = Int((totalRecords + pageSize - 1) / pageSize) Set countRs.Close Set countCmd = Nothing Set countRs = Nothing Set GetPagedRecords = rs ' 调用者需要关闭记录集和连接 End Function ' 使用示例 Dim totalPages, totalRecords, rsPage Set rsPage = GetPagedRecords("SELECT EmployeeID, FirstName, LastName FROM Employees", 2, 10, totalPages, totalRecords) Response.Write "

第 ” & currentPage & ” 页，共 ” & totalPages & ” 页，总记录数：” & totalRecords & “

”

If Not rsPage Is Nothing Then
Do While Not rsPage.EOF
Response.Write rsPage(“FirstName”) & ” ” & rsPage(“LastName”) & “
”
rsPage.MoveNext
Loop
rsPage.Close
Set rsPage = Nothing
End If
%>
4.3 SQL查询优化
4.3.1 避免N+1查询问题
<% ' 错误的N+1查询示例（应避免） Sub BadExample() Dim conn, rs, cmd, rsDetails Set conn = Server.CreateObject("ADODB.Connection") conn.Open GetConnectionString() ' 主查询 Set rs = Server.CreateObject("ADODB.Recordset") rs.Open "SELECT DepartmentID, DepartmentName FROM Departments", conn, 1, 1 Do While Not rs.EOF Response.Write "

” & rs(“DepartmentName”) & “

”

‘ 对每个部门执行单独查询（N+1问题）
Set cmd = Server.CreateObject(“ADODB.Command”)
cmd.ActiveConnection = conn
cmd.CommandText = “SELECT FirstName, LastName FROM Employees WHERE DepartmentID = ?”
cmd.Parameters.Append cmd.CreateParameter(“@id”, 3, 1, , rs(“DepartmentID”))
Set rsDetails = cmd.Execute

Do While Not rsDetails.EOF
Response.Write “

” & rsDetails(“FirstName”) & ” ” & rsDetails(“LastName”) & “

”
rsDetails.MoveNext
Loop

rsDetails.Close
Set rsDetails = Nothing
Set cmd = Nothing

rs.MoveNext
Loop

rs.Close
conn.Close
Set rs = Nothing
Set conn = Nothing
End Sub

‘ 正确的JOIN查询示例
Sub GoodExample()
Dim conn, rs

Set conn = Server.CreateObject(“ADODB.Connection”)
Set rs = Server.CreateObject(“ADODB.Recordset”)
conn.Open GetConnectionString()

‘ 使用JOIN一次性获取所有数据
rs.Open “SELECT d.DepartmentName, e.FirstName, e.LastName ” & _
“FROM Departments d ” & _
“LEFT JOIN Employees e ON d.DepartmentID = e.DepartmentID ” & _
“ORDER BY d.DepartmentName, e.LastName”, conn, 1, 1

Dim currentDept
currentDept = “”

Do While Not rs.EOF
If currentDept <> rs(“DepartmentName”) Then
currentDept = rs(“DepartmentName”)
Response.Write “

” & currentDept & “

”
End If

If Not IsNull(rs(“FirstName”)) Then
Response.Write “

” & rs(“FirstName”) & ” ” & rs(“LastName”) & “

”
End If

rs.MoveNext
Loop

rs.Close
conn.Close
Set rs = Nothing
Set conn = Nothing
End Sub
%>
4.3.2 使用存储过程
— 创建存储过程
CREATE PROCEDURE GetEmployeesByDepartment
@DepartmentID INT,
@PageNumber INT = 1,
@PageSize INT = 10
AS
BEGIN
SET NOCOUNT ON;

DECLARE @StartRow INT, @EndRow INT
SET @StartRow = (@PageNumber – 1) * @PageSize + 1
SET @EndRow = @PageNumber * @PageSize

SELECT
EmployeeID,
FirstName,
LastName,
Email,
DepartmentID
FROM (
SELECT
*,
ROW_NUMBER() OVER (ORDER BY LastName, FirstName) AS RowNum
FROM Employees
WHERE DepartmentID = @DepartmentID
) AS Emp
WHERE RowNum BETWEEN @StartRow AND @EndRow
ORDER BY LastName, FirstName;

— 返回总记录数
SELECT COUNT(*) AS TotalRecords
FROM Employees
WHERE DepartmentID = @DepartmentID;
END
<% ' 在ASP中调用存储过程 Function GetEmployeesByDepartmentSP(deptID, pageNumber, pageSize) Dim conn, cmd, rs, param Set conn = Server.CreateObject("ADODB.Connection") Set cmd = Server.CreateObject("ADODB.Command") Set rs = Server.CreateObject("ADODB.Recordset") conn.Open GetConnectionString() cmd.ActiveConnection = conn cmd.CommandText = "GetEmployeesByDepartment" cmd.CommandType = 4 ' adCmdStoredProc ' 添加参数 cmd.Parameters.Append cmd.CreateParameter("@DepartmentID", 3, 1, , deptID) cmd.Parameters.Append cmd.CreateParameter("@PageNumber", 3, 1, , pageNumber) cmd.Parameters.Append cmd.CreateParameter("@PageSize", 3, 1, , pageSize) ' 执行存储过程（可能返回多个结果集） Set rs = cmd.Execute ' 处理第一个结果集（员工数据） If Not rs.EOF Then Set GetEmployeesByDepartmentSP = rs Else Set GetEmployeesByDepartmentSP = Nothing End If ' 注意：调用者需要处理第二个结果集（总记录数） End Function %>
4.4 监控与诊断
4.4.1 查询执行时间监控
<% ' 查询性能监控函数 Function ExecuteWithTiming(sql, ByRef executionTime) Dim startTime, endTime, conn, rs startTime = Timer Set conn = Server.CreateObject("ADODB.Connection") Set rs = Server.CreateObject("ADODB.Recordset") conn.Open GetConnectionString() rs.Open sql, conn, 1, 1 endTime = Timer executionTime = (endTime - startTime) * 1000 ' 转换为毫秒 Set ExecuteWithTiming = rs ' 调用者需要关闭记录集和连接 End Function ' 使用示例 Dim execTime, rsPerf Set rsPerf = ExecuteWithTiming("SELECT * FROM LargeTable", execTime) Response.Write "

查询执行时间：” & FormatNumber(execTime, 2) & ” 毫秒

”

If Not rsPerf Is Nothing Then
‘ 处理数据…
rsPerf.Close
Set rsPerf = Nothing
End If
%>
4.4.2 SQL Server Profiler集成
虽然ASP无法直接调用Profiler，但可以通过查询系统视图来监控查询性能：
— 查找执行缓慢的查询
SELECT TOP 10
qs.execution_count,
qs.total_worker_time / qs.execution_count AS avg_cpu_time,
qs.total_elapsed_time / qs.execution_count AS avg_elapsed_time,
SUBSTRING(st.text, (qs.statement_start_offset/2)+1,
((CASE qs.statement_end_offset
WHEN -1 THEN DATALENGTH(st.text)
ELSE qs.statement_end_offset
END – qs.statement_start_offset)/2)+1) AS statement_text
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
ORDER BY avg_elapsed_time DESC;
五、事务处理与数据完整性
5.1 基本事务处理
<% Sub TransferFunds(fromAccount, toAccount, amount) Dim conn On Error Resume Next Set conn = Server.CreateObject("ADODB.Connection") conn.Open GetConnectionString() ' 开始事务 conn.BeginTrans ' 扣除转出账户 Dim cmd1 Set cmd1 = Server.CreateObject("ADODB.Command") cmd1.ActiveConnection = conn cmd1.CommandText = "UPDATE Accounts SET Balance = Balance - ? WHERE AccountID = ? AND Balance >= ?”
cmd1.Parameters.Append cmd1.CreateParameter(“@amount”, 6, 1, , amount) ‘ adDecimal
cmd1.Parameters.Append cmd1.CreateParameter(“@fromAccount”, 3, 1, , fromAccount)
cmd1.Parameters.Append cmd1.CreateParameter(“@minBalance”, 6, 1, , amount)

cmd1.Execute

If Err.Number <> 0 Then
conn.RollbackTrans
Response.Write “

转账失败：” & Err.Description & “

”
conn.Close
Set conn = Nothing
Exit Sub
End If

‘ 检查是否更新了记录（余额是否足够）
If cmd1.RecordsAffected = 0 Then
conn.RollbackTrans
Response.Write “

转出账户余额不足

”
conn.Close
Set conn = Nothing
Exit Sub
End If

‘ 增加转入账户
Dim cmd2
Set cmd2 = Server.CreateObject(“ADODB.Command”)
cmd2.ActiveConnection = conn
cmd2.CommandText = “UPDATE Accounts SET Balance = Balance + ? WHERE AccountID = ?”
cmd2.Parameters.Append cmd2.CreateParameter(“@amount”, 6, 1, , amount)
cmd2.Parameters.Append cmd2.CreateParameter(“@toAccount”, 3, 1, , toAccount)

cmd2.Execute

If Err.Number <> 0 Then
conn.RollbackTrans
Response.Write “

转账失败：” & Err.Description & “

”
conn.Close
Set conn = Nothing
Exit Sub
End If

‘ 记录交易日志
Dim cmd3
Set cmd3 = Server.CreateObject(“ADODB.Command”)
cmd3.ActiveConnection = conn
cmd3.CommandText = “INSERT INTO Transactions (FromAccount, ToAccount, Amount, TransactionDate) VALUES (?, ?, ?, GETDATE())”
cmd3.Parameters.Append cmd3.CreateParameter(“@from”, 3, 1, , fromAccount)
cmd3.Parameters.Append cmd3.CreateParameter(“@to”, 3, 1, , toAccount)
cmd3.Parameters.Append cmd3.CreateParameter(“@amount”, 6, 1, , amount)

cmd3.Execute

If Err.Number <> 0 Then
conn.RollbackTrans
Response.Write “

转账失败：” & Err.Description & “

”
conn.Close
Set conn = Nothing
Exit Sub
End If

‘ 提交事务
conn.CommitTrans

Response.Write “

转账成功！

”

conn.Close
Set conn = Nothing
End Sub
%>
5.2 分布式事务（MSDTC）
对于需要跨多个数据库的操作，可以使用MSDTC：
<% ' 需要启用MSDTC服务 Sub DistributedTransactionExample() Dim conn1, conn2 On Error Resume Next ' 连接到第一个数据库 Set conn1 = Server.CreateObject("ADODB.Connection") conn1.Open "Provider=SQLOLEDB;Data Source=SERVER1;Initial Catalog=DB1;Integrated Security=SSPI;" ' 连接到第二个数据库 Set conn2 = Server.CreateObject("ADODB.Connection") conn2.Open "Provider=SQLOLEDB;Data Source=SERVER2;Initial Catalog=DB2;Integrated Security=SSPI;" ' 开始分布式事务 conn1.BeginTrans conn2.BeginTrans ' 在第一个数据库执行操作 conn1.Execute "UPDATE Table1 SET Value = Value + 1 WHERE ID = 1" If Err.Number <> 0 Then
conn1.RollbackTrans
conn2.RollbackTrans
Response.Write “操作失败”
Exit Sub
End If

‘ 在第二个数据库执行操作
conn2.Execute “UPDATE Table2 SET Value = Value – 1 WHERE ID = 1”

If Err.Number <> 0 Then
conn1.RollbackTrans
conn2.RollbackTrans
Response.Write “操作失败”
Exit Sub
End If

‘ 提交事务
conn1.CommitTrans
conn2.CommitTrans

Response.Write “分布式事务成功”

conn1.Close
conn2.Close
Set conn1 = Nothing
Set conn2 = Nothing
End Sub
%>
六、安全最佳实践
6.1 防止SQL注入
<% ' 安全的用户登录验证 Function ValidateUser(username, password) Dim conn, cmd, rs, hashedPassword ' 对密码进行哈希处理（实际应用应使用更强的哈希算法） hashedPassword = HashPassword(password) Set conn = Server.CreateObject("ADODB.Connection") Set cmd = Server.CreateObject("ADODB.Command") conn.Open GetConnectionString() ' 使用参数化查询防止SQL注入 cmd.ActiveConnection = conn cmd.CommandText = "SELECT UserID, Username FROM Users WHERE Username = ? AND PasswordHash = ? AND IsActive = 1" cmd.CommandType = 1 cmd.Parameters.Append cmd.CreateParameter("@username", 200, 1, 50, username) cmd.Parameters.Append cmd.CreateParameter("@password", 200, 1, 255, hashedPassword) Set rs = cmd.Execute If Not rs.EOF Then ValidateUser = True Session("UserID") = rs("UserID") Session("Username") = rs("Username") Else ValidateUser = False End If rs.Close conn.Close Set rs = Nothing Set cmd = Nothing Set conn = Nothing End Function ' 简单的密码哈希函数（实际应用应使用bcrypt等） Function HashPassword(password) Dim i, result result = "" For i = 1 To Len(password) result = result & Hex(Asc(Mid(password, i, 1)) + 123) Next HashPassword = result End Function %>
6.2 输入验证
<% ' 输入验证函数 Function IsValidInput(input, inputType) Dim regex Select Case inputType Case "email" ' 邮箱验证 Set regex = New RegExp regex.Pattern = "^[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,}$" IsValidInput = regex.Test(input) Case "username" ' 用户名验证（只允许字母、数字、下划线） Set regex = New RegExp regex.Pattern = "^[a-zA-Z0-9_]{3,20}$" IsValidInput = regex.Test(input) Case "number" ' 数字验证 IsValidInput = IsNumeric(input) Case "date" ' 日期验证 IsValidInput = IsDate(input) Case Else IsValidInput = True End Select Set regex = Nothing End Function ' 使用示例 Dim username, email username = Request.Form("username") email = Request.Form("email") If Not IsValidInput(username, "username") Then Response.Write "用户名格式不正确" Exit Sub End If If Not IsValidInput(email, "email") Then Response.Write "邮箱格式不正确" Exit Sub End If %>
6.3 错误信息处理
<% ' 安全的错误处理 Sub SafeExecute(sql) Dim conn, cmd On Error Resume Next Set conn = Server.CreateObject("ADODB.Connection") Set cmd = Server.CreateObject("ADODB.Command") conn.Open GetConnectionString() If Err.Number <> 0 Then
‘ 记录错误日志（不显示给用户）
LogError “数据库连接失败：” & Err.Number & ” – ” & Err.Description
Response.Write “

系统繁忙，请稍后再试

”
Exit Sub
End If

cmd.ActiveConnection = conn
cmd.CommandText = sql
cmd.CommandType = 1

cmd.Execute

If Err.Number <> 0 Then
‘ 记录详细错误，但只显示通用信息给用户
LogError “SQL执行失败：” & sql & ” – 错误：” & Err.Number & ” – ” & Err.Description
Response.Write “

操作失败，请联系管理员

”
Else
Response.Write “

操作成功

”
End If

conn.Close
Set cmd = Nothing
Set conn = Nothing
End Sub

‘ 错误日志记录
Sub LogError(errorMessage)
Dim fso, file, logPath
logPath = Server.MapPath(“logs/error_” & FormatDateTime(Now, 2) & “.log”)

Set fso = Server.CreateObject(“Scripting.FileSystemObject”)
Set file = fso.OpenTextFile(logPath, 8, True) ‘ 8 = ForAppending
file.WriteLine “[” & Now & “] ” & errorMessage
file.Close

Set file = Nothing
Set fso = Nothing
End Sub
%>
七、高级主题
7.1 异步操作（模拟）
ASP本身不支持真正的异步操作，但可以通过一些技巧模拟：
<% ' 使用任务队列模拟异步处理 Sub AddToQueue(operationType, data) Dim conn, cmd Set conn = Server.CreateObject("ADODB.Connection") Set cmd = Server.CreateObject("ADODB.Command") conn.Open GetConnectionString() cmd.ActiveConnection = conn cmd.CommandText = "INSERT INTO TaskQueue (OperationType, Data, Status, CreatedAt) VALUES (?, ?, 'Pending', GETDATE())" cmd.Parameters.Append cmd.CreateParameter("@type", 200, 1, 50, operationType) cmd.Parameters.Append cmd.CreateParameter("@data", 200, 1, 1000, data) cmd.Execute conn.Close Set cmd = Nothing Set conn = Nothing End Sub ' 独立的处理程序（可由计划任务或Windows服务调用） Sub ProcessQueue() Dim conn, rs, cmd Set conn = Server.CreateObject("ADODB.Connection") Set rs = Server.CreateObject("ADODB.Recordset") conn.Open GetConnectionString() ' 获取待处理任务 rs.Open "SELECT TOP 10 * FROM TaskQueue WHERE Status = 'Pending' ORDER BY CreatedAt", conn, 1, 3 Do While Not rs.EOF On Error Resume Next Select Case rs("OperationType") Case "SendEmail" ' 调用邮件发送函数 SendEmail rs("Data") Case "GenerateReport" ' 调用报表生成函数 GenerateReport rs("Data") End Select If Err.Number = 0 Then ' 标记为已完成 Set cmd = Server.CreateObject("ADODB.Command") cmd.ActiveConnection = conn cmd.CommandText = "UPDATE TaskQueue SET Status = 'Completed', CompletedAt = GETDATE() WHERE TaskID = ?" cmd.Parameters.Append cmd.CreateParameter("@id", 3, 1, , rs("TaskID")) cmd.Execute Set cmd = Nothing Else ' 标记为失败 Set cmd = Server.CreateObject("ADODB.Command") cmd.ActiveConnection = conn cmd.CommandText = "UPDATE TaskQueue SET Status = 'Failed', ErrorMsg = ? WHERE TaskID = ?" cmd.Parameters.Append cmd.CreateParameter("@error", 200, 1, 1000, Err.Description) cmd.Parameters.Append cmd.CreateParameter("@id", 3, 1, , rs("TaskID")) cmd.Execute Set cmd = Nothing Err.Clear End If rs.MoveNext Loop rs.Close conn.Close Set rs = Nothing Set conn = Nothing End Sub %>
7.2 与.NET组件集成
<% ' 调用.NET组件（需要注册COM可见的.NET程序集） Dim netObject Set netObject = Server.CreateObject("MyNETNamespace.MyClass") If Not netObject Is Nothing Then ' 调用.NET方法处理数据 Dim result result = netObject.ProcessData(someData) ' 将结果存入数据库 Dim conn, cmd Set conn = Server.CreateObject("ADODB.Connection") Set cmd = Server.CreateObject("ADODB.Command") conn.Open GetConnectionString() cmd.ActiveConnection = conn cmd.CommandText = "INSERT INTO Results (ResultData) VALUES (?)" cmd.Parameters.Append cmd.CreateParameter("@data", 200, 1, 1000, result) cmd.Execute conn.Close Set cmd = Nothing Set conn = Nothing Set netObject = Nothing End If %>
7.3 与XML集成
<% ' 将查询结果转换为XML Function GetEmployeesXML() Dim conn, rs, xml Set conn = Server.CreateObject("ADODB.Connection") Set rs = Server.CreateObject("ADODB.Recordset") conn.Open GetConnectionString() rs.Open "SELECT EmployeeID, FirstName, LastName, Department FROM Employees", conn, 1, 1 xml = "” & vbCrLf
xml = xml & “” & vbCrLf

Do While Not rs.EOF
xml = xml & ” ” & vbCrLf
xml = xml & ” ” & rs(“EmployeeID”) & “” & vbCrLf
xml = xml & ” ” & Server.HTMLEncode(rs(“FirstName”) & ” ” & rs(“LastName”)) & “” & vbCrLf
xml = xml & ” ” & Server.HTMLEncode(rs(“Department”)) & “” & vbCrLf
xml = xml & ” ” & vbCrLf
rs.MoveNext
Loop

xml = xml & “”

rs.Close
conn.Close
Set rs = Nothing
Set conn = Nothing

GetEmployeesXML = xml
End Function

‘ 使用示例
Response.ContentType = “text/xml”
Response.Write GetEmployeesXML()
%>
八、部署与维护
8.1 连接字符串管理
<% ' 集中管理连接字符串 Function GetConnectionString() ' 方式1：从配置文件读取（推荐） ' Dim configPath, fso, file, connStr ' configPath = Server.MapPath("config/connection.config") ' Set fso = Server.CreateObject("Scripting.FileSystemObject") ' Set file = fso.OpenTextFile(configPath, 1) ' connStr = file.ReadAll ' file.Close ' GetConnectionString = connStr ' 方式2：从环境变量读取 ' GetConnectionString = Session("ConnectionString") ' 方式3：硬编码（仅用于开发） GetConnectionString = "Provider=SQLOLEDB;Data Source=YOUR_SERVER;Initial Catalog=YOUR_DB;Integrated Security=SSPI;" End Function ' 配置文件示例（connection.config） ' Provider=SQLOLEDB;Data Source=PROD_SERVER;Initial Catalog=PROD_DB;Integrated Security=SSPI; %>
8.2 健康检查
<% ' 数据库健康检查 Function CheckDatabaseHealth() Dim conn, rs, health health = "OK" On Error Resume Next Set conn = Server.CreateObject("ADODB.Connection") conn.Open GetConnectionString() If Err.Number <> 0 Then
health = “Connection Failed: ” & Err.Description
CheckDatabaseHealth = health
Exit Function
End If

‘ 测试简单查询
Set rs = Server.CreateObject(“ADODB.Recordset”)
rs.Open “SELECT GETDATE() AS CurrentTime”, conn, 1, 1

If Err.Number <> 0 Then
health = “Query Failed: ” & Err.Description
Else
If rs.EOF Then
health = “Query returned no data”
End If
End If

If Not rs Is Nothing Then rs.Close
If Not conn Is Nothing Then conn.Close
Set rs = Nothing
Set conn = Nothing

CheckDatabaseHealth = health
End Function

‘ 使用示例（可用于监控页面）
Response.Write “数据库健康状态：” & CheckDatabaseHealth()
%>
8.3 性能监控与调优
— SQL Server性能监控查询
— 查找最耗资源的查询
SELECT TOP 10
total_worker_time / execution_count AS avg_cpu_time,
total_elapsed_time / execution_count AS avg_elapsed_time,
total_logical_reads / execution_count AS avg_logical_reads,
execution_count,
SUBSTRING(st.text, (qs.statement_start_offset/2)+1,
((CASE qs.statement_end_offset
WHEN -1 THEN DATALENGTH(st.text)
ELSE qs.statement_end_offset
END – qs.statement_start_offset)/2)+1) AS statement_text
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
ORDER BY avg_elapsed_time DESC;

— 查找缺失的索引
SELECT
OBJECT_NAME(d.object_id) AS TableName,
d.equality_columns,
d.inequality_columns,
d.included_columns,
s.avg_user_impact,
s.user_seeks,
s.user_scans
FROM sys.dm_db_missing_index_details d
INNER JOIN sys.dm_db_missing_index_groups g ON d.index_handle = g.index_handle
INNER JOIN sys.dm_db_missing_index_group_stats s ON g.index_group_handle = s.group_handle
ORDER BY s.avg_user_impact DESC;
九、总结与最佳实践清单
9.1 性能优化清单
连接管理
使用连接池
及时关闭连接和记录集
使用适当的连接超时设置
查询优化
使用参数化查询
避免SELECT *，只选择需要的列
为常用查询创建索引
避免N+1查询问题
代码优化
使用缓存减少数据库访问
实现分页查询
使用存储过程
批量操作代替循环单条操作
9.2 安全清单
输入验证
所有用户输入都要验证
使用参数化查询防止SQL注入
对输出进行HTML编码
错误处理
不要向用户显示详细错误信息
记录错误日志
使用通用错误页面
权限管理
使用最小权限原则
避免使用sa账户
加密敏感配置信息
9.3 维护清单
监控
定期检查数据库性能
监控连接池使用情况
记录慢查询日志
备份
定期备份数据库
测试备份恢复流程
备份连接字符串配置
更新
保持SQL Server更新
更新数据库驱动
定期审查代码安全性
十、常见问题与解决方案
10.1 连接问题
问题：
出现”Provider cannot be found”错误
解决方案：
‘ 确保MDAC已安装，或使用更通用的连接字符串
‘ 尝试使用不同的提供程序
“Provider=MSOLEDBSQL;Server=SERVER;Database=DB;Trusted_Connection=yes;”
10.2 性能问题
问题：
页面加载缓慢
解决方案：
‘ 1. 添加连接池参数
“Min Pool Size=5;Max Pool Size=100;Pooling=true;”

‘ 2. 实现缓存
If Application(“CacheData”) <> “” Then
Set rs = Application(“CacheData”)
Else
‘ 从数据库获取并缓存
Set rs = GetFromDatabase()
Set Application(“CacheData”) = rs
End If
10.3 编码问题
问题：
中文显示乱码
解决方案：
‘ 1. 设置页面编码
<%@ Language=VBScript CodePage=65001 %>

‘ 2. 设置数据库连接编码
“Provider=SQLOLEDB;Data Source=SERVER;Initial Catalog=DB;Integrated Security=SSPI;Use Procedure for Prepare=1;Auto Translate=True;”

‘ 3. 对输出进行编码
Response.Write Server.HTMLEncode(rs(“中文字段”))
结语
ASP与SQL Server的结合虽然已有多年历史，但其稳定性和成熟度使其在许多企业应用中仍然发挥着重要作用。通过本文介绍的连接技术、CRUD操作、性能优化、安全实践等知识，您可以构建高效、安全、可维护的ASP数据库应用。
记住，技术的选择应基于实际需求。对于新项目，建议考虑更现代的技术栈如ASP.NET Core、Node.js或Python等。但对于维护现有系统或学习Web开发基础，掌握ASP与SQL Server的结合仍然具有重要价值。
关键要点总结：
始终使用参数化查询防止SQL注入
合理使用连接池和缓存提升性能
实施完善的错误处理和日志记录
定期监控和优化数据库查询
遵循安全最佳实践保护数据
通过遵循这些原则和实践，您将能够充分发挥ASP与SQL Server组合的潜力，构建出可靠的企业级Web应用。