第三章：数据库连接与配置

3.1 ConnectionConfig详解

3.1.1 基本配置项

ConnectionConfig是SqlSugar的核心配置类,包含了所有数据库连接相关的配置项。以下是一个完整的配置示例:

var config = new ConnectionConfig()
{
    ConnectionString = "Server=localhost;Database=TestDB;Uid=root;Pwd=123456;",
    DbType = DbType.MySql,
    IsAutoCloseConnection = true,
    InitKeyType = InitKeyType.Attribute,
    ConfigureExternalServices = new ConfigureExternalServices()
    {
        // 序列化服务
        SerializeService = new SerializeService(),
        // 实体服务
        EntityService = new EntityService(),
        // 实体命名服务
        EntityNameService = (type, entity) => { }
    }
};

var db = new SqlSugarClient(config);

关键配置项说明:

ConnectionString: 数据库连接字符串
DbType: 数据库类型(MySQL, SqlServer, PostgreSQL等)
IsAutoCloseConnection: 是否自动关闭连接,建议设为true
InitKeyType: 主键初始化方式(Attribute或SystemTable)

3.1.2 数据库类型

SqlSugar支持多种数据库类型:

// SQL Server
DbType = DbType.SqlServer

// MySQL
DbType = DbType.MySql

// PostgreSQL
DbType = DbType.PostgreSQL

// Oracle
DbType = DbType.Oracle

// SQLite
DbType = DbType.Sqlite

// 达梦数据库
DbType = DbType.Dm

// 人大金仓
DbType = DbType.Kdbndp

3.1.3 连接字符串

不同数据库的连接字符串格式:

// SQL Server
"Server=localhost;Database=TestDB;User Id=sa;Password=123456;TrustServerCertificate=true;"

// MySQL
"Server=localhost;Database=TestDB;Uid=root;Pwd=123456;CharSet=utf8mb4;"

// PostgreSQL
"Host=localhost;Port=5432;Database=TestDB;Username=postgres;Password=123456;"

// SQLite
"Data Source=C:\\TestDB.db;Version=3;"

// Oracle
"Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=ORCL)));User Id=system;Password=123456;"

3.2 连接池管理

3.2.1 连接池原理

连接池是一种创建和管理数据库连接缓存的技术,可以避免频繁创建和销毁连接带来的性能开销。

// SqlSugar默认使用ADO.NET的连接池机制
var config = new ConnectionConfig()
{
    ConnectionString = "Server=localhost;Database=TestDB;Uid=root;Pwd=123456;Pooling=true;Min Pool Size=5;Max Pool Size=100;",
    DbType = DbType.MySql,
    IsAutoCloseConnection = true
};

3.2.2 连接池配置

通过连接字符串配置连接池参数:

var connectionString = new StringBuilder()
    .Append("Server=localhost;")
    .Append("Database=TestDB;")
    .Append("Uid=root;")
    .Append("Pwd=123456;")
    .Append("Pooling=true;")              // 启用连接池
    .Append("Min Pool Size=5;")           // 最小连接数
    .Append("Max Pool Size=100;")         // 最大连接数
    .Append("Connection Lifetime=300;")   // 连接生命周期(秒)
    .Append("Connection Timeout=30;")     // 连接超时时间(秒)
    .ToString();

var config = new ConnectionConfig()
{
    ConnectionString = connectionString,
    DbType = DbType.MySql,
    IsAutoCloseConnection = true
};

3.2.3 连接池监控

监控连接池状态:

public class ConnectionPoolMonitor
{
    private readonly SqlSugarClient _db;

    public ConnectionPoolMonitor(SqlSugarClient db)
    {
        _db = db;
    }

    public void MonitorConnection()
    {
        // 监控SQL执行
        _db.Aop.OnLogExecuting = (sql, pars) =>
        {
            Console.WriteLine($"执行时间: {DateTime.Now}");
            Console.WriteLine($"SQL: {sql}");
        };

        // 监控SQL执行完成
        _db.Aop.OnLogExecuted = (sql, pars) =>
        {
            Console.WriteLine($"执行完成: {DateTime.Now}");
        };

        // 监控错误
        _db.Aop.OnError = (exp) =>
        {
            Console.WriteLine($"错误信息: {exp.Message}");
        };
    }
}

3.3 多数据库连接

3.3.1 多库配置

配置多个数据库连接:

var configList = new List<ConnectionConfig>()
{
    new ConnectionConfig()
    {
        ConfigId = "db1",
        ConnectionString = "Server=localhost;Database=DB1;Uid=root;Pwd=123456;",
        DbType = DbType.MySql,
        IsAutoCloseConnection = true
    },
    new ConnectionConfig()
    {
        ConfigId = "db2",
        ConnectionString = "Server=localhost;Database=DB2;Uid=root;Pwd=123456;",
        DbType = DbType.MySql,
        IsAutoCloseConnection = true
    }
};

var db = new SqlSugarScope(configList);

// 使用不同的数据库
var db1 = db.GetConnection("db1");
var db2 = db.GetConnection("db2");

// 在db1中查询
var users1 = db1.Queryable<User>().ToList();

// 在db2中查询
var users2 = db2.Queryable<User>().ToList();

3.3.2 动态切换数据库

根据条件动态切换数据库:

public class DatabaseSelector
{
    private readonly SqlSugarScope _db;

    public DatabaseSelector(SqlSugarScope db)
    {
        _db = db;
    }

    public SqlSugarClient GetDatabase(string tenantId)
    {
        // 根据租户ID选择数据库
        var configId = $"tenant_{tenantId}";
        return _db.GetConnection(configId);
    }

    public List<User> GetUsersByTenant(string tenantId)
    {
        var db = GetDatabase(tenantId);
        return db.Queryable<User>().ToList();
    }
}

3.3.3 主从数据库

配置主从数据库实现读写分离:

var configList = new List<ConnectionConfig>()
{
    new ConnectionConfig()
    {
        ConfigId = "master",
        ConnectionString = "Server=master.db;Database=TestDB;Uid=root;Pwd=123456;",
        DbType = DbType.MySql,
        IsAutoCloseConnection = true
    },
    new ConnectionConfig()
    {
        ConfigId = "slave1",
        ConnectionString = "Server=slave1.db;Database=TestDB;Uid=root;Pwd=123456;",
        DbType = DbType.MySql,
        IsAutoCloseConnection = true
    },
    new ConnectionConfig()
    {
        ConfigId = "slave2",
        ConnectionString = "Server=slave2.db;Database=TestDB;Uid=root;Pwd=123456;",
        DbType = DbType.MySql,
        IsAutoCloseConnection = true
    }
};

var db = new SqlSugarScope(configList);

public class MasterSlaveRepository
{
    private readonly SqlSugarScope _db;
    private readonly Random _random = new Random();

    public MasterSlaveRepository(SqlSugarScope db)
    {
        _db = db;
    }

    // 写操作使用主库
    public bool Insert(User user)
    {
        var master = _db.GetConnection("master");
        return master.Insertable(user).ExecuteCommand() > 0;
    }

    // 读操作使用从库
    public List<User> Query()
    {
        var slaveId = _random.Next(1, 3); // 随机选择从库
        var slave = _db.GetConnection($"slave{slaveId}");
        return slave.Queryable<User>().ToList();
    }
}

3.4 连接字符串构建器

使用构建器模式创建连接字符串:

public class ConnectionStringBuilder
{
    private string _server;
    private string _database;
    private string _userId;
    private string _password;
    private int _port;
    private string _charset = "utf8mb4";
    private bool _pooling = true;
    private int _minPoolSize = 5;
    private int _maxPoolSize = 100;

    public ConnectionStringBuilder WithServer(string server)
    {
        _server = server;
        return this;
    }

    public ConnectionStringBuilder WithDatabase(string database)
    {
        _database = database;
        return this;
    }

    public ConnectionStringBuilder WithCredentials(string userId, string password)
    {
        _userId = userId;
        _password = password;
        return this;
    }

    public ConnectionStringBuilder WithPooling(int minSize = 5, int maxSize = 100)
    {
        _pooling = true;
        _minPoolSize = minSize;
        _maxPoolSize = maxSize;
        return this;
    }

    public string Build()
    {
        return $"Server={_server};Database={_database};Uid={_userId};Pwd={_password};" +
               $"CharSet={_charset};Pooling={_pooling};Min Pool Size={_minPoolSize};" +
               $"Max Pool Size={_maxPoolSize};";
    }
}

// 使用示例
var connectionString = new ConnectionStringBuilder()
    .WithServer("localhost")
    .WithDatabase("TestDB")
    .WithCredentials("root", "123456")
    .WithPooling(10, 200)
    .Build();

3.5 连接生命周期管理

正确管理连接的生命周期:

public class ConnectionLifecycleManager
{
    // 方式1: 使用IsAutoCloseConnection
    public void AutoCloseExample()
    {
        var config = new ConnectionConfig()
        {
            ConnectionString = "...",
            DbType = DbType.MySql,
            IsAutoCloseConnection = true  // 自动关闭
        };
        
        var db = new SqlSugarClient(config);
        var users = db.Queryable<User>().ToList();
        // 连接自动关闭,无需手动处理
    }

    // 方式2: 手动管理连接
    public void ManualCloseExample()
    {
        var config = new ConnectionConfig()
        {
            ConnectionString = "...",
            DbType = DbType.MySql,
            IsAutoCloseConnection = false
        };
        
        var db = new SqlSugarClient(config);
        try
        {
            db.Open(); // 手动打开
            var users = db.Queryable<User>().ToList();
        }
        finally
        {
            db.Close(); // 手动关闭
        }
    }

    // 方式3: 使用SqlSugarScope(推荐用于依赖注入)
    public void ScopeExample()
    {
        var config = new ConnectionConfig()
        {
            ConnectionString = "...",
            DbType = DbType.MySql,
            IsAutoCloseConnection = true
        };
        
        var db = new SqlSugarScope(config);
        // SqlSugarScope会自动管理连接生命周期
        var users = db.Queryable<User>().ToList();
    }
}

3.6 连接安全性

保护数据库连接的安全性:

public class SecureConnectionManager
{
    // 1. 加密连接字符串
    public string EncryptConnectionString(string connectionString)
    {
        // 使用加密算法加密连接字符串
        var bytes = Encoding.UTF8.GetBytes(connectionString);
        var encrypted = Convert.ToBase64String(bytes);
        return encrypted;
    }

    // 2. 从配置文件读取加密的连接字符串
    public SqlSugarClient GetSecureClient()
    {
        var encryptedConnectionString = ConfigurationManager.AppSettings["EncryptedConnection"];
        var connectionString = DecryptConnectionString(encryptedConnectionString);

        var config = new ConnectionConfig()
        {
            ConnectionString = connectionString,
            DbType = DbType.MySql,
            IsAutoCloseConnection = true
        };

        return new SqlSugarClient(config);
    }

    private string DecryptConnectionString(string encrypted)
    {
        var bytes = Convert.FromBase64String(encrypted);
        return Encoding.UTF8.GetString(bytes);
    }

    // 3. 使用SSL连接
    public ConnectionConfig GetSSLConfig()
    {
        return new ConnectionConfig()
        {
            ConnectionString = "Server=localhost;Database=TestDB;Uid=root;Pwd=123456;SslMode=Required;",
            DbType = DbType.MySql,
            IsAutoCloseConnection = true
        };
    }

    // 4. 限制连接权限
    public void ConfigureWithLeastPrivilege()
    {
        // 为不同操作使用不同的数据库用户
        var readOnlyConfig = new ConnectionConfig()
        {
            ConfigId = "readonly",
            ConnectionString = "Server=localhost;Database=TestDB;Uid=reader;Pwd=123456;",
            DbType = DbType.MySql,
            IsAutoCloseConnection = true
        };

        var readWriteConfig = new ConnectionConfig()
        {
            ConfigId = "readwrite",
            ConnectionString = "Server=localhost;Database=TestDB;Uid=writer;Pwd=123456;",
            DbType = DbType.MySql,
            IsAutoCloseConnection = true
        };
    }
}

3.7 连接监控与诊断

监控和诊断数据库连接:

public class ConnectionDiagnostics
{
    private readonly SqlSugarClient _db;

    public ConnectionDiagnostics(SqlSugarClient db)
    {
        _db = db;
        ConfigureAop();
    }

    private void ConfigureAop()
    {
        // SQL执行前
        _db.Aop.OnLogExecuting = (sql, pars) =>
        {
            Console.WriteLine($"【执行SQL】{DateTime.Now}");
            Console.WriteLine($"SQL: {sql}");
            Console.WriteLine($"参数: {GetParams(pars)}");
        };

        // SQL执行后
        _db.Aop.OnLogExecuted = (sql, pars) =>
        {
            Console.WriteLine($"【执行完成】{DateTime.Now}");
        };

        // 执行错误
        _db.Aop.OnError = (exp) =>
        {
            Console.WriteLine($"【执行错误】{exp.Message}");
            Console.WriteLine($"堆栈: {exp.StackTrace}");
        };

        // 差异日志(用于数据审计)
        _db.Aop.OnDiffLogEvent = (diffLog) =>
        {
            Console.WriteLine($"【数据变更】表:{diffLog.TableName}");
            Console.WriteLine($"操作类型: {diffLog.DiffType}");
            Console.WriteLine($"变更前: {diffLog.BeforeData}");
            Console.WriteLine($"变更后: {diffLog.AfterData}");
        };
    }

    private string GetParams(SugarParameter[] pars)
    {
        if (pars == null || pars.Length == 0) return "无参数";
        return string.Join(", ", pars.Select(p => $"{p.ParameterName}={p.Value}"));
    }

    // 性能监控
    public void MonitorPerformance()
    {
        var stopwatch = new Stopwatch();
        
        _db.Aop.OnLogExecuting = (sql, pars) =>
        {
            stopwatch.Restart();
        };

        _db.Aop.OnLogExecuted = (sql, pars) =>
        {
            stopwatch.Stop();
            if (stopwatch.ElapsedMilliseconds > 1000) // 慢查询
            {
                Console.WriteLine($"【慢查询警告】耗时:{stopwatch.ElapsedMilliseconds}ms");
                Console.WriteLine($"SQL: {sql}");
            }
        };
    }

    // 连接测试
    public bool TestConnection()
    {
        try
        {
            return _db.Ado.GetInt("SELECT 1") == 1;
        }
        catch (Exception ex)
        {
            Console.WriteLine($"连接测试失败: {ex.Message}");
            return false;
        }
    }
}

3.8 最佳实践

连接配置最佳实践

public class ConnectionBestPractices
{
    // 1. 使用SqlSugarScope进行依赖注入
    public static void ConfigureServices(IServiceCollection services)
    {
        services.AddScoped<ISqlSugarClient>(provider =>
        {
            var config = new ConnectionConfig()
            {
                ConnectionString = "...",
                DbType = DbType.MySql,
                IsAutoCloseConnection = true,
                InitKeyType = InitKeyType.Attribute
            };

            var db = new SqlSugarScope(config);
            
            // 配置AOP
            db.Aop.OnLogExecuting = (sql, pars) =>
            {
                // 记录SQL日志
            };

            return db;
        });
    }

    // 2. 环境分离配置
    public static ConnectionConfig GetConfigByEnvironment(string env)
    {
        return env switch
        {
            "Development" => new ConnectionConfig()
            {
                ConnectionString = "Server=localhost;Database=TestDB_Dev;...",
                DbType = DbType.MySql,
                IsAutoCloseConnection = true
            },
            "Production" => new ConnectionConfig()
            {
                ConnectionString = "Server=prod.server;Database=TestDB;...",
                DbType = DbType.MySql,
                IsAutoCloseConnection = true
            },
            _ => throw new ArgumentException("未知环境")
        };
    }

    // 3. 连接重试机制
    public static SqlSugarClient CreateClientWithRetry(ConnectionConfig config, int maxRetries = 3)
    {
        for (int i = 0; i < maxRetries; i++)
        {
            try
            {
                var db = new SqlSugarClient(config);
                if (db.Ado.GetInt("SELECT 1") == 1)
                {
                    return db;
                }
            }
            catch (Exception ex)
            {
                if (i == maxRetries - 1) throw;
                Thread.Sleep(1000 * (i + 1)); // 递增等待时间
            }
        }
        throw new Exception("无法创建数据库连接");
    }
}

常见陷阱

忘记设置IsAutoCloseConnection: 可能导致连接泄漏
硬编码连接字符串: 安全风险,应使用配置文件
不配置连接池: 性能不佳
混用SqlSugarClient和SqlSugarScope: 可能导致连接管理混乱
忽略异常处理: 连接失败时没有适当的错误处理

本章小结

本章详细介绍了SqlSugar的数据库连接与配置,包括:

ConnectionConfig配置: 掌握了各种配置项的使用方法
连接池管理: 学习了如何优化连接池以提升性能
多数据库连接: 了解了多库配置和主从分离的实现
连接安全: 掌握了保护数据库连接的各种方法
监控诊断: 学会了如何监控和诊断连接问题
最佳实践: 总结了连接管理的最佳实践和常见陷阱

通过本章的学习,你应该能够熟练配置和管理SqlSugar的数据库连接,为后续的开发工作打下坚实基础。

下一章: 第04章-实体类与CodeFirst

此内容由惯性聚合(RSS阅读器)自动聚合整理，仅供阅读参考。原文来自 — 版权归原作者所有。

推荐订阅源

博客园 - 我才是银古

第三章：数据库连接与配置

目录

3.1 ConnectionConfig详解

3.1.1 基本配置项

3.1.2 数据库类型

3.1.3 连接字符串

3.2 连接池管理

3.2.1 连接池原理

3.2.2 连接池配置

3.2.3 连接池监控

3.3 多数据库连接

3.3.1 多库配置

3.3.2 动态切换数据库

3.3.3 主从数据库

3.4 连接字符串构建器

3.5 连接生命周期管理

3.6 连接安全性

3.7 连接监控与诊断

3.8 最佳实践

连接配置最佳实践

常见陷阱

本章小结