学习目标
通过本章学习，你将掌握： - ADO.NET核心概念和基础操作 - Entity Framework Core的使用 - 数据库连接和事务管理 - LINQ to SQL查询技术 - 数据库性能优化技巧 - NoSQL数据库操作 - 数据库设计最佳实践
18.1 ADO.NET基础
核心组件介绍
using System.Data;
using System.Data.SqlClient;
using Microsoft.Data.SqlClient; // 推荐使用新版本
// ADO.NET核心组件演示
public class AdoNetBasics
{
private readonly string _connectionString;
public AdoNetBasics(string connectionString)
{
_connectionString = connectionString;
}
// 基本数据库连接
public async Task<bool> TestConnectionAsync()
{
try
{
using var connection = new SqlConnection(_connectionString);
await connection.OpenAsync();
Console.WriteLine($"数据库连接成功");
Console.WriteLine($"数据库版本: {connection.ServerVersion}");
Console.WriteLine($"连接状态: {connection.State}");
return true;
}
catch (Exception ex)
{
Console.WriteLine($"数据库连接失败: {ex.Message}");
return false;
}
}
// 执行查询操作
public async Task<List<Employee>> GetEmployeesAsync()
{
var employees = new List<Employee>();
using var connection = new SqlConnection(_connectionString);
await connection.OpenAsync();
var sql = @"
SELECT Id, FirstName, LastName, Email, Department, Salary, HireDate
FROM Employees
ORDER BY LastName, FirstName";
using var command = new SqlCommand(sql, connection);
using var reader = await command.ExecuteReaderAsync();
while (await reader.ReadAsync())
{
employees.Add(new Employee
{
Id = reader.GetInt32("Id"),
FirstName = reader.GetString("FirstName"),
LastName = reader.GetString("LastName"),
Email = reader.GetString("Email"),
Department = reader.GetString("Department"),
Salary = reader.GetDecimal("Salary"),
HireDate = reader.GetDateTime("HireDate")
});
}
return employees;
}
// 执行插入操作
public async Task<int> InsertEmployeeAsync(Employee employee)
{
using var connection = new SqlConnection(_connectionString);
await connection.OpenAsync();
var sql = @"
INSERT INTO Employees (FirstName, LastName, Email, Department, Salary, HireDate)
VALUES (@FirstName, @LastName, @Email, @Department, @Salary, @HireDate);
SELECT SCOPE_IDENTITY();";
using var command = new SqlCommand(sql, connection);
// 使用参数化查询防止SQL注入
command.Parameters.AddWithValue("@FirstName", employee.FirstName);
command.Parameters.AddWithValue("@LastName", employee.LastName);
command.Parameters.AddWithValue("@Email", employee.Email);
command.Parameters.AddWithValue("@Department", employee.Department);
command.Parameters.AddWithValue("@Salary", employee.Salary);
command.Parameters.AddWithValue("@HireDate", employee.HireDate);
var result = await command.ExecuteScalarAsync();
return Convert.ToInt32(result);
}
// 执行更新操作
public async Task<bool> UpdateEmployeeAsync(Employee employee)
{
using var connection = new SqlConnection(_connectionString);
await connection.OpenAsync();
var sql = @"
UPDATE Employees
SET FirstName = @FirstName,
LastName = @LastName,
Email = @Email,
Department = @Department,
Salary = @Salary
WHERE Id = @Id";
using var command = new SqlCommand(sql, connection);
command.Parameters.AddWithValue("@Id", employee.Id);
command.Parameters.AddWithValue("@FirstName", employee.FirstName);
command.Parameters.AddWithValue("@LastName", employee.LastName);
command.Parameters.AddWithValue("@Email", employee.Email);
command.Parameters.AddWithValue("@Department", employee.Department);
command.Parameters.AddWithValue("@Salary", employee.Salary);
var rowsAffected = await command.ExecuteNonQueryAsync();
return rowsAffected > 0;
}
// 执行删除操作
public async Task<bool> DeleteEmployeeAsync(int employeeId)
{
using var connection = new SqlConnection(_connectionString);
await connection.OpenAsync();
var sql = "DELETE FROM Employees WHERE Id = @Id";
using var command = new SqlCommand(sql, connection);
command.Parameters.AddWithValue("@Id", employeeId);
var rowsAffected = await command.ExecuteNonQueryAsync();
return rowsAffected > 0;
}
// 批量操作
public async Task<int> BulkInsertEmployeesAsync(List<Employee> employees)
{
using var connection = new SqlConnection(_connectionString);
await connection.OpenAsync();
using var transaction = connection.BeginTransaction();
try
{
var sql = @"
INSERT INTO Employees (FirstName, LastName, Email, Department, Salary, HireDate)
VALUES (@FirstName, @LastName, @Email, @Department, @Salary, @HireDate)";
var insertedCount = 0;
foreach (var employee in employees)
{
using var command = new SqlCommand(sql, connection, transaction);
command.Parameters.AddWithValue("@FirstName", employee.FirstName);
command.Parameters.AddWithValue("@LastName", employee.LastName);
command.Parameters.AddWithValue("@Email", employee.Email);
command.Parameters.AddWithValue("@Department", employee.Department);
command.Parameters.AddWithValue("@Salary", employee.Salary);
command.Parameters.AddWithValue("@HireDate", employee.HireDate);
var rowsAffected = await command.ExecuteNonQueryAsync();
if (rowsAffected > 0)
insertedCount++;
}
transaction.Commit();
return insertedCount;
}
catch (Exception ex)
{
transaction.Rollback();
Console.WriteLine($"批量插入失败: {ex.Message}");
throw;
}
}
}
// 员工数据模型
public class Employee
{
public int Id { get; set; }
public string FirstName { get; set; }
public string LastName { get; set; }
public string Email { get; set; }
public string Department { get; set; }
public decimal Salary { get; set; }
public DateTime HireDate { get; set; }
public string FullName => $"{FirstName} {LastName}";
public override string ToString()
{
return $"{FullName} - {Department} - {Salary:C}";
}
}
连接字符串管理
using Microsoft.Extensions.Configuration;
// 连接字符串管理器
public class ConnectionStringManager
{
private readonly IConfiguration _configuration;
public ConnectionStringManager(IConfiguration configuration)
{
_configuration = configuration;
}
// 获取连接字符串
public string GetConnectionString(string name = "DefaultConnection")
{
return _configuration.GetConnectionString(name)
?? throw new InvalidOperationException($"连接字符串 '{name}' 未找到");
}
// 构建连接字符串
public static string BuildConnectionString(string server, string database,
string userId = null, string password = null, bool integratedSecurity = true)
{
var builder = new SqlConnectionStringBuilder
{
DataSource = server,
InitialCatalog = database,
IntegratedSecurity = integratedSecurity,
TrustServerCertificate = true, // 开发环境使用
ConnectTimeout = 30,
CommandTimeout = 60
};
if (!integratedSecurity)
{
builder.UserID = userId;
builder.Password = password;
}
return builder.ConnectionString;
}
// 测试连接字符串
public static async Task<bool> TestConnectionStringAsync(string connectionString)
{
try
{
using var connection = new SqlConnection(connectionString);
await connection.OpenAsync();
return true;
}
catch
{
return false;
}
}
// 获取数据库信息
public static async Task<DatabaseInfo> GetDatabaseInfoAsync(string connectionString)
{
using var connection = new SqlConnection(connectionString);
await connection.OpenAsync();
var sql = @"
SELECT
DB_NAME() as DatabaseName,
@@VERSION as ServerVersion,
SERVERPROPERTY('ProductVersion') as ProductVersion,
SERVERPROPERTY('Edition') as Edition,
SERVERPROPERTY('MachineName') as MachineName";
using var command = new SqlCommand(sql, connection);
using var reader = await command.ExecuteReaderAsync();
if (await reader.ReadAsync())
{
return new DatabaseInfo
{
DatabaseName = reader.GetString("DatabaseName"),
ServerVersion = reader.GetString("ServerVersion"),
ProductVersion = reader.GetString("ProductVersion"),
Edition = reader.GetString("Edition"),
MachineName = reader.GetString("MachineName")
};
}
return null;
}
}
// 数据库信息模型
public class DatabaseInfo
{
public string DatabaseName { get; set; }
public string ServerVersion { get; set; }
public string ProductVersion { get; set; }
public string Edition { get; set; }
public string MachineName { get; set; }
public override string ToString()
{
return $"数据库: {DatabaseName}, 版本: {ProductVersion}, 服务器: {MachineName}";
}
}
18.2 Entity Framework Core
DbContext配置
using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.Logging;
// 应用程序数据库上下文
public class ApplicationDbContext : DbContext
{
public ApplicationDbContext(DbContextOptions<ApplicationDbContext> options) : base(options)
{
}
// 数据集
public DbSet<Employee> Employees { get; set; }
public DbSet<Department> Departments { get; set; }
public DbSet<Project> Projects { get; set; }
public DbSet<EmployeeProject> EmployeeProjects { get; set; }
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
base.OnModelCreating(modelBuilder);
// 配置Employee实体
modelBuilder.Entity<Employee>(entity =>
{
entity.HasKey(e => e.Id);
entity.Property(e => e.FirstName).IsRequired().HasMaxLength(50);
entity.Property(e => e.LastName).IsRequired().HasMaxLength(50);
entity.Property(e => e.Email).IsRequired().HasMaxLength(100);
entity.HasIndex(e => e.Email).IsUnique();
entity.Property(e => e.Salary).HasColumnType("decimal(18,2)");
// 配置与Department的关系
entity.HasOne(e => e.Department)
.WithMany(d => d.Employees)
.HasForeignKey(e => e.DepartmentId)
.OnDelete(DeleteBehavior.Restrict);
});
// 配置Department实体
modelBuilder.Entity<Department>(entity =>
{
entity.HasKey(d => d.Id);
entity.Property(d => d.Name).IsRequired().HasMaxLength(100);
entity.Property(d => d.Description).HasMaxLength(500);
entity.HasIndex(d => d.Name).IsUnique();
});
// 配置Project实体
modelBuilder.Entity<Project>(entity =>
{
entity.HasKey(p => p.Id);
entity.Property(p => p.Name).IsRequired().HasMaxLength(200);
entity.Property(p => p.Description).HasMaxLength(1000);
entity.Property(p => p.Budget).HasColumnType("decimal(18,2)");
});
// 配置多对多关系
modelBuilder.Entity<EmployeeProject>(entity =>
{
entity.HasKey(ep => new { ep.EmployeeId, ep.ProjectId });
entity.HasOne(ep => ep.Employee)
.WithMany(e => e.EmployeeProjects)
.HasForeignKey(ep => ep.EmployeeId);
entity.HasOne(ep => ep.Project)
.WithMany(p => p.EmployeeProjects)
.HasForeignKey(ep => ep.ProjectId);
entity.Property(ep => ep.Role).HasMaxLength(100);
entity.Property(ep => ep.HoursAllocated).HasDefaultValue(0);
});
// 种子数据
SeedData(modelBuilder);
}
private void SeedData(ModelBuilder modelBuilder)
{
// 部门种子数据
modelBuilder.Entity<Department>().HasData(
new Department { Id = 1, Name = "技术部", Description = "负责软件开发和技术支持" },
new Department { Id = 2, Name = "销售部", Description = "负责产品销售和客户关系" },
new Department { Id = 3, Name = "人事部", Description = "负责人力资源管理" },
new Department { Id = 4, Name = "财务部", Description = "负责财务管理和会计" }
);
// 员工种子数据
modelBuilder.Entity<Employee>().HasData(
new Employee
{
Id = 1,
FirstName = "张",
LastName = "三",
Email = "zhang.san@company.com",
DepartmentId = 1,
Salary = 8000,
HireDate = new DateTime(2020, 1, 15)
},
new Employee
{
Id = 2,
FirstName = "李",
LastName = "四",
Email = "li.si@company.com",
DepartmentId = 1,
Salary = 9500,
HireDate = new DateTime(2019, 6, 10)
},
new Employee
{
Id = 3,
FirstName = "王",
LastName = "五",
Email = "wang.wu@company.com",
DepartmentId = 2,
Salary = 7500,
HireDate = new DateTime(2021, 3, 20)
}
);
// 项目种子数据
modelBuilder.Entity<Project>().HasData(
new Project
{
Id = 1,
Name = "电商平台开发",
Description = "开发新的电商平台系统",
Budget = 500000,
StartDate = new DateTime(2023, 1, 1),
EndDate = new DateTime(2023, 12, 31)
},
new Project
{
Id = 2,
Name = "移动应用开发",
Description = "开发配套的移动应用",
Budget = 300000,
StartDate = new DateTime(2023, 6, 1),
EndDate = new DateTime(2024, 3, 31)
}
);
}
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
if (!optionsBuilder.IsConfigured)
{
// 开发环境配置
optionsBuilder.UseSqlServer("Server=localhost;Database=CompanyDB;Trusted_Connection=true;TrustServerCertificate=true;")
.LogTo(Console.WriteLine, LogLevel.Information)
.EnableSensitiveDataLogging()
.EnableDetailedErrors();
}
}
}
// 更新的实体模型
public class Employee
{
public int Id { get; set; }
public string FirstName { get; set; }
public string LastName { get; set; }
public string Email { get; set; }
public decimal Salary { get; set; }
public DateTime HireDate { get; set; }
// 外键
public int DepartmentId { get; set; }
// 导航属性
public Department Department { get; set; }
public List<EmployeeProject> EmployeeProjects { get; set; } = new();
public string FullName => $"{FirstName} {LastName}";
}
public class Department
{
public int Id { get; set; }
public string Name { get; set; }
public string Description { get; set; }
// 导航属性
public List<Employee> Employees { get; set; } = new();
}
public class Project
{
public int Id { get; set; }
public string Name { get; set; }
public string Description { get; set; }
public decimal Budget { get; set; }
public DateTime StartDate { get; set; }
public DateTime? EndDate { get; set; }
// 导航属性
public List<EmployeeProject> EmployeeProjects { get; set; } = new();
}
public class EmployeeProject
{
public int EmployeeId { get; set; }
public int ProjectId { get; set; }
public string Role { get; set; }
public int HoursAllocated { get; set; }
public DateTime AssignedDate { get; set; }
// 导航属性
public Employee Employee { get; set; }
public Project Project { get; set; }
}
Entity Framework Core操作
// EF Core数据访问服务
public class EmployeeService
{
private readonly ApplicationDbContext _context;
private readonly ILogger<EmployeeService> _logger;
public EmployeeService(ApplicationDbContext context, ILogger<EmployeeService> logger)
{
_context = context;
_logger = logger;
}
// 获取所有员工（包含部门信息）
public async Task<List<Employee>> GetAllEmployeesAsync()
{
return await _context.Employees
.Include(e => e.Department)
.OrderBy(e => e.LastName)
.ThenBy(e => e.FirstName)
.ToListAsync();
}
// 根据ID获取员工
public async Task<Employee> GetEmployeeByIdAsync(int id)
{
return await _context.Employees
.Include(e => e.Department)
.Include(e => e.EmployeeProjects)
.ThenInclude(ep => ep.Project)
.FirstOrDefaultAsync(e => e.Id == id);
}
// 根据部门获取员工
public async Task<List<Employee>> GetEmployeesByDepartmentAsync(int departmentId)
{
return await _context.Employees
.Include(e => e.Department)
.Where(e => e.DepartmentId == departmentId)
.ToListAsync();
}
// 搜索员工
public async Task<List<Employee>> SearchEmployeesAsync(string searchTerm)
{
if (string.IsNullOrWhiteSpace(searchTerm))
return await GetAllEmployeesAsync();
searchTerm = searchTerm.ToLower();
return await _context.Employees
.Include(e => e.Department)
.Where(e => e.FirstName.ToLower().Contains(searchTerm) ||
e.LastName.ToLower().Contains(searchTerm) ||
e.Email.ToLower().Contains(searchTerm) ||
e.Department.Name.ToLower().Contains(searchTerm))
.ToListAsync();
}
// 添加员工
public async Task<Employee> AddEmployeeAsync(Employee employee)
{
try
{
_context.Employees.Add(employee);
await _context.SaveChangesAsync();
_logger.LogInformation($"员工 {employee.FullName} 添加成功，ID: {employee.Id}");
return employee;
}
catch (Exception ex)
{
_logger.LogError(ex, $"添加员工 {employee.FullName} 失败");
throw;
}
}
// 更新员工
public async Task<bool> UpdateEmployeeAsync(Employee employee)
{
try
{
_context.Employees.Update(employee);
var result = await _context.SaveChangesAsync();
_logger.LogInformation($"员工 {employee.FullName} 更新成功");
return result > 0;
}
catch (Exception ex)
{
_logger.LogError(ex, $"更新员工 {employee.FullName} 失败");
throw;
}
}
// 删除员工
public async Task<bool> DeleteEmployeeAsync(int id)
{
try
{
var employee = await _context.Employees.FindAsync(id);
if (employee == null)
return false;
_context.Employees.Remove(employee);
var result = await _context.SaveChangesAsync();
_logger.LogInformation($"员工 {employee.FullName} 删除成功");
return result > 0;
}
catch (Exception ex)
{
_logger.LogError(ex, $"删除员工 ID:{id} 失败");
throw;
}
}
// 批量添加员工
public async Task<int> BulkAddEmployeesAsync(List<Employee> employees)
{
using var transaction = await _context.Database.BeginTransactionAsync();
try
{
_context.Employees.AddRange(employees);
var result = await _context.SaveChangesAsync();
await transaction.CommitAsync();
_logger.LogInformation($"批量添加 {result} 个员工成功");
return result;
}
catch (Exception ex)
{
await transaction.RollbackAsync();
_logger.LogError(ex, "批量添加员工失败");
throw;
}
}
// 获取员工统计信息
public async Task<EmployeeStatistics> GetEmployeeStatisticsAsync()
{
var stats = await _context.Employees
.GroupBy(e => e.DepartmentId)
.Select(g => new DepartmentStatistic
{
DepartmentId = g.Key,
DepartmentName = g.First().Department.Name,
EmployeeCount = g.Count(),
AverageSalary = g.Average(e => e.Salary),
TotalSalary = g.Sum(e => e.Salary)
})
.ToListAsync();
return new EmployeeStatistics
{
TotalEmployees = await _context.Employees.CountAsync(),
AverageSalary = await _context.Employees.AverageAsync(e => e.Salary),
DepartmentStatistics = stats
};
}
}
// 统计信息模型
public class EmployeeStatistics
{
public int TotalEmployees { get; set; }
public decimal AverageSalary { get; set; }
public List<DepartmentStatistic> DepartmentStatistics { get; set; } = new();
}
public class DepartmentStatistic
{
public int DepartmentId { get; set; }
public string DepartmentName { get; set; }
public int EmployeeCount { get; set; }
public decimal AverageSalary { get; set; }
public decimal TotalSalary { get; set; }
}
## 18.3 事务管理
### 数据库事务基础
```csharp
// 事务管理服务
public class TransactionService
{
private readonly ApplicationDbContext _context;
private readonly ILogger<TransactionService> _logger;
public TransactionService(ApplicationDbContext context, ILogger<TransactionService> logger)
{
_context = context;
_logger = logger;
}
// 基本事务操作
public async Task<bool> TransferEmployeeAsync(int employeeId, int newDepartmentId, decimal salaryAdjustment)
{
using var transaction = await _context.Database.BeginTransactionAsync();
try
{
// 1. 获取员工信息
var employee = await _context.Employees
.Include(e => e.Department)
.FirstOrDefaultAsync(e => e.Id == employeeId);
if (employee == null)
{
_logger.LogWarning($"员工 ID:{employeeId} 不存在");
return false;
}
var oldDepartmentId = employee.DepartmentId;
var oldSalary = employee.Salary;
// 2. 更新员工部门和薪资
employee.DepartmentId = newDepartmentId;
employee.Salary += salaryAdjustment;
// 3. 记录转移历史
var transferHistory = new EmployeeTransferHistory
{
EmployeeId = employeeId,
FromDepartmentId = oldDepartmentId,
ToDepartmentId = newDepartmentId,
OldSalary = oldSalary,
NewSalary = employee.Salary,
TransferDate = DateTime.UtcNow,
Reason = "部门调动"
};
_context.EmployeeTransferHistories.Add(transferHistory);
// 4. 保存所有更改
await _context.SaveChangesAsync();
// 5. 提交事务
await transaction.CommitAsync();
_logger.LogInformation($"员工 {employee.FullName} 成功从部门 {oldDepartmentId} 转移到部门 {newDepartmentId}");
return true;
}
catch (Exception ex)
{
// 回滚事务
await transaction.RollbackAsync();
_logger.LogError(ex, $"员工转移失败，事务已回滚");
return false;
}
}
// 复杂事务操作：项目分配
public async Task<bool> AssignEmployeesToProjectAsync(int projectId, List<EmployeeProjectAssignment> assignments)
{
using var transaction = await _context.Database.BeginTransactionAsync();
try
{
// 1. 验证项目存在
var project = await _context.Projects.FindAsync(projectId);
if (project == null)
{
_logger.LogWarning($"项目 ID:{projectId} 不存在");
return false;
}
// 2. 验证所有员工存在
var employeeIds = assignments.Select(a => a.EmployeeId).ToList();
var existingEmployees = await _context.Employees
.Where(e => employeeIds.Contains(e.Id))
.Select(e => e.Id)
.ToListAsync();
var missingEmployees = employeeIds.Except(existingEmployees).ToList();
if (missingEmployees.Any())
{
_logger.LogWarning($"员工 ID:{string.Join(",", missingEmployees)} 不存在");
return false;
}
// 3. 检查员工是否已分配到该项目
var existingAssignments = await _context.EmployeeProjects
.Where(ep => ep.ProjectId == projectId && employeeIds.Contains(ep.EmployeeId))
.Select(ep => ep.EmployeeId)
.ToListAsync();
if (existingAssignments.Any())
{
_logger.LogWarning($"员工 ID:{string.Join(",", existingAssignments)} 已分配到项目 {projectId}");
return false;
}
// 4. 创建项目分配记录
var employeeProjects = assignments.Select(a => new EmployeeProject
{
EmployeeId = a.EmployeeId,
ProjectId = projectId,
Role = a.Role,
HoursAllocated = a.HoursAllocated,
AssignedDate = DateTime.UtcNow
}).ToList();
_context.EmployeeProjects.AddRange(employeeProjects);
// 5. 更新项目状态
project.Status = "进行中";
// 6. 保存更改
await _context.SaveChangesAsync();
// 7. 提交事务
await transaction.CommitAsync();
_logger.LogInformation($"成功将 {assignments.Count} 个员工分配到项目 {project.Name}");
return true;
}
catch (Exception ex)
{
await transaction.RollbackAsync();
_logger.LogError(ex, $"项目分配失败，事务已回滚");
return false;
}
}
// 分布式事务示例
public async Task<bool> ProcessPayrollAsync(int departmentId, decimal bonusPercentage)
{
using var transaction = await _context.Database.BeginTransactionAsync();
try
{
// 1. 获取部门员工
var employees = await _context.Employees
.Where(e => e.DepartmentId == departmentId)
.ToListAsync();
if (!employees.Any())
{
_logger.LogWarning($"部门 ID:{departmentId} 没有员工");
return false;
}
var totalBonus = 0m;
var payrollRecords = new List<PayrollRecord>();
// 2. 计算每个员工的奖金
foreach (var employee in employees)
{
var bonus = employee.Salary * (bonusPercentage / 100);
totalBonus += bonus;
payrollRecords.Add(new PayrollRecord
{
EmployeeId = employee.Id,
BaseSalary = employee.Salary,
Bonus = bonus,
TotalPay = employee.Salary + bonus,
PayPeriod = DateTime.UtcNow.ToString("yyyy-MM"),
ProcessedDate = DateTime.UtcNow
});
}
// 3. 检查部门预算
var department = await _context.Departments.FindAsync(departmentId);
if (department.Budget < totalBonus)
{
_logger.LogWarning($"部门预算不足，需要 {totalBonus:C}，可用 {department.Budget:C}");
return false;
}
// 4. 扣除部门预算
department.Budget -= totalBonus;
// 5. 添加工资记录
_context.PayrollRecords.AddRange(payrollRecords);
// 6. 保存更改
await _context.SaveChangesAsync();
// 7. 提交事务
await transaction.CommitAsync();
_logger.LogInformation($"部门 {department.Name} 工资处理完成，总奖金: {totalBonus:C}");
return true;
}
catch (Exception ex)
{
await transaction.RollbackAsync();
_logger.LogError(ex, "工资处理失败，事务已回滚");
return false;
}
}
}
// 支持事务的数据模型
public class EmployeeTransferHistory
{
public int Id { get; set; }
public int EmployeeId { get; set; }
public int FromDepartmentId { get; set; }
public int ToDepartmentId { get; set; }
public decimal OldSalary { get; set; }
public decimal NewSalary { get; set; }
public DateTime TransferDate { get; set; }
public string Reason { get; set; }
// 导航属性
public Employee Employee { get; set; }
public Department FromDepartment { get; set; }
public Department ToDepartment { get; set; }
}
public class PayrollRecord
{
public int Id { get; set; }
public int EmployeeId { get; set; }
public decimal BaseSalary { get; set; }
public decimal Bonus { get; set; }
public decimal TotalPay { get; set; }
public string PayPeriod { get; set; }
public DateTime ProcessedDate { get; set; }
// 导航属性
public Employee Employee { get; set; }
}
public class EmployeeProjectAssignment
{
public int EmployeeId { get; set; }
public string Role { get; set; }
public int HoursAllocated { get; set; }
}
// 更新Department模型以支持预算
public partial class Department
{
public decimal Budget { get; set; }
public string Status { get; set; } = "活跃";
}
// 更新Project模型以支持状态
public partial class Project
{
public string Status { get; set; } = "计划中";
}
18.4 LINQ查询技术
复杂LINQ查询
// LINQ查询服务
public class QueryService
{
private readonly ApplicationDbContext _context;
public QueryService(ApplicationDbContext context)
{
_context = context;
}
// 基础查询
public async Task<List<Employee>> GetHighSalaryEmployeesAsync(decimal minSalary)
{
return await _context.Employees
.Include(e => e.Department)
.Where(e => e.Salary >= minSalary)
.OrderByDescending(e => e.Salary)
.ToListAsync();
}
// 分组查询
public async Task<List<DepartmentSalaryReport>> GetDepartmentSalaryReportAsync()
{
return await _context.Employees
.Include(e => e.Department)
.GroupBy(e => new { e.DepartmentId, e.Department.Name })
.Select(g => new DepartmentSalaryReport
{
DepartmentId = g.Key.DepartmentId,
DepartmentName = g.Key.Name,
EmployeeCount = g.Count(),
MinSalary = g.Min(e => e.Salary),
MaxSalary = g.Max(e => e.Salary),
AverageSalary = g.Average(e => e.Salary),
TotalSalary = g.Sum(e => e.Salary)
})
.OrderByDescending(r => r.AverageSalary)
.ToListAsync();
}
// 连接查询
public async Task<List<EmployeeProjectInfo>> GetEmployeeProjectInfoAsync()
{
return await (from e in _context.Employees
join ep in _context.EmployeeProjects on e.Id equals ep.EmployeeId
join p in _context.Projects on ep.ProjectId equals p.Id
join d in _context.Departments on e.DepartmentId equals d.Id
select new EmployeeProjectInfo
{
EmployeeId = e.Id,
EmployeeName = e.FirstName + " " + e.LastName,
DepartmentName = d.Name,
ProjectName = p.Name,
Role = ep.Role,
HoursAllocated = ep.HoursAllocated,
ProjectBudget = p.Budget
})
.OrderBy(info => info.EmployeeName)
.ThenBy(info => info.ProjectName)
.ToListAsync();
}
// 子查询
public async Task<List<Employee>> GetEmployeesWithMostProjectsAsync()
{
// 找出参与项目最多的员工
var maxProjectCount = await _context.EmployeeProjects
.GroupBy(ep => ep.EmployeeId)
.Select(g => g.Count())
.MaxAsync();
var employeeIdsWithMostProjects = await _context.EmployeeProjects
.GroupBy(ep => ep.EmployeeId)
.Where(g => g.Count() == maxProjectCount)
.Select(g => g.Key)
.ToListAsync();
return await _context.Employees
.Include(e => e.Department)
.Include(e => e.EmployeeProjects)
.ThenInclude(ep => ep.Project)
.Where(e => employeeIdsWithMostProjects.Contains(e.Id))
.ToListAsync();
}
// 条件查询
public async Task<List<Employee>> SearchEmployeesAdvancedAsync(EmployeeSearchCriteria criteria)
{
var query = _context.Employees
.Include(e => e.Department)
.AsQueryable();
// 动态构建查询条件
if (!string.IsNullOrWhiteSpace(criteria.Name))
{
var name = criteria.Name.ToLower();
query = query.Where(e => e.FirstName.ToLower().Contains(name) ||
e.LastName.ToLower().Contains(name));
}
if (!string.IsNullOrWhiteSpace(criteria.Email))
{
query = query.Where(e => e.Email.ToLower().Contains(criteria.Email.ToLower()));
}
if (criteria.DepartmentId.HasValue)
{
query = query.Where(e => e.DepartmentId == criteria.DepartmentId.Value);
}
if (criteria.MinSalary.HasValue)
{
query = query.Where(e => e.Salary >= criteria.MinSalary.Value);
}
if (criteria.MaxSalary.HasValue)
{
query = query.Where(e => e.Salary <= criteria.MaxSalary.Value);
}
if (criteria.HireDateFrom.HasValue)
{
query = query.Where(e => e.HireDate >= criteria.HireDateFrom.Value);
}
if (criteria.HireDateTo.HasValue)
{
query = query.Where(e => e.HireDate <= criteria.HireDateTo.Value);
}
// 排序
query = criteria.SortBy?.ToLower() switch
{
"name" => criteria.SortDescending ?
query.OrderByDescending(e => e.LastName).ThenByDescending(e => e.FirstName) :
query.OrderBy(e => e.LastName).ThenBy(e => e.FirstName),
"salary" => criteria.SortDescending ?
query.OrderByDescending(e => e.Salary) :
query.OrderBy(e => e.Salary),
"hiredate" => criteria.SortDescending ?
query.OrderByDescending(e => e.HireDate) :
query.OrderBy(e => e.HireDate),
"department" => criteria.SortDescending ?
query.OrderByDescending(e => e.Department.Name) :
query.OrderBy(e => e.Department.Name),
_ => query.OrderBy(e => e.LastName).ThenBy(e => e.FirstName)
};
// 分页
if (criteria.PageSize > 0)
{
query = query.Skip((criteria.PageNumber - 1) * criteria.PageSize)
.Take(criteria.PageSize);
}
return await query.ToListAsync();
}
// 聚合查询
public async Task<CompanyStatistics> GetCompanyStatisticsAsync()
{
var totalEmployees = await _context.Employees.CountAsync();
var totalDepartments = await _context.Departments.CountAsync();
var totalProjects = await _context.Projects.CountAsync();
var salaryStats = await _context.Employees
.GroupBy(e => 1)
.Select(g => new
{
MinSalary = g.Min(e => e.Salary),
MaxSalary = g.Max(e => e.Salary),
AverageSalary = g.Average(e => e.Salary),
TotalSalary = g.Sum(e => e.Salary)
})
.FirstOrDefaultAsync();
var departmentDistribution = await _context.Employees
.GroupBy(e => e.Department.Name)
.Select(g => new DepartmentDistribution
{
DepartmentName = g.Key,
EmployeeCount = g.Count(),
Percentage = (double)g.Count() / totalEmployees * 100
})
.OrderByDescending(d => d.EmployeeCount)
.ToListAsync();
var projectStats = await _context.Projects
.GroupBy(p => 1)
.Select(g => new
{
TotalBudget = g.Sum(p => p.Budget),
AverageBudget = g.Average(p => p.Budget),
ActiveProjects = g.Count(p => p.Status == "进行中")
})
.FirstOrDefaultAsync();
return new CompanyStatistics
{
TotalEmployees = totalEmployees,
TotalDepartments = totalDepartments,
TotalProjects = totalProjects,
MinSalary = salaryStats?.MinSalary ?? 0,
MaxSalary = salaryStats?.MaxSalary ?? 0,
AverageSalary = salaryStats?.AverageSalary ?? 0,
TotalSalaryExpense = salaryStats?.TotalSalary ?? 0,
TotalProjectBudget = projectStats?.TotalBudget ?? 0,
AverageProjectBudget = projectStats?.AverageBudget ?? 0,
ActiveProjects = projectStats?.ActiveProjects ?? 0,
DepartmentDistribution = departmentDistribution
};
}
// 原生SQL查询
public async Task<List<EmployeeSalaryRank>> GetEmployeeSalaryRankingAsync()
{
var sql = @"
SELECT
e.Id,
e.FirstName + ' ' + e.LastName as FullName,
d.Name as DepartmentName,
e.Salary,
RANK() OVER (ORDER BY e.Salary DESC) as OverallRank,
RANK() OVER (PARTITION BY e.DepartmentId ORDER BY e.Salary DESC) as DepartmentRank
FROM Employees e
INNER JOIN Departments d ON e.DepartmentId = d.Id
ORDER BY e.Salary DESC";
return await _context.Database
.SqlQueryRaw<EmployeeSalaryRank>(sql)
.ToListAsync();
}
}
// 查询相关的数据模型
public class DepartmentSalaryReport
{
public int DepartmentId { get; set; }
public string DepartmentName { get; set; }
public int EmployeeCount { get; set; }
public decimal MinSalary { get; set; }
public decimal MaxSalary { get; set; }
public decimal AverageSalary { get; set; }
public decimal TotalSalary { get; set; }
}
public class EmployeeProjectInfo
{
public int EmployeeId { get; set; }
public string EmployeeName { get; set; }
public string DepartmentName { get; set; }
public string ProjectName { get; set; }
public string Role { get; set; }
public int HoursAllocated { get; set; }
public decimal ProjectBudget { get; set; }
}
public class EmployeeSearchCriteria
{
public string Name { get; set; }
public string Email { get; set; }
public int? DepartmentId { get; set; }
public decimal? MinSalary { get; set; }
public decimal? MaxSalary { get; set; }
public DateTime? HireDateFrom { get; set; }
public DateTime? HireDateTo { get; set; }
public string SortBy { get; set; } = "name";
public bool SortDescending { get; set; }
public int PageNumber { get; set; } = 1;
public int PageSize { get; set; } = 10;
}
public class CompanyStatistics
{
public int TotalEmployees { get; set; }
public int TotalDepartments { get; set; }
public int TotalProjects { get; set; }
public decimal MinSalary { get; set; }
public decimal MaxSalary { get; set; }
public decimal AverageSalary { get; set; }
public decimal TotalSalaryExpense { get; set; }
public decimal TotalProjectBudget { get; set; }
public decimal AverageProjectBudget { get; set; }
public int ActiveProjects { get; set; }
public List<DepartmentDistribution> DepartmentDistribution { get; set; } = new();
}
public class DepartmentDistribution
{
public string DepartmentName { get; set; }
public int EmployeeCount { get; set; }
public double Percentage { get; set; }
}
public class EmployeeSalaryRank
{
public int Id { get; set; }
public string FullName { get; set; }
public string DepartmentName { get; set; }
public decimal Salary { get; set; }
public int OverallRank { get; set; }
public int DepartmentRank { get; set; }
}
18.5 性能优化和最佳实践
数据库性能优化
// 性能优化服务
public class PerformanceOptimizationService
{
private readonly ApplicationDbContext _context;
private readonly ILogger<PerformanceOptimizationService> _logger;
private readonly IMemoryCache _cache;
public PerformanceOptimizationService(
ApplicationDbContext context,
ILogger<PerformanceOptimizationService> logger,
IMemoryCache cache)
{
_context = context;
_logger = logger;
_cache = cache;
}
// 查询优化：使用投影减少数据传输
public async Task<List<EmployeeSummary>> GetEmployeeSummariesAsync()
{
return await _context.Employees
.Select(e => new EmployeeSummary
{
Id = e.Id,
FullName = e.FirstName + " " + e.LastName,
DepartmentName = e.Department.Name,
Salary = e.Salary
})
.ToListAsync();
}
// 批量操作优化
public async Task<int> BulkUpdateSalariesAsync(List<SalaryUpdate> updates)
{
var employeeIds = updates.Select(u => u.EmployeeId).ToList();
var employees = await _context.Employees
.Where(e => employeeIds.Contains(e.Id))
.ToListAsync();
var updateDict = updates.ToDictionary(u => u.EmployeeId, u => u.NewSalary);
foreach (var employee in employees)
{
if (updateDict.TryGetValue(employee.Id, out var newSalary))
{
employee.Salary = newSalary;
}
}
return await _context.SaveChangesAsync();
}
// 分页查询优化
public async Task<PagedResult<Employee>> GetEmployeesPagedAsync(
int pageNumber,
int pageSize,
string searchTerm = null)
{
var query = _context.Employees
.Include(e => e.Department)
.AsQueryable();
if (!string.IsNullOrWhiteSpace(searchTerm))
{
var term = searchTerm.ToLower();
query = query.Where(e =>
e.FirstName.ToLower().Contains(term) ||
e.LastName.ToLower().Contains(term) ||
e.Email.ToLower().Contains(term));
}
var totalCount = await query.CountAsync();
var employees = await query
.OrderBy(e => e.LastName)
.ThenBy(e => e.FirstName)
.Skip((pageNumber - 1) * pageSize)
.Take(pageSize)
.ToListAsync();
return new PagedResult<Employee>
{
Items = employees,
TotalCount = totalCount,
PageNumber = pageNumber,
PageSize = pageSize,
TotalPages = (int)Math.Ceiling((double)totalCount / pageSize)
};
}
// 缓存优化
public async Task<List<Department>> GetDepartmentsCachedAsync()
{
const string cacheKey = "departments_all";
if (_cache.TryGetValue(cacheKey, out List<Department> cachedDepartments))
{
_logger.LogInformation("从缓存获取部门数据");
return cachedDepartments;
}
var departments = await _context.Departments
.OrderBy(d => d.Name)
.ToListAsync();
var cacheOptions = new MemoryCacheEntryOptions
{
AbsoluteExpirationRelativeToNow = TimeSpan.FromMinutes(30),
SlidingExpiration = TimeSpan.FromMinutes(5),
Priority = CacheItemPriority.Normal
};
_cache.Set(cacheKey, departments, cacheOptions);
_logger.LogInformation("部门数据已缓存");
return departments;
}
// 异步流处理大量数据
public async IAsyncEnumerable<Employee> GetAllEmployeesStreamAsync(
[EnumeratorCancellation] CancellationToken cancellationToken = default)
{
const int batchSize = 1000;
var skip = 0;
while (true)
{
var batch = await _context.Employees
.Include(e => e.Department)
.OrderBy(e => e.Id)
.Skip(skip)
.Take(batchSize)
.ToListAsync(cancellationToken);
if (!batch.Any())
break;
foreach (var employee in batch)
{
yield return employee;
}
skip += batchSize;
}
}
// 连接池优化示例
public async Task<DatabaseConnectionInfo> GetConnectionInfoAsync()
{
var connectionString = _context.Database.GetConnectionString();
var canConnect = await _context.Database.CanConnectAsync();
// 获取连接池统计信息（需要特定的数据库提供程序支持）
var poolInfo = new DatabaseConnectionInfo
{
ConnectionString = connectionString,
CanConnect = canConnect,
DatabaseName = _context.Database.GetDbConnection().Database,
ServerVersion = _context.Database.GetDbConnection().ServerVersion,
ConnectionTimeout = _context.Database.GetDbConnection().ConnectionTimeout
};
return poolInfo;
}
// 查询执行计划分析
public async Task<string> AnalyzeQueryPlanAsync(string sql)
{
try
{
// 获取查询执行计划（SQL Server示例）
var planQuery = $"SET SHOWPLAN_ALL ON; {sql}; SET SHOWPLAN_ALL OFF;";
using var command = _context.Database.GetDbConnection().CreateCommand();
command.CommandText = planQuery;
await _context.Database.OpenConnectionAsync();
using var reader = await command.ExecuteReaderAsync();
var plan = new StringBuilder();
while (await reader.ReadAsync())
{
for (int i = 0; i < reader.FieldCount; i++)
{
plan.AppendLine($"{reader.GetName(i)}: {reader.GetValue(i)}");
}
plan.AppendLine("---");
}
return plan.ToString();
}
catch (Exception ex)
{
_logger.LogError(ex, "查询计划分析失败");
return $"分析失败: {ex.Message}";
}
finally
{
await _context.Database.CloseConnectionAsync();
}
}
}
// 数据库监控服务
public class DatabaseMonitoringService
{
private readonly ApplicationDbContext _context;
private readonly ILogger<DatabaseMonitoringService> _logger;
public DatabaseMonitoringService(
ApplicationDbContext context,
ILogger<DatabaseMonitoringService> logger)
{
_context = context;
_logger = logger;
}
// 性能监控
public async Task<DatabasePerformanceMetrics> GetPerformanceMetricsAsync()
{
var stopwatch = Stopwatch.StartNew();
// 测试简单查询性能
var simpleQueryTime = await MeasureQueryTimeAsync(() =>
_context.Employees.CountAsync());
// 测试复杂查询性能
var complexQueryTime = await MeasureQueryTimeAsync(() =>
_context.Employees
.Include(e => e.Department)
.Include(e => e.EmployeeProjects)
.ThenInclude(ep => ep.Project)
.ToListAsync());
// 测试写入性能
var writeTime = await MeasureWriteTimeAsync();
stopwatch.Stop();
return new DatabasePerformanceMetrics
{
SimpleQueryTime = simpleQueryTime,
ComplexQueryTime = complexQueryTime,
WriteTime = writeTime,
TotalTestTime = stopwatch.ElapsedMilliseconds,
Timestamp = DateTime.UtcNow
};
}
private async Task<long> MeasureQueryTimeAsync(Func<Task> queryAction)
{
var stopwatch = Stopwatch.StartNew();
await queryAction();
stopwatch.Stop();
return stopwatch.ElapsedMilliseconds;
}
private async Task<long> MeasureWriteTimeAsync()
{
var stopwatch = Stopwatch.StartNew();
// 创建测试数据
var testEmployee = new Employee
{
FirstName = "Test",
LastName = "Performance",
Email = $"test.performance.{Guid.NewGuid()}@example.com",
DepartmentId = 1,
Salary = 50000,
HireDate = DateTime.UtcNow
};
_context.Employees.Add(testEmployee);
await _context.SaveChangesAsync();
// 清理测试数据
_context.Employees.Remove(testEmployee);
await _context.SaveChangesAsync();
stopwatch.Stop();
return stopwatch.ElapsedMilliseconds;
}
// 数据库健康检查
public async Task<DatabaseHealthStatus> CheckDatabaseHealthAsync()
{
var health = new DatabaseHealthStatus
{
CheckTime = DateTime.UtcNow
};
try
{
// 检查连接
health.CanConnect = await _context.Database.CanConnectAsync();
if (health.CanConnect)
{
// 检查表是否存在
var tableCount = await _context.Database
.SqlQueryRaw<int>("SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE'")
.FirstOrDefaultAsync();
health.TablesExist = tableCount > 0;
// 检查数据完整性
var employeeCount = await _context.Employees.CountAsync();
var departmentCount = await _context.Departments.CountAsync();
health.DataIntegrityCheck = employeeCount >= 0 && departmentCount >= 0;
// 检查索引
health.IndexesOptimal = await CheckIndexOptimizationAsync();
health.IsHealthy = health.TablesExist && health.DataIntegrityCheck;
}
}
catch (Exception ex)
{
health.IsHealthy = false;
health.ErrorMessage = ex.Message;
_logger.LogError(ex, "数据库健康检查失败");
}
return health;
}
private async Task<bool> CheckIndexOptimizationAsync()
{
try
{
// 检查索引碎片化（SQL Server示例）
var fragmentationQuery = @"
SELECT AVG(avg_fragmentation_in_percent) as AvgFragmentation
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'LIMITED')
WHERE index_id > 0";
var avgFragmentation = await _context.Database
.SqlQueryRaw<double>(fragmentationQuery)
.FirstOrDefaultAsync();
// 如果平均碎片化小于30%，认为索引状态良好
return avgFragmentation < 30.0;
}
catch
{
// 如果无法检查索引状态，返回true避免误报
return true;
}
}
}
// 性能相关的数据模型
public class EmployeeSummary
{
public int Id { get; set; }
public string FullName { get; set; }
public string DepartmentName { get; set; }
public decimal Salary { get; set; }
}
public class SalaryUpdate
{
public int EmployeeId { get; set; }
public decimal NewSalary { get; set; }
}
public class PagedResult<T>
{
public List<T> Items { get; set; } = new();
public int TotalCount { get; set; }
public int PageNumber { get; set; }
public int PageSize { get; set; }
public int TotalPages { get; set; }
public bool HasPreviousPage => PageNumber > 1;
public bool HasNextPage => PageNumber < TotalPages;
}
public class DatabaseConnectionInfo
{
public string ConnectionString { get; set; }
public bool CanConnect { get; set; }
public string DatabaseName { get; set; }
public string ServerVersion { get; set; }
public int ConnectionTimeout { get; set; }
}
public class DatabasePerformanceMetrics
{
public long SimpleQueryTime { get; set; }
public long ComplexQueryTime { get; set; }
public long WriteTime { get; set; }
public long TotalTestTime { get; set; }
public DateTime Timestamp { get; set; }
}
public class DatabaseHealthStatus
{
public DateTime CheckTime { get; set; }
public bool CanConnect { get; set; }
public bool TablesExist { get; set; }
public bool DataIntegrityCheck { get; set; }
public bool IndexesOptimal { get; set; }
public bool IsHealthy { get; set; }
public string ErrorMessage { get; set; }
}
数据库最佳实践
// 数据库最佳实践示例
public class DatabaseBestPracticesService
{
private readonly ApplicationDbContext _context;
private readonly ILogger<DatabaseBestPracticesService> _logger;
public DatabaseBestPracticesService(
ApplicationDbContext context,
ILogger<DatabaseBestPracticesService> logger)
{
_context = context;
_logger = logger;
}
// 安全的参数化查询
public async Task<List<Employee>> SearchEmployeesSafelyAsync(string searchTerm)
{
// 使用参数化查询防止SQL注入
var sql = @"
SELECT e.*, d.Name as DepartmentName
FROM Employees e
INNER JOIN Departments d ON e.DepartmentId = d.Id
WHERE e.FirstName LIKE @searchTerm
OR e.LastName LIKE @searchTerm
OR e.Email LIKE @searchTerm";
var parameter = new SqlParameter("@searchTerm", $"%{searchTerm}%");
return await _context.Employees
.FromSqlRaw(sql, parameter)
.Include(e => e.Department)
.ToListAsync();
}
// 数据验证和约束
public async Task<ValidationResult> ValidateEmployeeDataAsync(Employee employee)
{
var result = new ValidationResult();
// 检查邮箱唯一性
var existingEmployee = await _context.Employees
.FirstOrDefaultAsync(e => e.Email == employee.Email && e.Id != employee.Id);
if (existingEmployee != null)
{
result.AddError("Email", "邮箱地址已存在");
}
// 检查部门是否存在
var departmentExists = await _context.Departments
.AnyAsync(d => d.Id == employee.DepartmentId);
if (!departmentExists)
{
result.AddError("DepartmentId", "指定的部门不存在");
}
// 业务规则验证
if (employee.Salary < 0)
{
result.AddError("Salary", "薪资不能为负数");
}
if (employee.HireDate > DateTime.UtcNow)
{
result.AddError("HireDate", "入职日期不能是未来时间");
}
return result;
}
// 审计日志
public async Task<bool> CreateEmployeeWithAuditAsync(Employee employee, string createdBy)
{
using var transaction = await _context.Database.BeginTransactionAsync();
try
{
// 验证数据
var validation = await ValidateEmployeeDataAsync(employee);
if (!validation.IsValid)
{
_logger.LogWarning("员工数据验证失败: {Errors}",
string.Join(", ", validation.Errors.Select(e => $"{e.Key}: {e.Value}")));
return false;
}
// 创建员工
_context.Employees.Add(employee);
await _context.SaveChangesAsync();
// 记录审计日志
var auditLog = new AuditLog
{
TableName = "Employees",
RecordId = employee.Id.ToString(),
Action = "CREATE",
OldValues = null,
NewValues = JsonSerializer.Serialize(employee),
UserId = createdBy,
Timestamp = DateTime.UtcNow
};
_context.AuditLogs.Add(auditLog);
await _context.SaveChangesAsync();
await transaction.CommitAsync();
_logger.LogInformation("员工 {EmployeeName} 创建成功，操作者: {CreatedBy}",
employee.FullName, createdBy);
return true;
}
catch (Exception ex)
{
await transaction.RollbackAsync();
_logger.LogError(ex, "创建员工失败");
return false;
}
}
// 软删除实现
public async Task<bool> SoftDeleteEmployeeAsync(int employeeId, string deletedBy)
{
var employee = await _context.Employees.FindAsync(employeeId);
if (employee == null)
{
return false;
}
// 实现软删除
employee.IsDeleted = true;
employee.DeletedAt = DateTime.UtcNow;
employee.DeletedBy = deletedBy;
// 记录审计日志
var auditLog = new AuditLog
{
TableName = "Employees",
RecordId = employeeId.ToString(),
Action = "SOFT_DELETE",
OldValues = JsonSerializer.Serialize(new { IsDeleted = false }),
NewValues = JsonSerializer.Serialize(new { IsDeleted = true, DeletedAt = employee.DeletedAt }),
UserId = deletedBy,
Timestamp = DateTime.UtcNow
};
_context.AuditLogs.Add(auditLog);
var result = await _context.SaveChangesAsync();
_logger.LogInformation("员工 {EmployeeId} 已软删除，操作者: {DeletedBy}",
employeeId, deletedBy);
return result > 0;
}
// 数据备份
public async Task<bool> BackupEmployeeDataAsync(string backupPath)
{
try
{
var employees = await _context.Employees
.Include(e => e.Department)
.Where(e => !e.IsDeleted)
.ToListAsync();
var backup = new EmployeeBackup
{
BackupDate = DateTime.UtcNow,
EmployeeCount = employees.Count,
Employees = employees.Select(e => new EmployeeBackupData
{
Id = e.Id,
FirstName = e.FirstName,
LastName = e.LastName,
Email = e.Email,
DepartmentName = e.Department?.Name,
Salary = e.Salary,
HireDate = e.HireDate
}).ToList()
};
var json = JsonSerializer.Serialize(backup, new JsonSerializerOptions
{
WriteIndented = true,
PropertyNamingPolicy = JsonNamingPolicy.CamelCase
});
await File.WriteAllTextAsync(backupPath, json);
_logger.LogInformation("员工数据备份完成，文件: {BackupPath}，记录数: {Count}",
backupPath, employees.Count);
return true;
}
catch (Exception ex)
{
_logger.LogError(ex, "员工数据备份失败");
return false;
}
}
}
// 审计和验证相关的数据模型
public class AuditLog
{
public int Id { get; set; }
public string TableName { get; set; }
public string RecordId { get; set; }
public string Action { get; set; }
public string OldValues { get; set; }
public string NewValues { get; set; }
public string UserId { get; set; }
public DateTime Timestamp { get; set; }
}
public class ValidationResult
{
public Dictionary<string, string> Errors { get; set; } = new();
public bool IsValid => !Errors.Any();
public void AddError(string field, string message)
{
Errors[field] = message;
}
}
public class EmployeeBackup
{
public DateTime BackupDate { get; set; }
public int EmployeeCount { get; set; }
public List<EmployeeBackupData> Employees { get; set; } = new();
}
public class EmployeeBackupData
{
public int Id { get; set; }
public string FirstName { get; set; }
public string LastName { get; set; }
public string Email { get; set; }
public string DepartmentName { get; set; }
public decimal Salary { get; set; }
public DateTime HireDate { get; set; }
}
// 扩展Employee模型以支持软删除
public partial class Employee
{
public bool IsDeleted { get; set; }
public DateTime? DeletedAt { get; set; }
public string DeletedBy { get; set; }
}
18.6 实践练习
练习1：员工管理系统
// 完整的员工管理系统
public class EmployeeManagementSystem
{
private readonly ApplicationDbContext _context;
private readonly ILogger<EmployeeManagementSystem> _logger;
private readonly TransactionService _transactionService;
private readonly QueryService _queryService;
private readonly PerformanceOptimizationService _performanceService;
private readonly DatabaseBestPracticesService _bestPracticesService;
public EmployeeManagementSystem(
ApplicationDbContext context,
ILogger<EmployeeManagementSystem> logger,
TransactionService transactionService,
QueryService queryService,
PerformanceOptimizationService performanceService,
DatabaseBestPracticesService bestPracticesService)
{
_context = context;
_logger = logger;
_transactionService = transactionService;
_queryService = queryService;
_performanceService = performanceService;
_bestPracticesService = bestPracticesService;
}
// 员工入职流程
public async Task<EmployeeOnboardingResult> OnboardEmployeeAsync(EmployeeOnboardingRequest request)
{
var result = new EmployeeOnboardingResult();
using var transaction = await _context.Database.BeginTransactionAsync();
try
{
// 1. 创建员工记录
var employee = new Employee
{
FirstName = request.FirstName,
LastName = request.LastName,
Email = request.Email,
DepartmentId = request.DepartmentId,
Salary = request.Salary,
HireDate = DateTime.UtcNow
};
// 2. 验证员工数据
var validation = await _bestPracticesService.ValidateEmployeeDataAsync(employee);
if (!validation.IsValid)
{
result.Success = false;
result.Errors = validation.Errors;
return result;
}
// 3. 创建员工
_context.Employees.Add(employee);
await _context.SaveChangesAsync();
// 4. 分配到项目（如果指定）
if (request.ProjectAssignments?.Any() == true)
{
var assignments = request.ProjectAssignments.Select(pa => new EmployeeProjectAssignment
{
EmployeeId = employee.Id,
Role = pa.Role,
HoursAllocated = pa.HoursAllocated
}).ToList();
foreach (var assignment in request.ProjectAssignments)
{
var success = await _transactionService.AssignEmployeesToProjectAsync(
assignment.ProjectId,
new List<EmployeeProjectAssignment> { new EmployeeProjectAssignment
{
EmployeeId = employee.Id,
Role = assignment.Role,
HoursAllocated = assignment.HoursAllocated
}});
if (!success)
{
throw new InvalidOperationException($"无法将员工分配到项目 {assignment.ProjectId}");
}
}
}
// 5. 创建入职记录
var onboardingRecord = new EmployeeOnboarding
{
EmployeeId = employee.Id,
OnboardingDate = DateTime.UtcNow,
Status = "进行中",
AssignedMentorId = request.MentorId,
CompletionDeadline = DateTime.UtcNow.AddDays(30)
};
_context.EmployeeOnboardings.Add(onboardingRecord);
await _context.SaveChangesAsync();
await transaction.CommitAsync();
result.Success = true;
result.EmployeeId = employee.Id;
result.OnboardingId = onboardingRecord.Id;
_logger.LogInformation("员工 {EmployeeName} 入职成功，ID: {EmployeeId}",
employee.FullName, employee.Id);
return result;
}
catch (Exception ex)
{
await transaction.RollbackAsync();
_logger.LogError(ex, "员工入职失败");
result.Success = false;
result.Errors["General"] = ex.Message;
return result;
}
}
// 员工离职流程
public async Task<bool> OffboardEmployeeAsync(int employeeId, string reason, string processedBy)
{
using var transaction = await _context.Database.BeginTransactionAsync();
try
{
var employee = await _context.Employees
.Include(e => e.EmployeeProjects)
.FirstOrDefaultAsync(e => e.Id == employeeId);
if (employee == null)
{
return false;
}
// 1. 从所有项目中移除
if (employee.EmployeeProjects.Any())
{
_context.EmployeeProjects.RemoveRange(employee.EmployeeProjects);
}
// 2. 软删除员工
await _bestPracticesService.SoftDeleteEmployeeAsync(employeeId, processedBy);
// 3. 创建离职记录
var offboardingRecord = new EmployeeOffboarding
{
EmployeeId = employeeId,
OffboardingDate = DateTime.UtcNow,
Reason = reason,
ProcessedBy = processedBy,
FinalSalary = employee.Salary
};
_context.EmployeeOffboardings.Add(offboardingRecord);
await _context.SaveChangesAsync();
await transaction.CommitAsync();
_logger.LogInformation("员工 {EmployeeId} 离职处理完成，原因: {Reason}",
employeeId, reason);
return true;
}
catch (Exception ex)
{
await transaction.RollbackAsync();
_logger.LogError(ex, "员工离职处理失败");
return false;
}
}
// 生成员工报告
public async Task<EmployeeReport> GenerateEmployeeReportAsync(int employeeId)
{
var employee = await _context.Employees
.Include(e => e.Department)
.Include(e => e.EmployeeProjects)
.ThenInclude(ep => ep.Project)
.FirstOrDefaultAsync(e => e.Id == employeeId);
if (employee == null)
{
return null;
}
// 获取薪资历史
var salaryHistory = await _context.PayrollRecords
.Where(pr => pr.EmployeeId == employeeId)
.OrderByDescending(pr => pr.ProcessedDate)
.Take(12)
.ToListAsync();
// 获取转移历史
var transferHistory = await _context.EmployeeTransferHistories
.Where(eth => eth.EmployeeId == employeeId)
.Include(eth => eth.FromDepartment)
.Include(eth => eth.ToDepartment)
.OrderByDescending(eth => eth.TransferDate)
.ToListAsync();
// 计算绩效指标
var totalProjectHours = employee.EmployeeProjects.Sum(ep => ep.HoursAllocated);
var activeProjects = employee.EmployeeProjects.Count(ep => ep.Project.Status == "进行中");
return new EmployeeReport
{
Employee = employee,
SalaryHistory = salaryHistory,
TransferHistory = transferHistory,
TotalProjectHours = totalProjectHours,
ActiveProjectsCount = activeProjects,
AverageMonthlySalary = salaryHistory.Any() ? salaryHistory.Average(sh => sh.TotalPay) : employee.Salary,
GeneratedDate = DateTime.UtcNow
};
}
// 批量薪资调整
public async Task<BatchSalaryAdjustmentResult> BatchAdjustSalariesAsync(
List<SalaryAdjustment> adjustments,
string adjustedBy)
{
var result = new BatchSalaryAdjustmentResult();
using var transaction = await _context.Database.BeginTransactionAsync();
try
{
var employeeIds = adjustments.Select(a => a.EmployeeId).ToList();
var employees = await _context.Employees
.Where(e => employeeIds.Contains(e.Id))
.ToListAsync();
var adjustmentDict = adjustments.ToDictionary(a => a.EmployeeId);
var processedAdjustments = new List<ProcessedSalaryAdjustment>();
foreach (var employee in employees)
{
if (adjustmentDict.TryGetValue(employee.Id, out var adjustment))
{
var oldSalary = employee.Salary;
var newSalary = adjustment.AdjustmentType == "Percentage"
? oldSalary * (1 + adjustment.Amount / 100)
: oldSalary + adjustment.Amount;
employee.Salary = newSalary;
processedAdjustments.Add(new ProcessedSalaryAdjustment
{
EmployeeId = employee.Id,
EmployeeName = employee.FullName,
OldSalary = oldSalary,
NewSalary = newSalary,
AdjustmentAmount = newSalary - oldSalary,
AdjustmentType = adjustment.AdjustmentType,
Reason = adjustment.Reason
});
}
}
await _context.SaveChangesAsync();
// 记录批量调整历史
var batchRecord = new BatchSalaryAdjustmentHistory
{
AdjustmentDate = DateTime.UtcNow,
ProcessedBy = adjustedBy,
TotalEmployees = processedAdjustments.Count,
TotalAdjustmentAmount = processedAdjustments.Sum(pa => pa.AdjustmentAmount),
Details = JsonSerializer.Serialize(processedAdjustments)
};
_context.BatchSalaryAdjustmentHistories.Add(batchRecord);
await _context.SaveChangesAsync();
await transaction.CommitAsync();
result.Success = true;
result.ProcessedAdjustments = processedAdjustments;
result.BatchId = batchRecord.Id;
_logger.LogInformation("批量薪资调整完成，处理 {Count} 个员工，总调整金额: {Amount:C}",
processedAdjustments.Count, batchRecord.TotalAdjustmentAmount);
return result;
}
catch (Exception ex)
{
await transaction.RollbackAsync();
_logger.LogError(ex, "批量薪资调整失败");
result.Success = false;
result.ErrorMessage = ex.Message;
return result;
}
}
}
// 员工管理系统相关的数据模型
public class EmployeeOnboardingRequest
{
public string FirstName { get; set; }
public string LastName { get; set; }
public string Email { get; set; }
public int DepartmentId { get; set; }
public decimal Salary { get; set; }
public int? MentorId { get; set; }
public List<ProjectAssignmentRequest> ProjectAssignments { get; set; } = new();
}
public class ProjectAssignmentRequest
{
public int ProjectId { get; set; }
public string Role { get; set; }
public int HoursAllocated { get; set; }
}
public class EmployeeOnboardingResult
{
public bool Success { get; set; }
public int EmployeeId { get; set; }
public int OnboardingId { get; set; }
public Dictionary<string, string> Errors { get; set; } = new();
}
public class EmployeeOnboarding
{
public int Id { get; set; }
public int EmployeeId { get; set; }
public DateTime OnboardingDate { get; set; }
public string Status { get; set; }
public int? AssignedMentorId { get; set; }
public DateTime CompletionDeadline { get; set; }
public DateTime? CompletedDate { get; set; }
// 导航属性
public Employee Employee { get; set; }
public Employee AssignedMentor { get; set; }
}
public class EmployeeOffboarding
{
public int Id { get; set; }
public int EmployeeId { get; set; }
public DateTime OffboardingDate { get; set; }
public string Reason { get; set; }
public string ProcessedBy { get; set; }
public decimal FinalSalary { get; set; }
// 导航属性
public Employee Employee { get; set; }
}
public class EmployeeReport
{
public Employee Employee { get; set; }
public List<PayrollRecord> SalaryHistory { get; set; } = new();
public List<EmployeeTransferHistory> TransferHistory { get; set; } = new();
public int TotalProjectHours { get; set; }
public int ActiveProjectsCount { get; set; }
public decimal AverageMonthlySalary { get; set; }
public DateTime GeneratedDate { get; set; }
}
public class SalaryAdjustment
{
public int EmployeeId { get; set; }
public decimal Amount { get; set; }
public string AdjustmentType { get; set; } // "Fixed" or "Percentage"
public string Reason { get; set; }
}
public class ProcessedSalaryAdjustment
{
public int EmployeeId { get; set; }
public string EmployeeName { get; set; }
public decimal OldSalary { get; set; }
public decimal NewSalary { get; set; }
public decimal AdjustmentAmount { get; set; }
public string AdjustmentType { get; set; }
public string Reason { get; set; }
}
public class BatchSalaryAdjustmentResult
{
public bool Success { get; set; }
public int BatchId { get; set; }
public List<ProcessedSalaryAdjustment> ProcessedAdjustments { get; set; } = new();
public string ErrorMessage { get; set; }
}
public class BatchSalaryAdjustmentHistory
{
public int Id { get; set; }
public DateTime AdjustmentDate { get; set; }
public string ProcessedBy { get; set; }
public int TotalEmployees { get; set; }
public decimal TotalAdjustmentAmount { get; set; }
public string Details { get; set; }
}
练习2：数据分析和报告系统
// 数据分析和报告系统
public class DataAnalyticsService
{
private readonly ApplicationDbContext _context;
private readonly ILogger<DataAnalyticsService> _logger;
public DataAnalyticsService(ApplicationDbContext context, ILogger<DataAnalyticsService> logger)
{
_context = context;
_logger = logger;
}
// 生成综合分析报告
public async Task<ComprehensiveAnalyticsReport> GenerateComprehensiveReportAsync(
DateTime startDate,
DateTime endDate)
{
var report = new ComprehensiveAnalyticsReport
{
ReportPeriod = new DateRange { StartDate = startDate, EndDate = endDate },
GeneratedDate = DateTime.UtcNow
};
// 员工统计
report.EmployeeAnalytics = await GenerateEmployeeAnalyticsAsync(startDate, endDate);
// 部门分析
report.DepartmentAnalytics = await GenerateDepartmentAnalyticsAsync(startDate, endDate);
// 项目分析
report.ProjectAnalytics = await GenerateProjectAnalyticsAsync(startDate, endDate);
// 薪资分析
report.SalaryAnalytics = await GenerateSalaryAnalyticsAsync(startDate, endDate);
// 趋势分析
report.TrendAnalytics = await GenerateTrendAnalyticsAsync(startDate, endDate);
return report;
}
private async Task<EmployeeAnalytics> GenerateEmployeeAnalyticsAsync(DateTime startDate, DateTime endDate)
{
var totalEmployees = await _context.Employees.CountAsync(e => !e.IsDeleted);
var newHires = await _context.Employees
.CountAsync(e => e.HireDate >= startDate && e.HireDate <= endDate);
var departures = await _context.EmployeeOffboardings
.CountAsync(eo => eo.OffboardingDate >= startDate && eo.OffboardingDate <= endDate);
var avgTenure = await _context.Employees
.Where(e => !e.IsDeleted)
.Select(e => EF.Functions.DateDiffDay(e.HireDate, DateTime.UtcNow))
.AverageAsync();
var turnoverRate = totalEmployees > 0 ? (double)departures / totalEmployees * 100 : 0;
return new EmployeeAnalytics
{
TotalEmployees = totalEmployees,
NewHires = newHires,
Departures = departures,
NetChange = newHires - departures,
TurnoverRate = turnoverRate,
AverageTenureDays = avgTenure
};
}
private async Task<List<DepartmentAnalytics>> GenerateDepartmentAnalyticsAsync(DateTime startDate, DateTime endDate)
{
return await _context.Departments
.Select(d => new DepartmentAnalytics
{
DepartmentId = d.Id,
DepartmentName = d.Name,
EmployeeCount = d.Employees.Count(e => !e.IsDeleted),
AverageSalary = d.Employees.Where(e => !e.IsDeleted).Average(e => e.Salary),
TotalSalaryExpense = d.Employees.Where(e => !e.IsDeleted).Sum(e => e.Salary),
NewHires = d.Employees.Count(e => e.HireDate >= startDate && e.HireDate <= endDate),
ActiveProjects = d.Employees
.SelectMany(e => e.EmployeeProjects)
.Select(ep => ep.Project)
.Distinct()
.Count(p => p.Status == "进行中"),
Budget = d.Budget,
BudgetUtilization = d.Budget > 0 ?
d.Employees.Where(e => !e.IsDeleted).Sum(e => e.Salary) / d.Budget * 100 : 0
})
.ToListAsync();
}
private async Task<ProjectAnalytics> GenerateProjectAnalyticsAsync(DateTime startDate, DateTime endDate)
{
var totalProjects = await _context.Projects.CountAsync();
var activeProjects = await _context.Projects.CountAsync(p => p.Status == "进行中");
var completedProjects = await _context.Projects.CountAsync(p => p.Status == "已完成");
var totalBudget = await _context.Projects.SumAsync(p => p.Budget);
var avgBudget = await _context.Projects.AverageAsync(p => p.Budget);
var projectsByStatus = await _context.Projects
.GroupBy(p => p.Status)
.Select(g => new ProjectStatusCount
{
Status = g.Key,
Count = g.Count(),
TotalBudget = g.Sum(p => p.Budget)
})
.ToListAsync();
return new ProjectAnalytics
{
TotalProjects = totalProjects,
ActiveProjects = activeProjects,
CompletedProjects = completedProjects,
TotalBudget = totalBudget,
AverageBudget = avgBudget,
ProjectsByStatus = projectsByStatus
};
}
private async Task<SalaryAnalytics> GenerateSalaryAnalyticsAsync(DateTime startDate, DateTime endDate)
{
var salaryStats = await _context.Employees
.Where(e => !e.IsDeleted)
.GroupBy(e => 1)
.Select(g => new
{
MinSalary = g.Min(e => e.Salary),
MaxSalary = g.Max(e => e.Salary),
AverageSalary = g.Average(e => e.Salary),
MedianSalary = g.OrderBy(e => e.Salary)
.Skip(g.Count() / 2)
.Take(1)
.Select(e => e.Salary)
.FirstOrDefault(),
TotalSalaryExpense = g.Sum(e => e.Salary)
})
.FirstOrDefaultAsync();
var salaryRanges = await _context.Employees
.Where(e => !e.IsDeleted)
.GroupBy(e =>
e.Salary < 50000 ? "< 50K" :
e.Salary < 75000 ? "50K - 75K" :
e.Salary < 100000 ? "75K - 100K" :
e.Salary < 150000 ? "100K - 150K" : "> 150K")
.Select(g => new SalaryRangeCount
{
Range = g.Key,
Count = g.Count(),
Percentage = (double)g.Count() / _context.Employees.Count(e => !e.IsDeleted) * 100
})
.ToListAsync();
return new SalaryAnalytics
{
MinSalary = salaryStats?.MinSalary ?? 0,
MaxSalary = salaryStats?.MaxSalary ?? 0,
AverageSalary = salaryStats?.AverageSalary ?? 0,
MedianSalary = salaryStats?.MedianSalary ?? 0,
TotalSalaryExpense = salaryStats?.TotalSalaryExpense ?? 0,
SalaryRanges = salaryRanges
};
}
private async Task<TrendAnalytics> GenerateTrendAnalyticsAsync(DateTime startDate, DateTime endDate)
{
// 按月统计雇佣趋势
var hiringTrend = await _context.Employees
.Where(e => e.HireDate >= startDate && e.HireDate <= endDate)
.GroupBy(e => new { Year = e.HireDate.Year, Month = e.HireDate.Month })
.Select(g => new MonthlyTrend
{
Year = g.Key.Year,
Month = g.Key.Month,
Count = g.Count()
})
.OrderBy(mt => mt.Year)
.ThenBy(mt => mt.Month)
.ToListAsync();
// 按月统计离职趋势
var departureTrend = await _context.EmployeeOffboardings
.Where(eo => eo.OffboardingDate >= startDate && eo.OffboardingDate <= endDate)
.GroupBy(eo => new { Year = eo.OffboardingDate.Year, Month = eo.OffboardingDate.Month })
.Select(g => new MonthlyTrend
{
Year = g.Key.Year,
Month = g.Key.Month,
Count = g.Count()
})
.OrderBy(mt => mt.Year)
.ThenBy(mt => mt.Month)
.ToListAsync();
return new TrendAnalytics
{
HiringTrend = hiringTrend,
DepartureTrend = departureTrend
};
}
// 导出报告到Excel
public async Task<byte[]> ExportReportToExcelAsync(ComprehensiveAnalyticsReport report)
{
// 这里可以使用EPPlus或其他Excel库来生成Excel文件
// 为了简化，这里返回一个模拟的字节数组
var json = JsonSerializer.Serialize(report, new JsonSerializerOptions
{
WriteIndented = true,
PropertyNamingPolicy = JsonNamingPolicy.CamelCase
});
return Encoding.UTF8.GetBytes(json);
}
}
// 数据分析相关的数据模型
public class ComprehensiveAnalyticsReport
{
public DateRange ReportPeriod { get; set; }
public DateTime GeneratedDate { get; set; }
public EmployeeAnalytics EmployeeAnalytics { get; set; }
public List<DepartmentAnalytics> DepartmentAnalytics { get; set; } = new();
public ProjectAnalytics ProjectAnalytics { get; set; }
public SalaryAnalytics SalaryAnalytics { get; set; }
public TrendAnalytics TrendAnalytics { get; set; }
}
public class DateRange
{
public DateTime StartDate { get; set; }
public DateTime EndDate { get; set; }
}
public class EmployeeAnalytics
{
public int TotalEmployees { get; set; }
public int NewHires { get; set; }
public int Departures { get; set; }
public int NetChange { get; set; }
public double TurnoverRate { get; set; }
public double AverageTenureDays { get; set; }
}
public class DepartmentAnalytics
{
public int DepartmentId { get; set; }
public string DepartmentName { get; set; }
public int EmployeeCount { get; set; }
public decimal AverageSalary { get; set; }
public decimal TotalSalaryExpense { get; set; }
public int NewHires { get; set; }
public int ActiveProjects { get; set; }
public decimal Budget { get; set; }
public double BudgetUtilization { get; set; }
}
public class ProjectAnalytics
{
public int TotalProjects { get; set; }
public int ActiveProjects { get; set; }
public int CompletedProjects { get; set; }
public decimal TotalBudget { get; set; }
public decimal AverageBudget { get; set; }
public List<ProjectStatusCount> ProjectsByStatus { get; set; } = new();
}
public class ProjectStatusCount
{
public string Status { get; set; }
public int Count { get; set; }
public decimal TotalBudget { get; set; }
}
public class SalaryAnalytics
{
public decimal MinSalary { get; set; }
public decimal MaxSalary { get; set; }
public decimal AverageSalary { get; set; }
public decimal MedianSalary { get; set; }
public decimal TotalSalaryExpense { get; set; }
public List<SalaryRangeCount> SalaryRanges { get; set; } = new();
}
public class SalaryRangeCount
{
public string Range { get; set; }
public int Count { get; set; }
public double Percentage { get; set; }
}
public class TrendAnalytics
{
public List<MonthlyTrend> HiringTrend { get; set; } = new();
public List<MonthlyTrend> DepartureTrend { get; set; } = new();
}
public class MonthlyTrend
{
public int Year { get; set; }
public int Month { get; set; }
public int Count { get; set; }
}
练习演示
// 数据库编程演示
public class DatabaseProgrammingDemo
{
private readonly EmployeeManagementSystem _employeeSystem;
private readonly DataAnalyticsService _analyticsService;
private readonly ILogger<DatabaseProgrammingDemo> _logger;
public DatabaseProgrammingDemo(
EmployeeManagementSystem employeeSystem,
DataAnalyticsService analyticsService,
ILogger<DatabaseProgrammingDemo> logger)
{
_employeeSystem = employeeSystem;
_analyticsService = analyticsService;
_logger = logger;
}
public async Task RunDemoAsync()
{
_logger.LogInformation("开始数据库编程演示");
try
{
// 1. 员工入职演示
await DemoEmployeeOnboardingAsync();
// 2. 批量薪资调整演示
await DemoBatchSalaryAdjustmentAsync();
// 3. 数据分析报告演示
await DemoAnalyticsReportAsync();
// 4. 员工离职演示
await DemoEmployeeOffboardingAsync();
_logger.LogInformation("数据库编程演示完成");
}
catch (Exception ex)
{
_logger.LogError(ex, "演示过程中发生错误");
}
}
private async Task DemoEmployeeOnboardingAsync()
{
_logger.LogInformation("演示员工入职流程");
var onboardingRequest = new EmployeeOnboardingRequest
{
FirstName = "张",
LastName = "三",
Email = "zhangsan@company.com",
DepartmentId = 1,
Salary = 80000,
MentorId = 1,
ProjectAssignments = new List<ProjectAssignmentRequest>
{
new ProjectAssignmentRequest
{
ProjectId = 1,
Role = "开发工程师",
HoursAllocated = 40
}
}
};
var result = await _employeeSystem.OnboardEmployeeAsync(onboardingRequest);
if (result.Success)
{
_logger.LogInformation("员工入职成功，员工ID: {EmployeeId}", result.EmployeeId);
// 生成员工报告
var report = await _employeeSystem.GenerateEmployeeReportAsync(result.EmployeeId);
_logger.LogInformation("员工报告生成完成，活跃项目数: {ActiveProjects}",
report.ActiveProjectsCount);
}
else
{
_logger.LogWarning("员工入职失败: {Errors}",
string.Join(", ", result.Errors.Select(e => $"{e.Key}: {e.Value}")));
}
}
private async Task DemoBatchSalaryAdjustmentAsync()
{
_logger.LogInformation("演示批量薪资调整");
var adjustments = new List<SalaryAdjustment>
{
new SalaryAdjustment
{
EmployeeId = 1,
Amount = 10,
AdjustmentType = "Percentage",
Reason = "年度调薪"
},
new SalaryAdjustment
{
EmployeeId = 2,
Amount = 5000,
AdjustmentType = "Fixed",
Reason = "晋升调薪"
}
};
var result = await _employeeSystem.BatchAdjustSalariesAsync(adjustments, "HR Manager");
if (result.Success)
{
_logger.LogInformation("批量薪资调整成功，处理 {Count} 个员工",
result.ProcessedAdjustments.Count);
foreach (var adjustment in result.ProcessedAdjustments)
{
_logger.LogInformation("员工 {Name}: {OldSalary:C} -> {NewSalary:C} (调整: {Amount:C})",
adjustment.EmployeeName, adjustment.OldSalary,
adjustment.NewSalary, adjustment.AdjustmentAmount);
}
}
else
{
_logger.LogWarning("批量薪资调整失败: {Error}", result.ErrorMessage);
}
}
private async Task DemoAnalyticsReportAsync()
{
_logger.LogInformation("演示数据分析报告生成");
var startDate = DateTime.UtcNow.AddMonths(-12);
var endDate = DateTime.UtcNow;
var report = await _analyticsService.GenerateComprehensiveReportAsync(startDate, endDate);
_logger.LogInformation("分析报告生成完成:");
_logger.LogInformation("- 总员工数: {TotalEmployees}", report.EmployeeAnalytics.TotalEmployees);
_logger.LogInformation("- 新入职: {NewHires}", report.EmployeeAnalytics.NewHires);
_logger.LogInformation("- 离职: {Departures}", report.EmployeeAnalytics.Departures);
_logger.LogInformation("- 流失率: {TurnoverRate:F2}%", report.EmployeeAnalytics.TurnoverRate);
_logger.LogInformation("- 平均薪资: {AverageSalary:C}", report.SalaryAnalytics.AverageSalary);
_logger.LogInformation("- 活跃项目: {ActiveProjects}", report.ProjectAnalytics.ActiveProjects);
// 导出报告
var excelData = await _analyticsService.ExportReportToExcelAsync(report);
_logger.LogInformation("报告已导出，大小: {Size} 字节", excelData.Length);
}
private async Task DemoEmployeeOffboardingAsync()
{
_logger.LogInformation("演示员工离职流程");
// 假设员工ID为1的员工离职
var success = await _employeeSystem.OffboardEmployeeAsync(1, "个人发展", "HR Manager");
if (success)
{
_logger.LogInformation("员工离职处理完成");
}
else
{
_logger.LogWarning("员工离职处理失败");
}
}
}
18.7 本章总结
在本章中，我们深入学习了C#数据库编程的各个方面：
核心概念
- ADO.NET基础：连接字符串管理、基本数据库操作、参数化查询
- Entity Framework Core：DbContext配置、实体关系映射、Code First开发
- 事务管理：ACID特性、事务隔离级别、分布式事务处理
- LINQ查询：复杂查询构建、性能优化、原生SQL集成
高级技术
- 性能优化：查询优化、缓存策略、批量操作、异步流处理
- 数据库监控：性能指标收集、健康检查、索引优化分析
- 最佳实践：安全编程、数据验证、审计日志、软删除实现
实际应用
- 员工管理系统：完整的业务流程实现，包括入职、离职、薪资管理
- 数据分析系统：综合报告生成、趋势分析、数据可视化支持
- 企业级功能：批量操作、事务处理、数据备份恢复
重要技能
- 掌握现代数据库开发模式和最佳实践
- 理解性能优化和监控的重要性
- 学会设计可扩展和可维护的数据访问层
- 具备处理复杂业务逻辑和数据关系的能力
数据库编程是现代应用开发的核心技能，通过本章的学习，你已经具备了开发企业级数据库应用的能力。下一章我们将学习单元测试和集成测试，了解如何确保代码质量和系统可靠性。
