第十三章：多数据库支持

13.1 支持的数据库

13.1.1 数据库类型概览

SqlSugar支持以下数据库：

public enum DbType
{
    MySql = 0,          // MySQL 5.x/8.x
    SqlServer = 1,      // SQL Server 2008+
    Sqlite = 2,         // SQLite
    Oracle = 3,         // Oracle
    PostgreSQL = 4,     // PostgreSQL
    Dm = 5,             // 达梦数据库
    Kdbndp = 6,         // 人大金仓 KingbaseES
    Oscar = 7,          // 南大通用 Oscar
    MySqlConnector = 8, // MySQL Connector
    Access = 9,         // Microsoft Access
    OpenGauss = 10,     // 华为 OpenGauss
    QuestDB = 11,       // QuestDB
    HG = 12,            // 瀚高数据库
    ClickHouse = 13,    // ClickHouse
    GBase = 14,         // 南大通用 GBase
    Odbc = 15,          // ODBC
    Custom = 900        // 自定义
}

13.1.2 连接字符串配置

各数据库的连接字符串示例：

public class ConnectionStringExamples
{
    // MySQL
    public static string MySQL = 
        "Server=localhost;Port=3306;Database=testdb;Uid=root;Pwd=123456;";
    
    // SQL Server
    public static string SqlServer = 
        "Server=.;Database=testdb;User ID=sa;Password=123456;";
    
    // PostgreSQL
    public static string PostgreSQL = 
        "Host=localhost;Port=5432;Database=testdb;Username=postgres;Password=123456;";
    
    // Oracle
    public static string Oracle = 
        "Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521))" +
        "(CONNECT_DATA=(SERVICE_NAME=ORCL)));User Id=system;Password=123456;";
    
    // SQLite
    public static string SQLite = 
        "Data Source=./database.db;";
    
    // 达梦
    public static string Dm = 
        "Server=localhost;Port=5236;Database=testdb;User ID=SYSDBA;PWD=SYSDBA;";
    
    // 人大金仓
    public static string Kdbndp = 
        "Server=localhost;Port=54321;Database=testdb;User ID=system;Password=123456;";
    
    // OpenGauss
    public static string OpenGauss = 
        "Host=localhost;Port=5432;Database=testdb;Username=gaussdb;Password=123456;";
}

13.2 MySQL数据库

MySQL配置与使用

public class MySQLExample
{
    public static SqlSugarClient GetMySqlClient()
    {
        return new SqlSugarClient(new ConnectionConfig()
        {
            ConnectionString = "Server=localhost;Database=testdb;Uid=root;Pwd=123456;",
            DbType = DbType.MySql,
            IsAutoCloseConnection = true,
            InitKeyType = InitKeyType.Attribute
        });
    }
    
    // MySQL特有功能
    public static void MySqlSpecificFeatures()
    {
        var db = GetMySqlClient();
        
        // 使用MySQL函数
        var students = db.Queryable<Student>()
            .Where(s => SqlFunc.MappingColumn(default(DateTime), "DATE(create_time)") == DateTime.Today)
            .ToList();
        
        // 批量插入（MySQL优化）
        var list = new List<Student>();
        for (int i = 0; i < 10000; i++)
        {
            list.Add(new Student { Name = $"Student{i}" });
        }
        
        // MySQL使用批量插入优化
        db.Fastest<Student>().BulkCopy(list);
        
        // 使用ON DUPLICATE KEY UPDATE
        db.Insertable(new Student { Id = 1, Name = "Test" })
            .ExecuteCommand();
        
        // 查询执行计划
        db.Aop.OnLogExecuting = (sql, pars) =>
        {
            var explainSql = $"EXPLAIN {sql}";
            var dt = db.Ado.GetDataTable(explainSql, pars);
            // 分析执行计划
        };
    }
    
    // MySQL存储引擎选择
    public static void CreateTableWithEngine()
    {
        var db = GetMySqlClient();
        
        // 创建InnoDB表（默认）
        db.CodeFirst.InitTables<Student>();
        
        // 创建MyISAM表
        db.Ado.ExecuteCommand(@"
            CREATE TABLE student_myisam (
                id INT PRIMARY KEY AUTO_INCREMENT,
                name VARCHAR(50)
            ) ENGINE=MyISAM;
        ");
    }
    
    // MySQL全文索引
    public static void FullTextSearch()
    {
        var db = GetMySqlClient();
        
        // 创建全文索引
        db.Ado.ExecuteCommand(@"
            ALTER TABLE article 
            ADD FULLTEXT INDEX ft_content (content);
        ");
        
        // 全文搜索
        var sql = @"
            SELECT * FROM article 
            WHERE MATCH(content) AGAINST(? IN NATURAL LANGUAGE MODE)";
        
        var result = db.Ado.SqlQuery<Article>(sql, "搜索关键词");
    }
}

// MySQL特有特性
[SugarTable("student")]
public class Student
{
    [SugarColumn(IsPrimaryKey = true, IsIdentity = true)]
    public int Id { get; set; }
    
    [SugarColumn(Length = 50, ColumnDataType = "VARCHAR", IsNullable = false)]
    public string Name { get; set; }
    
    // MySQL的ENUM类型
    [SugarColumn(ColumnDataType = "ENUM('male','female')")]
    public string Gender { get; set; }
    
    // MySQL的JSON类型
    [SugarColumn(ColumnDataType = "JSON")]
    public string ExtData { get; set; }
}

13.3 SQL Server数据库

SQL Server配置与使用

public class SqlServerExample
{
    public static SqlSugarClient GetSqlServerClient()
    {
        return new SqlSugarClient(new ConnectionConfig()
        {
            ConnectionString = "Server=.;Database=testdb;User ID=sa;Password=123456;",
            DbType = DbType.SqlServer,
            IsAutoCloseConnection = true,
            MoreSettings = new ConnMoreSettings
            {
                // SQL Server特定设置
                IsAutoRemoveDataCache = true,
                SqlServerCodeFirstNvarchar = true  // 默认使用NVARCHAR
            }
        });
    }
    
    // SQL Server特有功能
    public static void SqlServerSpecificFeatures()
    {
        var db = GetSqlServerClient();
        
        // 使用NOLOCK提示
        var list = db.Queryable<Student>()
            .With(SqlWith.NoLock)
            .ToList();
        
        // 使用表变量
        var sql = @"
            DECLARE @TempTable TABLE (
                Id INT,
                Name NVARCHAR(50)
            );
            
            INSERT INTO @TempTable SELECT Id, Name FROM Student WHERE Age > 18;
            SELECT * FROM @TempTable;
        ";
        var result = db.Ado.SqlQuery<Student>(sql);
        
        // 使用CTE（公共表表达式）
        var cte = db.Queryable<Student>()
            .Select(s => new { s.Id, s.Name, s.ClassId });
        
        var finalResult = db.Queryable<Class>()
            .InnerJoin(cte, (c, s) => c.Id == s.ClassId)
            .Select((c, s) => new { c.ClassName, s.Name })
            .ToList();
        
        // 分页优化（使用OFFSET FETCH）
        var pageData = db.Queryable<Student>()
            .OrderBy(s => s.Id)
            .Skip(10)
            .Take(20)
            .ToList();
    }
    
    // SQL Server特有数据类型
    public static void SqlServerDataTypes()
    {
        var db = GetSqlServerClient();
        
        // 使用GUID
        var entity = new StudentWithGuid
        {
            Id = Guid.NewGuid(),
            Name = "Test"
        };
        db.Insertable(entity).ExecuteCommand();
        
        // 使用HierarchyId
        var sql = "SELECT * FROM Department WHERE OrgNode.IsDescendantOf('/1/') = 1";
        var result = db.Ado.SqlQuery<Department>(sql);
    }
    
    // 全文搜索
    public static void FullTextSearch()
    {
        var db = GetSqlServerClient();
        
        // 创建全文索引
        db.Ado.ExecuteCommand(@"
            CREATE FULLTEXT CATALOG ftCatalog AS DEFAULT;
            CREATE FULLTEXT INDEX ON Article(Content)
            KEY INDEX PK_Article;
        ");
        
        // 全文搜索
        var sql = @"
            SELECT * FROM Article 
            WHERE CONTAINS(Content, '搜索关键词')";
        var result = db.Ado.SqlQuery<Article>(sql);
    }
}

// SQL Server特有实体
[SugarTable("student")]
public class StudentWithGuid
{
    [SugarColumn(IsPrimaryKey = true)]
    public Guid Id { get; set; }
    
    [SugarColumn(ColumnDataType = "NVARCHAR(50)")]
    public string Name { get; set; }
    
    // TIMESTAMP类型（行版本）
    [SugarColumn(ColumnDataType = "TIMESTAMP", IsEnableUpdateVersionValidation = true)]
    public byte[] RowVersion { get; set; }
}

13.4 PostgreSQL数据库

PostgreSQL配置与使用

public class PostgreSQLExample
{
    public static SqlSugarClient GetPostgreSqlClient()
    {
        return new SqlSugarClient(new ConnectionConfig()
        {
            ConnectionString = "Host=localhost;Database=testdb;Username=postgres;Password=123456;",
            DbType = DbType.PostgreSQL,
            IsAutoCloseConnection = true
        });
    }
    
    // PostgreSQL特有功能
    public static void PostgreSqlSpecificFeatures()
    {
        var db = GetPostgreSqlClient();
        
        // 使用数组类型
        var sql = "SELECT * FROM student WHERE tags @> ARRAY['tag1']";
        var result = db.Ado.SqlQuery<Student>(sql);
        
        // 使用JSONB类型
        var students = db.Queryable<Student>()
            .Where("ext_data->>'city' = @city", new { city = "北京" })
            .ToList();
        
        // 使用序列
        var nextVal = db.Ado.GetInt("SELECT nextval('student_id_seq')");
        
        // 使用RETURNING子句
        var insertSql = @"
            INSERT INTO student (name, age) 
            VALUES (@name, @age) 
            RETURNING id";
        var newId = db.Ado.GetInt(insertSql, new { name = "Test", age = 18 });
    }
    
    // PostgreSQL全文搜索
    public static void FullTextSearch()
    {
        var db = GetPostgreSqlClient();
        
        // 创建GIN索引
        db.Ado.ExecuteCommand(@"
            CREATE INDEX idx_article_content 
            ON article 
            USING GIN (to_tsvector('chinese', content));
        ");
        
        // 全文搜索
        var sql = @"
            SELECT * FROM article 
            WHERE to_tsvector('chinese', content) @@ to_tsquery('chinese', @keyword)";
        var result = db.Ado.SqlQuery<Article>(sql, new { keyword = "搜索" });
    }
    
    // 使用PostgreSQL特有数据类型
    public static void PostgreSqlDataTypes()
    {
        var db = GetPostgreSqlClient();
        
        // 创建枚举类型
        db.Ado.ExecuteCommand(@"
            CREATE TYPE gender AS ENUM ('male', 'female', 'other');
        ");
        
        // 使用UUID
        var entity = new StudentPg
        {
            Id = Guid.NewGuid(),
            Name = "Test",
            Tags = new[] { "tag1", "tag2" }
        };
        db.Insertable(entity).ExecuteCommand();
    }
}

// PostgreSQL特有实体
[SugarTable("student")]
public class StudentPg
{
    [SugarColumn(IsPrimaryKey = true, ColumnDataType = "UUID")]
    public Guid Id { get; set; }
    
    public string Name { get; set; }
    
    // 数组类型
    [SugarColumn(ColumnDataType = "TEXT[]")]
    public string[] Tags { get; set; }
    
    // JSONB类型
    [SugarColumn(ColumnDataType = "JSONB")]
    public string ExtData { get; set; }
    
    // 范围类型
    [SugarColumn(ColumnDataType = "INT4RANGE")]
    public string AgeRange { get; set; }
}

13.5 Oracle数据库

Oracle配置与使用

public class OracleExample
{
    public static SqlSugarClient GetOracleClient()
    {
        return new SqlSugarClient(new ConnectionConfig()
        {
            ConnectionString = "Data Source=ORCL;User Id=system;Password=123456;",
            DbType = DbType.Oracle,
            IsAutoCloseConnection = true,
            MoreSettings = new ConnMoreSettings
            {
                // Oracle使用大写表名和列名
                IsAutoToUpper = true
            }
        });
    }
    
    // Oracle特有功能
    public static void OracleSpecificFeatures()
    {
        var db = GetOracleClient();
        
        // 使用序列
        var sql = "SELECT student_seq.NEXTVAL FROM DUAL";
        var nextId = db.Ado.GetInt(sql);
        
        // 使用分页（ROWNUM）
        var pageData = db.Queryable<Student>()
            .OrderBy(s => s.Id)
            .Skip(10)
            .Take(20)
            .ToList();
        
        // 使用MERGE语句
        var mergeSql = @"
            MERGE INTO student t
            USING (SELECT :id AS id, :name AS name FROM DUAL) s
            ON (t.id = s.id)
            WHEN MATCHED THEN
                UPDATE SET t.name = s.name
            WHEN NOT MATCHED THEN
                INSERT (id, name) VALUES (s.id, s.name)";
        
        db.Ado.ExecuteCommand(mergeSql, new { id = 1, name = "Test" });
        
        // 使用分区表
        db.Ado.ExecuteCommand(@"
            CREATE TABLE student (
                id NUMBER PRIMARY KEY,
                name VARCHAR2(50),
                create_date DATE
            )
            PARTITION BY RANGE (create_date) (
                PARTITION p2023 VALUES LESS THAN (TO_DATE('2024-01-01', 'YYYY-MM-DD')),
                PARTITION p2024 VALUES LESS THAN (TO_DATE('2025-01-01', 'YYYY-MM-DD'))
            );
        ");
    }
    
    // Oracle存储过程调用
    public static void CallStoredProcedure()
    {
        var db = GetOracleClient();
        
        // 调用存储过程
        var parameters = new[]
        {
            new SugarParameter("@p_id", 1),
            new SugarParameter("@p_name", "Test"),
            new SugarParameter("@p_result", null, true) // 输出参数
        };
        
        db.Ado.UseStoredProcedure().ExecuteCommand("sp_student_insert", parameters);
        
        var result = parameters[2].Value;
        Console.WriteLine($"存储过程返回: {result}");
    }
}

// Oracle特有实体
[SugarTable("STUDENT")]
public class StudentOracle
{
    [SugarColumn(IsPrimaryKey = true, OracleSequenceName = "STUDENT_SEQ")]
    public int Id { get; set; }
    
    [SugarColumn(ColumnDataType = "VARCHAR2(50)")]
    public string Name { get; set; }
    
    [SugarColumn(ColumnDataType = "NUMBER(10,2)")]
    public decimal Score { get; set; }
    
    // CLOB类型
    [SugarColumn(ColumnDataType = "CLOB")]
    public string Description { get; set; }
}

13.6 SQLite数据库

SQLite配置与使用

public class SQLiteExample
{
    public static SqlSugarClient GetSQLiteClient()
    {
        return new SqlSugarClient(new ConnectionConfig()
        {
            ConnectionString = "Data Source=./test.db;",
            DbType = DbType.Sqlite,
            IsAutoCloseConnection = true,
            InitKeyType = InitKeyType.Attribute
        });
    }
    
    // SQLite特有功能
    public static void SQLiteSpecificFeatures()
    {
        var db = GetSQLiteClient();
        
        // 创建内存数据库
        var memoryDb = new SqlSugarClient(new ConnectionConfig()
        {
            ConnectionString = "Data Source=:memory:",
            DbType = DbType.Sqlite,
            IsAutoCloseConnection = false  // 内存数据库不能自动关闭连接
        });
        
        // 创建表
        memoryDb.CodeFirst.InitTables<Student>();
        
        // 使用事务（SQLite是文件数据库，需要注意并发）
        db.Ado.BeginTran();
        try
        {
            db.Insertable(new Student { Name = "Test" }).ExecuteCommand();
            db.Ado.CommitTran();
        }
        catch
        {
            db.Ado.RollbackTran();
        }
        
        // 启用WAL模式（提高并发性能）
        db.Ado.ExecuteCommand("PRAGMA journal_mode=WAL;");
        
        // 查询SQLite版本
        var version = db.Ado.GetString("SELECT sqlite_version()");
        Console.WriteLine($"SQLite版本: {version}");
    }
    
    // SQLite全文搜索
    public static void FullTextSearch()
    {
        var db = GetSQLiteClient();
        
        // 创建FTS5虚拟表
        db.Ado.ExecuteCommand(@"
            CREATE VIRTUAL TABLE article_fts 
            USING fts5(title, content);
        ");
        
        // 插入数据
        db.Ado.ExecuteCommand(@"
            INSERT INTO article_fts (title, content)
            SELECT title, content FROM article;
        ");
        
        // 全文搜索
        var sql = "SELECT * FROM article_fts WHERE article_fts MATCH '搜索关键词'";
        var result = db.Ado.SqlQuery<Article>(sql);
    }
    
    // SQLite附加数据库
    public static void AttachDatabase()
    {
        var db = GetSQLiteClient();
        
        // 附加另一个数据库
        db.Ado.ExecuteCommand("ATTACH DATABASE './other.db' AS other_db");
        
        // 跨数据库查询
        var sql = "SELECT * FROM main.student UNION SELECT * FROM other_db.student";
        var result = db.Ado.SqlQuery<Student>(sql);
        
        // 分离数据库
        db.Ado.ExecuteCommand("DETACH DATABASE other_db");
    }
}

13.7 国产数据库

13.7.1 达梦数据库

public class DmExample
{
    public static SqlSugarClient GetDmClient()
    {
        return new SqlSugarClient(new ConnectionConfig()
        {
            ConnectionString = "Server=localhost;Port=5236;Database=testdb;User ID=SYSDBA;PWD=SYSDBA;",
            DbType = DbType.Dm,
            IsAutoCloseConnection = true
        });
    }
    
    // 达梦数据库使用示例
    public static void DmSpecificFeatures()
    {
        var db = GetDmClient();
        
        // 基本CRUD操作
        var student = new Student
        {
            Name = "张三",
            Age = 18,
            Email = "zhangsan@example.com"
        };
        
        // 插入
        var id = db.Insertable(student).ExecuteReturnIdentity();
        
        // 查询
        var list = db.Queryable<Student>()
            .Where(s => s.Age > 18)
            .ToList();
        
        // 更新
        db.Updateable(student).ExecuteCommand();
        
        // 删除
        db.Deleteable<Student>().Where(s => s.Id == id).ExecuteCommand();
        
        // 使用序列
        var nextId = db.Ado.GetInt("SELECT SEQ_STUDENT.NEXTVAL FROM DUAL");
    }
    
    // 达梦数据库分页
    public static void DmPagination()
    {
        var db = GetDmClient();
        
        int totalCount = 0;
        var pageData = db.Queryable<Student>()
            .OrderBy(s => s.Id)
            .ToPageList(1, 20, ref totalCount);
        
        Console.WriteLine($"总记录数: {totalCount}");
    }
}

13.7.2 人大金仓

public class KingbaseExample
{
    public static SqlSugarClient GetKingbaseClient()
    {
        return new SqlSugarClient(new ConnectionConfig()
        {
            ConnectionString = "Server=localhost;Port=54321;Database=testdb;User ID=system;Password=123456;",
            DbType = DbType.Kdbndp,
            IsAutoCloseConnection = true
        });
    }
    
    // 人大金仓使用示例
    public static void KingbaseSpecificFeatures()
    {
        var db = GetKingbaseClient();
        
        // 人大金仓兼容PostgreSQL的大部分特性
        // 使用序列
        var nextId = db.Ado.GetInt("SELECT nextval('student_id_seq')");
        
        // 批量插入
        var students = new List<Student>();
        for (int i = 0; i < 1000; i++)
        {
            students.Add(new Student { Name = $"Student{i}", Age = 18 });
        }
        db.Insertable(students).ExecuteCommand();
        
        // 使用COPY命令（高性能批量导入）
        var copyCommand = @"
            COPY student (name, age, email) 
            FROM STDIN WITH (FORMAT CSV)";
        // 执行COPY命令需要使用特定的API
    }
    
    // 金仓数据库特有功能
    public static void KingbaseAdvancedFeatures()
    {
        var db = GetKingbaseClient();
        
        // 使用分区表
        db.Ado.ExecuteCommand(@"
            CREATE TABLE student (
                id SERIAL PRIMARY KEY,
                name VARCHAR(50),
                create_date DATE
            ) PARTITION BY RANGE (create_date);
            
            CREATE TABLE student_2023 PARTITION OF student
            FOR VALUES FROM ('2023-01-01') TO ('2024-01-01');
            
            CREATE TABLE student_2024 PARTITION OF student
            FOR VALUES FROM ('2024-01-01') TO ('2025-01-01');
        ");
    }
}

13.7.3 南大通用

public class GBaseExample
{
    public static SqlSugarClient GetGBaseClient()
    {
        return new SqlSugarClient(new ConnectionConfig()
        {
            ConnectionString = "Host=localhost;Port=5432;Database=testdb;Username=gbase;Password=123456;",
            DbType = DbType.GBase,
            IsAutoCloseConnection = true
        });
    }
    
    // 南大通用数据库使用
    public static void GBaseSpecificFeatures()
    {
        var db = GetGBaseClient();
        
        // 基本操作与PostgreSQL类似
        var students = db.Queryable<Student>()
            .Where(s => s.Age > 18)
            .ToList();
        
        // 批量操作
        var list = new List<Student>();
        for (int i = 0; i < 5000; i++)
        {
            list.Add(new Student { Name = $"Student{i}" });
        }
        db.Insertable(list).PageSize(1000).ExecuteCommand();
    }
}

13.8 数据库切换

运行时切换数据库

public class DatabaseSwitcher
{
    // 配置多数据库
    public static SqlSugarScope CreateMultiDatabase()
    {
        var configs = new List<ConnectionConfig>
        {
            new ConnectionConfig
            {
                ConfigId = "MySQL",
                ConnectionString = "Server=localhost;Database=testdb;Uid=root;Pwd=123456;",
                DbType = DbType.MySql,
                IsAutoCloseConnection = true
            },
            new ConnectionConfig
            {
                ConfigId = "SqlServer",
                ConnectionString = "Server=.;Database=testdb;User ID=sa;Password=123456;",
                DbType = DbType.SqlServer,
                IsAutoCloseConnection = true
            },
            new ConnectionConfig
            {
                ConfigId = "PostgreSQL",
                ConnectionString = "Host=localhost;Database=testdb;Username=postgres;Password=123456;",
                DbType = DbType.PostgreSQL,
                IsAutoCloseConnection = true
            }
        };
        
        return new SqlSugarScope(configs);
    }
    
    // 动态切换数据库
    public static void SwitchDatabase()
    {
        var scope = CreateMultiDatabase();
        
        // 使用MySQL
        var mysqlDb = scope.GetConnection("MySQL");
        var mysqlData = mysqlDb.Queryable<Student>().ToList();
        
        // 使用SQL Server
        var sqlServerDb = scope.GetConnection("SqlServer");
        var sqlServerData = sqlServerDb.Queryable<Student>().ToList();
        
        // 使用PostgreSQL
        var pgDb = scope.GetConnection("PostgreSQL");
        var pgData = pgDb.Queryable<Student>().ToList();
    }
    
    // 根据配置切换
    public static SqlSugarClient CreateByConfig(string dbType, string connectionString)
    {
        var config = new ConnectionConfig
        {
            ConnectionString = connectionString,
            DbType = dbType.ToLower() switch
            {
                "mysql" => DbType.MySql,
                "sqlserver" => DbType.SqlServer,
                "postgresql" => DbType.PostgreSQL,
                "oracle" => DbType.Oracle,
                "sqlite" => DbType.Sqlite,
                "dm" => DbType.Dm,
                _ => throw new NotSupportedException($"不支持的数据库类型: {dbType}")
            },
            IsAutoCloseConnection = true
        };
        
        return new SqlSugarClient(config);
    }
}

13.9 数据库迁移

跨数据库数据迁移

public class DatabaseMigration
{
    // 从MySQL迁移到SQL Server
    public static void MigrateFromMySqlToSqlServer()
    {
        // 源数据库（MySQL）
        var sourceDb = new SqlSugarClient(new ConnectionConfig
        {
            ConnectionString = "Server=localhost;Database=source;Uid=root;Pwd=123456;",
            DbType = DbType.MySql,
            IsAutoCloseConnection = true
        });
        
        // 目标数据库（SQL Server）
        var targetDb = new SqlSugarClient(new ConnectionConfig
        {
            ConnectionString = "Server=.;Database=target;User ID=sa;Password=123456;",
            DbType = DbType.SqlServer,
            IsAutoCloseConnection = true
        });
        
        // 创建目标表
        targetDb.CodeFirst.InitTables<Student>();
        
        // 分批迁移数据
        int pageSize = 10000;
        int pageIndex = 1;
        int totalCount = 0;
        
        do
        {
            var data = sourceDb.Queryable<Student>()
                .ToPageList(pageIndex, pageSize, ref totalCount);
            
            if (data.Count > 0)
            {
                targetDb.Insertable(data).ExecuteCommand();
                Console.WriteLine($"已迁移 {pageIndex * pageSize} / {totalCount} 条记录");
            }
            
            pageIndex++;
        } while ((pageIndex - 1) * pageSize < totalCount);
        
        Console.WriteLine("数据迁移完成");
    }
    
    // 通用迁移工具
    public static void MigrateTables<T>(
        SqlSugarClient sourceDb,
        SqlSugarClient targetDb,
        int batchSize = 5000) where T : class, new()
    {
        // 创建目标表
        targetDb.CodeFirst.InitTables<T>();
        
        // 清空目标表
        targetDb.Deleteable<T>().Where("1=1").ExecuteCommand();
        
        // 分批迁移
        int pageIndex = 1;
        int totalCount = 0;
        
        do
        {
            var data = sourceDb.Queryable<T>()
                .ToPageList(pageIndex, batchSize, ref totalCount);
            
            if (data.Count > 0)
            {
                targetDb.Insertable(data).ExecuteCommand();
            }
            
            pageIndex++;
        } while ((pageIndex - 1) * batchSize < totalCount);
    }
    
    // 迁移整个数据库
    public static void MigrateDatabase(
        SqlSugarClient sourceDb,
        SqlSugarClient targetDb)
    {
        // 获取所有表
        var tables = sourceDb.DbMaintenance.GetTableInfoList();
        
        foreach (var table in tables)
        {
            Console.WriteLine($"正在迁移表: {table.Name}");
            
            // 获取表结构
            var columns = sourceDb.DbMaintenance.GetColumnInfosByTableName(table.Name, false);
            
            // 在目标数据库创建表
            // ... 根据columns创建表结构
            
            // 迁移数据
            var sql = $"SELECT * FROM {table.Name}";
            var dt = sourceDb.Ado.GetDataTable(sql);
            
            targetDb.Ado.ExecuteCommand($"DELETE FROM {table.Name}");
            // 批量插入数据
            foreach (DataRow row in dt.Rows)
            {
                // 插入数据
            }
            
            Console.WriteLine($"表 {table.Name} 迁移完成，共 {dt.Rows.Count} 条记录");
        }
    }
}

13.10 兼容性考虑

编写跨数据库兼容代码

public class DatabaseCompatibility
{
    // 获取数据库类型特定的SQL
    public static string GetCurrentDateSql(DbType dbType)
    {
        return dbType switch
        {
            DbType.MySql => "NOW()",
            DbType.SqlServer => "GETDATE()",
            DbType.PostgreSQL => "NOW()",
            DbType.Oracle => "SYSDATE",
            DbType.Sqlite => "datetime('now')",
            DbType.Dm => "SYSDATE",
            _ => "GETDATE()"
        };
    }
    
    // 跨数据库查询示例
    public static List<Student> GetStudentsByDate(SqlSugarClient db)
    {
        var dbType = db.CurrentConnectionConfig.DbType;
        var dateSql = GetCurrentDateSql(dbType);
        
        // 使用SqlFunc确保跨数据库兼容
        return db.Queryable<Student>()
            .Where(s => s.CreateTime >= DateTime.Today)
            .ToList();
    }
    
    // 处理数据类型差异
    public static void HandleDataTypeDifferences()
    {
        // 使用ColumnDataType指定特定数据库的类型
        var config = new ConnectionConfig
        {
            DbType = DbType.MySql,
            InitKeyType = InitKeyType.Attribute
        };
        
        var db = new SqlSugarClient(config);
        
        // 动态适配数据类型
        db.CodeFirst.SetStringDefaultLength(200);
        
        if (db.CurrentConnectionConfig.DbType == DbType.SqlServer)
        {
            db.CurrentConnectionConfig.MoreSettings = new ConnMoreSettings
            {
                SqlServerCodeFirstNvarchar = true
            };
        }
    }
    
    // 兼容性最佳实践
    public static class CompatibilityBestPractices
    {
        // 1. 使用SqlFunc而不是原生SQL函数
        public static void UseSqlFunc(SqlSugarClient db)
        {
            // ❌ 不兼容
            // var sql = "SELECT * FROM student WHERE DATE(create_time) = CURDATE()";
            
            // ✅ 兼容
            var list = db.Queryable<Student>()
                .Where(s => SqlFunc.DateIsSame(s.CreateTime, DateTime.Today))
                .ToList();
        }
        
        // 2. 避免使用数据库特定功能
        public static void AvoidDbSpecificFeatures(SqlSugarClient db)
        {
            // 使用标准SQL功能
            var list = db.Queryable<Student>()
                .Where(s => s.Name.Contains("test"))
                .ToList();
        }
        
        // 3. 使用条件编译处理差异
        public static void ConditionalCode(SqlSugarClient db)
        {
            var dbType = db.CurrentConnectionConfig.DbType;
            
            if (dbType == DbType.MySql)
            {
                // MySQL特定代码
                db.Ado.ExecuteCommand("SET NAMES utf8mb4");
            }
            else if (dbType == DbType.SqlServer)
            {
                // SQL Server特定代码
                db.Ado.ExecuteCommand("SET ANSI_NULLS ON");
            }
        }
    }
}

// 跨数据库实体定义
public class CrossDatabaseEntity
{
    [SugarColumn(IsPrimaryKey = true, IsIdentity = true)]
    public int Id { get; set; }
    
    // 使用通用的类型
    [SugarColumn(Length = 50)]
    public string Name { get; set; }
    
    // 根据数据库类型自动适配
    public DateTime CreateTime { get; set; }
    
    // 使用Decimal确保精度一致
    [SugarColumn(DecimalDigits = 2)]
    public decimal Amount { get; set; }
}

13.11 本章小结

本章详细介绍了SqlSugar的多数据库支持功能：

数据库支持：了解了SqlSugar支持的所有数据库类型及其连接配置
主流数据库：掌握了MySQL、SQL Server、PostgreSQL、Oracle、SQLite的使用方法和特有功能
国产数据库：学会了达梦、人大金仓、南大通用等国产数据库的集成使用
数据库切换：实现了运行时动态切换不同类型的数据库
数据迁移：掌握了跨数据库数据迁移的方法和最佳实践
兼容性：学会了编写跨数据库兼容的代码，处理不同数据库的差异

通过本章的学习，您应该能够在项目中灵活使用不同类型的数据库，并能够处理数据库迁移和兼容性问题。在下一章中，我们将学习仓储模式与依赖注入。

znlgis 博客

第十三章：多数据库支持

目录

13.1 支持的数据库

13.1.1 数据库类型概览

13.1.2 连接字符串配置

13.2 MySQL数据库

MySQL配置与使用

13.3 SQL Server数据库

SQL Server配置与使用

13.4 PostgreSQL数据库

PostgreSQL配置与使用

13.5 Oracle数据库

Oracle配置与使用

13.6 SQLite数据库

SQLite配置与使用

13.7 国产数据库

13.7.1 达梦数据库

13.7.2 人大金仓

13.7.3 南大通用

13.8 数据库切换

运行时切换数据库

13.9 数据库迁移

跨数据库数据迁移

13.10 兼容性考虑

编写跨数据库兼容代码

13.11 本章小结