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

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的数据库连接,为后续的开发工作打下坚实基础。

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

znlgis 博客