第十五章：高级特性与最佳实践
目录
- 15.1 批量操作
- 15.2 存储过程和函数
- 15.3 视图支持
- 15.4 临时表
- 15.5 XML和JSON支持
- 15.6 设计模式
- 15.7 错误处理
- 15.8 测试策略
- 15.9 部署考虑
- 15.10 综合最佳实践
- 15.11 教程系列总结
- 15.12 本章小结
15.1 批量操作
15.1.1 Fastest批量插入
SqlSugar提供了极速批量插入功能：
public class FastestBulkInsert
{
// Fastest模式：最快的批量插入
public static void BulkInsertFastest()
{
var db = GetSqlSugarClient();
// 准备大量数据
var students = new List<Student>();
for (int i = 0; i < 100000; i++)
{
students.Add(new Student
{
Name = $"Student{i}",
Age = 18 + (i % 10),
Email = $"student{i}@test.com",
CreateTime = DateTime.Now
});
}
var sw = Stopwatch.StartNew();
// 使用Fastest模式（最快）
db.Fastest<Student>().BulkCopy(students);
sw.Stop();
Console.WriteLine($"插入10万条数据耗时: {sw.ElapsedMilliseconds}ms");
}
// Fastest与普通插入性能对比
public static void PerformanceComparison()
{
var db = GetSqlSugarClient();
var students = GenerateTestData(10000);
// 方式1：循环插入（最慢）
var sw1 = Stopwatch.StartNew();
foreach (var student in students)
{
db.Insertable(student).ExecuteCommand();
}
sw1.Stop();
Console.WriteLine($"循环插入耗时: {sw1.ElapsedMilliseconds}ms");
// 方式2：批量插入（较快）
var sw2 = Stopwatch.StartNew();
db.Insertable(students).ExecuteCommand();
sw2.Stop();
Console.WriteLine($"批量插入耗时: {sw2.ElapsedMilliseconds}ms");
// 方式3：Fastest模式（最快）
var sw3 = Stopwatch.StartNew();
db.Fastest<Student>().BulkCopy(students);
sw3.Stop();
Console.WriteLine($"Fastest插入耗时: {sw3.ElapsedMilliseconds}ms");
}
// Fastest高级配置
public static void FastestAdvancedConfig()
{
var db = GetSqlSugarClient();
var students = GenerateTestData(50000);
db.Fastest<Student>()
.PageSize(10000) // 每次处理10000条
.AS("student_backup") // 插入到备份表
.BulkCopy(students);
}
// Fastest更新
public static void FastestBulkUpdate()
{
var db = GetSqlSugarClient();
// 先查询需要更新的数据
var students = db.Queryable<Student>()
.Where(s => s.Age < 20)
.ToList();
// 修改数据
foreach (var student in students)
{
student.Age += 1;
}
// 批量更新
db.Fastest<Student>().BulkUpdate(students);
}
}
15.1.2 批量更新删除
public class BulkOperations
{
// 批量更新
public static void BulkUpdate()
{
var db = GetSqlSugarClient();
// 方式1：根据条件批量更新
db.Updateable<Student>()
.SetColumns(s => new Student { Age = s.Age + 1 })
.Where(s => s.ClassId == 1)
.ExecuteCommand();
// 方式2：批量更新实体列表
var students = db.Queryable<Student>()
.Where(s => s.Age < 18)
.ToList();
foreach (var student in students)
{
student.Age = 18;
}
db.Updateable(students).ExecuteCommand();
// 方式3：使用Fastest批量更新
db.Fastest<Student>().BulkUpdate(students);
}
// 批量删除
public static void BulkDelete()
{
var db = GetSqlSugarClient();
// 方式1：根据条件批量删除
db.Deleteable<Student>()
.Where(s => s.CreateTime < DateTime.Now.AddYears(-1))
.ExecuteCommand();
// 方式2：根据ID列表删除
var ids = new List<int> { 1, 2, 3, 4, 5 };
db.Deleteable<Student>()
.In(ids)
.ExecuteCommand();
// 方式3：批量删除实体列表
var students = db.Queryable<Student>()
.Where(s => s.Status == 0)
.ToList();
db.Deleteable(students).ExecuteCommand();
}
// 批量Merge操作（插入或更新）
public static void BulkMerge()
{
var db = GetSqlSugarClient();
var students = GenerateTestData(1000);
// SQL Server
if (db.CurrentConnectionConfig.DbType == DbType.SqlServer)
{
db.Storageable(students)
.ToStorage()
.AsInsertable.ExecuteCommand(); // 插入不存在的
db.Storageable(students)
.ToStorage()
.AsUpdateable.ExecuteCommand(); // 更新已存在的
}
// 或使用自定义逻辑
foreach (var student in students)
{
var existing = db.Queryable<Student>()
.First(s => s.Id == student.Id);
if (existing == null)
{
db.Insertable(student).ExecuteCommand();
}
else
{
db.Updateable(student).ExecuteCommand();
}
}
}
}
15.2 存储过程和函数
public class StoredProcedureExamples
{
// 调用存储过程
public static void CallStoredProcedure()
{
var db = GetSqlSugarClient();
// 无参数存储过程
db.Ado.UseStoredProcedure().GetDataTable("sp_GetAllStudents");
// 带输入参数
var parameters = new[]
{
new SugarParameter("@ClassId", 1)
};
var dt = db.Ado.UseStoredProcedure()
.GetDataTable("sp_GetStudentsByClass", parameters);
// 带输出参数
var outParams = new[]
{
new SugarParameter("@ClassId", 1),
new SugarParameter("@TotalCount", null, true) // 输出参数
};
db.Ado.UseStoredProcedure()
.ExecuteCommand("sp_GetStudentCount", outParams);
var totalCount = outParams[1].Value;
Console.WriteLine($"总数: {totalCount}");
}
// 调用存储过程返回实体
public static List<Student> CallStoredProcedureReturnEntity()
{
var db = GetSqlSugarClient();
var parameters = new[]
{
new SugarParameter("@ClassId", 1)
};
return db.Ado.UseStoredProcedure()
.SqlQuery<Student>("sp_GetStudentsByClass", parameters);
}
// 调用标量函数
public static object CallScalarFunction()
{
var db = GetSqlSugarClient();
var result = db.Ado.GetScalar(
"SELECT dbo.fn_GetStudentCount(@ClassId)",
new { ClassId = 1 });
return result;
}
// 调用表值函数
public static List<Student> CallTableValuedFunction()
{
var db = GetSqlSugarClient();
var sql = "SELECT * FROM dbo.fn_GetStudentsByClass(@ClassId)";
return db.Ado.SqlQuery<Student>(sql, new { ClassId = 1 });
}
// 创建存储过程
public static void CreateStoredProcedure()
{
var db = GetSqlSugarClient();
var sql = @"
CREATE PROCEDURE sp_InsertStudent
@Name NVARCHAR(50),
@Age INT,
@Email NVARCHAR(100),
@NewId INT OUTPUT
AS
BEGIN
INSERT INTO Student (Name, Age, Email, CreateTime)
VALUES (@Name, @Age, @Email, GETDATE())
SET @NewId = SCOPE_IDENTITY()
END
";
db.Ado.ExecuteCommand(sql);
}
}
15.3 视图支持
// 定义视图实体
[SugarTable("v_student_class")]
public class StudentClassView
{
public int StudentId { get; set; }
public string StudentName { get; set; }
public int Age { get; set; }
public string ClassName { get; set; }
public string TeacherName { get; set; }
}
public class ViewExamples
{
// 查询视图
public static List<StudentClassView> QueryView()
{
var db = GetSqlSugarClient();
return db.Queryable<StudentClassView>()
.Where(v => v.Age > 18)
.ToList();
}
// 创建视图
public static void CreateView()
{
var db = GetSqlSugarClient();
var sql = @"
CREATE VIEW v_student_class AS
SELECT
s.Id AS StudentId,
s.Name AS StudentName,
s.Age,
c.Name AS ClassName,
t.Name AS TeacherName
FROM Student s
INNER JOIN Class c ON s.ClassId = c.Id
INNER JOIN Teacher t ON c.TeacherId = t.Id
";
db.Ado.ExecuteCommand(sql);
}
// 使用SqlFunc查询视图
public static void QueryViewWithSqlFunc()
{
var db = GetSqlSugarClient();
var result = db.Queryable<StudentClassView>()
.Where(v => SqlFunc.IsNullOrEmpty(v.TeacherName) == false)
.Select(v => new
{
v.StudentName,
v.ClassName,
v.TeacherName
})
.ToList();
}
}
15.4 临时表
public class TempTableExamples
{
// 创建临时表
public static void CreateTempTable()
{
var db = GetSqlSugarClient();
// SQL Server临时表
db.Ado.ExecuteCommand(@"
CREATE TABLE #TempStudent (
Id INT,
Name NVARCHAR(50),
Age INT
)
");
// 插入数据到临时表
db.Ado.ExecuteCommand(@"
INSERT INTO #TempStudent (Id, Name, Age)
SELECT Id, Name, Age FROM Student WHERE Age > 18
");
// 查询临时表
var result = db.Ado.SqlQuery<Student>("SELECT * FROM #TempStudent");
// 临时表会在连接关闭时自动删除
}
// 使用表变量（SQL Server）
public static void UseTableVariable()
{
var db = GetSqlSugarClient();
var sql = @"
DECLARE @TempTable TABLE (
Id INT,
Name NVARCHAR(50),
Age INT
)
INSERT INTO @TempTable
SELECT Id, Name, Age FROM Student WHERE Age > 18
SELECT * FROM @TempTable
";
var result = db.Ado.SqlQuery<Student>(sql);
}
// CTE（公共表表达式）
public static void UseCTE()
{
var db = GetSqlSugarClient();
var sql = @"
WITH StudentCTE AS (
SELECT Id, Name, Age, ClassId
FROM Student
WHERE Age > 18
)
SELECT s.*, c.Name AS ClassName
FROM StudentCTE s
INNER JOIN Class c ON s.ClassId = c.Id
";
var result = db.Ado.SqlQuery<dynamic>(sql);
}
}
15.5 XML和JSON支持
public class XmlJsonExamples
{
// JSON列操作（PostgreSQL, MySQL 5.7+）
public static void JsonColumnOperations()
{
var db = GetSqlSugarClient();
// 查询JSON字段
var sql = @"
SELECT * FROM student
WHERE JSON_EXTRACT(ext_data, '$.city') = '北京'
";
var result = db.Ado.SqlQuery<Student>(sql);
// 更新JSON字段
db.Ado.ExecuteCommand(@"
UPDATE student
SET ext_data = JSON_SET(ext_data, '$.city', '上海')
WHERE id = 1
");
}
// 序列化为JSON
public static void SerializeToJson()
{
var db = GetSqlSugarClient();
var students = db.Queryable<Student>().ToList();
var json = JsonConvert.SerializeObject(students, Formatting.Indented);
Console.WriteLine(json);
File.WriteAllText("students.json", json);
}
// 从JSON导入
public static void ImportFromJson()
{
var db = GetSqlSugarClient();
var json = File.ReadAllText("students.json");
var students = JsonConvert.DeserializeObject<List<Student>>(json);
db.Insertable(students).ExecuteCommand();
}
// XML操作（SQL Server）
public static void XmlOperations()
{
var db = GetSqlSugarClient();
// 查询返回XML
var xml = db.Ado.GetString(@"
SELECT Id, Name, Age
FROM Student
FOR XML AUTO, ROOT('Students')
");
Console.WriteLine(xml);
// 从XML导入
db.Ado.ExecuteCommand(@"
DECLARE @xml XML = '<Students>
<Student><Name>张三</Name><Age>18</Age></Student>
<Student><Name>李四</Name><Age>20</Age></Student>
</Students>'
INSERT INTO Student (Name, Age)
SELECT
T.c.value('Name[1]', 'NVARCHAR(50)'),
T.c.value('Age[1]', 'INT')
FROM @xml.nodes('/Students/Student') T(c)
");
}
}
15.6 设计模式
// 1. 单例模式
public class DatabaseSingleton
{
private static SqlSugarClient _instance;
private static readonly object _lock = new object();
public static SqlSugarClient Instance
{
get
{
if (_instance == null)
{
lock (_lock)
{
if (_instance == null)
{
_instance = new SqlSugarClient(new ConnectionConfig
{
ConnectionString = "...",
DbType = DbType.SqlServer,
IsAutoCloseConnection = true
});
}
}
}
return _instance;
}
}
}
// 2. 工厂模式
public interface IDatabaseFactory
{
SqlSugarClient CreateDatabase(string dbType);
}
public class DatabaseFactory : IDatabaseFactory
{
private readonly IConfiguration _configuration;
public DatabaseFactory(IConfiguration configuration)
{
_configuration = configuration;
}
public SqlSugarClient CreateDatabase(string dbType)
{
var connectionString = _configuration.GetConnectionString(dbType);
var config = new ConnectionConfig
{
ConnectionString = connectionString,
DbType = GetDbType(dbType),
IsAutoCloseConnection = true
};
return new SqlSugarClient(config);
}
private DbType GetDbType(string dbType)
{
return dbType.ToLower() switch
{
"mysql" => DbType.MySql,
"sqlserver" => DbType.SqlServer,
"postgresql" => DbType.PostgreSQL,
_ => throw new NotSupportedException()
};
}
}
// 3. 策略模式
public interface IQueryStrategy<T>
{
List<T> Execute(SqlSugarClient db);
}
public class ActiveStudentsStrategy : IQueryStrategy<Student>
{
public List<Student> Execute(SqlSugarClient db)
{
return db.Queryable<Student>()
.Where(s => s.Status == 1)
.ToList();
}
}
public class GraduatedStudentsStrategy : IQueryStrategy<Student>
{
public List<Student> Execute(SqlSugarClient db)
{
return db.Queryable<Student>()
.Where(s => s.Status == 2)
.ToList();
}
}
// 4. 装饰器模式
public class LoggingRepositoryDecorator<T> : IRepository<T> where T : class, new()
{
private readonly IRepository<T> _repository;
private readonly ILogger _logger;
public LoggingRepositoryDecorator(IRepository<T> repository, ILogger logger)
{
_repository = repository;
_logger = logger;
}
public T GetById(object id)
{
_logger.LogInformation($"Getting {typeof(T).Name} by id: {id}");
var result = _repository.GetById(id);
_logger.LogInformation($"Retrieved {typeof(T).Name}: {result != null}");
return result;
}
public int Insert(T entity)
{
_logger.LogInformation($"Inserting {typeof(T).Name}");
var result = _repository.Insert(entity);
_logger.LogInformation($"Inserted {typeof(T).Name} with id: {result}");
return result;
}
// 实现其他方法...
}
// 5. 建造者模式
public class QueryBuilder<T> where T : class, new()
{
private readonly SqlSugarClient _db;
private ISugarQueryable<T> _queryable;
public QueryBuilder(SqlSugarClient db)
{
_db = db;
_queryable = db.Queryable<T>();
}
public QueryBuilder<T> Where(Expression<Func<T, bool>> expression)
{
_queryable = _queryable.Where(expression);
return this;
}
public QueryBuilder<T> OrderBy(Expression<Func<T, object>> expression)
{
_queryable = _queryable.OrderBy(expression);
return this;
}
public QueryBuilder<T> Take(int count)
{
_queryable = _queryable.Take(count);
return this;
}
public List<T> Build()
{
return _queryable.ToList();
}
}
// 使用
var students = new QueryBuilder<Student>(db)
.Where(s => s.Age > 18)
.OrderBy(s => s.Name)
.Take(10)
.Build();
15.7 错误处理
public class ErrorHandlingExamples
{
// 全局错误处理
public static void ConfigureGlobalErrorHandling(SqlSugarClient db)
{
db.Aop.OnError = (exp) =>
{
// 记录错误日志
Logger.LogError(exp, "SQL执行错误");
// 发送错误通知
if (IsCriticalError(exp))
{
SendErrorNotification(exp);
}
// 根据错误类型处理
if (exp.Message.Contains("timeout"))
{
// 处理超时错误
HandleTimeoutError(exp);
}
else if (exp.Message.Contains("deadlock"))
{
// 处理死锁错误
HandleDeadlockError(exp);
}
};
}
// 重试机制
public static T ExecuteWithRetry<T>(Func<T> operation, int maxRetries = 3)
{
int retries = 0;
while (true)
{
try
{
return operation();
}
catch (Exception ex)
{
retries++;
if (retries >= maxRetries)
{
Logger.LogError(ex, $"操作失败，已重试{maxRetries}次");
throw;
}
// 判断是否为可重试的错误
if (!IsRetriableError(ex))
{
throw;
}
// 指数退避
var delay = TimeSpan.FromSeconds(Math.Pow(2, retries));
Logger.LogWarning($"操作失败，{delay.TotalSeconds}秒后重试（第{retries}次）");
Thread.Sleep(delay);
}
}
}
// 事务错误处理
public static bool ExecuteInTransaction(SqlSugarClient db, Action operation)
{
try
{
db.BeginTran();
operation();
db.CommitTran();
return true;
}
catch (Exception ex)
{
db.RollbackTran();
Logger.LogError(ex, "事务执行失败");
return false;
}
}
// 自定义异常
public class DatabaseException : Exception
{
public string Sql { get; set; }
public object[] Parameters { get; set; }
public DatabaseException(string message, string sql, object[] parameters)
: base(message)
{
Sql = sql;
Parameters = parameters;
}
}
// 验证异常
public class ValidationException : Exception
{
public Dictionary<string, string> Errors { get; set; }
public ValidationException(string message) : base(message)
{
Errors = new Dictionary<string, string>();
}
public ValidationException(Dictionary<string, string> errors)
: base("验证失败")
{
Errors = errors;
}
}
private static bool IsCriticalError(SqlSugarException exp)
{
return exp.Message.Contains("connection") ||
exp.Message.Contains("login");
}
private static bool IsRetriableError(Exception ex)
{
return ex.Message.Contains("timeout") ||
ex.Message.Contains("deadlock");
}
private static void HandleTimeoutError(SqlSugarException exp)
{
Logger.LogWarning($"SQL超时: {exp.Sql}");
}
private static void HandleDeadlockError(SqlSugarException exp)
{
Logger.LogWarning($"发生死锁: {exp.Sql}");
}
private static void SendErrorNotification(SqlSugarException exp)
{
// 发送邮件或其他通知
}
}
15.8 测试策略
// 单元测试
public class StudentRepositoryTests
{
private Mock<ISqlSugarClient> _mockDb;
private StudentRepository _repository;
[SetUp]
public void Setup()
{
_mockDb = new Mock<ISqlSugarClient>();
_repository = new StudentRepository(_mockDb.Object);
}
[Test]
public void GetById_ShouldReturnStudent()
{
// Arrange
var expectedStudent = new Student { Id = 1, Name = "Test" };
_mockDb.Setup(db => db.Queryable<Student>())
.Returns(new MockQueryable<Student>(new[] { expectedStudent }));
// Act
var result = _repository.GetById(1);
// Assert
Assert.AreEqual(expectedStudent.Id, result.Id);
Assert.AreEqual(expectedStudent.Name, result.Name);
}
[Test]
public void Insert_ShouldReturnNewId()
{
// Arrange
var student = new Student { Name = "New Student" };
var expectedId = 1;
_mockDb.Setup(db => db.Insertable(student).ExecuteReturnIdentity())
.Returns(expectedId);
// Act
var result = _repository.Insert(student);
// Assert
Assert.AreEqual(expectedId, result);
}
}
// 集成测试
public class StudentServiceIntegrationTests
{
private SqlSugarClient _db;
private StudentService _service;
[SetUp]
public void Setup()
{
// 使用测试数据库
_db = new SqlSugarClient(new ConnectionConfig
{
ConnectionString = "Server=.;Database=TestDB;...",
DbType = DbType.SqlServer,
IsAutoCloseConnection = true
});
// 创建测试表
_db.CodeFirst.InitTables<Student>();
_service = new StudentService(new StudentRepository(_db), null, null);
}
[TearDown]
public void TearDown()
{
// 清理测试数据
_db.Deleteable<Student>().Where("1=1").ExecuteCommand();
}
[Test]
public void CreateAndGetStudent_ShouldWork()
{
// Arrange
var dto = new CreateStudentDto
{
Name = "Test Student",
Age = 18,
Email = "test@test.com"
};
// Act
var id = _service.Create(dto);
var result = _service.GetById(id);
// Assert
Assert.IsNotNull(result);
Assert.AreEqual(dto.Name, result.Name);
Assert.AreEqual(dto.Age, result.Age);
}
}
// 性能测试
public class PerformanceTests
{
[Test]
public void BulkInsert_ShouldBePerformant()
{
var db = GetTestDatabase();
var students = GenerateTestData(10000);
var sw = Stopwatch.StartNew();
db.Fastest<Student>().BulkCopy(students);
sw.Stop();
// 断言：10000条记录应该在2秒内完成
Assert.Less(sw.ElapsedMilliseconds, 2000);
}
[Test]
public void QueryWithIndex_ShouldBePerformant()
{
var db = GetTestDatabase();
// 插入测试数据
var students = GenerateTestData(100000);
db.Fastest<Student>().BulkCopy(students);
// 创建索引
db.DbMaintenance.CreateIndex("student", "idx_name", "Name");
var sw = Stopwatch.StartNew();
var result = db.Queryable<Student>()
.Where(s => s.Name == "Test")
.ToList();
sw.Stop();
// 断言：有索引的查询应该很快
Assert.Less(sw.ElapsedMilliseconds, 100);
}
}
15.9 部署考虑
public class DeploymentConsiderations
{
// 配置管理
public static void ConfigurationManagement()
{
// 从配置文件读取
var builder = new ConfigurationBuilder()
.AddJsonFile("appsettings.json")
.AddEnvironmentVariables();
var configuration = builder.Build();
var connectionString = configuration.GetConnectionString("DefaultConnection");
var dbType = configuration["Database:Type"];
var db = new SqlSugarClient(new ConnectionConfig
{
ConnectionString = connectionString,
DbType = dbType == "MySQL" ? DbType.MySql : DbType.SqlServer,
IsAutoCloseConnection = true
});
}
// 连接池配置
public static void ConnectionPooling()
{
var config = new ConnectionConfig
{
ConnectionString = "Server=.;Database=test;...",
DbType = DbType.SqlServer,
IsAutoCloseConnection = true,
// 连接池配置
MoreSettings = new ConnMoreSettings
{
IsAutoRemoveDataCache = true,
SqlServerCodeFirstNvarchar = true
}
};
// 使用SqlSugarScope支持多线程
var db = new SqlSugarScope(config);
}
// 数据库迁移
public static void DatabaseMigration()
{
var db = GetDatabase();
// 1. 检查表是否存在
if (!db.DbMaintenance.IsAnyTable("student", false))
{
// 2. 创建表
db.CodeFirst.InitTables<Student>();
}
// 3. 检查列是否存在
if (!db.DbMaintenance.IsAnyColumn("student", "Email"))
{
// 4. 添加列
db.DbMaintenance.AddColumn("student", new DbColumnInfo
{
DbColumnName = "Email",
DataType = "varchar(100)"
});
}
// 5. 数据迁移
MigrateData(db);
}
// 版本控制
public class DatabaseVersion
{
public int Version { get; set; }
public string Description { get; set; }
public DateTime AppliedDate { get; set; }
}
public static void ApplyMigrations(SqlSugarClient db)
{
// 创建版本表
if (!db.DbMaintenance.IsAnyTable("__migrations", false))
{
db.CodeFirst.InitTables<DatabaseVersion>();
}
// 获取当前版本
var currentVersion = db.Queryable<DatabaseVersion>()
.OrderBy(v => v.Version, OrderByType.Desc)
.Select(v => v.Version)
.FirstOrDefault();
// 应用新迁移
var migrations = GetPendingMigrations(currentVersion);
foreach (var migration in migrations)
{
migration.Execute(db);
db.Insertable(new DatabaseVersion
{
Version = migration.Version,
Description = migration.Description,
AppliedDate = DateTime.Now
}).ExecuteCommand();
}
}
// 健康检查
public static bool HealthCheck(SqlSugarClient db)
{
try
{
// 执行简单查询
db.Ado.GetInt("SELECT 1");
return true;
}
catch
{
return false;
}
}
}
15.10 综合最佳实践
public class BestPractices
{
// 1. 使用using语句确保资源释放
public static void UseUsingStatement()
{
using (var db = new SqlSugarClient(new ConnectionConfig { ... }))
{
var students = db.Queryable<Student>().ToList();
}
// db会自动释放
}
// 2. 使用参数化查询防止SQL注入
public static void UseParameterizedQuery()
{
var db = GetDatabase();
var name = "'; DROP TABLE Student; --";
// ❌ 错误：字符串拼接
// var sql = $"SELECT * FROM Student WHERE Name = '{name}'";
// ✅ 正确：参数化查询
var students = db.Queryable<Student>()
.Where(s => s.Name == name)
.ToList();
}
// 3. 合理使用事务
public static void UseTransactionsProperly()
{
var db = GetDatabase();
try
{
db.BeginTran();
// 执行多个相关操作
db.Insertable(new Order()).ExecuteCommand();
db.Insertable(new OrderItem()).ExecuteCommand();
db.Updateable<Product>().ExecuteCommand();
db.CommitTran();
}
catch
{
db.RollbackTran();
throw;
}
}
// 4. 避免N+1查询
public static void AvoidNPlusOneQueries()
{
var db = GetDatabase();
// ❌ 错误：N+1查询
var students = db.Queryable<Student>().ToList();
foreach (var student in students)
{
var @class = db.Queryable<Class>()
.First(c => c.Id == student.ClassId); // N次查询
}
// ✅ 正确：使用Include或Join
var studentsWithClass = db.Queryable<Student>()
.Includes(s => s.Class)
.ToList();
}
// 5. 使用异步操作提高性能
public static async Task<List<Student>> UseAsyncOperations()
{
var db = GetDatabase();
return await db.Queryable<Student>()
.Where(s => s.Age > 18)
.ToListAsync();
}
// 6. 合理使用缓存
public static void UseCachingWisely()
{
var db = GetDatabase();
// 对不常变化的数据使用缓存
var countries = db.Queryable<Country>()
.WithCache(3600) // 缓存1小时
.ToList();
// 对频繁变化的数据不使用缓存
var orders = db.Queryable<Order>()
.Where(o => o.CreateTime >= DateTime.Today)
.ToList(); // 不缓存
}
// 7. 只查询需要的字段
public static void SelectOnlyNeededColumns()
{
var db = GetDatabase();
// ❌ 错误：查询所有字段
var students = db.Queryable<Student>().ToList();
// ✅ 正确：只查询需要的字段
var studentNames = db.Queryable<Student>()
.Select(s => new { s.Id, s.Name })
.ToList();
}
// 8. 使用分页避免大量数据
public static void UsePaginationForLargeDatasets()
{
var db = GetDatabase();
int totalCount = 0;
var pageData = db.Queryable<Student>()
.ToPageList(1, 100, ref totalCount);
}
// 9. 配置日志记录
public static void ConfigureLogging()
{
var db = new SqlSugarClient(new ConnectionConfig
{
// ...
IsAutoCloseConnection = true,
// 只在开发环境记录SQL
#if DEBUG
AopEvents = new AopEvents
{
OnLogExecuting = (sql, pars) =>
{
Console.WriteLine(sql);
}
}
#endif
});
}
// 10. 使用工作单元模式
public static void UseUnitOfWorkPattern()
{
using (var uow = new UnitOfWork(GetDatabase()))
{
try
{
uow.BeginTran();
var studentRepo = uow.GetRepository<Student>();
var classRepo = uow.GetRepository<Class>();
studentRepo.Insert(new Student());
classRepo.Update(new Class());
uow.CommitTran();
}
catch
{
uow.RollbackTran();
throw;
}
}
}
}
// 完整的最佳实践示例
public class CompleteExample
{
private readonly ILogger<CompleteExample> _logger;
private readonly IUnitOfWork _unitOfWork;
private readonly IMapper _mapper;
public CompleteExample(
ILogger<CompleteExample> logger,
IUnitOfWork unitOfWork,
IMapper mapper)
{
_logger = logger;
_unitOfWork = unitOfWork;
_mapper = mapper;
}
public async Task<PageResult<StudentDto>> GetStudentsAsync(StudentQueryDto query)
{
try
{
_logger.LogInformation("查询学生列表");
var repository = _unitOfWork.GetRepository<Student>();
var queryable = _unitOfWork.Db.Queryable<Student>()
.WhereIF(!string.IsNullOrEmpty(query.Keyword),
s => s.Name.Contains(query.Keyword))
.WhereIF(query.ClassId.HasValue,
s => s.ClassId == query.ClassId)
.OrderBy(s => s.CreateTime, OrderByType.Desc);
int totalCount = 0;
var list = await queryable
.ToPageListAsync(query.PageIndex, query.PageSize, ref totalCount);
var dtos = _mapper.Map<List<StudentDto>>(list);
return new PageResult<StudentDto>
{
PageIndex = query.PageIndex,
PageSize = query.PageSize,
TotalCount = totalCount,
Items = dtos
};
}
catch (Exception ex)
{
_logger.LogError(ex, "查询学生列表失败");
throw;
}
}
}
15.11 教程系列总结
通过本系列15章的学习，我们完整地学习了SqlSugar ORM框架：
基础篇（第1-5章）
- 第一章：了解了SqlSugar的特点和安装配置
- 第二章：掌握了实体配置和CodeFirst
- 第三章：学会了基础的CRUD操作
- 第四章：深入学习了查询功能
- 第五章：掌握了高级查询技巧
进阶篇（第6-10章）
- 第六章：学会了复杂的多表查询和连接
- 第七章：掌握了事务处理
- 第八章：理解了导航属性和延迟加载
- 第九章：学会了分页查询和性能优化
- 第十章：掌握了分表分库和多租户架构
高级篇（第11-15章）
- 第十一章：实现了AOP和日志监控
- 第十二章：掌握了DbFirst和代码生成
- 第十三章：学会了多数据库支持
- 第十四章：理解了仓储模式和依赖注入
- 第十五章：学习了高级特性和最佳实践
核心知识点回顾
// 1. 连接配置
var db = new SqlSugarClient(new ConnectionConfig
{
ConnectionString = "...",
DbType = DbType.SqlServer,
IsAutoCloseConnection = true
});
// 2. 实体定义
[SugarTable("student")]
public class Student
{
[SugarColumn(IsPrimaryKey = true, IsIdentity = true)]
public int Id { get; set; }
[SugarColumn(Length = 50, IsNullable = false)]
public string Name { get; set; }
}
// 3. 基础操作
// 插入
db.Insertable(student).ExecuteCommand();
// 查询
db.Queryable<Student>().ToList();
// 更新
db.Updateable(student).ExecuteCommand();
// 删除
db.Deleteable<Student>().Where(s => s.Id == 1).ExecuteCommand();
// 4. 复杂查询
var result = db.Queryable<Student>()
.LeftJoin<Class>((s, c) => s.ClassId == c.Id)
.Where(s => s.Age > 18)
.OrderBy(s => s.CreateTime, OrderByType.Desc)
.Select((s, c) => new { s.Name, c.ClassName })
.ToList();
// 5. 事务
db.Ado.BeginTran();
try
{
db.Insertable(student).ExecuteCommand();
db.Insertable(@class).ExecuteCommand();
db.Ado.CommitTran();
}
catch
{
db.Ado.RollbackTran();
}
// 6. 分页
int total = 0;
var pageData = db.Queryable<Student>()
.ToPageList(1, 20, ref total);
// 7. 导航属性
var students = db.Queryable<Student>()
.Includes(s => s.Class)
.ToList();
// 8. 仓储模式
public interface IRepository<T>
{
T GetById(object id);
int Insert(T entity);
bool Update(T entity);
bool Delete(object id);
}
学习建议
- 循序渐进：从基础CRUD开始，逐步学习高级特性
- 动手实践：每个知识点都要亲自编写代码验证
- 阅读文档：官方文档是最权威的参考资料
- 参与社区：在GitHub和论坛上与其他开发者交流
- 关注更新：SqlSugar持续更新，关注新特性
- 性能优化：始终关注查询性能和最佳实践
- 安全第一：注意SQL注入防护和数据安全
- 代码规范：遵循良好的编码规范和设计模式
实际项目应用
在实际项目中使用SqlSugar的建议：
// 1. 项目结构
/*
MyProject
├── MyProject.Models // 实体模型
├── MyProject.Repository // 数据访问层
├── MyProject.Service // 业务逻辑层
├── MyProject.Api // API层
└── MyProject.Tests // 测试项目
*/
// 2. 依赖注入配置
public void ConfigureServices(IServiceCollection services)
{
// 注册SqlSugar
services.AddSingleton<ISqlSugarClient>(provider => { ... });
// 注册仓储
services.AddScoped(typeof(IRepository<>), typeof(Repository<>));
// 注册服务
services.AddScoped<IStudentService, StudentService>();
}
// 3. 统一返回格式
public class ApiResult<T>
{
public bool Success { get; set; }
public string Message { get; set; }
public T Data { get; set; }
public int Code { get; set; }
}
// 4. 统一异常处理
public class GlobalExceptionFilter : IExceptionFilter
{
public void OnException(ExceptionContext context)
{
var result = new ApiResult<object>
{
Success = false,
Message = context.Exception.Message,
Code = 500
};
context.Result = new JsonResult(result);
}
}
15.12 本章小结
本章作为教程的最后一章，介绍了SqlSugar的高级特性和最佳实践：
- 批量操作：掌握了Fastest等高性能批量操作方法
- 存储过程：学会了调用存储过程和函数
- 视图支持：了解了视图的查询和创建
- 临时表：掌握了临时表和CTE的使用
- XML/JSON：学会了处理XML和JSON数据
- 设计模式：应用了多种设计模式优化代码
- 错误处理：实现了完善的错误处理机制
- 测试策略：掌握了单元测试和集成测试方法
- 部署考虑：了解了生产环境的部署要点
- 最佳实践：总结了开发中的各种最佳实践
通过完整的15章学习，您现在应该能够：
- 熟练使用SqlSugar进行数据库开发
- 设计和实现高性能的数据访问层
- 构建可维护、可扩展的企业级应用
- 解决实际开发中遇到的各种问题
- 遵循最佳实践编写高质量代码
感谢您完成本系列教程的学习！
SqlSugar是一个功能强大且不断发展的ORM框架，希望本教程能够帮助您快速掌握并应用到实际项目中。祝您在.NET开发的道路上越走越远！
相关资源：
继续学习：
- 深入学习EF Core对比
- 研究微服务架构
- 探索云原生开发
- 学习分布式事务